Solved

How to avoid "Series1" showing in legend of a multi-page grouped stacked bar chart report (SSRS 2008)

Posted on 2014-07-30
5
1,053 Views
1 Endorsement
Last Modified: 2014-08-06
Hi,

I'll start by showing you the query behind the report:

SELECT     q.QuestionNumber, q.Abstract, a.AnswerNumber, a.AnswerText, resp.FinalScore, grp.GroupName
FROM         A_AssessmentInfo AS qs INNER JOIN
                      A_Question AS q ON q.AssessmentID = qs.ID INNER JOIN
                      A_Answer AS a ON a.QuestionID = q.ID LEFT OUTER JOIN
                      A_Response AS resp ON a.ID = resp.AnswerID LEFT OUTER JOIN
                      A_ProjectUsers AS pu ON pu.PersonID = resp.PersonID AND pu.UserResponseStatusID = 2 AND pu.AssessmentID = @QuestionSetID LEFT OUTER JOIN
                      [$Groups] AS grp ON grp.ID = resp.GroupID AND resp.GroupID IS NOT NULL
WHERE     (q.AssessmentID = @QuestionSetID)
ORDER BY q.QuestionNumber, a.AnswerNumber

Open in new window


The report displays 1 question per page, with a stacked bar chart for each possible answer.
All answers are shown; even those that were not selected.

The issue is that I see "Series1" (or 2 or 3) in the legend.
I understand why.  The above query gets ALL answers for ALL questions, and therefore gets ALL groups.
But some questions have answers that were not selected by a particular group; hence I get "SeriesN" in my legend.

Is there any way to avoid this?
If this were a single chart I suspect there would be no problem.
But having multiple charts, each on their own page, all based on the same query, is causing me grief.

Any suggestions would be greatly appreciated.

I've also attached a screenshot showing the issue.

Thanks in advance

Jim
Screen-Shot-2014-07-30-at-4.11.19-PM.png
1
Comment
Question by:jlj30
[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
  • 3
  • 2
5 Comments
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 500 total points
ID: 40233559
<<The above query gets ALL answers for ALL questions, and therefore gets ALL groups.
But some questions have answers that were not selected by a particular group>>
I think this is what is causing the problem. When I have a data set that looks like this:
1My chart looks like this:
2I 'solved' this by using
isnull(grp.GroupName, ' ')

Open in new window

in the query now the chart looks like this:
3Better but not perfect. I remember from you other question about this that you also want to assign a standard color to every group. When you use this technique you can assign white to the empty group so it won't be visible on the legend.
But a more elegant solution would be to make sure the result set would look like this:
4This can probably be done by replacing the groupname in the query with:
isnull(grp.GroupName,(select top 1 GroupName from [$Groups] order by ID)) as GroupName

Open in new window

0
 

Author Comment

by:jlj30
ID: 40234601
HI Nicobo,

Thanks for your reply.  You obviously put some thought and effort into it.

I get it and I like it.
It may mean a bit of a gap in the legend where the item with no text and a white fill occupies, but I think I can live with that.  I'll post a screenshot of my results once I've implemented this technique.

Thanks again

Jim
0
 

Author Closing Comment

by:jlj30
ID: 40234610
It was nice that Nicobo took the time to reproduce my scenario.
Although I have yet to implement the suggested technique, I have confidence in the approach.
Jim
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 40234689
<<It may mean a bit of a gap in the legend where the item with no text and a white fill occupies>> Yes, that's the disadvantage indeed. And notice that it is not 'no text' but a space when you use '' you'll still have the same problem but with ' ' it will work.
I don't know your data but if the group General is almost always present for example you might consider my final suggestion for a solution. And it should also be possible to write a (sub)query that returns one of the groups that is present for an answer that does occur. So if you have one question with groups x and y it returns x for the 'empty' answers, and if for another question you have group q and z it returns q for the empty answers.
0
 

Author Comment

by:jlj30
ID: 40244205
Hi Nicobo,

I may be straying into "new question" territory here, so please let me know if I should be submitting a new post.
I've taking your latest advice and assigned a "used group" to all empty answers.
This has solved my original problem - avoiding "Series1" appearing in the legend.

I have also stored the group colour in the database, so that is now retrieved as part of my query and used in the series Fill expression.  This too is working as you can see by the bar colours in the attached chart.

My problem now is the legend seems to have the correct colour for the first group in the legend, but all of the others are black!  See the attached screenshots that show the data that is being returned by my query, and a chart of the question.  As you can see, the SME group was assigned to the NULL answer and with the same colour assignment.

Any thoughts on this issue?

Thanks for all of your assistance.

Jim
Screen-Shot-2014-08-06-at-12.54.23-PM.pn
Screen-Shot-2014-08-06-at-1.02.17-PM.png
0

Featured Post

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

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

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 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