3

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.

thnkwthprtls
  • 3,287
  • 11
  • 42
  • 63
  • 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 Apr 11 '19 at 22:54

1 Answers1

2

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

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
  • Thanks for your answer, however I was not able to get this to work. I tried adding the _ rows from the query and was able to do so, but was not able to use this to create stacks and clusters, I'm still only able to use one or the other regardless of whether the _ values display – thnkwthprtls Apr 23 '19 at 12:30
  • I'm confused. The screenshot above matches what you sketched, and I've provided an RDL file as a working demo. The chart type is stacked column. – Mike Honey Apr 23 '19 at 19:34