SQL Server BI Step by Step SSRS 1
½ñÌìÎÒÃÇ¿ªÊ¼SQL SERVER BIµÄÁíÍâÒ»¸öÖØÒªµÄ²¿·Ö --Reporting Service£¬Ïà¶ÔÓÚIntegration ServiceºÍAnalysis Service£¬Reporing ServiceÔÚ¹úÄÚµÄʹÓÃÕßÓ¦¸Ã¶àºÜ¶à.Ò»·½ÃæÓÉÓÚReporing Service·ÑÓñȽϵͣ¬Ö±½Ó¸½ÊôÔÚSQL SERVERÖУ¬ÁíÍâÒ»·½ÃæÆäʵSSRSÔںܴó³Ì¶ÈÉÏ»¹ÊÇÂú×ãÎÒÃǵı¨±íÐèÇóµÄ¡£ ÔÚSQL Server 2008ÖУ¬SSRS¸üÊÇÔںܶ෽Ã涼ÓÐËù¼ÓÇ¿£¬”Èç¹û΢ÈíÔÚ2008µÄBIÖÐͶÈëÁË10ÃÀÔª£¬ÄDZßÆäÖеÄ8ÃÀÔª¶¼Í¶Èëµ½ÁËReporing ServceÖД.ÎÒÃǾÍÀ´´ó¸Å¿´Ò»Ï£¬SSRS 2008ÌṩÁËÄÄЩеÄÌØÐԺ͸ıä:
1.ÍÑÀëÁËIIS
SSRS 2008²»ÔÙÒÀÀµIIS£¬ÆäÄÚÖÃÁË´¦ÀíÓû§ÇëÇóµÄ¹¦ÄÜ¡£µ±Æô¶¯SSRSʱ£¬¿ÉÒÔÖ±½Ó·ÃÎÊReportsºÍReportServer£¬²¢ÇÒ°²È«ÐÔºÍÐÔÄÜÔöÇ¿¡£ÓкܶàÅóÓÑ·´Ó¦µÄÌå»á¾ÍÊÇ£¬”ʹÓÃÁËSSRS 2008ÒԺ󣬲»Äܹ»ÊµÏÖÄäÃû·ÃÎʱ¨±íÁË?”
2.ÐÂÔöµ¼³öµ½WordÑ¡Ïî,µ¼³öCSVÔöÇ¿
ÔÚÒÔÇ°µÄµ¼³öXML,Pdf ,ExcelµÈ¸ñʽµÄ»ù´¡ÉÏ£¬Ìí¼ÓÁËÖÚÍûËù¹éµÄµ¼³öWord¸ñʽÎļþµÄÖ§³Ö¡£ÁíÍâµ¼³öCSV¸ñʽÊý¾Ý¹¦ÄÜÔöÇ¿¡£
3.±¨±í¿ª·¢·½Ê½µÄ¸Ä±ä.
×ó²àÁгöÁËÎĵµ´ó¸ÙºÍ±¨±íÊý¾Ý£¬±¨±íÊý¾ÝÖпÉÒÔºÜÖ±½ÓµÄÉèÖñ¨±í²ÎÊý£¬¹ÜÀíÊý¾Ý¼¯¡£Ö÷ÇøÓòÖ»ÓÐÉè¼ÆºÍÁ½¸öTab¡£Éè¼Æ½çÃæÔÚÏÂÃæ¿ÉÒÔ¶ÔÐÐ×éºÍÁÐ×é½øÐÐÉèÖÃ.
4.ÐÂÔöÁËͼ±í¹¤¾ß
ÓÉÓÚ΢ÈíÊÕ¹ºDundas,SSRSÖÕÓÚÔÚÕâ·½ÃæÓÐËù¼ÓÇ¿¡£Ìí¼ÓÁËͼ±íºÍÒÇ±í¹¦ÄÜ£¬Í¼±í°üÀ¨¸÷ÖÖÖù״ͼ£¬ÕÛÏßͼ£¬±ýͼ£¬ÌõÐÎͼµÈ£¬ÒDZí¿ÉÄÜÒ²ºÜÇ¿´ó£¬ÁíÍâÔÚR2°æ±¾Öл¹Ìí¼ÓÁËMaps£¬Ö±½Ó¿ÉÊÓ»¯Éè¼Æ£¬ÓëBingµÄMaps¼¯³É.
5.Tablix
Tablix = Table + Matrix,Tablix ½áºÏÁ˱íºÍ¾ØÕóÊý¾ÝÇøµÄ¹¦ÄÜ£¬ÔÊÐíºÏ²¢¶à¸öÁÐ
Ïà¹ØÎĵµ£º
ÎÒÃÇÔÚÊý¾Ý¿âÖÐʹÓñíµÄʱºò,¾³£»áÓöµ½Á½ÖÖʹÓñíµÄ·½·¨,·Ö±ð¾ÍÊÇʹÓÃÁÙʱ±í¼°±í±äÁ¿¡£ÔÚʵ¼ÊʹÓõÄʱºò£¬ÎÒÃÇÈçºÎÁé»îµÄÔÚ´æ´¢¹ý³ÌÖÐÔËÓÃËüÃÇ£¬ËäÈ»ËüÃÇʵÏֵŦÄÜ»ù±¾ÉÏÊÇÒ»ÑùµÄ£¬¿ÉÈçºÎÔÚÒ»¸ö´æ´¢¹ý³ÌÖÐÓÐʱºòȥʹÓÃÁÙʱ±í¶ø²»Ê¹Óñí±äÁ¿£¬ÓÐʱºòȥʹÓñí±äÁ¿¶ø²»Ê¹ÓÃÁÙʱ±íÄØ?
¡¡¡¡ÁÙʱ±í
¡¡¡¡ÁÙʱ±íÓëÓÀ¾Ã± ......
----start
Ç°Ã棬ÎÒÃǽéÉÜÁË ¾¡Á¿±ÜÃâÔÚSQLÓï¾äµÄWHERE×Ó¾äÖÐʹÓú¯Êý£¬ÒòΪÕâÑù×ö»áʹ¸Ã×Ö¶ÎÉϵÄË÷ÒýʧЧ£¬Ó°ÏìSQLÓï¾äµÄÐÔÄÜ¡£»ùÓÚͬÑùµÄµÀÀí£¬ÎÒÃÇÒ²Ó¦¸Ã±ÜÃâʹÓÃLIKE¡£¿¼ÂÇÏÂÃæµÄÇé¿ö£º
CREATE TABLE USER
(
NAME VARCHAR(20) NOT NULL,---ÐÕÃû
MYNUMBER VARCHAR(18)---Éí·ÝÖ¤ºÅÂë
);&nb ......
´óÅúÁ¿²åÈëÊý¾Ýʱ 1,myisam±í,¿ÉÒÔÏÈͨ¹ý alter table table_name disable keys;#ÏȹرձíµÄË÷Òý¼ì²é,×¢ÒâÊÇ·ÇΨһË÷Òý! load data infile ¡®/path/file¡¯ into table table_name; alter table table_name anable keys;#ÔÙ´ò¿ªË÷Òý ¿É´ó´ó¼Ó¿ìµ¼Èë.»¹ÓпÉÒÔÉèÖÃbulk_insert_buffer_sizeÖµÀ´Ìá¸ß²åÈëËÙ¶È ......
½ñÌìÓöµ½Ò»¸öÎÊÌ⣺
ÔÚÆ´Ò»Ìõ¶¯Ì¬SqlÓï¾äʱ£¬ÓÉÓÚÊÇÓÃ×ֶεÄÖµ×öÁÐÃû£¬ËùÒÔÁÐÃûÓõ½Á˺º×Ö¡£
Ò»ÔËÐУ¬±¨´í£ºÔÚ“¡¢”¸½¼þÓÐÓï·¨´íÎó¡£
ºÜÄÉÃÆ£¬ÄѵÀ³¬³¤ÁË£¿
³¢ÊÔÔÚÆóÒµ¹ÜÀíÆ÷ÖÐÌí¼ÓÕâ¸öÁÐÃû£¬·¢ÏÖÌí¼ÓÍêÖ®ºóϵͳ×Ô¶¯Ìí¼ÓÁËÒ»¶ÔÖÐÀ¨ºÅ£¬¶ÔÓÚ"aa¡¢bb","aa£¬bb"Ò²¶¼Ìí¼ÓÁËÖÐÀ¨ºÅ¡£
¿´À´ÊÇ΢ÈíΪÁËÒÔ·ÀÍòÒ»£¬Á¬ ......
1.ÔÚ²éѯ·ÖÎöÆ÷ϲéѯExcelÎĵµ
Select * from
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source = "c:\²âÊÔ.xls";User ID = Admin;Password=;Extended properties=Excel8.0)....Sheet1$
2.´ÓÊý¾Ý¿âÖе¼³öÊý¾Ý²¢´æµ½ÎļþÖÐ
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN out c:\temp1.xls -c -q -S"."- ......