"тнιѕ вℓσg ¢συℓ∂ ѕανє уσυя мσηєу ιƒ тιмє = мσηєу" - ∂.мαηנαℓу

Friday 7 March 2014

SQL Pivot Based Custom Report in CRM 2011 using SSRS OR Sample Report with SQL Pivot in SSRS

In this post we would try to understand PIVOT relational operator in SQL. PIVOT is useful when we need to develop SQL based reports in SSRS. Please note that this post is based on a sample report in CRM 2011 called Competitor Win / Loss and its not a silver bullet.  If you are an expert in SQL please ignore this post.

With reference from Microsoft,

"You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output"

Scenario:
The report is based on a very simple concept. An Opportunity which has Competitor / Competitors. As you all know in CRM we might add competitors related to the Opportunity. 

We would like to see a report which displays counts of open, won and lost opportunities against corresponding competitor ( if competitor exists ).

This could be achieved in many ways. But here we are going to try it with SQL PIVOT concept.

1. Lets have a look at the sample opportunities in CRM.


2. An opportunity could have more than one competitors.


3. This report uses temporary tables to achieve the expected results. To have a better understanding, lets divide the query into 2 parts. PIVOT would be applied in the second part.
Please note the comments with the first part query.

--Temporary Table
Create table #CompetitorOppIds (
      opportunityid uniqueidentifier,
      competitorid uniqueidentifier,
      name nvarchar(max),
      statecode int,
      lostto int
     primary key clustered
      (   
            [opportunityid],
            [competitorid]
      )   
)
-- To retrieve the OPEN Opportunities details
insert into #CompetitorOppIds
select oppcomp.opportunityid, oppcomp.competitorid, FilteredCompetitor.name, o.statecode, 0
from   FilteredOpportunity    as o
join FilteredOpportunityCompetitors  as oppcomp
on (o.opportunityid = oppcomp.opportunityid)
-- To retrieve the Competitor Name
INNER JOIN FilteredCompetitor ON
oppcomp.competitorid = FilteredCompetitor.competitorid
where o.statecode <> 2

-- To retieve the Closed Opportunities details ( WON and LOST Opportunities are here)
insert into #CompetitorOppIds
select oppc.opportunityid, oppc.competitorid, FilteredCompetitor.name, o.statecode, 0
from  FilteredOpportunity  as o
join FilteredOpportunityClose as oppc
on (o.opportunityid = oppc.opportunityid and o.statecode = 2 and oppc.statecode = 1 and oppc.competitorid IS NOT NULL)
-- To retrieve the Competitor Name
INNER JOIN FilteredCompetitor ON
oppc.competitorid = FilteredCompetitor.competitorid
--Lets see whats inside the temp table now
SELECT * FROM #CompetitorOppIds

4. And we get the following result in the temporary table.


5. Time for PIVOT. We need a count of Opportunities  ( Aggregate function ) based on unique values of statecode.
ie: If statecode=1  It means its a WON Opportunity
 If statecode=0  It means its a OPEN Opportunity
 If statecode=2  It means its a LOST Opportunity

In simple words, we are going to turn the table by setting the unique values as Columns.

Please note the comments with the PIVOT

-- Here we select the columns to be displayed on our PIVOT result table.
SELECT  name,competitorid, [0] AS openopp,
[1] AS wonopp, [2] AS lostopp
FROM
--Here we select the necessary data for the columns we selected above
( SELECT
competitorid,name, statecode
FROM #CompetitorOppIds ) AS PivotData
PIVOT (
-- In this scenario, we need the COUNT aggregate function
COUNT(statecode)
FOR statecode IN ([0],[1],[2])

) AS PivotResult ORDER BY competitorid

-- finally drop the temporary table
DROP TABLE #CompetitorOppIds


6.  Here is the PIVOT results. 


7. Here is the whole query

--Temporary Table
Create table #CompetitorOppIds (
      opportunityid uniqueidentifier,
      competitorid uniqueidentifier,
      name nvarchar(max),
      statecode int,
      lostto int
     primary key clustered
      (   
            [opportunityid],
            [competitorid]
      )   
)
-- To retrieve the OPEN Opportunities details
insert into #CompetitorOppIds
select oppcomp.opportunityid, oppcomp.competitorid, FilteredCompetitor.name, o.statecode, 0
from   FilteredOpportunity    as o
join FilteredOpportunityCompetitors  as oppcomp
on (o.opportunityid = oppcomp.opportunityid)
-- To retrieve the Competitor Name
INNER JOIN FilteredCompetitor ON
oppcomp.competitorid = FilteredCompetitor.competitorid
where o.statecode <> 2

-- To retieve the Closed Opportunities details ( WON and LOST Opportunities are here)
insert into #CompetitorOppIds
select oppc.opportunityid, oppc.competitorid, FilteredCompetitor.name, o.statecode, 0
from  FilteredOpportunity  as o
join FilteredOpportunityClose as oppc
on (o.opportunityid = oppc.opportunityid and o.statecode = 2 and oppc.statecode = 1 and oppc.competitorid IS NOT NULL)
-- To retrieve the Competitor Name
INNER JOIN FilteredCompetitor ON
oppc.competitorid = FilteredCompetitor.competitorid

-- Here we selet the columns to be displayed on our PIVOT result table.
SELECT  name,competitorid, [0] AS openopp,
[1] AS wonopp, [2] AS lostopp
FROM
--Here we select the necessary data for the columns we selected above
( SELECT
competitorid,name, statecode
FROM #CompetitorOppIds ) AS PivotData
PIVOT (
-- In this scenario, we need the COUNT aggregate function
COUNT(statecode)
FOR statecode IN ([0],[1],[2])

) AS PivotResult ORDER BY competitorid

DROP TABLE #CompetitorOppIds

We need to place the above query in a Dataset and then do the report design
Please refer the below links for the basics.



9. The simple report design is shown is below.


10. Proof is in the pudding !