Show Total In SSRS Chart

Question

I am creating a line chart from an Analysis Services cube, with a date category, a count data field, and a status series. Here's a screenshot to explain:

picasaweb.google.com/lh/photo/fP16V4sB18O1xSTrdDV-_A?authkey=Gv1sRgCLHRmcjwtI2mzAE

I want to add the blue total line, which sums all the statuses together.

I don't want to add a "Total" member to the Status dimension, because that wouldn't make sense. I've tried adding another data field and scoping it to sum everything, but I can't figure out how to make the series field only apply to a single data field—so this ends up duplicating all the statuses and getting 8 lines instead of 5.

This should be possible... I don't want to resort to writing SQL against the underlying database.

Answer 1

Alright so I figured out a reasonable solution in MDX. I think it would be better solved in SSRS, but I haven't figured that out yet.

The trick is to add the [Total] member to the result set, instead of adding it to the actual Dimension. Simplified MDX is here:

WITH 
MEMBER [Execution Status].[Execution Status].[Total] AS 
    AGGREGATE([Execution Status].[Execution Status].[Pass].Siblings)

SELECT 
    {[Measures].[Count]} ON COLUMNS,
    {[Execution Status].[Execution Status].Children,
     [Execution Status].[Execution Status].[Total] } ON ROWS

FROM [CUBE]

This defines an additional member for Total which aggregates all the Statuses. Then all the Status members as well as the Total are returned. SSRS doesn't care--it still groups by the new set.

I did have some issues with getting the Aggregation to work, which is why I'm using [Pass].Siblings. [Execution Status].Children always returned (null), and [Execution Status].[All] returned 2x the correct answer, presumably because of the [All] member. I could manually enumerate all the statuses as well. Still not sure what the issue there is...