SSRS: Can I create a chart out of ReportItems?

0
votes

I'm working in SSRS, and I have a calculation that creates a percentage difference between two other cell references (which are themselves generated from a stored procedure I've written).

=Format((ReportItems!CCM01.Value-ReportItems!PCM01.Value)/ReportItems!PCM01.Value,"P1")

In fact, I have 12 of these (because this report is for a rolling 12-month period), CCM01-12 and PCM01-12.

Is there a way to use those calculated cells to generate charts? Or a way to use a series of formulas to generate a chart? Or am I going to have to go back to the stored procedure and write a formula to generate items in the DataSet that I would then use to populate the chart?

1
sql-serverreporting-servicesssrs-2012
If you can aggregate these values from the dataset in a Tablix (I am assuming these are probably textboxes on a totals line) then you should be able to use the same dataset in the chart to do what you want. But without some example data it is difficult to be certain. – David Tansey
Take a look at this post: jontjones.blogspot.com/2011/05/… -- although it is rather brief as an example. – David Tansey

1 Answers

2
votes

I don't think you can because it will give an error about report item expressions only allowing report items within the same grouping scope. In any case, there are better ways to achieve this.

Firstly, you don't want to be doing this:

=Format((ReportItems!CCM01.Value-ReportItems!PCM01.Value)/ReportItems!PCM01.Value,"P1")

because this will return a string which is hard for doing calculations without casting back to a double. So your expression should be:

=(ReportItems!CCM01.Value-ReportItems!PCM01.Value)/ReportItems!PCM01.Value

and put the formatting into the Format property; set that to P1.

But really, it is easier to calculate on the fields than the report items:

=(Fields!CCM01.Value-Fields!PCM01.Value)/Fields!PCM01.Value

which we can make part of the dataset by adding these expressions to the dataset fields collection. So you would make 12 calculated fields (right-click dataset, Add calculated field) based on the calculation above, lets call these PCD01 to PCD12.

Now the fields are in the dataset, they are easy to add to the table and we can chart them. Ideally we would unpivot the data so instead of working with 12 columns of each of the three data points in one row, we would have three data points and 12 rows - then your table and chart would be very simple and you would only need the one calculated field. However, lets continue with what we have.

Add a chart to your report. In the Chart Data panel, at the top section Ʃ Values press the + sign and add each of PCD01 to PCD12.