Chart Data from a calculated field
Hello,
I have a set of data set out in a matrix report and I am charting with a line graph.
The problem I am running into is getting the value I want charted to display. It is not a data source value but a value in the column group that is calculating a percentage of that current value to the rows total.
the table is kind of like this.
_ Company _
Month: | Total | Percent|
Instead of showing the value for that series I want to display the percentage that was calculated.
In the Data->Values Expression field I have tried a series of things to no avail.
Is there anyway to accomplish this or would I have to aggregate all the values and feed this into a SQL column before designing the report?
May 11th, 2011 7:03pm
I'd recommend adding the calculated field to your dataset, SQL Server is much better at this than Reporting Services.
Free Windows Admin Tool Kit Click here and download it now
May 11th, 2011 9:22pm
Hi,
Generally, it is also possible to display calculated values in the chart.
For example, I have a dataset as the following table shows:
Category
Year
Order Quantity
Accessory
2001
xxx
Accessory
2002
xxx
…
…
…
Bike
2001
xxx
Bike
2002
xxx
…
…
…
Then, I added a matrix which is similar to the one you posted. I specified the Year field as the row group and specified the Category field as the Column group.
Year
[Category]
[Year]
[Sum(OrderQuantity)]
=Sum(Fields!OrderQuantity.Value) / Sum(Fields!OrderQuantity.Value, "Year")
In order to display the percentage in a chart, I added a chart, specified the Year field the Category Groups and specified the Category field to the Series Groups. After that, I
specified the data values of the chart to the expression like
=Sum(Fields!OrderQuantity.Value) / Sum(Fields!OrderQuantity.Value,"Chart1_CategoryGroup")
Please note the scope name “Chart!_CategoryGroup” indicates the Year group. Please change it based on you report.
When the report is rendering, it would look like
If anything is unclear, please feel free to ask.
Thanks,
Tony Chain
Tony Chain [MSFT CSG] | Microsoft Community Support
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
May 18th, 2011 3:33am