?
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
Medium Priority
?
1,094 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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this short article I will be talking about two functions in the SQL Server Reporting Services (SSRS) function stack.  Those functions are IIF() and Switch().  And I'll be showing you how easy it is to add an Else part to the Switch function. T…
Introduction: This article is aimed at report developers who are used to developing reports using relational databases and have gotten a first-time assignment to develop reports on OLAP cubes. It demonstrates how to build a report using SQL Ser…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
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…
Suggested Courses
Course of the Month7 days, 23 hours left to enroll

765 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