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

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.

 

 

 

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)

 

 

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