Create a Pie Chart from single row, multiple column dataset in SSRS

1
votes

I'd like to create a pie chart from a dataset that returns a single row with multiple columns in SSRS. Is this even possible?

I've tried unpivoting the data so I can have multiple rows, and I can't quite pull it. It seems I can't unpivot aggregated data.

So I'm not sure what my next step is. Should I try to unpivot the data, or can I build an expression for the values in the pie chart?

Here's sample data:

ID   |  IndivMonthSales  |  IndivBudget  | IndivDifference
PAW     $30,456.31          $60,000.00     $29543.69

The result I'm looking for is the Pie chart to use the values from IndivMonthSales and IndivDifference (which add up to 100%)

I guess there's 2 questions:

  1. is it possible to unpivot aggregated data (if so, how?)
  2. is it possible to use expressions for Pie Chart values (if so, how?)

I'm very grateful for any and all help.

Cheers!

Dave

1
sql-serverreporting-serviceschartsssrs-2008ssrs-2008-r2
If you are always going to have the same results from you dataset then I would modify it simply to return the two value you want as rows. something like /* first just emulate your current dataset / SELECT 30456.31 as IMS, 60000 as IBudget, 29543.69 as IDiff INTO #t / now the poor man's pivot!*/ SELECT 'IMS' AS Caption, IMS AS Value FROM #t UNION SELECT 'IDiff' AS Caption, IDiff AS Value FROM #t for example, then just drop the value into the data fields section and caption into either the series or category fields sections. - Alan Schofield
Thanks for this, I have huge trouble with unpivoting this data as it is aggregated. - David Connelly

1 Answers

2
votes

I believe what you are asking for can be very simply solved by placing both measurements as values in the pie wizard:

enter image description here

Resulting in :

enter image description here