mr_patel
asked on
SSRS 2008 Average Column in Column Chart
Hi all, is it possible to display an Average Column in the Column chart in SSRS 2008? I have range of scores (300-310,311-320,321-330, etc.) on the X-axis and number of students on the Y-axis. I need to display a vertical line in different color for the Average score. I tried the Moving average but that's not what I need.
Any help is appreciated,
Thank you.
Any help is appreciated,
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks itcouple for the suggestion. I'll give it a try and let you know.
ASKER
itcouple, that worked like a charm!! Thanks a lot for the suggestion. But now it created another issue. The PointWidth for the bar width doesn't work anymore! As soon as I remove the UNION from the query, the bar width is fine, but with the UNION, it just draws one thin line, no matter what the PointWidth is. Any ideas? Thanks again.
Hi
Can you attach screenshot. I want to see how it looks maybe I will use it for my reports :)
Regarding the widht issue. Make sure your data type is correct when using UNION and expression does explicit conversion. If that doesn't help paste your expression here and I'll try to help.
Regards
Emil
Can you attach screenshot. I want to see how it looks maybe I will use it for my reports :)
Regarding the widht issue. Make sure your data type is correct when using UNION and expression does explicit conversion. If that doesn't help paste your expression here and I'll try to help.
Regards
Emil
ASKER
I've attached the screenshot. Here is my query:
SELECT ScaledScore, ScaledScore - ScaledScore % 10 + 5 AS Range, COUNT(*) AS count,0 As IsAverage
FROM vApplication
WHERE (RouteID = 'NG') AND (ExamDate >= @DateFrom) AND (ExamDate <= @DateTo)
GROUP BY ScaledScore, ScaledScore - ScaledScore % 10
UNION
SELECT Avg(ScaledScore) As ScaledScore, Avg(ScaledScore) AS Range, COUNT(*) As count, 1 As IsAverage
FROM vApplication
where schoolID = @SchoolID And (RouteID = 'NG') AND (ExamDate >= @DateFrom) AND (ExamDate <= @DateTo)
Untitled.png
SELECT ScaledScore, ScaledScore - ScaledScore % 10 + 5 AS Range, COUNT(*) AS count,0 As IsAverage
FROM vApplication
WHERE (RouteID = 'NG') AND (ExamDate >= @DateFrom) AND (ExamDate <= @DateTo)
GROUP BY ScaledScore, ScaledScore - ScaledScore % 10
UNION
SELECT Avg(ScaledScore) As ScaledScore, Avg(ScaledScore) AS Range, COUNT(*) As count, 1 As IsAverage
FROM vApplication
where schoolID = @SchoolID And (RouteID = 'NG') AND (ExamDate >= @DateFrom) AND (ExamDate <= @DateTo)
Untitled.png
Hi
I cannot see anything wrong with the query. Do you use expressions on the chart?
Regards
Emil
I cannot see anything wrong with the query. Do you use expressions on the chart?
Regards
Emil
ASKER
No, the only expression I have is in the Color. I also tried restoring the report from a backup and added the UNION, but no luck.
Hi
Try removing expression in the colour and see if it changes the behaviour. If it doesn't then try to do union with different figures to see if makes any difference. I'm afraid in this case it might be that you will have to find 'lower level' issue to try to resolve it. Also without union try to see if X or Y scale changes.
Regards
Emil
Try removing expression in the colour and see if it changes the behaviour. If it doesn't then try to do union with different figures to see if makes any difference. I'm afraid in this case it might be that you will have to find 'lower level' issue to try to resolve it. Also without union try to see if X or Y scale changes.
Regards
Emil
ASKER
I'll try that. Thanks for the suggestions.
I just read again your request and noticed you want a line.... you can get it on Y axis (horizontal line) but vertical would be rather difficult to do as it doesn't belong "naturally" to the graph. You should be still able to do that but adding extra row (see above solution) and changing the width of the bar. It should be possible (not 100% sure) to do using expressions or programmatically. But I suggest to consider option 2.
Regards
Emil