Solved

SSRS Graph - Stacked Bar

Posted on 2016-08-17
2
60 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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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