微软BI 之SSRS 系列 - 基于时间段参数的 MDX 查询以及时间日历 Date Picker 的时间类型参数化

今天在天善问答里看到一个问题，如果我没有理解错的话，它应该是指比如在一个报表中选取一个时间段，然后求出这个时间段的某个 Measure 的 SUM 和。并且同时求出这两个时间点对应的上一年的时间点之间的同一个 Measure 的 SUM 和。

比如当前选取的时间点是 2004年1月8日，结束时间点是 2004年3月1日。那么不仅要求这个时间段的某度量值总和，并且还要求 2003年1月8日到2003年3月1日时间段的某度量值总和。 也就是说，这个时间段是一个动态的，根据输入的两个时间段决定当年和上一年的计算。

这个里面有几个小知识点，于是总结了一下，先看下面这个 Report  的效果。

还有使用 Date Picker 的效果 (注意如果使用日历控件，那么 StartDate 和 EndDate 的类型都是 Date/Time 类型)-

非 Date Picker 参数时的设计

在 Cube 中查一下验证一下，查询结果都是一样的。

WITH MEMBER [Measures].[Reseller Sales Amount of Period]
AS
SUM(
 [Date].[Calendar].[Date].&[20040108]:[Date].[Calendar].[Date].&[20040301]
,[Measures].[Reseller Sales Amount]
   )
MEMBER [Measures].[Reseller Sales Amount of Last Period]
AS
SUM(
 [Date].[Calendar].[Date].&[20030108]:[Date].[Calendar].[Date].&[20030301]
,[Measures].[Reseller Sales Amount]
   )
SELECT NON EMPTY{[Measures].[Reseller Sales Amount of Period],
 [Measures].[Reseller Sales Amount of Last Period]} ON COLUMNS,
NON EMPTY { ([Product].[Category].[Category].ALLMEMBERS ) } ON ROWS
FROM [Step-by-Step]

新建一个 Dataset  - Datedata，连接的是 MDX Step by Step 中的示例Cube。

MDX 查询语句取得时间成员和可以用在 Report 下拉框中的标签。

WITH MEMBER [Measures].[DateValue]
AS
   [Date].[Calendar].CurrentMember.UniqueName
MEMBER [Measures].[DateLabel]
AS
   [Date].[Calendar].CurrentMember.Name
SELECT
{  [Measures].[DateValue],  [Measures].[DateLabel] } ON COLUMNS,
{ [Date].[Calendar].[Date]} ON ROWS
FROM [Step-by-Step]

创建两个时间参数 StartDate 和 EndDate 并且绑定到 DateData 中的时间成员，它们选择的都是 Text 类型，这一点一定要注意！！！。

新建一个 Dataset 用来展现表格上的数据，注意在 SSRS Report 中的参数使用一般都是通过这种类似于子查询的方式完成的。

WITH MEMBER [Measures].[Reseller Sales Amount of Last Year]
AS SUM(
 {
  -- 根据传入的参数分别取得上一年的开始时间点和结束时间点
  PARALLELPERIOD([Date].[Calendar].[Calendar Year],
     1,
     STRTOMEMBER(@FromDateCalendar, CONSTRAINED)
   ):
  PARALLELPERIOD([Date].[Calendar].[Calendar Year],
     1,
     STRTOMEMBER(@ToDateCalendar, CONSTRAINED)
   )
  },
  [Measures].[Reseller Sales Amount]
)
SELECT NON EMPTY {[Measures].[Reseller Sales Amount],
    [Measures].[Reseller Sales Amount of Last Year]} ON COLUMNS,
NON EMPTY { ([Product].[Category].[Category].ALLMEMBERS ) } ON ROWS
FROM (
             -- Report 传入的开始参数和结束参数
   SELECT ( STRTOMEMBER(@FromDateCalendar, CONSTRAINED) : STRTOMEMBER(@ToDateCalendar, CONSTRAINED) )
   ON COLUMNS
   FROM [Step-by-Step]
)

保存并在 Dataset 中绑定两个时间参数。

 

这样基本上就完成了基于时间区间的同比时间段的报表。

还有一点要注意一下，比如像这个查询是不会返回结果的，因为2004年的上一年不是闰年。

但是是有2003年2月28日这一天的，尽管下面的查询返回一个 NULL 但是这个成员是存在的。

Date Picker 时的设计

在文章一开始的时候就介绍了，如果把时间参数设计成日历形式那么就需要将参数的类型从 Text 类型改成  Date/Time 类型。

并且这里没有指定可供选择的值，因为一旦指定了值日历控件的样式就又会变成下拉框的样式。

同时在 DateSet 那里要改变参数的格式，因为 Date\Time 类型是时间类型，并不是字符串类型，因此需要将它们转变成字符串格式。

转变的格式代码如下：

="[Date].[Calendar].[Date].&["& Format(CDate(Parameters!StartDate.Value),"yyyyMMdd") + "]"

你可以从网上搜索到很多种不同的别人告诉你如何转换格式的经验和代码，但是要注意，你首先要确定的是你要转换的对象是什么？它需要什么格式？

先最好能够通过下面这种最简单的代码查看一下这种 Hard Code 形式的参数行不行，没有问题之后再放到上面去编辑。

在网上搜索到的转换过程一般都是使用作者自己代码示例的层次结构，比如说有的就是[Date].[Calendar].&[20040228], 少了一个 Level。

第二就是转换的字符串格式可能并不是 yyyyMMdd 的类型，而是 yyyy-MM-dd 的类型，所以这两点一定要注意。注意到了，你的转换才会成功！

最后一个问题，就是如果用户选择了一个非法的成员传入的时候就会出现这个错误，原因是我们的成员并不包含 11/1/2013 或者 11/29/2013。所以实际上还是使用下拉框的形式最直接，最简单，因为它可直接限定可选择的内容，可以做成年月日级联的效果。

---

总结：

1. 在 SSRS Report 中使用下拉框等具有值和标签特征的组件时，可以参照本文的方式来绑定。
2. 有关 SSRS Report 传递 MDX 查询参数的相关文章还可以参看 SSRS 系列 - 使用带参数的 MDX 查询实现一个分组聚合功能的报表。
3. 有关 MDX 时间函数 PARALLELPERIOD 可以参看这篇文章 MDX Step by Step 读书笔记(九) - Working with Time 处理时间。
4. STRTOMEMBER 函数的使用也需要注意一下，还有其它比较类似的例如 STRTOSET 等等都是可以将字符串转成对象或者集合。

 

---

更多 BI 文章请参看 BI 系列随笔列表 (SSIS, SSRS, SSAS, MDX, SQL Server)

如果觉得这篇文章看了对您有帮助，请帮助推荐，以方便他人在 BIWORK 博客推荐栏中快速看到这些文章。

---