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

Wednesday, 18 March 2015

JS Development in CRM 2011- Tips

    This post is regarding a javascript development tip which is very useful in CRM 2011. It was not my idea but one of my colleague's. And it was an eye opener for me. As we all know we write js code on form load as well as on change event of different fields in CRM 2011. At a later point have you ever struggled to see on which fields we have js code ?  I have. My colleague's suggestion was to use attach events by using the addonchange js function in CRM 2011. He also mentioned that this would make the js code more transparent. addonChange function is nothing but a js function to be called when the attribute changes. Please read the comments below. Thanks to my colleague for this recommendation.


//Form Load
//onLoad function should be added to the Form load event of Form editor as usual
function onLoad() {

    FillData();// FillData Function call on Form load
    attachEvents();
}


//Function to attach events to different fields
function attachEvents() {

    //Attach Onchange event of lookup1(new_City) and lookup2(new_Profession)
    //You could attach as many fields as you prefer
    //Please note that the function name is passed as a parameter to addonchange function
    //Its the function we would like to call during onchange event of these fields.
    Xrm.Page.getAttribute("new_City").addOnChange(FillData);
    Xrm.Page.getAttribute("new_Profession").addOnChange(FillData);
    //No need to add FillData function call on the onchange event of each field on
    //the Form editor instead here we attach it using addOnChange function.
}

//Fill data based on lookup1 - new_City and lookup2 - new_Profession
function FillData() {

    //Js code to fill in the field based new_City and new_Profession
     //For instance you could fill another field using these 2 field values
}


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 !








Monday, 30 December 2013

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.