SSRS 2012 - Creating a Stacked and Clustered Bar Chart

3
votes

Suppose I have a table of sales from a car dealership with the following columns:

Salesperson

VehicleType (Can be "car" or "truck")

SaleType (Can be "basic" or "luxury")

Using SSDT2012, I would like to make a bar chart displaying the sales stored in this table. I would like the data grouped on the X-axis by Salesperson. For each Salesperson grouping, I would like 2 clustered columns, each representing a total count of sales for each VehicleType. I would like each of these columns to also display stacked data for how much of those totals were each SalesType. Essentially I would like the chart to look like below:

enter image description here

(Please note the text labels in the bars are for clarification purposes and do not need to be displayed on the actual report)

I know ways to display this data as 4 clustered columns, and as 1 stacked column, but cannot find a way to do this. I found one work-around involving setting the second series to use the secondary axis, but in the real report I am creating that is already being used by something else so I am unable to do that. I also tried the solution from another SO post here: SSRS BIDS clustered stacked bar chart but as the poster mentions in the comments there the formatting does not match what I need. If anyone knows of a way to accomplish the above, or even whether it simply can't be done in SSDT2012, I'd greatly appreciate it.

1
reporting-servicesreportingssrs-2012sql-server-data-tools
I don't see how you can do it - it seems like you'd need an extra grouping. What about creating a MATRIX based on salesman by column (group row on 1) and repeating the chart for each next to each other? The bad part would be an axis for each. – Hannover Fist

1 Answers

2
votes

I would tackle this mainly by faking extra "Salesperson" entries in the dataset. For your sample data I would add a row for:

Salesperson= "A_" | VehicleType = "car" | SaleType = "basic" | Sales = 0

Then you just need to suppress the labels for those "_" entries. You can do that with Expressions on the Category Group Labels, e.g.

=If ( Fields!Salesperson.Value.Contains("_"),"", Fields!Salesperson.Value )

=If ( Fields!Salesperson.Value.Contains("_"),"", Fields!VehicleType.Value)

I've shared a demo for this, it's in my Demo folder, file: Report Builder demo - Stacked and Clustered Bar Chart.rdl

https://1drv.ms/f/s!AGLFDsG7h6JPgw4

Stacked and Clustered Bar Chart