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

Tuesday, 1 October 2013

SQL based Report in CRM 2011 using SSRS - Sample Report Competitor Win / Loss

Please refer the following posts for basics.

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.html


SQL 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.html


      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


" 

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)



This means Opportunity is lost and Opportunity close activity is completed and it has a competitor.

For Opportunity,


statecode
State
0
Open

State
1
Won

State
2
Lost

For Opportunity Close Entity,


statecode
State
0
Open
State
1
Completed
State
2
Canceled







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



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
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. 






20 comments:

  1. Thanks for updating new information about CRM. As an application developer you have shared lot of details about CRM. Also share your updated details about cloud in this website.
    Cloud computing training

    ReplyDelete
  2. Thanks so very much for taking your time to create this very useful and informative site. I have learned a lot from your site. Thanks!!

    Salesforce Training

    ReplyDelete
  3. Cloud Computing Training

    I have read your blog and i got a very useful and knowledgeable information from your blog.its really a very nice article.You have done a great job . If anyone want to get real time Cloud Computing Course in Chennai, Please visit FITA academy located at Chennai Velachery which offer best Cloud Computing Training in Chennai.

    Cloud Training in Chennai

    ReplyDelete
  4. The information you have posted here is really useful and interesting too & here, I had a chance to gather some useful tactics in programming, thanks for sharing and I have an expectation about your future blogs keep your updates please.
    Salesforce training in Chennai|Salesforce courses in Chennai

    ReplyDelete
  5. Your posts is really helpful for me.Thanks for your wonderful post. I am very happy to read your post.very nice !!!
    Mysql Training in chennai | Mysql Training chennai | Mysql course in chennai | Mysql course chennai

    ReplyDelete
  6. Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.CCNA training in chennai | CCNA training chennai | CCNA course in chennai | CCNA course chennai

    ReplyDelete
  7. In coming years, cloud computing is going to rule the world. The cloud based CRM tool provider like Salesforce have massive demand in the market. Thus talking salesforce training in Chennai from reputed Salesforce training institutes in Chennai will ensure bright career prospects for aspiring professionals.

    ReplyDelete
  8. Your posts is really helpful for me.Thanks for your wonderful post. I am very happy to read your post. Mysql training in chennai | Mysql training chennai | Mysql course in chennai | Mysql course chennai

    ReplyDelete
  9. The information you posted here is useful to make my career better. Thanks for sharing such a informative post. keep updates...

    Regards..
    Salesforce Developer Training in Chennai

    ReplyDelete
  10. Well post in recent day’s customer relationship play vital role to get good platform in business industry, Salesforce crm tool helps you to maintain your customer relationship enhancement.
    Regards,
    Salesforce training institute in Chennai|Salesforce training

    ReplyDelete
  11. There are lots of information about latest technology and how to get trained in them, like Hadoop Training Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies(Hadoop Training in Chennai). By the way you are running a great blog. Thanks for sharing this.

    ReplyDelete
  12. Thanks for sharing your informative article on Hive ODBC Driver. Your article is very descriptive and assists me to learn whole concept in detail. Hadoop Training in Chennai

    ReplyDelete
  13. Cloud is one of the tremendous technology that any company in this world would rely on(cloud computing training). Using this technology many tough tasks can be accomplished easily in no time. Your content are also explaining the same(Cloud computing training institutes in chennai). Thanks for sharing this in here. You are running a great blog, keep up this good work.

    ReplyDelete
  14. There are lots of information about latest technology and how to get trained in them, like Big Data Course in Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies(Big Data Training Chennai). By the way you are running a great blog. Thanks for sharing this.

    Big Data Training in Chennai | Big Data Training

    ReplyDelete
  15. Wiztech Automation Solutions is the Best Training institute in Chennai,started in the year 2006 and it extended its circle through providing the best Education as per the Global Quality Standards. Hence our Training Center in Chennai was Recognized by IAO and ISO for its inspiring Education Quality Standards. Wiztech Automation Solution, the PLC SCADA Training Academy in Chennai offers both PLC, SCADA, DCS, VFD, Drives, Control Panels, HMI, Pneumatics, Embedded systems, VLSI, IT, Web Designing, AutoCad Training courses in chennai with latest various brands. Wiztech Automation Solutions offers Real Time Training Courses with 100% Placement support in chennai.

    PLC Training in chennai
    SCADA Training in chennai
    Embedded Systems Training in chennai
    VLSI Training in chennai
    Automation Training in chennai
    Industrial Automation Training in chennai
    Process Automation Training in chennai
    DCS Training in chennai
    Inplant Training in chennai
    Placement
    PLC Course in chennai
    Best PLC Training in chennai
    PLC Training in chennai
    Robotics Training in chennai
    Embedded Training in chennai
    IT Training in chennai
    Web designing Training in chennai
    AutoCad Training in chennai

    ReplyDelete
  16. Welcome to Wiztech Automation - Embedded System Training in Chennai. We have knowledgeable Team for Embedded Courses handling and we also are after Job Placements offer provide once your Successful Completion of Course. We are Providing on Microcontrollers such as 8051, PIC, AVR, ARM7, ARM9, ARM11 and RTOS. Free Accommodation, Individual Focus, Best Lab facilities, 100% Practical Training and Job opportunities.

    Embedded System Training in chennai
    Embedded System Training Institute in chennai
    Embedded Training in chennai
    Embedded Course in chennai
    Best Embedded System Training in chennai
    Best Embedded System Training Institute in chennai
    Best Embedded System Training Institutes in chennai
    Embedded Training Institute in chennai
    Embedded System Course in chennai
    Best Embedded System Training in chennai

    ReplyDelete
  17. Hello admin, thank you for your informative post on hadoop training in Chennai. It helped a lot in training my students during our hadoop training Chennai sessions. We at Fita, provide big data training in Chennai for students who are interested in choosing a career in big data.

    ReplyDelete
  18. Thanks for sharing informative article on Salesforce technology. Your article helped me a lot to understand the career prospects in cloud computing technology. Cloud Computing Training in Chennai

    ReplyDelete

  19. This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    Android Training in Chennai
    Ios Training in Chennai

    ReplyDelete