Please refer the following posts for basics.
This post aims to learn SQL based reporting for CRM 2011 using SSRS. This time I would like to pick up a sample report from CRM called Competitor Win / Loss. If you are an expert in SQL please ignore this post.
Scenario:
The report is based on the simple concept Opportunities which has Competitor / Competitors. As you all know in CRM we might add competitors related to the Opportunity. Also remember that if we loose an opportunity we might select a Competitor by whom we lost the golden opportunity.
For instance a competitor was involved in total 10 Opportunities. 5 Opportunities were won by Business ( which is good for Business.) 3 are Open ( Competitor is still trying on it ) 2 Lost ( Lost for the Business and lost against this competitor)
In CRM we have 3 Competitors listed. We also have 3 Open and 2 Closed Opportunities. Lets see how these competitors are involved in the 5 sample Opportunities.
This report uses temporary tables to achieve the expected results.
Lets examine the query in detail as the first part and then the report design. Because query is the important element of this report.
1. The core query used in this report could be found in the dataset DSCompetitor. And it looks something like
"
How does it look like ? A hard nut? May be but we are gonna crack it.
For now try to understand 3 things and rest is on the way.
I. This query is using 2 parameters
III. 3rd point is that it has a @SQL variable which holds the whole query. We might split this, for a better understanding.
2. Lets take this hard nut to SQL Management Studio and crack it. So that we could understand it in a better way.
First step is to declare the 2 parameters as shown below
After this try to execute the query. You might get some error something like this. Please do this step only if you get this error message.
Never mind. In select queries you could see something like
Just remove the simple brackets from 3 places.
3. So now our query looks like
"
4. As the next step we are gonna crack the @SQL. We also replaced the parameters with values. Please note we are doing all these to understand the query in detail.
Now the query looks like
"
5. Lets dig more
Also please note the following info about the joining tables
Ref: MSDN
Opportunity entity:
Joins ref( http://www.w3schools.com/sql/sql_join.asp)
Clustered and Nonclustered index --ref (http://technet.microsoft.com/en-us/library/ms190457.aspx)
CREATE STATISTICS --ref( http://msdn.microsoft.com/en-us/library/ms188038.aspx)
Query part by part.
Query Part 1:
INNER JOIN between FilteredOpportunity and FitereredOpportunityCompetitors to find out which Opportunities are in either OPEN state or WON state. We have 4.
The info we need are opportunityid, competitorid, name, statecode and lostto
From this Join query we get the Opportunities those are won or open which has competitor.
---------------------------------------------------------------------------------------------------------------------------
Nota Bene:
Its good to understand the difference between SELECT INTO and INSERT INTO SELECT statements.
The SELECT INTO statement selects data from one table and inserts it into a new table. (Ref:http://www.w3schools.com/sql/sql_select_into.asp)
For instance, if table is not created before
The other one is
The INSERT INTO SELECT statement selects data from one table and inserts it into an existing table. Any existing rows in the target table are unaffected. ( Ref: http://www.w3schools.com/sql/sql_insert_into_select.asp )
For instance, we have already created a temporary table and we would like to insert a few more rows. OR appending some rows to an existing temporary table
---------------------------------------------------------------------------------------------------------------------------
Query Part 2:
This means Opportunity is lost and Opportunity close activity is completed and it has a competitor.
For Opportunity,
For Opportunity Close Entity,
These rows ( row ) appended to the existing temporary table using the INSERT INTO SELECT statement.
We would like to have the competitor name and further details. So
Query part 3: Temporary table INNER JOIN FilteredCompetitor
SQL based Report in CRM 2011 using SSRS - Sample Report User Summary - Part 1
http://crmdm.blogspot.com/2013/09/sql-based-report-in-crm-2011-using-ssrs.htmlSQL based Report in CRM 2011 using SSRS - Sample Report User Summary - Part 2
http://crmdm.blogspot.com/2013/09/sql-based-report-in-crm-2011-using-ssrs_29.htmlThis post aims to learn SQL based reporting for CRM 2011 using SSRS. This time I would like to pick up a sample report from CRM called Competitor Win / Loss. If you are an expert in SQL please ignore this post.
Scenario:
The report is based on the simple concept Opportunities which has Competitor / Competitors. As you all know in CRM we might add competitors related to the Opportunity. Also remember that if we loose an opportunity we might select a Competitor by whom we lost the golden opportunity.
For instance a competitor was involved in total 10 Opportunities. 5 Opportunities were won by Business ( which is good for Business.) 3 are Open ( Competitor is still trying on it ) 2 Lost ( Lost for the Business and lost against this competitor)
In CRM we have 3 Competitors listed. We also have 3 Open and 2 Closed Opportunities. Lets see how these competitors are involved in the 5 sample Opportunities.
This report uses temporary tables to achieve the expected results.
Lets examine the query in detail as the first part and then the report design. Because query is the important element of this report.
1. The core query used in this report could be found in the dataset DSCompetitor. And it looks something like
"
Create table #CompetitorOppIds (
opportunityid uniqueidentifier,
competitorid uniqueidentifier,
name nvarchar(max),
statecode int,
lostto int
primary key clustered
(
[opportunityid],
[competitorid]
)
)
create statistics compoppstat on #CompetitorOppIds(opportunityid, competitorid)
Declare @SQL nVarchar(max)
Set @SQL =
'insert
into #CompetitorOppIds
select
oppcomp.opportunityid, oppcomp.competitorid, NULL, o.statecode, 0
from
(' +
@CRM_FilteredOpportunity + ') as o
join
FilteredOpportunityCompetitors as
oppcomp
on
(o.opportunityid = oppcomp.opportunityid)
where
o.statecode <> 2
insert
into #CompetitorOppIds
select
oppc.opportunityid, oppc.competitorid, NULL, o.statecode, 0
from
(' +
@CRM_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)
Select
filcomp.name
as Competitor,
comp.competitorid,
sum(Case
when comp.statecode = 0 then 1 else 0 end) as openopp,
sum(
Case when comp.statecode = 1 or comp.statecode = 2 then 1 else 0 end) as
closeopp,
sum(case
when comp.statecode = 1 then 1 else 0 end) as wonopp,
sum(case
when comp.statecode = 2 then 1 else 0 end) as lostopp
From
#CompetitorOppIds comp
join
(' +
@CRM_FilteredCompetitor + ') as filcomp
on
(filcomp.competitorid = comp.competitorid)
group
by comp.competitorid, filcomp.name '
Exec
(@SQL)
drop table #CompetitorOppIds"
How does it look like ? A hard nut? May be but we are gonna crack it.
For now try to understand 3 things and rest is on the way.
I. This query is using 2 parameters
- @CRM_FilteredCompetitor
- @CRM_FilteredOpportunity
This also means that we need to pass these 2 values to the query if we need to play around this query outside the report ( could be SQL Management Studio)
II. And its using a temporary table called #CompetitorOppIds
III. 3rd point is that it has a @SQL variable which holds the whole query. We might split this, for a better understanding.
2. Lets take this hard nut to SQL Management Studio and crack it. So that we could understand it in a better way.
First step is to declare the 2 parameters as shown below
Declare
@CRM_FilteredOpportunity nVarchar(40)
Declare
@CRM_FilteredCompetitor nVarchar(40)
SET
@CRM_FilteredOpportunity='FilteredOpportunity'
SET
@CRM_FilteredCompetitor='FilteredCompetitor'
After this try to execute the query. You might get some error something like this. Please do this step only if you get this error message.
Msg 102, Level 15, State 1,
Line 3
Incorrect syntax near ')'.
Msg 102, Level 15, State 1,
Line 10
Incorrect syntax near ')'.
Msg 102, Level 15, State 1,
Line 22
Incorrect syntax near ')'.
Never mind. In select queries you could see something like
from (' +
@CRM_FilteredOpportunity + ')
Just remove the simple brackets from 3 places.
3. So now our query looks like
"
Declare
@CRM_FilteredOpportunity nVarchar(40)
Declare
@CRM_FilteredCompetitor nVarchar(40)
SET
@CRM_FilteredOpportunity='FilteredOpportunity'
SET
@CRM_FilteredCompetitor='FilteredCompetitor'
Create table #CompetitorOppIds (
opportunityid uniqueidentifier,
competitorid uniqueidentifier,
name nvarchar(max),
statecode int,
lostto int
primary key clustered
(
[opportunityid],
[competitorid]
)
)
create statistics compoppstat on
#CompetitorOppIds(opportunityid, competitorid)
Declare @SQL nVarchar(max)
Set @SQL =
'insert into
#CompetitorOppIds
select
oppcomp.opportunityid, oppcomp.competitorid, NULL, o.statecode, 0
from ' +
@CRM_FilteredOpportunity + ' as o
join
FilteredOpportunityCompetitors as
oppcomp
on
(o.opportunityid = oppcomp.opportunityid)
where o.statecode
<> 2
insert into
#CompetitorOppIds
select
oppc.opportunityid, oppc.competitorid, NULL, o.statecode, 0
from ' +
@CRM_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)
Select
filcomp.name as
Competitor,
comp.competitorid,
sum(Case when
comp.statecode = 0 then 1 else 0 end) as openopp,
sum( Case when
comp.statecode = 1 or comp.statecode = 2 then 1 else 0 end) as closeopp,
sum(case when
comp.statecode = 1 then 1 else 0 end) as wonopp,
sum(case when
comp.statecode = 2 then 1 else 0 end) as lostopp
From
#CompetitorOppIds comp
join ' +
@CRM_FilteredCompetitor + ' as filcomp
on
(filcomp.competitorid = comp.competitorid)
group by
comp.competitorid, filcomp.name '
Exec (@SQL)
drop table #CompetitorOppIds"
4. As the next step we are gonna crack the @SQL. We also replaced the parameters with values. Please note we are doing all these to understand the query in detail.
Now the query looks like
"
Create table #CompetitorOppIds (
opportunityid uniqueidentifier,
competitorid uniqueidentifier,
name nvarchar(max),
statecode int,
lostto int
primary key clustered
(
[opportunityid],
[competitorid]
)
)
create statistics compoppstat on
#CompetitorOppIds(opportunityid, competitorid)
insert into #CompetitorOppIds
select oppcomp.opportunityid,
oppcomp.competitorid,
NULL, o.statecode, 0
from FilteredOpportunity as o
join
FilteredOpportunityCompetitors as oppcomp
on (o.opportunityid =
oppcomp.opportunityid)
where o.statecode <> 2
insert into #CompetitorOppIds
select oppc.opportunityid, oppc.competitorid, NULL, 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)
Select
filcomp.name as Competitor,
comp.competitorid,
sum(Case when comp.statecode = 0 then 1 else 0 end) as openopp,
sum( Case when comp.statecode = 1 or comp.statecode = 2 then 1 else 0 end) as closeopp,
sum(case when comp.statecode = 1 then 1 else 0 end) as wonopp,
sum(case when comp.statecode = 2 then 1 else 0 end) as lostopp
From
#CompetitorOppIds comp
join
FilteredCompetitor as filcomp
on (filcomp.competitorid = comp.competitorid)
group by comp.competitorid, filcomp.name
drop table #CompetitorOppIds"
5. Lets dig more
Also please note the following info about the joining tables
Ref: MSDN
- FilteredOpportunity--Potential revenue-generating event or sale to an account, that needs to be tracked through a sales process to completion.
- FilteredCompetitor--Tracks information about a business competing for the sale represented by a lead or opportunity.
- FilteredOpportunityClose--Activity that is created automatically when an opportunity is closed, containing information such as the description of the closing and actual revenue.
- FilteredOpportunityCompetitors-- Association between opportunities and competitors.
Opportunity entity:
statecode
|
State
|
0
|
Open
|
State
|
1
|
Won
|
|
State
|
2
|
Lost
|
Joins ref( http://www.w3schools.com/sql/sql_join.asp)
Clustered and Nonclustered index --ref (http://technet.microsoft.com/en-us/library/ms190457.aspx)
CREATE STATISTICS --ref( http://msdn.microsoft.com/en-us/library/ms188038.aspx)
Query part by part.
Query Part 1:
INNER JOIN between FilteredOpportunity and FitereredOpportunityCompetitors to find out which Opportunities are in either OPEN state or WON state. We have 4.
insert into #CompetitorOppIds
select oppcomp.opportunityid,
oppcomp.competitorid,
NULL, o.statecode, 0
from FilteredOpportunity as o
join
FilteredOpportunityCompetitors as oppcomp
on (o.opportunityid =
oppcomp.opportunityid)
where o.statecode <> 2
The info we need are opportunityid, competitorid, name, statecode and lostto
From this Join query we get the Opportunities those are won or open which has competitor.
---------------------------------------------------------------------------------------------------------------------------
Nota Bene:
Its good to understand the difference between SELECT INTO and INSERT INTO SELECT statements.
The SELECT INTO statement selects data from one table and inserts it into a new table. (Ref:http://www.w3schools.com/sql/sql_select_into.asp)
For instance, if table is not created before
The other one is
The INSERT INTO SELECT statement selects data from one table and inserts it into an existing table. Any existing rows in the target table are unaffected. ( Ref: http://www.w3schools.com/sql/sql_insert_into_select.asp )
For instance, we have already created a temporary table and we would like to insert a few more rows. OR appending some rows to an existing temporary table
---------------------------------------------------------------------------------------------------------------------------
Query Part 2:
insert into #CompetitorOppIds
select oppc.opportunityid, oppc.competitorid, NULL, 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)
statecode
|
State
|
0
|
Open
|
State
|
1
|
Won
| |
State
|
2
|
Lost
|
statecode
|
State
|
0
|
Open
|
State
|
1
|
Completed
|
|
State
|
2
|
Canceled
|
We would like to have the competitor name and further details. So
Query part 3: Temporary table INNER JOIN FilteredCompetitor
Select
filcomp.name as Competitor,
comp.competitorid,
sum(Case when comp.statecode = 0 then 1 else 0 end) as openopp,
sum( Case when comp.statecode = 1 or comp.statecode = 2 then 1 else 0 end) as closeopp,
sum(case when comp.statecode = 1 then 1 else 0 end) as wonopp,
sum(case when comp.statecode = 2 then 1 else 0 end) as lostopp
From
#CompetitorOppIds comp
join
FilteredCompetitor as filcomp
on (filcomp.competitorid = comp.competitorid)
group by comp.competitorid, filcomp.name
This is a clever query. So we got Competitor name ,id, next is aggregations
SUM ( Open Opp)
SUM ( Closed Opp)
SUM( Won Opp)
SUM (Lost Opp)
We lost one opportunity because of the 3rd Competitor. And we won one Opportunity against the same competitor
Lets test it once more. Lets close one opportunity as lost and see the result again.
We lost one more opportunity because of the second Competitor. There are no more open opportunities against this Competitor. Whereas first and third competitors are still having open opportunities against them. Fingers crossed for Business and Competitors !
6. Here is the test query with some more comments. Please note this is just one approach to learn sql reporting with CRM 2011. If you have a better approach please feel to try that. Our target is same, which is nothing but learning the sql based reports for CRM 2011.
"--- This is our temporary table. Please note that its prefixed with ‘#’ character
6. Here is the test query with some more comments. Please note this is just one approach to learn sql reporting with CRM 2011. If you have a better approach please feel to try that. Our target is same, which is nothing but learning the sql based reports for CRM 2011.
"--- This is our temporary table. Please note that its prefixed with ‘#’ character
Create table #CompetitorOppIds (
opportunityid uniqueidentifier,
competitorid uniqueidentifier,
name nvarchar(max),
statecode int,
lostto int
primary key clustered
(
[opportunityid],
[competitorid]
)
)
--- Please note
the fields defined for the temporary table
---Indexing
helps SQL server to retrieve the rows quickly
--This is to
improve query performance
create statistics compoppstat on
#CompetitorOppIds(opportunityid, competitorid)
insert into #CompetitorOppIds
select oppcomp.opportunityid,
oppcomp.competitorid,
NULL, o.statecode, 0
from FilteredOpportunity as o
join
FilteredOpportunityCompetitors as oppcomp
on (o.opportunityid =
oppcomp.opportunityid)
where o.statecode <> 2
--Lets see whats
inside the temp table now
SELECT * FROM
#CompetitorOppIds
insert into #CompetitorOppIds
select oppc.opportunityid, oppc.competitorid, NULL, 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)
--Lets see whats
inside the temp table now
SELECT * FROM
#CompetitorOppIds
Select
filcomp.name as Competitor,
comp.competitorid,
sum(Case when comp.statecode = 0 then 1 else 0 end) as openopp,
sum( Case when comp.statecode = 1 or comp.statecode = 2 then 1 else 0 end) as closeopp,
sum(case when comp.statecode = 1 then 1 else 0 end) as wonopp,
sum(case when comp.statecode = 2 then 1 else 0 end) as lostopp
From
#CompetitorOppIds comp
join
FilteredCompetitor as filcomp
on (filcomp.competitorid = comp.competitorid)
group by comp.competitorid, filcomp.name
--Deletion of
temporary table
drop table #CompetitorOppIds"
7. Competitor Win / Loss report is using this query. The report design is not complex for this report. Its based on matrix control. For more details about matrix control please refer this post.
http://crmdm.blogspot.com/2013/09/custom-fetchxml-based-grid-report-with.html
8. Here is the preview of the report. Please note that the main part of this report is the SQL query.
http://crmdm.blogspot.com/2013/09/custom-fetchxml-based-grid-report-with.html
