July 21, 2011 at 1:51 am
Hi All
Im trying to add an average line from a set of data in BIDS 2008 but having difficulty.
when I put in the expression in the data fields avg(Field!Fieldname) it just gives me the average of each data point rather than summing all the data and dividing it by how many data rows there is. For example the data average I have at the moment is 28 therefore I would like one straight line to show marked at 28. I tried using sum(avg(field!Fieldname)) but it just gives me an error that you can not use running total with aggregated functions.
Therefore I thought to add the sum of the field in the query(dataset) then I can just use AVG function. But Query builder doesnt like SUM function.
Any ideas?
Thanks
R
July 21, 2011 at 2:42 am
Ive tried so many different things, but BIDS keeps erroring.
I have the average value as a text box on the matrix table. So I have tried as a data field in the chart
=Reportitems!textbox6
I get the error
"The Y expression for the chart ‘Chart1’ refers to the report item ‘Textbox6’. Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."
If someone can tell me how to use SUM function with the query then I think this will help me get further, as at the moment it errors saying "Operand data type varchar is invalid for sum operator."
Or help me reference the text box it would be a great help
July 21, 2011 at 6:02 am
Have you tried just creating the average in an expression rather than using the AVG function?:
SUM(Field!Fieldname) / COUNT(Field!Fieldname)
With AVG function usually not working the way I want I have gone this route many times in reports (although not in charts).
July 21, 2011 at 6:51 am
Yes I have, but this still gives me the average for each indvidual value rather than the whole value.
Thats is why I want to add a sum field in the query so it sums this first
July 21, 2011 at 8:50 am
To get around the "Operand data type varchar is invalid for sum operator" error, cast the value to integer prior to SUM:
SUM(CINT(Fields!FieldName.Value))
July 22, 2011 at 1:20 am
Thank you, but Im wanting the sum in the Query not in an Expression or calculated field, as the charts doesnt allow calculated fields as the data field.
I did try CINT anyway in the query but I got the error ''CINT' is not a recognized built-in function name.'
July 22, 2011 at 1:23 am
OK I have managed to added the Average line. After much research I had to add Calculated series and select Mean from the drop down menu.
How ever my next task is to get the AVG+SIG line on the chart. Which is the forumla:
=Round(Avg(Fields!NAME.Value)-3*StDev(Fields!NAME.Value),1)
Does anyone know how I can get this forumla on the chart?? I have tried just adding the forumla but again it does it for each data value rather the data as awhole.
July 22, 2011 at 3:01 am
Does anyone know how to add custom forumal to calculated series on a chart?
August 29, 2011 at 2:58 pm
rkaria (7/22/2011)
OK I have managed to added the Average line. After much research I had to add Calculated series and select Mean from the drop down menu.How ever my next task is to get the AVG+SIG line on the chart. Which is the forumla:
=Round(Avg(Fields!NAME.Value)-3*StDev(Fields!NAME.Value),1)
Does anyone know how I can get this forumla on the chart?? I have tried just adding the forumla but again it does it for each data value rather the data as awhole.
I think what you're looking for is something like this:
=Round(Avg(Fields!NAME.Value, "DataSet1")-3*StDev(Fields!NAME.Value, "DataSet1"),1)
That will tell it to calculate the Average for Dataset1 and the Standard Deviation for all of DataSet1 (substitute your dataset name for "DataSet1").
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply