我在 SQL Server 2016 上运行了以下 MDX 查询:
SELECT NON EMPTY(Measures.[Enrolments Count]) ON COLUMNS,
NON EMPTY FILTER ( [Term Record Creation].[Year].CHILDREN * [Term Record Creation].[Week].CHILDREN *
[Term Enrolments].[Term Year].CHILDREN ,
[Term Record Creation].[Week] <= 5)
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [Enrolments]
我试图只接受在一年的第 5 周(该周是 ISO 周)之前创建学期记录的注册。
的Term Year
是,其中一个学期开始了一年。它是术语记录创建维度(即[Term Record Creation].[Year]
和 )之外的一列[Term Record Creation].[Week]
。
我遇到的问题是过滤器正在做某种过滤(因为我的度量列很低),但我仍然在一年中的所有周中使用一个数字来对抗它们,例如我有如下一行结果集:
Creation Year Creation Week Term Year Enrolments Count 2012 16 2013 4
如果有人能解释出了什么问题并提供解决方案,我将不胜感激。
谢谢。
也许尝试只过滤一周:
SELECT
NON EMPTY
Measures.[Enrolments Count] ON COLUMNS
,NON EMPTY
[Term Record Creation].[Year].Children*
Filter
(
[Term Record Creation].[Week].Children
,
[Term Record Creation].[Week] <= 5
)*
[Term Enrolments].[Term Year].Children ON ROWS
FROM [Enrolments];
尽管我认为 Daylo 是正确的,因为您需要使用密钥:
SELECT
NON EMPTY
Measures.[Enrolments Count] ON COLUMNS
,NON EMPTY
[Term Record Creation].[Year].Children*
Filter
(
[Term Record Creation].[Week].Children
,
[Term Record Creation].[Week].CURRENTMEMBER.MEMBER_KEY <= 5
)*
[Term Enrolments].[Term Year].Children ON ROWS
FROM [Enrolments];
在我们的立方体中,一周的关键是这样的,201709
所以这可能会更好:
SELECT
NON EMPTY
Measures.[Enrolments Count] ON COLUMNS
,NON EMPTY
[Term Record Creation].[Year].Children*
Filter
(
[Term Record Creation].[Week].Children
,
Cint([Term Record Creation].[Week].CurrentMember.Member_Caption) <= 5
)*
[Term Enrolments].[Term Year].Children ON ROWS
FROM [Enrolments];
好的 - 最后一种方法针对类似的脚本进行了很好的测试AdvWrks
:
SELECT
[Measures].[Internet Sales Amount] ON 0
,
[Product].[Product Categories].[Subcategory]
*
Filter
(
[Date].[Day of Month].[Day of Month]
,
Cint([Date].[Day of Month].CurrentMember.Member_Caption) > 10
) ON 1
FROM [Adventure Works];
结果:
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句