locked
PerformancePoint Analytical Grid - Add Grand Total Columns/Rows RRS feed

  • Question

  • I cannot get the syntax correct to add totals to my analytic grid. I need a total that sums each column (by location) and a total that sums each row (by day). Please help! auto generated mdx query is below - currently does not include any changes for the totals.

    SELECT
    HIERARCHIZE( { [Dimension1].[Division].&[A] } ) * { [Dimension1].[Location].[Location].ALLMEMBERS }
    ON COLUMNS,

    { [Date].[Date].[Date].ALLMEMBERS }
    ON ROWS

    FROM [Ticket]

    WHERE ( [Measures].[Avg MTD Daily Revenue], [Date].[Month].&[2012-09-01T00:00:00] )

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FONT_FLAGS, FORE_COLOR, BACK_COLOR

    Thursday, October 4, 2012 9:11 PM

All replies

  • Here is an example of doing something like that going against the adventure works using HIERARCHIZE and POST to get the totals at the end.

    SELECT
    NON EMPTY Hierarchize({DrilldownLevel({[Geography].[Geography].[All Geographies]},,,
    INCLUDE_CALC_MEMBERS)}, POST) ON COLUMNS,
    NON EMPTY HIERARCHIZE({DrilldownLevel({ [Date].[Date].ALLMEMBERS },,,INCLUDE_CALC_MEMBERS)}, POST) ON ROWS
    FROM [Adventure Works]
    WHERE ( [Measures].[Reseller Sales Amount], [Date].[Month].&[2008]&[3] )
    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FONT_FLAGS, FORE_COLOR, BACK_COLOR

    There are other ways to get at a total amount by using aggregates as well like this post - Grand Total Field in Analytic Grid Report

    Don't forget you could also use an Excel Services file to do the same thing and include that in your dashboard:)


    Dan English's BI Blog

    Friday, October 5, 2012 11:30 AM
  • thanks, but i'm still having trouble getting the syntax right in my mdx query. any assistance? it looks like all that i should have needed to add was "POST", which i assume helps with the ordering, but i need to get the actual total column in the first place.
    • Edited by kayluhh Friday, October 5, 2012 7:06 PM
    Friday, October 5, 2012 6:56 PM
  • Okay, so using your MDX try the following (I included NON EMTPY as well, that is optional):

    SELECT
    NON EMPTY HIERARCHIZE( { [Dimension1].[Division].&[A] }  * { [Dimension1].[Location].ALLMEMBERS },POST)  ON COLUMNS,
     
    NON EMPTY HIERARCHIZE( { [Date].[Date].ALLMEMBERS }, POST) ON ROWS
     
    FROM [Ticket]
     
    WHERE ( [Measures].[Avg MTD Daily Revenue], [Date].[Month].&[2012-09-01T00:00:00] )
     
    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FONT_FLAGS, FORE_COLOR, BACK_COLOR


    Dan English's BI Blog

    Friday, October 5, 2012 7:57 PM
  • That was the change that I made previously. It looks no different. There are still no Total columns/rows anywhere. Is there something else that needs to be added? I just don't understand what POST is supposed to do. I need something that sums up each field in each row.
    Friday, October 5, 2012 8:43 PM
  • The POST in this case puts the Totals at the end versus being the first row.  This is the ALL member.  Review the MDX code and compare yours to mine, there must be differences.  Pay particular attention to the depth you are going with the dimension references, you were going to far and missing out on the ALL member.

    Based on my example with Adventure Works that I previously displayed here is the screenshot and I highlighted the totals that get included on the Rows and Columns.  If this does not work for you then I would highly recommend doing this in Excel and using Excel Services in your dashboard.


    Dan English's BI Blog

    Friday, October 5, 2012 9:15 PM
  • Thanks for the explanation. I got it to work. Is there a way to change the title of the column?
    Friday, October 5, 2012 9:38 PM
  • That will simply reference your ALL member name.

    If you don't like this output I would highly recommend just using the Excel option or you can always use Reporting Services.

    Unfortunately there isn't just an easy option to enable like there was with ProClarity:)  Power View tables will have totaling capabilities:D


    Dan English's BI Blog

    Friday, October 5, 2012 9:44 PM
  • Do you have experience with Excel Services? I added that as a last resort, but I'm trying to pass a PerformancePoint filter to a parameter on the Excel file but it gives me an error "Attempted request on an invalid state. Unable to perform the operation." I was hoping I could just do the MDX and not deal with this.
    Friday, October 5, 2012 9:49 PM
  • I have a blog posting you can review which walks you through the steps using Excel Services with PerformancePoint and setting up parameters.

    Using Excel Services Reports with PerformancePoint Server (PPS)

    At the end of the posting is a PDF that you can download as well for reference.


    Dan English's BI Blog

    Friday, October 5, 2012 9:52 PM
  • I could probably live with the "All" name (or rename it in the dimension) if there is a way to change the hierarchy so that the "All" sits on the same line as my locations (A, B, C....). Is this possible?


    • Edited by kayluhh Friday, October 5, 2012 10:22 PM
    Friday, October 5, 2012 9:53 PM
  • In the Edit tab in the DD ribbon you can change the Layout from compact to tabular in the View section. This only works for the Rows though, not the columns.

    If you want more control over it then I would just suggest using Excel or Reporting Services. By using the custom MDX query you lose all interaction with the analytical report, so not a big deal if you switch technologies, probably gain more in the end.


    Dan English's BI Blog

    Tuesday, October 9, 2012 1:30 AM
  • I had to change my MDX query, and now the post isn't working as previously thought. I am trying to get the sum of all the rows as well as all of the columns. MDX query is below. Screenshot for reference to the output. Can you help? I saw something about a Row_Sum/Column_Sum w/ the AGGREGATE function. Know anything about that?

    SELECT
    NON EMPTY HIERARCHIZE( { DESCENDANTS( [Dimension1].[Div].[All], [Dimension1].[Div].[Div] ) } ) * HIERARCHIZE( { DESCENDANTS( [Dimension1].[Location].[All], [Dimension1].[Location].[Location] ) } )
    ON COLUMNS,

    HIERARCHIZE( { DESCENDANTS( [Date].[Date].[All], [Date].[Date].[Date] ) } )
    ON ROWS

    FROM [Cube]

    Wednesday, October 10, 2012 5:28 PM
  • The reason you are losing the totals or the 'All' is because of the DESCENDANTS function.  You need to include additional option at end of that like SELF_AND_BEFORE to get your 'All' member included again or simply use the DrilldownLevel like I displayed previsouly.  You need the 'All' member or you will not get your totals, so review how you are pulling the members.

    Dan English's BI Blog

    Wednesday, October 10, 2012 6:33 PM