Contact Us Today!

SQL Server Reporting Services Adds New Expressions for MS CRM Reporting – Tutorial

SQL Server Reporting Services Adds New Expressions for MS CRM Reporting – Tutorial

CRM-Reporting-4 SSRS 2008 R2 includes some new expressions which allow us to bring into one table values from another dataset based on the table’s current dataset fields. In other words, Lookup <fieldname> from <dataset2> where <fieldname><dataset1> = <fieldname><dataset2>.


The LookUp function is based on a 1 to 1 relationship, bringing in one value based on the match.


The LookUpSet function is based on a 1 to many relationship, pulling in multiple values based on the match.


Here are some examples:


We have a dataset (AccountInfo) retrieving Account information.


We have a second dataset (AccountTotalSales) using a SUM aggregate grouped on AccountID pulling in Total Sales for each Account.


In our Table, we can now use the LookUp function in a Textbox Expression.


=LookUp(Fields!accountid.Value,Fields!accountid.Value,Fields!TotalSales.Value, "AccountTotalSales ")


The first field (Fields!accountid.Value ) in the expression is from the current dataset used by the table (Source Data).


The second field (Fields!accountid.Value ) is from the second dataset (Destination Data). The first field is looking for a match to this field.


The third field (Fields!TotalSales.Value ) is the value we are trying to return from the second dataset and place in the current table.


The last value in the expression is the Scope -- the name of the second Dataset ("AccountTotalSales ") we are trying to retrieve data from.

SQL Server Reporting



But wait, we are now getting an Error. Since LookUpSet brings back a collection, we cannot simply display it in a textbox. We need to concatenate the results. This can be accomplished using the Join function. =Join(LookUpSet(Fields!accountid.Value,Fields!accountid.Value,Fields!name.Value, "AccountOpportunities "),"," + vbcrlf)


SQL Server Reporting


We have returned all Opportunities associated to the Account, concatenating them with a comma and line break when multiple opportunity names are returned.

Microsoft Dynamics CRM 2011 Implementation and Integration Solutions
Microsoft Dynamics CRM for Wholesale Distribution
Microsoft Dynamics CRM for Prophet 21® ERP
Microsoft Dynamics CRM 2011 Product Information Page |