SQL based custom Report in CRM 2011 using SSRS - Sample Report - Subreports in SSRS OR Drilldown reports in SSRS

This post aims to learn SQL based reporting for CRM 2011 using SSRS.  Please note that this report is not a perfect design but just a sample to learn subreports OR Drilldown reports.

Lets see how could we make calls  from a main report to subreports OR How could we drill down an SQL based SSRS report. ( Please note that this report is not applicable for CRM 2011 Online version )

Consider a sample scenario:

We need a list of System entities for which the auditing is enabled -- So this means that it would be our main report.

When the users clicks on the Entity,  they should be able to see the list of system attributes of this entity for which the auditing is enabled.-- This would be our subreport.

Main Report -- > Subreport

List of Entities Displayed-- >User Clicks on an Entity--> Pass the Entity name as a parameter to subreport --> Sub report catches the parameter and filter the sub report based on the Entity name received from the main report.

Time for kick off !

For a better understanding we are doing the main report first and then the subreport. after that we would be connecting these 2 reports.

1. Main Report: which lists the system entities for which auditing is enabled.

2. First step is to form the required sql query for the main report.

Please note that there are no Filtered views available for this scenario. Its always recommended to use Filtered Views for CRM 2011 SQL reports.

Here is the query for the main report. Feel free to make necessary changes as per your scenario.

SELECT DISTINCT Name AS ENTITY

FROM         MetadataSchema.Entity WITH (NOLOCK)

WHERE     (IsAuditEnabled = 1) AND (IsCustomEntity=0)

3. Create a Report server project called DEMO

4. Right click on the Report server project and a report to it. Lets name the report as 'EntityMainReport'

5. Click on the Add button.

5. Here is our report design area.

6. The next step is to define a data source. Lets right click on the Data sources and 'Add Data source'

7. Lets call the data source as DatasourceCRM. Please provide your CRM DB Connection string. You could click on the Edit button to choose your server and then CRM Database.

Nevertheless, the connection string sample is given below.

Data Source=sql server name;Initial Catalog=CRM DB

For instance, Data Source=mysqlserver;Initial Catalog=mycrm_MSCRM

8. The next step is to define the dataset. So right click on the Datasets and 'Add Dataset'.

9. Lets call it as DSetEntity. Please define the Dataset as shown below. Say 'Okay' and press 'Okay'

10. In this context we need a Table control. So lets choose the table control and drag it to the design area.

11. Entity Main Report Design.

12. Now either drag or select the ENTITY on the 'Data' cell as shown below.

13. Here is the screen shot after selection.

14. Report preview is given below.

15.  In this scenario we do not need the first and third columns. So we could remove it.

We just need to select the relevant column and Delete it.

16. Here is the modified Entity Main report preview.

17. Now lets create the subreport.

Here is the query for the sub report.

SELECT     MetadataSchema.Entity.Name AS ENTITY, MetadataSchema.Attribute.Name AS ATTRIBUTE

FROM         MetadataSchema.Attribute WITH (NOLOCK) INNER JOIN

                      MetadataSchema.Entity WITH (NOLOCK) ON MetadataSchema.Attribute.EntityId = MetadataSchema.Entity.EntityId

WHERE     (MetadataSchema.Attribute.IsAuditEnabled = 1) AND (MetadataSchema.Entity.IsAuditEnabled = 1) AND

 MetadataSchema.Entity.IsCustomEntity=0

AND MetadataSchema.Attribute.IsCustomField=0

GROUP BY MetadataSchema.Entity.Name, MetadataSchema.Attribute.Name

ORDER BY ENTITY

18. Lets add a new report namely AttributesSubReport.

19.  As shown in step 8, add a data source. And then add a data set called DSetAttributes as shown below.

20.  As shown in step 10, drag a table control. And then select the Data as attributes.

21.  We need a parent row group as Entity. It could be done as shown below.

22.  Here is the screen shot of the design. As mentioned earlier we could remove the unwanted columns and headings. Here is the modified design.

23. Attributes Subreport preview is given below.

24.  We could have a better design by modifying the table control.

Page 1 preview

Page 2 Preivew:

25. The next step is to define a parameter for the sub report. And this would be our linking item with our main report.

How to define a parameter in a SSRS report?

The first part is to define a parameter as shown below.

 Lets name it as 'Entity'. In this context the data type is Text. We could change Parameter visibility to hidden once we link this report to Main report. For now visibility = visible.

Now we need to add this parameter in our Dataset, DSetAttributes as shown below

Next step is to modify the query in such a way that the SQL query accepts this parameter. Here is the modified SQL query.

SELECT     MetadataSchema.Entity.Name AS ENTITY, MetadataSchema.Attribute.Name AS ATTRIBUTE

FROM         MetadataSchema.Attribute WITH (NOLOCK) INNER JOIN

                      MetadataSchema.Entity WITH (NOLOCK) ON MetadataSchema.Attribute.EntityId = MetadataSchema.Entity.EntityId

WHERE     (MetadataSchema.Attribute.IsAuditEnabled = 1) AND (MetadataSchema.Entity.IsAuditEnabled = 1) AND

 MetadataSchema.Entity.IsCustomEntity=0

AND MetadataSchema.Attribute.IsCustomField=0 AND

(MetadataSchema.Entity.Name = @Entity)

GROUP BY MetadataSchema.Entity.Name, MetadataSchema.Attribute.Name

ORDER BY ENTITY

Please note the added condition

 AND

(MetadataSchema.Entity.Name = @Entity)

Apply this query in the Dataset, DSetAttributes and press ok to save it.

In the preview, the user would be prompted for the parameter.

For contact entity, here is the result.

26. Now we are going to link main report and subreport.

We need to go back to Entity main report and refer the subreport from there.

Select the Entity text box properties as shown below.

27. Select the Action area and then choose the report

Action-->Go to report-> Choose report--> Add parameter --> Choose parameter 

as shown below.

This means the when the user click on the entity, it would pass the text box value and open the sub report.

28. Time for Testing !

Main report preview.

When the user clicks on the Systemuser entity the main report drills down to subreport and displays items for the selected entity. The next part is to hide the highlighted user prompt.

The user prompt could be hidden as shown below.

And here is the subreport after choose Systemuser from the main report. Its possible to traverse back from the subreport  to main report as shown below.

Free ebooks from Microsoft Press

Merry Xmas !

Here are some free ebooks from Microsoft Press.

http://blogs.msdn.com/b/microsoft_press/archive/tags/ebooks/

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.