Solved

SSRS Graph - Stacked Bar

Posted on 2016-08-17
2
47 Views
Last Modified: 2016-08-22
I am trying to create a Stacked Bar Graph in SSRS - i have the below data

Month      Q1              Q2               Q3               Q4
May            50.00%      20.00%      30.00%      0.00%
June            38.46%      30.77%      15.38%      15.38%
July            30.77%      38.46%      15.38%      15.38%
August  46.15%      7.69%      15.38%      30.77%

I want to have the stack by Q1 then the data be for each month, then Q2,Q3, Q4 and so on, I can easily do it in Excel (attached example) but cant reproduce in SSRS - hoping someone can help

Thanks

Book1.xlsx
0
Comment
Question by:dkilby
2 Comments
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 500 total points
ID: 41764254
I'm afraid that's not possible in SSRS. So you need to reformat your data so the columns are Month, Quarter, Value. If you use SQL Server you can do that using UNPIVOT. Suppose your original query was like this:
select 'May' as Month, 50 as Q1, 20 as Q2, 30 as Q3, 0 as Q4
union all
select 'June' as Month, 38 as Q1, 30 as Q2, 15 as Q3, 15 as Q4
union all
select 'July' as Month, 30 as Q1, 38  as Q2, 15 as Q3, 15 as Q4
union all
select 'August' as Month, 46 as Q1, 7 as Q2, 15 as Q3, 38 as Q4

Open in new window

(That's not what your query looks like of course, but the result has the same structure as your example.) Then you can move the 4 quarters into 1 column like this:
select Month, Quarter, Value  from 
(
select 'May' as Month, 50 as Q1, 20 as Q2, 30 as Q3, 0 as Q4
union all
select 'June' as Month, 38 as Q1, 30 as Q2, 15 as Q3, 15 as Q4
union all
select 'July' as Month, 30 as Q1, 38  as Q2, 15 as Q3, 15 as Q4
union all
select 'August' as Month, 46 as Q1, 7 as Q2, 15 as Q3, 38 as Q4
) as original
UNPIVOT (Value for Quarter IN (Q1, Q2, Q3, Q4)) as new

Open in new window

With this result you can create a chart using Value as Values, Quarter as Category Groups and Month as Series Groups.

Please let me know if you need any further help solving this issue.
0
 

Author Closing Comment

by:dkilby
ID: 41766189
Thanks, worked perfectly
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Written by Valentino Vranken. A while ago I wrote an article called Chart Optimization Tips (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Chart-Optimization-Tips.html).  This article explained how …
Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

912 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

19 Experts available now in Live!

Get 1:1 Help Now