如何在MDX中将级别的总和显示为其最后一个子项的值 [英] How to display the total of a level as the value of its last child in MDX

问题描述

我有一个MDX查询，该查询列出了OLAP数据库中所有周"和天"级别的度量.例如

I have an MDX query which lists a measure for all 'Week' and 'Day' levels in the OLAP database. For example

SELECT 
{
    HIERARCHIZE( {[Business Time].[Week].members, [Business Time].[Date].members} )
} 
ON ROWS,
{
    [Measures].[Quantity]
} 
ON COLUMNS 
FROM [Sales]

尽管在此处显示一周的度量，但我不想显示所有天"值的总和，而是要显示一周中最后一天的值.例如

Where the measure is displayed for a Week though, instead of showing the total of all the Day values, I would like to show the value for the last day within the week. For example

第1周:12
9月15日:10
9月16日:20
9月17日:12
9月18日:15
9月19日:8
9月20日:9
9月21日:12
第2周:15
9月22日:12
9月23日:15

Week 1: 12
15 Sept: 10
16 Sept: 20
17 Sept: 12
18 Sept: 15
19 Sept: 8
20 Sept: 9
21 Sept: 12
Week 2: 15
22 Sept: 12
23 Sept: 15

如何在MDX中实现这一目标?

How can I achieve this within the MDX?

推荐答案

仅在以星期为单位显示时，将新的计算得出的度量添加到MDX的开头，以显示最后一天的值，否则保持不变:

Add a new calculated measures onto the start of your MDX that shows the last day's value only if it is being shown at a week level, otherwise leave it unaltered:

WITH MEMBER [Measures].[Blah] AS 
'IIF(
   [Business Time].currentMember.level.name = "Week",
   ([Business Time].currentMember.lastChild, [Measures].[Quantity]),
   ([Business Time].currentMember, [Measures].[Quantity])
)'

我希望有一个客户提出这个奇怪的要求，我预计您将在一个月内接到同一办公室内某人的电话，说此报告中的每周总计"是错误的！

I expect a client asked for this odd request, and I predict you'll get a call in a month from someone in the same office, saying the weekly 'total' is wrong on this report!

这篇关于如何在MDX中将级别的总和显示为其最后一个子项的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！