《Microsoft SQL Server 2008 MDX Step by Step》学习笔记十三：创建报表（本书完）

在水一万

　　SQL Server 2008中SQL应用系列及BI笔记系列--目录索引
　　导读:本文主要创建报表，也就是MDX结果的最终展现，包括：
　　■1、创建一个简单的报表项目
　　■2、连接到Analysis Services
　　■3、设计DataSet
　　■4、添加参数到DataSet
　　■5、在Report中设置数据
　　本文所用数据库和所有源码，请到微软官网下载
　　本文末尾处附本文所用示例下载。
　　
　　1、创建一个最简单的报表项目
　　步骤如下：第一步：在VS2008环境中新建一个Report项目，如下图所示：
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1pxCR6RQlPNhRcO-02RDl9LDD5ugnkolJ0xew08Tcs3SWhsq-7EwMkxIVvQ-4ohTlJ1etZMH7jr19ukEd_cdcbDQ/2011-12-5%2010-34-09.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1pxCR6RQlPNhTtcFAv-Tn_Mi9CzJPQ6LXR5NkN8siEdK0JFXMgfRXO21sQKmmkiQ-52oK2tH5CMHhPXvcSEUl4Qg/2011-12-5%2010-47-11.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1pxCR6RQlPNhRcO-02RDl9LDD5ugnkolJ0xew08Tcs3SWhsq-7EwMkxIVvQ-4ohTlJ1etZMH7jr19ukEd_cdcbDQ/2011-12-5%2010-34-09.png?psid=1
　　二、连接到Analysis Services
　　创建一个数据源即可。
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1p6scCZtqcVVC61Rrw6ZOGLaUE5Re5YTVftV1wkXxmzifmGLvFeAHFHM-ss2GfF1si9VyZo5tHHSwtyGuwfQvSLw/2011-12-7%2010-21-58.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1p6scCZtqcVVCt-jhVdWvo_BYRTmFf8ePFQghQaPoS8IN7EyeMYXtQMY-0qb2utIWb-ebPuETd4RTGLptL3HsdwQ/2011-12-7%2010-26-15.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1pa-Iew5VCQdYMqPw1FlfVV5Fc8t0Su0tHSUi-vQua0UgGWm-3Iy6btnMO39pvciaACi7g9MFSozD83YukntUwAg/2011-12-7%2010-27-21.png?psid=1
　　三、设计DataSet
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1p8J-biW5Q7HDg-NmTstysZfyoTBZ38DgxJzolFlWaHss0zYhFYnGt4edqAakvtvq1niwdIFCi--C4CtlnsUgCZA/2011-12-7%2010-50-57.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1p8J-biW5Q7HD8wycLyTa-6JeE2qbxIXYCIqOtpvcTS4HApnZMJAxdhs1bZtrZu2ZeyR7F_M0ZCD9HyFdLGFASVg/2011-12-7%2010-54-40.png?psid=1
　　添加一个计算成员
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1p8J-biW5Q7HBOg4ipWz0YxJSxsv8Mh14QdJDrDpxneJAK5US-09lDoVMmQS1QY4RBURLt9KgOztJ2ch0u6kBTfQ/2011-12-7%2010-56-22.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1pvGYBU7AgqUqPA93Yagu8_u0zpkwJmN7RN3qxpP0wL0CXxU_cCk0Wr3_FW-o954iahkGUdDyWxR9LFmrmmnMHPA/2011-12-7%2010-57-47.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1pui3qWR3wA2Tt6mcXD5NqO48IFcdo0uGdQ_qmqWiUrrMfyq9DAu0caSzahwvv9jolNiT0T3YkbY6ZE3Y6NeVBAw/2011-12-7%2010-59-34.png?psid=1
　　
　　例12-1
　　WITH
MEMBER [Measures].[Reseller Sales Per Order] AS
([Measures].[Reseller Sales Amount]) / ([Measures].[Reseller Order Count])
SELECT
NON EMPTY {
[Measures].[Reseller Order Count],
[Measures].[Reseller Sales Per Order],
[Measures].[Reseller Sales Amount]
} ON COLUMNS,
NON EMPTY { ( [Product].[Product].[Product].ALLMEMBERS ) }
DIMENSION PROPERTIES
MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS
FROM [Step-by-Step]
CELL PROPERTIES
VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
　　
　　修改MDX查询：
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1pNI-TbOhs2Lxx6fGJeGD0QLVJxQAJO2lAXb7Z4Il8SDdUVSxLaG_tCw9l7dqYriF7byVhTTnc7WfcNk3o8qNg8Q/2011-12-7%2011-01-19.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1pNI-TbOhs2LxWUs4E_falBqThbnwLiZLkbl8k0uB4-d62LawwaFBv_jYuKZ0LhAnla6obU7oA7JKSUvbDQC2v_A/2011-12-7%2011-04-27.png?psid=1
　　至此，设计DataSet完成。
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1pNrhqGhH6B6DZWzk5EZz0EIPr5IKyrqAAo1yL8ORhd2l5E0vTkmp5tm__G0jp32VS4602uzqojIRU67LnQyJpxg/2011-12-7%2011-46-15.png?psid=1
　　四、添加参数到DataSet
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1pVzr4ySYN8NgBNvVIyWVAiuIltSoKOzt_VQRi31x9Tmia9eeEUgrOgOXaEJgUc0QSqmIt8W7b6Fh8CoixVSJ_uQ/2011-12-7%2011-57-36.png?psid=1
　　注意到查询也发生了变化
　　例12-2
　　WITH
MEMBER [Measures].[Reseller Sales Per Order] AS
([Measures].[Reseller Sales Amount]) / ([Measures].[Reseller Order Count])
SELECT
{
[Measures].[Reseller Order Count],
[Measures].[Reseller Sales Per Order],
[Measures].[Reseller Sales Amount]
} ON COLUMNS,
{ ([Product].[Product].[Product].ALLMEMBERS ) }
DIMENSION PROPERTIES
MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS
FROM (
SELECT
( { [Product].[Product Categories].[Subcategory].&[1] } ) ON COLUMNS
FROM [Step-by-Step]
)
WHERE ( [Product].[Product Categories].[Subcategory].&[1] )
CELL PROPERTIES
VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1ppDzJwz-yvef3Kga4OfqE4vxbxzfdWHTh_BKz4qIx_SmxWXzzGeXvcH1fW5u3EV_qDK4KGeiijMtTm-xLF3t96Q/2011-12-7%2013-02-49.png?psid=1
　　
　　例12-3
　　WITH
MEMBER [Measures].[Reseller Sales Per Order] AS
([Measures].[Reseller Sales Amount]) / ([Measures].[Reseller Order Count])
SELECT
{
[Measures].[Reseller Order Count],
[Measures].[Reseller Sales Per Order],
[Measures].[Reseller Sales Amount]
} ON COLUMNS,
{ ([Product].[Product].[Product].ALLMEMBERS ) }
DIMENSION PROPERTIES
MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS
FROM (
SELECT
( STRTOSET(@ProductProductCategories, CONSTRAINED) ) ON COLUMNS
FROM [Step-by-Step]
)
WHERE
( IIF(
STRTOSET(@ProductProductCategories, CONSTRAINED).Count = 1,
　　STRTOSET(@ProductProductCategories, CONSTRAINED),
[Product].[Product Categories].currentmember
)
)
CELL PROPERTIES
VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
　　
　　可以修改我们刚添加的参数：
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1ppI3ER-Jx-fPM1LY8Rz-P84pzM1nluQo-qVTeJ0Tew8-niW9E8XdTS9so8Nidpe380b64zjo_HLIB03c842werw/2011-12-7%2013-11-44.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1paR2lvqj7QlwNLuMACyXh9nEtsf9Mo5kRkSqAIjHjaWT0npgMvWQrzeW2qOT1DWl33Bhrn6Ml8gbeur8gAT1EFw/2011-12-7%2013-14-50.png?psid=1
　　
　　修改参数以限制到Subcategory及以上的级别
　　例12-4
　　WITH MEMBER [Measures].[ParameterCaption] AS
　　[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION
　　MEMBER [Measures].[ParameterValue] AS
　　[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME
　　MEMBER [Measures].[ParameterLevel] AS
　　[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL
　　SELECT {
　　[Measures].[ParameterCaption],
　　[Measures].[ParameterValue],
　　[Measures].[ParameterLevel]
　　} ON COLUMNS ,
　　 [Product].[Product Categories].ALLMEMBERS ON ROWS
　　FROM [Step-By-Step]
　　
　　修改后的结果：
　　例12-5
　　WITH
MEMBER [Measures].[ParameterCaption] AS
[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS
[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS
[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL
SELECT
{
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]
} ON COLUMNS,
{
Descendants(
[Product].[Product Categories].[All Products],
[Product].[Product Categories].[Subcategory],
SELF_AND_BEFORE
)
} ON ROWS
FROM [Step-by-Step]
　　重要：Report Service提供“Select All”选项，此时，当某个用户设置All Members时，在参数的DataSet中的每个可用Member将被选择并提交。
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1pQ_n-QYTTmU8YMCgOjROsuJkBRfmWmDcgOephnIi23RMTi17yVME16Z1GFSBY0cJoDAAAnRbOq-NyrbG7wms3qA/2011-12-7%2013-37-56.png?psid=1
　　附带介绍一下Filter 操作符（运算符）：
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1pmMFpPVw7zTbRJa-nAC5aDp8iC0J2MK7tTGgxthicTXN2gTcxSMRVQ1p6byiswZpbILvPC5fsfWZPhFB_NAMlww/2011-12-7%2013-40-38.png?psid=1
　　在上面我们也用到三个字符转换函数：
　　StrToSet（http://msdn.microsoft.com/zh-cn/library/ms144782.aspx）
　　StrToMember（http://msdn.microsoft.com/zh-cn/library/ms146022.aspx）
　　StrToTuple（http://msdn.microsoft.com/zh-cn/library/ms146079.aspx）
　　
　　5、在Report中设置数据
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1p6z53UALqmBaHaQMkO45K_ZH-3WiAFz6ozWRrgnEsASAgnejiQDkU6qcHRHcsyLcl5jf2qJo0NpZCicfE-_qujg/2011-12-7%2014-09-26.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1pAHOseh4QvHSv64dS5C5h6o92I94NNuC1GTvxYbU608DAVBa56g7o-_abQpzQBCPqShKkv28unsd8VrKJ08MgKA/2011-12-7%2014-17-41.png?psid=1
　　增加report汇总
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1phzGRE0jwQw7Oc0TV2ck8Mtv2aYRwjDJ7az53U7L3asXh8O9iQJv5RrysSUWst88EQ265N9IqRyJ3cfoHx-R2OA/2011-12-7%2014-23-18.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1phzGRE0jwQw7KszW08QPVfYGDFBaw-fGJvb8Rvz-Pgtyd6w36c1r-_VRsfux1tI7t0ct6X9KDvOe8JkuBUiGh0g/2011-12-7%2014-25-32.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1pv7teHYV5yS0PmcuwL3uzXruYEf7DajeIu1zS-t6n9yHFnW-R0ojZf8Lq8mAehFDWZMMtXpE1mfOikzIJapFpiw/2011-12-7%2014-39-22.png?psid=1
　　上图的结果我们通过前节的练习，知道，结果是不准确的，Report Service默认使用Sum进行汇总，实际上我们需要使用的是Aggregate函数。
　　Reseller Order Count列基于Reseller Order Count度量，使用Distinct Count聚合函数。下面我们修改以使用Aggregate函数。
　　对第三列和第四列依次使用Aggregate函数:
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1pS11t9Jfc-DoLkKgtCFdVYJjTShenFndyNuJJc6fxuOUNBRCheaZZHv-ZnL3ExRJfodj0bpa-Oa7n5i154zvQzw/2011-12-7%2014-47-29.png?psid=1
　　修改后的效果：
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1pS11t9Jfc-Drf5cbPEpMAAc9jYRD3ZsqVoAYRCD5kT141W92QmUdPSXxogj5vhRNXWlKSSGX15f2yPCgdeyWDAg/2011-12-7%2014-49-22.png?psid=1
　　格式化表格
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1pHTLRrAPYs9MhZLDudSdUMIfPCQRJAvAxT2o_ZZERMAfow2rRgHuVbTyi0G387a_yzEMZfzDJ3jwbw3e2gIacKw/2011-12-7%2015-11-16.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1p0sB-UTObXWYQUEmnvSTtIWzcCuazIem_8cC7SDr-EYwIIgLLqSQTtFsv75a8qRF2e1imFOLqyJN3beRnayEKQw/2011-12-7%2015-17-00.png?psid=1
　　注意:Currency与当前的操作系统设置相关。
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1pPwrZROSFPV8-izCa9xhsR6tPEfVo-Q0dru1OSl2lRX6ec1-cImKp8FbRECatNSr61cafBLQCoELz-Vt9agZCWA/2011-12-7%2015-19-36.png?psid=1
　　当然，你可以尝试更多的显示效果，例如背景色：
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1pGLMGlG5wZeXpxIoTOLgLw3M9icia1ublKG6fo8E7v3IfKWwiYW6LbyizajtSktLW6k0uFN2arVvzbH4eXjgX_g/2011-12-7%2015-32-37.png?psid=1
http://onexin.iyunv.com/source/plugin/onexin_bigdata/https://public.bay.livefilestore.com/y1pGLMGlG5wZeXl_fN0u1E6WgVAhS1DnSINWGvAl1Lb_d85w5iXL8Gi_yw56t8iYXCUHRzRlTcLSxsDeJUAiin3Kw/2011-12-7%2015-33-09.png?psid=1
　　
　　
　　小结：本文是关于Report的初级入门知识，主要是前面所学MdX函数应用的前台展示。本书的学习也到此结束，总的来说，这算是一本书相当简易的基础书，其中的大部分内容都可以在MSDN中找到。
　　下面一本书将是Cube的提高《Expert Cube Development with Microsoft SQL Server 2008 Analysis Services》，欢迎有兴趣的同学一起读书。
　　本文所用演示代码：下载
　　
　　参考资源：
　　1、MDX官方教程（http://msdn.microsoft.com/zh-cn/library/ms145506.aspx）