CRM Software Logo

Microsoft Dynamics 365 & Dynamics CRM experts provide reviews and opinions to aid professionals with the Dynamics 365 selection process.

 
 
Beringer Technology Group

New Reporting Expressions in SQL Server 2008 R2 / Microsoft CRM – Tutorial

Email | Print

SSRS 2008 R2 (SQL Server Reporting Services) 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.

CRM-Reporting-4

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.

CRM-Reporting-1

 

If you want to bring back multiple values from the second dataset, you would use:

=LookUpSet(Fields!accountid.Value,Fields!customerid.Value,Fields!name.Value, "AccountOpportunities ")

Here we are trying to list all of the Opportunities related to the Account in our first dataset.

 

CRM-Reporting-2

 

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)

 

CRM-Reporting-3

 

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

 

Beringer Associates, Inc. –  800.796.4854

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

Ask This Expert a Question / Leave a Comment

 

 
 
Show Buttons
Hide Buttons