Column totals within the same MDX result set...

Question

For the result set as specified below, how do I display column totals ?
As well I am doing some calculations to produce an adjustment to forecast. I would like to list these calculations below the report as information. Would it be possible to do that within the same query? In SQL, a union would achieve all of this. In MDX I am not clear on how this can be achieved. I have listed the result that I would like.

So far my MDX query is listing the result set without totals (in bold) and without the calculation columns specified below (in bold)

	Actuals To Date	Forecast pro-rata	Projection
Jan-09	6,280,412	(null)	6,280,412
Feb-09	6,295,246	(null)	6,295,246
Mar-09	5,820,100	(null)	5,820,100
Apr-09	4,767,627	(null)	4,767,627
May-09	4,758,952	(null)	4,758,952
Jun-09	4,677,882	(null)	4,677,882
Jul-09	5,155,159	(null)	5,155,159
Aug-09	4,850,019	(null)	4,850,019
Sep-09	4,961,642	(null)	4,961,642
Oct-09	(null)	3,594,920	3,594,920
Nov-09	(null)	3,271,273	3,271,273
Dec-09	(null)	2,880,756	2,880,756
2009 Total	47,567,039	9,746,949	57,313,988
Total Forecast Variance		-11,664
Remaining Forecast		319,051
Adjustment % to Forecast Target		3.65%

Help in resolving this would be much appreciated.

Thank you...

Answer 1

Hi,

In MDX it should be more easy than in SQL to add the totals:
In your rows axis, just add the member "Year 2009" after the months (assuming it is contained the same time hierarchy as the months).

Adding calculations here is a bit more tricky, as the dimensionality of an axis has to be consitent, i. e. the first column of the rows axis would have to have members of the same hierarchy, and if it is used, a second cttribute column in the row axis again would have to have only members of the same hierarchy, and so forth.

So, one solution could be to to add "Total Forcast Variance", "Remaining Forecats", and "Adjustment % to Forecast target" into the time hierarchy. But this is not very logical.
Another possibility would be to add a "Calculations" dimension or attribute to your cube, containing the members "Value", "Total Forcast Variance", "Remaining Forecats", and "Adjustment % to Forecast target", and then to build your rows axis from these two dimensions as follows:

Time	Calculation	Actuals To Date	Forecast pro-rata	Projection
Jan-09	Value	6,280,412	(null)	6,280,412
Feb-09	Value	6,295,246	(null)	6,295,246
Mar-09	Value	5,820,100	(null)	5,820,100
Apr-09	Value	4,767,627	(null)	4,767,627
May-09	Value	4,758,952	(null)	4,758,952
Jun-09	Value	4,677,882	(null)	4,677,882
Jul-09	Value	5,155,159	(null)	5,155,159
Aug-09	Value	4,850,019	(null)	4,850,019
Sep-09	Value	4,961,642	(null)	4,961,642
Oct-09	Value	(null)	3,594,920	3,594,920
Nov-09	Value	(null)	3,271,273	3,271,273
Dec-09	Value	(null)	2,880,756	2,880,756
Year 2009	Value	47,567,039	9,746,949	57,313,988
Year 2009	Total Forecast Variance		-11,664
Year 2009	Remaining Forecast		319,051
Year 2009	Adjustment % to Forecast Target		3.65%

This approach is not easy to explain, and roughly would work as follows:

• Add a dimension table named "Calculations" containing a key column and an attribute column, and just containing one record where the attribute column is the text "Value".
• Reference this dimension from your fact table, just all records should refer to this one record of the dimension table.
• Re-build you cube with this dimension added.
• In the calculations tab, add new members to this dimension (not to the measures dimension, as the calculation dialog proposes by default!). These members should refer e. g. "Measures.CurrentMember" and do the calculations necessary.

A third, and maybe the easiest way, would be to use your client tool to define the calculations. But how easy this is this depends a lot which tool you use.

And a fourth approach would be to run two separate queries and put the results together:
One for the months and the year total, and one containing the three calculation rows. This would make up for two simple rectangular tables without too much complexity in each of them.

Frank

Answer 2

Thank you very much for your detailed response.

The client tool we are using is PPS Dashboard designer. This is a custom MDX report & calculations are easily definable within the query. At this point we felt it best to go with two separate queries and display as 2 reports within the same page. The fourth approach you mentioned above; how do I achieve this; i.e. putting the results of 2 queries together? If I can combine the results that would be great.



Also my totals do not work as I had anticipated: I added the Year member to my row axis; but it returns a null. Works for most measures that I have in the cube; but not for the measures I have specified. These measures (you had greatly helped with this) are calculated in the following way:

Code Snippet

[Actuals To Date] = IIf(Intersect({[Date].[Calendar].CurrentMember}, NULL : [Last Month].Item(0)).Count = 1, [Prod Amount], Null)
[Forecast pro-rata] = IIf(Intersect({[Date].[Calendar].CurrentMember}, [Current Month].Item(0) : NULL).Count = 1, [Forecast Amount], Null)
[Projection] = [Actuals To Date] + [Forecast pro-rata]

SELECT {[Actuals to Date], [Forecast pro-rata], [Projection]} ON 0,
{Descendants([Date].[Calendar].[Year].&[2009], [Date].[Calendar].[Month]), [Date].[Calendar].[Year].&[2009]} ON 1
FROM [MyCube]

Results:

              Actuals To Date Forecast pro-rata          Projection
Jan 2009     6,280,412               (null)                      6,280,412
Feb 2009     6,295,246               (null)                      6,295,246
Mar 2009     5,820,100               (null)                      5,820,100
Apr 2009     4,767,627               (null)                      4,767,627
May 2009    4,758,952              (null)                      4,758,952
Jun 2009     4,677,882               (null)                      4,677,882
Jul 2009      5,155,159                (null)                      5,155,159
Aug 2009    4,850,019              (null)                      4,850,019
Sep 2009    4,961,642              (null)                      4,961,642
Oct 2009     5,065,544               (null)                      5,065,544
Nov 2009       (null)                 3,271,273                3,271,273
Dec 2009       (null)                 2,880,756                2,880,756
2009             (null)                   (null)                          (null)


How do I resolve this ?

Thank you,

Answer 3

Try changing the =1 in your members at the top to >=1.  You are grabbing more than one month at a time and I think that might be making it choose null.

Not 100% but hope it helps.

Answer 4

I do need to return more than one month at a time. Changing the count to >=1 does not help at all!

Answer 5

Feel free to ignore me if you feel my help is of no help, because I am no MVP like some of these folks.

What about wrapping a descendants function around your currentmember, so that you always get a set of months in the first set in the intersect function?  Seems like it should work the way you have it, but it must be the results of the intersect function that are causing the problem.

Answer 6

Hi,

Yes, implementing this using the descendants on month level should work. In the other post, the report just contained months, so I proposed a slightly more simple way of calculation then.

Here is how you should change the [Actuals To Date] and [Forecast pro-rata] definition:

[Actuals To Date] = Aggregate(Intersect(Descendants([Date].[Calendar].CurrentMember, [Date].[Calendar].[Month]), NULL : [Last Month].Item(0)), [Prod Amount])
[Forecast pro-rata] = Aggregate(Intersect(Descendants([Date].[Calendar].CurrentMember, [Date].[Calendar].[Month]), [Current Month].Item(0) : NULL), [Forecast Amount])

This goes down from whatever time period is in place for the current cell (a year, a quarter, or a month) to month level, then intersects the months with the range to be used for forecast or actuals, respectively, and then sums up the appropriate measure across all months in the intersection.


With regard to PPS dashboard designer, I do not know this tool. Can you not just put the two reports below each other?

Frank

Answer 7

Thank you... the totals worked perfectly after I changed the definition of the above 2 measures as you had specified.

With regards to display of calculations, I am displaying as a separate report, below the main one, and that is an acceptable solution for us presently.