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.

Active Directory data as SharePoint 2010 list using BDC

 Active Directory data as SharePoint 2010 list using BDC

Situation

Create SharePoint List that displays all ad users and contacts

Although there may be several ways to approach this problem I have found the BDC external lists as the easiest way

You can accomplish this using two modules

SQL Views doing a LDAP Query using Linked MS SQL Server

BDC External List Connecting  to this view and create external list using this data source.

For the SQL Linked using refer to different ways to doing it. I prefer the Linked Server way. Here is the good post you can refer to

Basically what you have to do is

1. Run the System Stored Proc to add Linked server by running

EXEC sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'

2. Set credentials to the linked server User Account that can perform LDAP query

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'DOMAIN\UserAccount',@rmtpassword=N'DomainAccountPassword'


3. Create a SQL Dialet Query View By right clicking the database and say Create view and paste this

//The below is for querying the users with WHERE condition. Be careful with ad objects. Find all the list of available AD

SELECT cn AS Name, mail AS EmailID, department, telephoneNumber
FROM         OPENQUERY(ADSI,
                      'SELECT cn,mail,department,telephoneNumber FROM ''LDAP://DC=xxxx,DC=xxx'' WHERE objectCategory=''person'' AND objectClass=''user'' ORDER BY cn')
 WHERE     (department IS NOT NULL)

//You can use the below for Contacts from AD

CREATE VIEW viewADContacts
AS
SELECT  [Name], sn [Last Name], street [Street], l [City], st [State]
FROM OPENQUERY( ADSI,
     'SELECT name, sn, street, l, st
      FROM ''LDAP:// OU=Sales,DC=activeds,DC=Fabrikam,DC=Com''
      WHERE objectCategory=''Person'' AND
      objectClass = ''contact''')
GO
SELECT * FROM viewADContacts

Ref -http://msdn.microsoft.com/en-us/library/windows/desktop/aa772380%28v=vs.85%29.aspx

4. Create a External Lists using BDC

I prefer to use ReverttoItself type BDC Identity to avoid the user type in password twice using Impersonated Windows Identity.

For this to work first run this pwershell command
$serviceApplications = Get-spserviceapplication$bdc = $serviceApplications[i]#Choose the index ‘i’ that corresponds to BDC,#or filter by $_.GetType().Name. Do not filter by $_.TypeName.$bdc.RevertToSelfAllowed = $true

Ref-http://support.microsoft.com/kb/982586

 Or this

$bcsServiceApp = Get-SPServiceApplication | where {$_ -match "Business Data Connectivity Service"}

$bcsServiceApp.RevertToSelfAllowed = $true;

$bcsServiceApp.Update();

Ref- http://zimmergren.net/technical/sp-2010-bcs-problem-with-authenticationmode-and-reverttoself

Once set give the the web application app pool account the necessary access to the database where you created the view in step 2.

Choose the datasource provide the app pool identity , choose the view, create the Read Items and Read List Items methods and create a external list out of it. 




You are not done yet until you go to the BDC Service and set the Object Permissions to the Data Store .

I usually add "All Authenticated Users" or "Domain Users" to the datastore.
Now navigate to the list you created on the web application and you should all be done