Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SSRS 2008 Average Column in Column Chart

Posted on 2010-08-30
10
Medium Priority
?
1,126 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:mr_patel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 10

Accepted Solution

by:
itcouple earned 1000 total points
ID: 33567134
Hi

Yes it is possible but do you want to do that? Basically you just add an extra row (you might use union in your dataset) which will display 'average range' with an here is the issue: How many students do you want to show for this average???

Alternative approach is to highlight 'average' range so it is different colour. In this case you need to identify in your dataset row which represent average and flag using new field IsAverage (true/false) if false then standard colour else average colour.

Let me know if this suggestions will work for you.

Regards
Emil
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33567210
Hi

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
0
 

Author Comment

by:mr_patel
ID: 33567338
Thanks itcouple for the suggestion. I'll give it a try and let you know.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:mr_patel
ID: 33568891
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.
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33571198
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
0
 

Author Comment

by:mr_patel
ID: 33571341
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
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33571546
Hi
I cannot see anything wrong with the query. Do you use expressions on the chart?

Regards
Emil
0
 

Author Comment

by:mr_patel
ID: 33573550
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.
0
 
LVL 10

Expert Comment

by:itcouple
ID: 33574334
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
0
 

Author Comment

by:mr_patel
ID: 33576382
I'll try that. Thanks for the suggestions.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question