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
967 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
  • 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

777 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