Change chart type of one cube measure value within same chart
I've tried to sum up the question as best as possible in the title! Using SSRS 2005 with SSAS 2005 also... I have a report, with one dataset connecting to a cube. The report contains one measure, broken down by one dimension value. Lets say the measure is called 'Inc' and the possible breakdown from the dimension (IncType) gives us 'A', 'B', 'C', 'D': IncType Inc A 1 B 2 C 3 D 4 I have this table on the report, with one user parameter to allow the user to select a year (the dataset is broken down by month also but I haven't illustrated that to save space). Underneath the table, I also have a bar chart, which points at exactly the same things. My question is, how can I change the chart style, where the IncType is 'A' to a LINE style graph within the same chart? In Excel, you can right click on one value in the series, and change it's type... I've managed to EXCLUDE 'A' using a filter but this defeats the object, but I need to show just the 'A' values as a line... Hope this makes sense, thanks for any help. Cheers Lee
November 22nd, 2010 6:56am

Can you confirm my understanding first - You want a bar chart which wiull show the summed up values (B+C+D) for each montha and you want a line chart showing A for each month in the same graph OR you want a stacked bar chart for B,C,D and a line chart for A OR you want a normal bar chart for B,C,D (so for each month, 3 bars would be there) and a line chart for A First 2 options should be possible by tweaking your query, but not the third one,Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
Free Windows Admin Tool Kit Click here and download it now
November 22nd, 2010 7:40am

Hi Jason Second option...all within the one chart. Interesting how the third option wouldn't be possible though, when it's the same object but of a different style? Cheers Lee
November 22nd, 2010 8:18am

Lee, you caught me there. The third option is also possible, very m,uch the same way as of the second option :) This is a workaround and hence will have it's own shares of problems:- 1) Modify your query like shown below with member [Measures].[Sales_A] as ([Dimension].[IncType].[A],[Measures].[Sales]) set [BCD] as {[Dimension].[IncType].children}-{[Dimension].[IncType].[A]} select non empty {[Measures].[Sales_A],[Measures].[Sales] } on 0, non empty {[BCD]*[Time].[Month].children } on 1 from [CubeName] 2) Now put the 2 measures as the data and the month in the category and the [BCD] in the series of the column chart. 3) Make the chart as a hybrid line chart (Go to the data values, select properties of Sales_A and should be able to see an option - plot data as line). This option is there for only column charts. Now you should be able to see a line chart for A and the column charts for B,C,D (can make it stacked column chart for option 2 and leave it like it is for option 3) Now, the poroblem would be that the legend would be messed up. You can delete the legend and make your own custom legend using a custom palette. Should be able to find some help for this part in the later section of this blog http://road-blogs.blogspot.com/2010/06/display-total-on-top-of-stacked-chart_8589.html Note : I dont have a system with me to test this out now, and am telling it from memory. So forgive if there are any syntax errors. This shoudl work but if not, let me know and I shall try this at home too. Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
Free Windows Admin Tool Kit Click here and download it now
November 22nd, 2010 9:45am

Many thanks Jason for this - I think I see what is happening here, I just need to decide whether it's worth us implementing (the legend is quite important in this case). It's opened up some nice options however... Thanks again Lee
November 23rd, 2010 4:42am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics