在 MDX 中获取过去 6 个月的数据
我正在使用 SSRS 来创建报告。对于指标，我从分析服务中提取数据。我想修改查询设计器创建的 mdx 查询，使其仅包含过去 6 个月的数据。现在的查询如下所示：
SELECT NON EMPTY { [Measures].[Cumulative Count] } ON COLUMNS,
NON EMPTY { ([Work Item].[Microsoft_VSTS_Common_Discipline].[Microsoft_VSTS_Common_Discipline].ALLMEMBERS * [Date].[Year Month Date].[Month].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM
( SELECT ( { [Work Item].[System_WorkItemType].&[Task] } ) ON COLUMNS
FROM
( SELECT ( { [Date].[Year Month Date].[Year].&[2010-01-01T00:00:00] } ) ON COLUMNS FROM [Team System]))
WHERE ( [Work Item].[System_WorkItemType].&[Task] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS*
这里，我提取工作项的累积计数，其中工作项类型为任务，工作项的年份为 2010 年。但是，我不想提取 2010 年以来的所有月份，而是希望过去6个月。有谁知道我该如何在上面的查询中做到这一点。
I am using SSRS to create reports. For the metrics i am pulling out data from the analysis services. I want to modify the mdx query created by the query designer to include data only from past 6 months. The query looks like this right now:
SELECT NON EMPTY { [Measures].[Cumulative Count] } ON COLUMNS,
NON EMPTY { ([Work Item].[Microsoft_VSTS_Common_Discipline].[Microsoft_VSTS_Common_Discipline].ALLMEMBERS * [Date].[Year Month Date].[Month].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM
( SELECT ( { [Work Item].[System_WorkItemType].&[Task] } ) ON COLUMNS
FROM
( SELECT ( { [Date].[Year Month Date].[Year].&[2010-01-01T00:00:00] } ) ON COLUMNS FROM [Team System]))
WHERE ( [Work Item].[System_WorkItemType].&[Task] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS*
Here I am pulling out the cumulative count of the work items where the work item type is task and the year of the work item is 2010. However instead of pulling out all the months from 2010 i want past 6 months. Does anyone know how can i do this in the above query.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
创建一个新的计算成员，仅返回最近 6 个月的数据。
Create a new calculated member that returns data for only the last 6 months.
我过去曾通过向 MDX 添加 VBA 函数来完成此操作（Analysis Services 允许这样做）。您可以获取当前日期、回溯 6 个月、获取月份/年份，并以与您的会员姓名相同的格式写入字符串。然后使用
StrToSet将此字符串包含在您的 MDX 查询中。I have done this in the past by adding VBA functions to my MDX (Analysis Services allows this). You can get the current date, work back 6 months, grab the month/year, and write a string in the same format as your member names. Then use
StrToSetto include this string in your MDX query.