Clustered Stacked Column SSRS 2008 R2 Bar Graph

First of all it is not a feature that can be found easily on SSRS 2008. We spent couple of days googling and triying it out.

We achieved this
 

My requirements were something like this
Stack the Approved/Pending, Emergency/Normal IT Tickets Stacked.
Categorized by Year Opened/Week Opened

So here is how we did. The dataset looked liked this





This is the query that can help you frame this.( Thanks to Iman for helping me on this query)
 Select
GroupCat,
Type,
Week,
--DateOpened,
YearOpened,
sum([Normal Trouble Tickets]) as 'Normal Trouble Tickets',
sum([Emergency Trouble Tickets]) as 'Emergency Trouble Tickets',
sum([Approved Access Requests]) as 'Approved Access Requests',
sum([Pending Access Requests]) as 'Pending Access Requests',
sum([Approved Change Requests]) as 'Approved Change Requests',
sum([Pending Change Requests]) as 'Pending Change Requests',
sum([Approved Hardware Requests]) as 'Approved Hardware Requests',
sum([Pending Hardware Requests]) as 'Pending Hardware Requests'

from
(
SELECT
 '1' AS GroupCat,'Emergency Trouble Tickets' AS Type,
COUNT(distinct ActionID) AS 'Normal Trouble Tickets' ,
0 as 'Emergency Trouble Tickets' ,
0 as 'Approved Access Requests',
0 as 'Pending Access Requests',
0 as 'Approved Change Requests',
0 as  'Pending Change Requests',
0 as 'Approved Hardware Requests',
0 as 'Pending Hardware Requests',
StatusStr,
DATEPART(wk,DateOpened) AS Week,DateOpened,
YEAR(DateOpened) AS YearOpened
FROM All_Actions WHERE ((Category='IT Service Desk')) AND StandardYesNo002=0
GROUP BY ActionID,DateOpened, StatusStr

UNION
SELECT
 '1' AS GroupCat,'Normal Trouble Tickets' AS Type,
0 as  'Normal Trouble Tickets' ,
COUNT(distinct ActionID) AS 'Emergency Trouble Tickets' ,
0 as 'Approved Access Requests',
0 as 'Pending Access Requests',
0 as 'Approved Change Requests',
0 as  'Pending Change Requests',
0 as 'Approved Hardware Requests',
0 as 'Pending Hardware Requests',
StatusStr,
DATEPART(wk,DateOpened) AS Week,DateOpened,
YEAR(DateOpened) AS YearOpened
FROM All_Actions WHERE ((Category='IT Service Desk')) AND StandardYesNo002=1
GROUP BY DateOpened,StatusStr

UNION
SELECT
 '2' AS GroupCat,'Approved Access Requests' AS Type,
0 as  'Normal Trouble Tickets' ,
0 as 'Emergency Trouble Tickets' ,
COUNT(distinct ActionID) AS 'Approved Access Requests',
0 as 'Pending Access Requests',
0 as 'Approved Change Requests',
0 as  'Pending Change Requests',
0 as 'Approved Hardware Requests',
0 as 'Pending Hardware Requests',
StatusStr,
DATEPART(wk,CompleteInvestigationDate) AS Week,CompleteInvestigationDate AS DateOpened,
YEAR(CompleteInvestigationDate) AS YearOpened
FROM All_Actions WHERE ((Category='IT Access Form')) AND CompleteInvestigationDate IS NOT NULL
GROUP BY ActionID,CompleteInvestigationDate,StatusStr

UNION
SELECT
 '2' AS GroupCat,'Pending Access Requests' AS Type,
0 as  'Normal Trouble Tickets' ,
0 as 'Emergency Trouble Tickets' ,
0 as 'Approved Access Requests',
COUNT(distinct ActionID) AS 'Pending Access Requests',
0 as 'Approved Change Requests',
0 as  'Pending Change Requests',
0 as 'Approved Hardware Requests',
0 as 'Pending Hardware Requests',
StatusStr,
DATEPART(wk,DateOpened) AS Week,DateOpened,
YEAR(DateOpened) AS YearOpened
FROM All_Actions WHERE ((Category='IT Access Form')) AND CompleteInvestigationDate IS NULL
GROUP BY ActionID,DateOpened,StatusStr

UNION
SELECT
 '3' AS GroupCat,'Approved Change Requests' AS Type,
0 as  'Normal Trouble Tickets' ,
0 as 'Emergency Trouble Tickets' ,
0 as 'Approved Access Requests',
0 as 'Pending Access Requests',
COUNT(distinct ActionID) AS  'Approved Change Requests',
0 as  'Pending Change Requests',
0 as 'Approved Hardware Requests',
0 as 'Pending Hardware Requests',
StatusStr,
DATEPART(wk,CompleteInvestigationDate) AS Week, CompleteInvestigationDate as DateOpened,
YEAR(CompleteInvestigationDate) AS YearOpened
FROM All_Actions WHERE ((Category='IT Change Form')) AND CompleteInvestigationDate IS NOT NULL
GROUP BY ActionID,CompleteInvestigationDate,StatusStr

UNION
SELECT
 '3' AS GroupCat,'Pending Change Requests' AS Type,
0 as  'Normal Trouble Tickets' ,
0 as 'Emergency Trouble Tickets' ,
0 as 'Approved Access Requests',
0 as 'Pending Access Requests',
0 as 'Approved Change Requests',
COUNT(distinct ActionID) AS 'Pending Change Requests',
0 as 'Approved Hardware Requests',
0 as 'Pending Hardware Requests',
StatusStr,
DATEPART(wk,DateOpened) AS Week,DateOpened,
YEAR(DateOpened) AS YearOpened
FROM All_Actions WHERE ((Category='IT Change Form')) AND CompleteInvestigationDate IS NULL
GROUP BY ActionID,DateOpened,StatusStr

UNION
SELECT
 '4' AS GroupCat,'Approved Hardware Requests' AS Type,
0 as  'Normal Trouble Tickets' ,
0 as 'Emergency Trouble Tickets' ,
0 as 'Approved Access Requests',
0 as 'Pending Access Requests',
0 as 'Approved Change Requests',
0 as 'Pending Change Requests',
COUNT(distinct ActionID) AS 'Approved Hardware Requests',
0 as 'Pending Hardware Requests',
StatusStr,
DATEPART(wk,CompleteInvestigationDate) AS Week,CompleteInvestigationDate as DateOpened,
YEAR(CompleteInvestigationDate) AS YearOpened
FROM All_Actions WHERE ((Category='IT Request for New Hardware')) AND CompleteInvestigationDate IS NOT NULL
GROUP BY ActionID,CompleteInvestigationDate,StatusStr

UNION
SELECT
 '4' AS GroupCat,'Pending Hardware Requests' AS Type,
0 as  'Normal Trouble Tickets' ,
0 as 'Emergency Trouble Tickets' ,
0 as 'Approved Access Requests',
0 as 'Pending Access Requests',
0 as 'Approved Change Requests',
0 as 'Pending Change Requests',
0 as 'Approved Hardware Requests',
COUNT(distinct ActionID) AS 'Pending Hardware Requests',
StatusStr,
DATEPART(wk,DateOpened) AS Week,DateOpened,
YEAR(DateOpened) AS YearOpened
FROM All_Actions WHERE ((Category='IT Request for New Hardware')) AND CompleteInvestigationDate IS NULL
GROUP BY ActionID,DateOpened,StatusStr

) as a
where
dateopened between @DateFrom and @DateTo

group by
GroupCat,
Type,
Week,
--DateOpened,
YearOpened
order by
YearOpened,
week

One your dataset is ready plug it in to your Bar Graph SSRS report.
Change the chart type stacked column.
Choose/Add the Ticket Cat Columns to the Series Columns.
Add the Year Opened and Week to the Categories
Keep the Series Groups Section Blank



Now we will start clubbing the series you need to stack one above the other, for that you will use the series axis fix.

Go to the Sum Values in the chart data right click and say properties. Go to Axes and Chart Area



The SS above is default settings. Keep the Normal and Emergency Ticks as above so that both get grouped on same axes.

For next group series Pending Access and Approved Access switch the Horizontal Axes To Secondary.

For the Other group of Pending Change and Approved Change Switch the Vertical Axes to Secondary and keep the Horizontal axes to Primary.

Fore the last change it to Secondary and Secondary.

All Set.

Optionally you can set the Interval to the same for both axes on primary and secondary.