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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics