Help Adding a calculated field in a SSRS chart

Jason Yousef
Jason Yousef used Ask the Experts™
on
Hello all,
I'm working on a report to get a projection for some data, I need to add some data to the chart which is a matter of drag and drop for any data field in the existing dataset, but what about a calculated field?? how can I do it?

when I try to add a calculated field to the dataset, it gives error "The expression used for the calculated field '=Sum(Fields!Total2010.Value)/DateDiff("d", "01/01/2010", Parameters!Day.Value)*365' includes an aggregate function. Aggregate functions cannot be used in calculated field expressions"


and I can't find a way of adding it directly to the chart

any help will be appreciated please.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
When you generate calculated fields, the current calculated information is normally derived in the current row information within the dataset.  Based on your calculated field, you are trying to add all Total2010 value for all rows in one cell/calculated field location and that is why you are getting that error.

Instead of using a calculated field within a dataset, why don't you just deal with the aggregation outside of the dataset (i.e. using other controls (e.g. textbox) within the Report Designer to contain the calculated result so that aggregation will work)?  Once you have the required value within these controls, you can then consume this in your SSRS chart through its properties or expressions.

Jason YousefSr. BI  Developer

Author

Commented:
Hi Alfred,
Thanks for your reply..
Yep i'm doing that, i'm displaying the calculated field in a text box, but now I need to add it to the chart, and it's not allowing me..

and can't seem a way of doing it through SQL...
By the way, since you are using a chart (is this 3D chart?), you can actually generate a derived column out of Total2010 aggregation.

I am not sure what exactly you are doing but for example, in its Series Data you can aggregate the values by summing up the field value using the Series Properties of the Chart Control.  Can you provide more information on what exactly you are trying to do?
Also, check the following link below.  It has some discussion on using aggregated data used outside of the dataset in a chart control.  I hope this helps.

http://www.codeproject.com/KB/vb/MicrosoftReports.aspx
Jason YousefSr. BI  Developer

Author

Commented:
I didn't know that,Thanks for your help :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial