none
Cube/MDX group totals in SSRS grid? RRS feed

  • Question

  • Hi there,

    Simple question, but hard to solve. At least for me it is!

    I want to query a SSAS Cube with MDX, then display results in a SSRS grid, grouped on store type. It should display information for each store ofcourse, and display the totals for each group. Here's the catch...it should use the Cube's totals as some columns contain percentages which should not be summed by SSRS.

    I am having a hard time getting the total rows from my MDX into the total row of the SSRS grid? Is there a robust way to do this? Preferably without much hassle in script, as this would overcomplicate maintainability of reports.

    Thanks for your effort in advance.
    Monday, February 23, 2009 6:29 PM

All replies

  • Use the Aggregate() function in your total rows.  That will tell SSRS to use the value from the cube.  I'm not referring to the Aggregate() MDX function, SSRS has it's own Aggregate() function which you will use in the report itself.

    -Sean
    Program Manager, SQL Server Reporting Services
    • Proposed as answer by Sean Boon Monday, February 23, 2009 6:48 PM
    • Unproposed as answer by tss68nl Friday, March 6, 2009 3:49 PM
    Monday, February 23, 2009 6:48 PM
  •  It tells me I cannot use Aggregate because the report has filters.

    Ofcourse, my report has filters, it would not be any use otherwise unfortunately. So I have the choice, to either create duplicate query recordsets with the filters in MDX to cater for the different views of data, or forget about showing totals?

    It gathers a list of data from all stores in the cube, and the query is quite expensive. The report is a analysis of multiple categories within that data, and hence filters are used to limit the results in particular grids and charts. Creating a query for each grid and chart would cause the processing time to go up from 10 seconds to about 1.5 minutes.

    Monday, February 23, 2009 7:25 PM
  • I have a similar problem: one of my measures is defined as follows (BTW: there's no code formatting style for MDX), where Xirr() is a function in a .NET-Assembly:

    1 CREATE MEMBER CURRENTCUBE.[MEASURES].[Xirr]  
    2  AS FinanceMathAS.Xirr(  
    3     [Time].[Time].[Date].Members.Item(0):
        ClosingPeriod([
    Time].[Time].[Date], [Time].[Time].CurrentMember),   
    4     [Measures].[Amount]  
    5   ),   
    6 VISIBLE = 1;  

    In my report I would like to show this measure for each detail row, but also for several groups. The only way for calculating a group's value is calling the Xiir()-function again (which will use the group's scope then). 

    e.g. I need something like:

    group A | group 1 | detail | Xirr-value
    group A | group 1 | detail | Xirr-value
    group A | group 1 | (null) | Xirr-value
    group A | group 2 | detail | Xirr-value
    ...
    group A | group 2 | (null) | Xirr-value
    ...
    group A | (null)  | (null) | Xirr-value
    ...
    (null)  | (null)  | (null) | Xirr-value

    How can I solve this using Reporting Services?

    Thanks in advance,
    Gerald

    • Edited by actinium.ga Tuesday, February 24, 2009 9:47 AM MDX formatting
    Tuesday, February 24, 2009 9:46 AM
  • This is still not answered unfortunately. I would like to display Cube values with rollup/totals whilst keeping the possibility to have filters in my report, as most reports with more than one region have I think.

    Anyone?
    Friday, March 6, 2009 3:51 PM
  • How about editing the MDX so that AS returns the totals for you.  Something like

    {Dim1.Hir1.Level1.members,Dim1.Hir1.[All]}*{Dim2.Hir2.Level2.members,Dim2.Hir2.[All]} on rows

    You can still format the totals differently by an expression detecting the All member

    Hope that helps,

    Richard
    For real-time OLAP and data mining demonstrations see http://RichardLees.com.au/Sites/Demonstrations
    Richard
    Friday, June 5, 2009 6:41 AM
  • Thanks for your effort in this to solve the problem.

    However, the totals already come from the MDX, it's just that SSRS does not seem to have any way to handle these totals and put them into the grid.

    It's getting tedious for the programmers to create custom code for detecting the totals line in the grid every time, not to mention the maintainability hit the reports get by implementing custom code every single grid in each report. Hence my question to find out if there is a robust build-in way to tackle this issue.

    So far I am disappointed regarding SSRS ;) All other tools seem to be great in the BI package though ;)
    Wednesday, June 17, 2009 8:04 AM
  • Exactly.  You really don't want to be looking for total lines in the RS report.  SSRS has all the functionality you would want for sub-totals and totals.  So don't include the totals (All members) in the MDX and as SR to group the data and put out footer lines with totals.

    That should make it very easy for you.

    Richard
    http://RichardLees.com.au

    Richard
    Wednesday, June 17, 2009 8:58 AM
  • Thanks for the answer.

    Still something is unclear to me. If I omit the total results in the MDX, and use SSRS for the total line, how would SSRS calculate the totals on percentage columns as they are mentioned in the start post?

    The percentage calculations are quite complicated, and would need about 6 to 8 base value columns. These calculations are not to be spread around multiple layers as this would increase the chance on errors or inconsistencies. I could ofcourse create calculated measures that present the total value for both main division members, but that would clutter my cube with measures that do not present any usuable end-value. Again, this would comprimise maintainability of the product. Both seem undesirable scenarios.

    I just want SSRS to display the totals as calculated by the cube, so I keep my single point of definition for the calculations. It seems the only way is to use scripts to detect the total, or split calculations into the presentation layer. In this particular behaviour SSRS falls short to other tools where Excel and BIDS for example can display cube totals just fine.
    Wednesday, June 17, 2009 10:37 AM
  • OK, I see your point.  I have done this both ways.  My experience was easier with AS just providing the raw data and having RS calculate the group summaries.  However, I did have an occasion when I needed to do what you are trying to do by receiving summary records from AS and format them as summaries.  I did this rather crudely by chaning the formatting based on the member caption.  If it is All, then you are at a summary record.  It might be more complicated for you with summary records at intermediate levels.  To help with this, you might have a calculated measure that returns the level numer or level name of the hierarchy.  Your report could use the level to determine if this row should be formatted as a total line.

    Here is the code to return the level number and name, if that helps you.

    with

     


      member measures.Level as [Client Host].[Client Geography].level.ordinal

      member

     

    measures.LevelName as [Client Host].[Client Geography].level.name

    Richard


    Richard
    Thursday, June 18, 2009 10:23 AM
  • Hi I am having the same issue. When i query it in mdx, i am getting roll up value. but when i dispaly the calculated members in ssrs, its summing up and giving me duplicate values. i am not getting proper percentage. Is there atleast a custonm code to resolve this.
    Thursday, December 24, 2009 6:46 AM
  • Just a shot in the dark here but I thought you might want to try it.

    Using RichardLees method to get the ordinal of the level, sort your results (in your mdx query) so that your totals are returned before your details.  then in ssrs, in your total fields, use the FIRST function instead of the SUM with a scope on your group.  this will return your total for that specific group.

    hope this helps!

    Thursday, May 3, 2012 11:08 PM