• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1188
  • Last Modified:

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

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
jlj30
Asked:
jlj30
  • 3
  • 2
1 Solution
 
Nico BontenbalCommented:
<<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
 
jlj30Author Commented:
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
 
jlj30Author Commented:
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
 
Nico BontenbalCommented:
<<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
 
jlj30Author Commented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now