Search code examples
ssasmdxperformancepoint

Inconsistent roll up behavior in a PerformancePoint grid object

I have a basic dashboard which consists of a single "grid" object displaying 3 measures across time broken up by a business unit hierarchy...

When filtering on a single item in any of the levels in the business unit hierarchy, everything rolls up correctly...

enter image description here

However, when I filter on more than one item in the same level, the rolled up values are no longer based on visual totals...

enter image description here

In the image above, the values in the "All" row represent values for the entire unfiltered business unit hierarchy. It would be ideal if the grid rolled up as if "VisualTotals" was enabled...regardless of the filter selection. In that case, for the second image above, I would expect the values in the "All" row to look like...

PO E-PO Count = 3531     (497 + 3034)
PO Count =      7923     (2094 + 5829)

I've considered using the VisualTotals() MDX function, but I'd prefer not to customize the query because I read somewhere that it reduces the end users' ability to dill up/down/across.

Solution

  • Turns out this is just how PPS works. I ended up using profiler to capture the MDX being generated in both scenarios and PPS uses a calculated member to aggregate the multi-select (instead of using SUBSELECTs like Excel) so when the ROWS level is pieced together with the HIERARCHIZE function, you get the effect of non-visual totals...

    WITH MEMBER [Business Unit].[Segment Division Plant].[ Aggregation] AS 
Aggregate
(
  {
    [Business Unit].[Segment Division Plant].[Plant].&[PAK]&[FLG]&[2520]
   ,[Business Unit].[Segment Division Plant].[Plant].&[PAK]&[FLG]&[2504]
  }
) 
,SOLVE_ORDER = 0 
SELECT

...<snip>...

    Hierarchize
    (
      {
        [Business Unit].[Division Plant].[All]
       ,Descendants
        (
          [Business Unit].[Division Plant].[All],
         ,AFTER
        )
      }
    ) ON ROWS

...<snap>...

    If it used a SUBSELECT like Excel the behavior represent VisualTotals and the MDX would look like the following...

      FROM 
  (
    SELECT 
      {
        [Business Unit].[Segment Division Plant].[Plant].&[PAK]&[FLG]&[2504]
       ,[Business Unit].[Segment Division Plant].[Plant].&[PAK]&[FLG]&[2520]
      } ON COLUMNS
   FROM [<cube name>]
  )