SSRS - dynamically restructure dataset for chart

Question

I'm trying to build a bar char to show how which drugs are being used to treat certain cases. This is the shape of the data I need to get the chart type I desire.

drug    case 1  case 2   case 3
X       100     300      250
Y       200     150      150
Z       300     50       300

The problem is that the data is held in the actual dataset like stored like below because each row in the table is for a different report.

 X case 1   X case 2    X case 3    Y case 1    Y case 2    Y case 3    Z case 1    Z case 2    Z case 3
100         300         250         200         150         150         300         50          300

I've tried using the data in this format but it produces a confusing and unpleasant graph. As this report is going to be run against all the rows of my data source I need to be able to dynamically restructure itself (or create a secondary data source) to allow this to happen.

Is this actually possible in SSRS?

Answer 1

I would do this in SQL.

Option1

If you can go back to the raw data that makes up the dataset you described, use that data in the format

[DrugName] [CaseNumber] [Value]
   X             1       100
   X             2       300
   X             3       250
   Y             1       200
   Y             2       150
   Y             3       150
   Z             1       300
   Z             2        50
   Z             3       300

Your chart can just the case and drug as series and category (or the other way around perhaps)

Option2

If you can't get to the raw data then you will have to UNPIVOT the dataset you have (I'm assuming SQL Server here..)

Look at the UNPIVOT operator

https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Once you have unpivoted it into something like this...

[DrugCaseNum] [value]
'X Case 1'     100
'X Case 2'     200
etc...

then you could use this as a subquery and split the DrugCaseNum column into its constituent parts.