Contact Us Today!

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

CRM-Reporting-4SSRS 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

 

info@beringer.net | www.beringer.net

 

Beringer-Partner-Signature3