SSRS Report: Lookup Functions

Resouces:

• http://www.mssqltips.com/sqlservertip/2141/sql-server-2008-r2-reporting-services-lookup-functions/

Scenario: Assume that a table is bound to a dataset that includes a field for the product identifier ProductID. A separate dataset called "Product" contains the corresponding product identifier ID and the product name Name.

In the following expression, Lookup compares the value of ProductID to ID in each row of the dataset called "Product" and, when a match is found, returns the value of the Name field for that row.

=Lookup(Fields!ProductID.Value, Fields!ID.Value, Fields!Name.Value, "Product")

SQL Server 2008 R2 Reporting Services Lookup Functions

By: Ray Barley | Read Comments (4) | Print Ray is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert. Related Tips: More

Problem

I noticed a new Lookup function in SQL Server 2008 R2 Reporting Services. This sounds like something that I've needed for quite some time. Can you provide an example of how I can use this?

Solution

There are three lookup functions in SQL Server 2008 R2 Reporting Services: Lookup, LookupSet and MultiLookup. In this tip I will review each function and provide a simple report to show how it is used.

Lookup Function

I will use the following tables from the AdventureWorksDW2008R2 database in my sample report:

Assume that my report contains a Dataset for each table; e.g. SalesQuotaDataset and EmployeeDataset. I want to list the rows in the SalesQuotaDatset on the report and include the employee's name from the EmployeeDataset. You might be thinking why not just have a single dataset and get the employee name by a join. You can do that. However, the Lookup is the way to go, if what you have are two datasets and you cannot change the queries.

The Lookup function has the following parameters:

Lookup(source_expression, destination_expression, result_expression, dataset)

• source_expression - specifies the value in the current scope that you want to use as the lookup; e.g. EmployeeKey in the SalesQuotaDataset
• destination_expression - specifies the value that you want to match in the destination dataset; e.g. EmployeeKey in the EmployeeDataset
• result_expression - is what you want the lookup function to return; e.g. EmployeeName from the EmployeeDataset.
• dataset - is the name of the destination dataset; e.g. EmployeeDataset

The actual Lookup expression in my report is:

=Lookup(Fields!EmployeeKey.Value, Fields!EmployeeKey.Value, Fields!EmployeeName.Value, "EmployeeDataset")

The following is a portion of the output of my report:

The EmployeeName column has the Lookup expression; the query for the EmployeeDataset concatenates the Last Name and First Name.

---

LookupSet Function

I will use the following tables from the AdventureWorksDW2008R2 database in my sample report:

Assume that my report contains a Dataset for each table; e.g.ProductCategory and ProductSubcategory. I want to print each Product Category and include the list of its Product Subcategories, all on the same output line.

The LookupSet function has the following parameters:

LookupSet(source_expression, destination_expression, result_expression, dataset)

The parameters are the same as Lookup function described above. However, the output of the LookupSet function (i.e. the expression_result) is the list of matching values in the destination dataset. In this case I want to get the list of EnglishProductSubcategoryNames for a given ProductCategoryKey in the ProductSubcategory dataset. In order to print a list of items inside of a single cell in a report, I will use the Join function which will transform the list of items into a single string and put in a comma to delimit each item. The following expression performs the LookupSet function and applies the Join:

=Join(LookupSet(Fields!ProductCategoryKey.Value,Fields!ProductCategoryKey.Value,Fields!EnglishProductSubcategoryName.Value,"ProductSubcategory"), ",")

The sample output from my report is shown below:

I used a PlaceHolder instead of a TextBox for the Product Subcategory Names. This opens up the possibility of rendering HTML instead of just plain text.

---

MultiLookup Function

I will use the following tables from the AdventureWorksDW2008R2 database in my sample report:

I want to create a report where the user can select one or more Sales Reasons from DimSalesReason then see which sales orders had any items that are tagged with any of these sales reasons.

The MultiLookup function has the following parameters:

Multilookup(source_expression, destination_expression, result_expression, dataset)

The parameters are the same as the previous lookup functions except that the first parameter is actually a list of values. The report will have a Sales Reason parameter that allows multiple selections; the selected Sales Reasons will be printed in the report header. The report will list each sales order that contains any item that matches one or more of the selected Sales Reasons. My sample report is shown below:

There are two lookup expressions used in this report; I'm using the following MultiLookup to retrieve the Sales Reason Name for each Sales Reason selected in the multi-select report parameter:

=Join(MultiLookup(Parameters!SalesReason.Value, Fields!SalesReasonKey.Value, Fields!SalesReasonName.Value, "SalesReason"), "
")

Note that since the Sales Reason parameter allows multiple selections, Parameters!SalesReason.Value is a list containing every value selected by the user. The above expression is rendered in a PlaceHolder that has the markup type set to HTML so I can show each Sales Reason on a separate line.

The second lookup expression is a LookupSet that retrieves the list of Sales Reasons for all items on the order:

=Join(LookupSet(Fields!SalesOrderNumber.Value, Fields!SalesOrderNumber.Value, Fields!SalesReasonName.Value, "SalesOrderReasonBridge"), ", ")

The SalesOrderReasonBridge dataset is using the following query which gets every Sales Order that has any items tagged with any of the Sales Reasons selected for the report and also grabs the Sales Reason Name:

select distinct f.SalesOrderNumber,f.SalesReasonKey,r.SalesReasonName  from dbo.FactInternetSalesReason f  join dbo.DimSalesReason r on r.SalesReasonKey = f.SalesReasonKey  where f.SalesReasonKey IN (@SalesReason)  order by SalesOrderNumber  

Next Steps

• The Lookup functions are certainly a welcome addition to Reporting Services; hopefully they will work for the majority of our requirements.
• You can download the three sample reports here.
• You can download the AdventureWorks sample database for SQL Server 2008 R2 from CodePlex.

Related Tips: More | Become a paid author

Last Update: 10/25/2010 

SSRS Report: If condition to set Border width for column

Scenario: You want to change the border width change dynamically.
Solution: Select the Tablix1-->Go to Properties windows-->Select Border width and click on Default and select Expression and type
=IIF((Fields!BoxNumber.Value mod 6)=0, "2pt", "1pt"), ,

SSRS Report: Switch case

=switch(
Fields!BoxNumber.Value <=6, "Mon" & Fields!BoxNumber.Value,
Fields!BoxNumber.Value <=12, "Tue" & Fields!BoxNumber.Value mod 7 +1,
Fields!BoxNumber.Value <=18, "Wed" & Fields!BoxNumber.Value mod 13 +1,
Fields!BoxNumber.Value <=24, "Thu" & Fields!BoxNumber.Value mod 19 +1,
Fields!BoxNumber.Value <=30, "Fri" & Fields!BoxNumber.Value mod 25 +1
)

SSRS Report: Limit no of columns in SSRS matrix report

you want to add a column break to the matrix control, this requirement can be acheived in Reporting Services, steps are below:

Step one: copy the following code to the custom code area

Dim FlagTable As System.Collections.Hashtable

Dim Flag AS Integer

 Function MyFunc(ByVal NewValue As Object) As Integer

If (FlagTable Is Nothing) Then

FlagTable = New System.Collections.Hashtable

End If

If (NewValue Is Nothing) Then

NewValue = "-"

End If

If (Not FlagTable .Contains(NewValue )) Then

Flag =Flag + 1

FlagTable.Add(NewValue, nothing)

End If

MyFunc = Flag

End Function

Step two: Add a list to your report

1.      Right-click the list ,and then select Properties.

2.      Click Edit details group… button

3.      Type in the expression =Ceiling(Code.MyFunc(Fields!Productname.Value)/2)

Note:

1) Fields!Productname.Value is your column group datafield

2) 2 is the number of the columns you want to display in a row

Step three: Sort the dataset by column group field, and then drag the matrix into the list

1.      Switch to the data tab.

2.      Add ‘order by datafield’ to sort the dataset by the column group datafield  here is a example:

select * from vProductProfitability

where Year=2003 and

MonthNumberOfYear in (1,2,3,4,5,6,7,8,9,10,11,12)

order by Productname

3.      Drag the matrix into the list and preview the report.

Below picture2 is just a sample for you, we can see that original matrix is too long, however picture 2 can fit the width of your report body. Of course you can reset the number of the column in one matrix.

Beside this , you can also spread the columns from one matrix into several matrixes. You can first copy one matrix and then paste it into several ones you want. Then set the filter for each column group to make sure that the total columns’ length in one matrix just fit a page’s width. However I would recommend you adopt the first method, it is more flexible than the second method.