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
920 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

705 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now