add an all/total column to a bar chart

0
votes

I have a bar chart in SSRS that is showing data drawn from SSAS, the data looks like:

name: value: Target: Calculated%

Product 1: 10: 100: 0.1:

Product 2: 15: 30: 0.5:

where calculated% is a calculated member in the query - ((100/target)*value)/100

the chart shows calculated% as values on Y, and name on X axis.

I need to add a column that is the total of the other columns, so if returned from the query it would look like:

Products Total: 25: 130: 0.19

The thinking behind this is the chart will show how near each product is to its individual target - as a percentage - whilst also showing how near the overall target all products are.

any idea how I can either get the chart to add this total column or alter the query to return the extra row? seems quite easy to do with SQL but this uses MDX and I'm not very familier with that.

Code;

WITH MEMBER [Measures].[PercentOfTarget] AS ((100/ [Measures].[Value])*[Measures].[Enquiry Count])/100 SELECT NON EMPTY { [Measures].[PercentOfTarget], [Measures].[Strech Target Value], [Measures].[Value], [Measures].[Enquiry Count] } ON COLUMNS, NON EMPTY { ([Industry Sector].[Reporting Sector].[Reporting Sector].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@StageStartDateFinancialYearName, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Industry Sector].[Reporting Sector].&[Advanced Engineering], [Industry Sector].[Reporting Sector].&[Business Professional And Financial Services], [Industry Sector].[Reporting Sector].&[DigiMedia], [Industry Sector].[Reporting Sector].&[Food And Drink], [Industry Sector].[Reporting Sector].&[IT], [Industry Sector].[Reporting Sector].&[Life Sciences], [Industry Sector].[Reporting Sector].&[Logistics] } ) ON COLUMNS FROM ( SELECT ( { [Enquiry Type].[Enquiry Type].&[Project] } ) ON COLUMNS FROM [Invest Cube]))) WHERE ( [Enquiry Type].[Enquiry Type].&[Project], IIF( STRTOSET(@StageStartDateFinancialYearName, CONSTRAINED).Count = 1, STRTOSET(@StageStartDateFinancialYearName, CONSTRAINED), [Stage Start Date].[Financial Year Name].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
1
reporting-servicesssasmdx
Can you add the MDX that you have to the question? – Wayne Maurer
Added Code, Cannot make it format nicely - pointers welcome :) – Stuart

1 Answers

1
votes

The individual product rows are specified by the following line:

{ ([Industry Sector].[Reporting Sector].[Reporting Sector].ALLMEMBERS) }

The query provided is complex, querying from sub queries; I've never written an MDX query in this way, but maybe you could try the following:

{ [Industry Sector].[Reporting Sector].[Reporting Sector].ALLMEMBERS, [Industry Sector].[Reporting Sector].[Reporting Sector].All  }

Hopefully adding the 'All' member to the set will provide the total row you want.