SQL Server Reporting Services Lookup, LookupSet and MultiLookup Functions

By:   |   Comments (15)   |   Related: > Reporting Services Development


Problem

I noticed the Lookup function in SQL Server 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 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.

SSRS Lookup Function

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

there are three lookup functions in SQL Server 2008 R2 Reporting Services

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 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.

SSRS LookupSet Function

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

The LookupSet function

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:

use the Join function which will transform the list of items into a single string and put in a comma to delimit each item

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.

SSRS MultiLookup Function

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

The MultiLookup function

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:

The Lookup functions are a welcome addition to Reporting Services

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, March 21, 2013 - 5:27:16 AM - Ray Barley Back To Top (22928)

I found this in the book MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook:

 

WITH
MEMBER [Internet Sales CP] AS
   Sum( { [Date].[Calendar].[Date].&[20071201] :
          [Date].[Calendar].[Date].&[20071224] },
        [Measures].[Internet Sales Amount] )
MEMBER [Internet Sales PP] As
   Sum({ [Date].[Calendar].[Date].&[20071201] :
         [Date].[Calendar].[Date].&[20071224] },
         ( ParallelPeriod( [Date].[Calendar].[Calendar Year],
                           1, 
                           [Date].[Calendar].CurrentMember ),
       [Measures].[Internet Sales Amount] )
      )
   , FORMAT_STRING = 'Currency'
MEMBER [Internet Sales YoY %] As
   iif( [Internet Sales PP] = 0, null,
        ( [Internet Sales CP] / [Internet Sales PP] ) )
   , FORMAT_STRING = 'Percent'      
SELECT
   { [Internet Sales PP],
     [Internet Sales CP],   
     [Internet Sales YoY %] } ON 0,
   { [Product].[Color].MEMBERS } ON 1
FROM
   [Adventure Works]
 

Wednesday, March 20, 2013 - 6:00:33 PM - Frank Piacente Back To Top (22920)

 

Thanks Raymond,

 

I do have the concatenated fields in calculated fields on both datasets.  The problem I'm having is that if I use two concatenated fields, it returns

data (although not the correct data).  If I try to use three or more concatenated fields, it returns no data.

Wednesday, March 20, 2013 - 8:31:45 AM - Raymond Barley Back To Top (22896)

If you want to do a lookup on multiple columns I would try adding a calculated field to each dataset and use an expression that concatenates the multiple field values into the calculated field.  Right click on your dataset and select Add Calculated Field. 

Tuesday, March 19, 2013 - 7:31:05 PM - Frank Piacente Back To Top (22884)

 

I am trying to use the lookup function using multiple columns.  I get returned data when I concatenate two columns together, and use the calculated field in the lookup expression.  When I try to concatenate three columns, it doe not return any data.  Is there a trick to using more than one column in the Lookup function?

Monday, March 11, 2013 - 8:18:22 AM - Raymond Barley Back To Top (22703)

What I showed is how to take columns and turn them in to rows; i.e. this is referred to as unpivot.  The above is a query from a table.  You have to figure out how to unpivot whatever your query is.  Unpivot is probably one of the hardest things to do in T-SQL.  The syntax just isn't intuitive to me.

Sunday, March 10, 2013 - 11:36:59 PM - Chaerul Back To Top (22697)

Dear Raymond, i think this can'not solve my problem, because my source is result of query from table.. any other way?

Friday, March 8, 2013 - 8:34:25 AM - Raymond Barley Back To Top (22664)

I think what you need is the unpivot (available in SQL 2005 and later)

Here's an example:

create table t

(

c0 int identity,

c1 varchar(50),

c2 varchar(50)

)

insert into t values ('baris 1', 'baris 2')

select row_number() over(order by cx), unpivotcol

from t

unpivot(unpivotcol for cx in ([c1], [c2])) u

 
The output from the select is:
 
                     unpivotcol
-------------------- --------------------------------------------------
1                    baris 1
2                    baris 2

Friday, March 8, 2013 - 6:10:11 AM - Chaerul Back To Top (22656)

i had one row from database when i view this data on reporting service i get view:

baris 1 baris 2

i want to view on reporting service to be

  1. baris 1
  2. baris 2

please help?

Thanks

Wednesday, July 25, 2012 - 9:32:09 AM - viral Back To Top (18798)

Yes, SSIS is better option, but I need to list out all these data at runtime and that too without linkserver to avoid performance issue, so SSIS / nightly process  might not help here.

Please let me know, if you have any suggetion / option too.

 

Thanks for your prompt reply.

 

Wednesday, July 25, 2012 - 8:20:41 AM - Ray Barley Back To Top (18796)

The Lookup is essentially a join and what you are looking for is a union so I don't think the Lookup will help you.

A simple solution might be an SSIS package that copies the table from one server to another so you can get all of the rows in both tables from a single server.

Wednesday, July 25, 2012 - 5:47:39 AM - viral Back To Top (18794)

Hi

 

I have 2 same tables in 2 different servers (say both have table name 'SalesPersonNames' which has field 'SalesPersonName' VARCHAR(Max)). Now to avoid Link server, I need to use share datasource .

So my first datasource will pick 'SalesPersonName' from first server  and another datasource will pick 'SalesPersonName' from 2nd server and I need to show in single List with alphabetical order.

In SQL , i can use 'Union' and can use linkserver or OpenRowQuery  with 'Order By' on 'SalesPersonName' can do this , but can I do the using Lookup to avoid link server?

Thursday, May 31, 2012 - 12:05:52 PM - Sgedela Back To Top (17751)

Hi Thanks for the reply..

Finaly I Got it worked...the function you referred me helped ! Thanks a lot

Wednesday, May 30, 2012 - 4:40:27 PM - Ray Barley Back To Top (17738)

I think with a report the only way a user can enter multiple values would be if they separate them with some sort of delimiter; e.g. a comma.

Then you query needs to transform the delimited values into a table that you can join with your existing table where you figure out whether the phone number exists or not.  To transform the delimited values into a table you can use some sort of split function; here's an example: http://www.mssqltips.com/sqlservertip/1665/sql-server-clr-and-tsql-functions-to-parse-a-delimited-string/ has a T-SQL function that will parse a delimited string and return a table.

 

 

Wednesday, May 30, 2012 - 2:40:49 PM - sgedela Back To Top (17735)

Hi

I need to create report where user checks by entering multiple phone numbers and checks whether those number exists or not and I need to display by saying match found and not found along with phone numbers he enters...

For example I have these phone numbers in my table 

2018087621
2018267812
2018464973
2018562444
2018661915
2018660946
2018369037
2018563128
2018552019
2018562796

If I pass ‘2018087621’ in the parameter I get results like this 

Phone Number Match Found 
2018087621 Match Found

If I enter wrong phone number '2018087620’ I get results like this

Phone Number Match Found 
2018087620 Match not Found



But If I wanted to enter multiple values like ‘2010087620’ and 2010087621’ I need to get results like this

Phone Number Match Found 
2018087621 Match Found
2018087620 Match not Found

I am able to do first two scenarios and not sure how to get the third one..

any help would be appreciated !

Thanks
SG

Wednesday, November 3, 2010 - 5:08:55 PM - Dayn Barlow Back To Top (10331)

Comment:

For "MultiLookup Function" portion of the article

Keep in mind that "IN" clause will work natively with interger parameters where as varchar based parameters must be parsed to add the '' around the value for the in clause.  I came upon this example that worked well for my needs.

Select DATA

WHERE Disp

.Disposition IN (SELECT ITEM FROM dbo.fncSplit2Table(@Disposition,','))

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Utilizing the below table function

ALTER

 

 

FUNCTION [dbo].

[fncSplit2Table]

(

@ItemList

 

NVARCHAR(4000

)

 

 

,@delimiter CHAR(1

))

 

RETURNS

 

 

@IDTable TABLE (Item VARCHAR(50) PRIMARY KEY CLUSTERED)

AS

 

 

BEGIN

 

 

DECLARE

 

 

@tempItemList NVARCHAR(4000)

SET

 

 

@tempItemList =

@ItemList

DECLARE

 

 

@i INT

DECLARE

 

 

@Item NVARCHAR(4000)

SET

 

 

@tempItemList = REPLACE (@tempItemList, ' ', '')

SET

 

 

@i = CHARINDEX(@delimiter, @tempItemList)

WHILE

 

 

(LEN(@tempItemList) > 0)

BEGIN

 

 

IF @i =

0

 

 

SET @Item =

@tempItemList

 

 

ELSE

 

 

SET @Item = LEFT(@tempItemList, @i - 1)

 

 

INSERT INTO @IDTable(Item) VALUES(@Item)

 

 

IF @i =

0

 

 

SET @tempItemList = ''

 

 

ELSE

 

 

SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)

 

 

SET @i = CHARINDEX(@delimiter, @tempItemList)

END

RETURN

END














get free sql tips
agree to terms