导读:本文主要创建报表,也就是MDX结果的最终展现,包括:
■1、创建一个简单的报表项目
■2、连接到Analysis Services
■3、设计DataSet
■4、添加参数到DataSet
■5、在Report中设置数据
本文所用数据库和所有源码,请到微软官网下载
本文末尾处附本文所用示例下载。
1、创建一个最简单的报表项目
步骤如下:第一步:在VS2008环境中新建一个Report项目,如下图所示:
二、连接到Analysis Services
创建一个数据源即可。
三、设计DataSet
添加一个计算成员
例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查询:
至此,设计DataSet完成。
四、添加参数到DataSet
注意到查询也发生了变化
例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
例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
可以修改我们刚添加的参数:
修改参数以限制到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将被选择并提交。
附带介绍一下Filter 操作符(运算符):
在上面我们也用到三个字符转换函数:
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中设置数据
增加report汇总
上图的结果我们通过前节的练习,知道,结果是不准确的,Report Service默认使用Sum进行汇总,实际上我们需要使用的是Aggregate函数。
Reseller Order Count列基于Reseller Order Count度量,使用Distinct Count聚合函数。下面我们修改以使用Aggregate函数。
对第三列和第四列依次使用Aggregate函数:
修改后的效果:
格式化表格
注意:Currency与当前的操作系统设置相关。
当然,你可以尝试更多的显示效果,例如背景色:
小结:本文是关于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)
分享到:
相关推荐
Microsoft SQL Server 2008 MDX Step by Step Teach yourself the Multidimensional Expressions (MDX) query language—one step at a time. With this practical, learn-by-doing tutorial, you’ll build the ...
Provides information on the fundamentals of Microsoft SQL Server 2005 Analysis Services. Teach yourself the fundamentals of SQL Server Analysis Services—one step at a time. With this practical, ...
Microsoft+SQL+Server+2008+MDX+Step+by+Step 京华志&精华志出品 希望大家互相学习,互相进步 支持CSDN 支持微软
Provides information on the fundamentals of Microsoft SQL Server 2005 Analysis Services. Teach yourself the fundamentals of SQL Server Analysis Services—one step at a time. With this practical, ...
Microsoft.Press.Microsoft.SQL.Server.2008.MDX.Step.by.Step.Feb.2009.rar
This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written by key members of the Microsoft Analysis Services product development team....
"Practical DMX Queries for Microsoft SQL Server Analysis Services 2008" contains more than 250 downloadable DMX queries you can use to extract and visualize data. The application, syntax, and results...
《SQL Server2012Analysis Services高级教程(第2版)》由Microsoft产品团队的主要成员(哈日那思、菲尔格林等)编写,介绍了如何应用这些新功能来创建复杂的BI解决方案。为了实现这一目的,你将在本书中了解到如何...
Provides information on the fundamentals of Microsoft SQL Server 2005 Analysis Services. Teach yourself the fundamentals of SQL Server Analysis Services—one step at a time. With this practical, ...
Smart Business Intelligence Solutions with Microsoft SQL Server 2008 Discover how to: Manage the development life cycle and build a BI team Dig into SQL Server Analysis Services, Integration ...
项目需要用MDX,所以翻译了Professional Microsoft SQL Server 2012 Analysis Services with MDX and DAX.pdf中第II部分第三章的内容。有翻译不当之处请联系QQ:185633513或发送EMAIL:yubinsure@gmail.com进行修正。
MDX step by step CD which including every chapter's sample
利用Microsoft SQL Server 2008实现灵活的商业智能解决方案使用Microsoft 完善的BI工具构建B0解决方案的必备指南,使用SQLServer 2008设计、开发和部署更有效的数据集成、报表、分析解决方案所需的权威操作指南。...
This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written by key members of the Microsoft Analysis Services product development team....
利用Microsoft SQL Server 2008实现灵活的商业智能解决方案使用Microsoft 完善的BI工具构建B0解决方案的必备指南,使用SQLServer 2008设计、开发和部署更有效的数据集成、报表、分析解决方案所需的权威操作指南。...
This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written by key members of the Microsoft Analysis Services product development team....
This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written by key members of the Microsoft Analysis Services product development team....
This title serves as an authoritative guide to Microsofts new "SQL Server 2012 Analysis Services" BI product and is written by key members of the Microsoft Analysis Services product development team....
MDX.Solutions.with.Microsoft.SQL.Server.Analysis.Services.2005