locked
PerformancePoint Dashboard Analytical Grid Column Sum RRS feed

  • Question

  • I am trying to use the MDX alternative to calculate the sum of one column but could not seem to achieve the outcome. For a simplified model I have the following analytical grid of 2 columns and 4 rows:

    +------------------------------------------------
    |Measures, JobType    +  December 2010 +
    |-------------------------+---------------------|
    |JobType Description   |                           |
    |-------------------------+---------------------|
    |   JobType1               |      £25                |
    |-------------------------+---------------------|
    |   JobType2               |      £15                |
    |-------------------------+---------------------|
    |   JobType3               |      £35                |
    |-------------------------+---------------------|
    |   JobType4               |      £55                |
    |-------------------------+---------------------|


    Would like to have a Grand Total showing £130 after JobType4.

    Currently, the MDX code looks like this:

    WITH member [Measures].[Grand Total] as SUM([Measures].[Invoice Total])

    SELECT
    { [Date_Invoice].[Calendar Year].[Month].&[2010-12-01T00:00:00] }
    ON COLUMNS,

    { [Measures].[Invoice Total] } * HIERARCHIZE ( { [Job].[JobType].[Job Type].&[JobType1], [Job].[JobType].[Job Type].&[JobType2], [Job].[JobType].[Job Type].&[JobType3], [Job].[JobType].[Job Type].&[JobType4] } )
    ON ROWS

    FROM [CR Cube]

    I am certain this can be done. Where and how should I place the Grand Total? I'm fairly new to MDX. Please help out as this could also benefit others.
    I don't have Adventure Works, so please use example model given above.

    Many thanks in advance.

    DT

    Tuesday, January 4, 2011 4:20 PM

Answers

  • I believe the issue is the MDX syntax and your use of the CHILDREN function and the creation of the Aggregate member.  Remove a section in the Aggregate name and reference the All member with the CHILDREN function.  Like the following possibly:

     

     WITH member [Job].[JobType].[Grand Total] as AGGREGATE(EXISTING { [Job].[JobType].[All Job Types].CHILDREN })

    SELECT { [Date_Invoice].[Calendar Year].[Month].&[2010-12-01T00:00:00] } ON COLUMNS,

    { { [Job].[JobType].[All Job Types].CHILDREN }, { [Job].[JobType].[Grand Total] }} ON ROWS

    FROM [CR Cube]
    WHERE ([Measures].[Invoice Total])

    • Edited by Dan English Wednesday, January 5, 2011 11:40 AM cleaning up the MDX copy from SSMS
    • Marked as answer by techhermit Thursday, January 6, 2011 9:56 AM
    Wednesday, January 5, 2011 11:38 AM

All replies

  • You can create an Aggregate member.  Take a look at a previous post here for an example - Grand Total Field in Analytic Grid Report

     


    Dan English's BI Blog
    Tuesday, January 4, 2011 8:41 PM
  • Dan,

    Thanks for your reply.

    I have spent hours reviewing the posting you mentioned prior to posting my case here as I still do not understand and/or get it to work. I have even stripped down the model to show only 1 column and 2 rows with £ amount (e.g. £3 & £7).

    Would you, or someone be kind enough to show me? Please?

    DT

    Wednesday, January 5, 2011 9:05 AM
  • Ok I had been working to make that post work as well. The only issue I get is when I have null values in my measure, the aggregation fails (returns null).

    Here is that might work on you end DT:

    WITH member [Job].[JobType].[All Job Type].[Grand Total] as

    AGGREGATE(EXISTING { [Job].[JobType].[All Job Type].CHILDREN })

    SELECT
    { [Date_Invoice].[Calendar Year].[Month].&[2010-12-01T00:00:00] }
    ON COLUMNS,

    { {  [Job].[JobType].[All Job Type].CHILDREN }, { [Job].[JobType].[All Job Type].[Grand Total] }}
    ON ROWS

    FROM [CR Cube]

    WHERE ([Measures].[Invoice Total])

    --------------------------------

    If you get null in the Grand total last row then probably there are members in you dimension which are empty. To make it work replace 2 occurrences of

    { [Job].[JobType].[Job Type].CHILDREN } with

    { [Job].[JobType].[Job Type].&[JobType1], [Job].[JobType].[Job Type].&[JobType2], [Job].[JobType].[Job Type].&[JobType3], [Job].[JobType].[Job Type].&[JobType4] }

    and you should get a result as desired.

     

    Edit: Thanks Dan for the input, including the All with Children functions resolves the null value aggregation. Modified the query above.


    http://dailyitsolutions.blogspot.com/
    Wednesday, January 5, 2011 10:19 AM
  • I believe the issue is the MDX syntax and your use of the CHILDREN function and the creation of the Aggregate member.  Remove a section in the Aggregate name and reference the All member with the CHILDREN function.  Like the following possibly:

     

     WITH member [Job].[JobType].[Grand Total] as AGGREGATE(EXISTING { [Job].[JobType].[All Job Types].CHILDREN })

    SELECT { [Date_Invoice].[Calendar Year].[Month].&[2010-12-01T00:00:00] } ON COLUMNS,

    { { [Job].[JobType].[All Job Types].CHILDREN }, { [Job].[JobType].[Grand Total] }} ON ROWS

    FROM [CR Cube]
    WHERE ([Measures].[Invoice Total])

    • Edited by Dan English Wednesday, January 5, 2011 11:40 AM cleaning up the MDX copy from SSMS
    • Marked as answer by techhermit Thursday, January 6, 2011 9:56 AM
    Wednesday, January 5, 2011 11:38 AM
  • @ Umair, Dan:

    Many thanks for your input. Using the MDX snippet you provided as skeleton, everything seems to be working! This is such a relieve!

    Is it true that I can't use implement time intelligence filter or any filters after executing a report in MDX (like the above)? I can't seem to drag & drop the original filter. It wouldn't allow me to create a connection.

     

    DT

    Wednesday, January 5, 2011 4:15 PM
  • In order to get this to work you need an endpoint to map the filter to.  With custom MDX you must define parameters.  Take a look at this posting by the PPS Team in the Custom MDX Queries section - http://blogs.msdn.com/b/performancepoint/archive/2008/10/20/mapping-dashboard-filters-to-analytic-charts-and-grids.aspx.  You will see an example of a paramter defined and then information on mapping the filter to that endpoint.


    Dan English's BI Blog
    Wednesday, January 5, 2011 5:08 PM