Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Looking Up Data On Different Sources

ValentinoVBI Consultant
CERTIFIED EXPERT
1998: C++ - SQL Server 6.5
2000-2007: C++, VB6, C#, java - SQL Server 7.0-2005
2008-...: SQL Server 2005-2016
2014-2017: MVP Data Platform
Published:

Introduction

As you'll probably know, a data region in a SQL Server Reporting Services report can be linked to only one dataset.  This makes it troublesome when you need to display data from more than one dataset in the same data region.  SQL Server 2008 R2 has got some interesting new functionality to facilitate data retrieval from other datasets.

In this article I will show you how exactly this works by using the new Lookup, LookupSet and MultiLookup SSRS functions.

I'm using the AdventureWorks 2008R2 relational database and the AdventureWorksDW 2008R2 data warehouse, available from CodePlex.  The main data is coming from the data warehouse while all the lookups are done on the relational database.

Setting Up A Basic Table Report

In my report I've created a dataset called dsInternetSales.  This dataset is retrieving data from the AdventureWorksDW2008R2 data warehouse using the following query:

select PROD.EnglishProductName, PROD.ProductAlternateKey, PROD.ListPrice,
                          PSC.EnglishProductSubcategoryName, PC.EnglishProductCategoryName,
                          S.OrderQuantity, S.SalesAmount
                      from FactInternetSales S
                      inner join DimProduct PROD on S.ProductKey = PROD.ProductKey
                      inner join DimProductSubcategory PSC 
                          on PROD.ProductSubcategoryKey = PSC.ProductSubcategoryKey
                      inner join DimProductCategory PC on PSC.ProductCategoryKey = PC.ProductCategoryKey

Open in new window


Using that dataset, I've set up a Table as shown in following screenshot.

 Basic Table Report
The Details group has been set up to group on ProductAlternateKey.  On top of the Details group, I've grouped on EnglishProductSubcategoryName and the top-level group is grouping on EnglishProductCategoryName.  The Order Quantity column is displaying the sum of the OrderQuantity values for each ProductAlternateKey.

The result is a report that shows all (internet) sales per product, without any filtering.  Very useful report if you want to know how many items your company has sold since it's existence.  Okay, management would probably like to see some filtering on here, but that's not the purpose of this article.

Here's what it looks like in Preview:

 Basic Table Report Rendered

Adding Data From Another Database

Imagine now that you need to add an extra line under each product, containing the product description.  But this description is not available in the data warehouse.  In fact it could even be stored on another server.

In the example here we will retrieve the description from the AdventureWorks2008R2 relational database.

Setting Up The Second Dataset
I've created an additional dataset called dsProductInfo, using the following query:

select P.ProductNumber, PD.Description
                      from Production.Product P
                      inner join Production.ProductModel PM on P.ProductModelID = PM.ProductModelID
                      inner join Production.ProductModelProductDescriptionCulture PMPDC
                          on PMPDC.ProductModelID = PM.ProductModelID
                          and PMPDC.CultureID = 'en'
                      inner join Production.ProductDescription PD
                          on PMPDC.ProductDescriptionID = PD.ProductDescriptionID

Open in new window


Not only does it retrieve the product's description, we're also fetching the ProductNumber.  Here's what part of the result looks like:

 Result of product description query
The reason that we're retrieving ProductNumber as well is because it matches with the ProductAlternateKey which we've retrieved earlier in our first dataset.  And this is very important because that's the key on which we're going to link the datasets.

Using The Lookup Function
I've added an additional row inside the Details group and inserted a Placeholder to retrieve the product's description, using the new Lookup function.

 Additional row inside Details group
So, what does the Placeholder's expression look like?  Here it is:

=Lookup(
                          Fields!ProductAlternateKey.Value,
                          Fields!ProductNumber.Value,
                          Fields!Description.Value,
                          "dsProductInfo"
                      )

Open in new window


As you can see, the Lookup function requires four parameters.

The first parameter is the key value in your current dataset, the dataset used by the table data region.  In our case that's the ProductAlternateKey field in the dsInternetSales dataset.

The second parameter is the name of the key field in the second dataset, the one on which the lookup will happen.  In our case that's the ProductNumber in the dsProductInfo dataset.

The third parameter is the field from the second dataset that you're wanting to retrieve using the lookup, in our case the Description field from dsProductInfo.

And finally, the last parameter is the name of the dataset on which you want to do the lookup.

Please note that parameter number four is a string parameter, so the value needs to be enclosed by double quotes.  If you forget about that, you'll get a couple of nice error messages like these:


[rsInvalidLookupScope]  The Value expression for the textrun 'Textbox29.Paragraphs[0].TextRuns[0]' has a scope parameter that is not valid for a lookup function. The scope parameter must be set to a string constant that is the name of a dataset.

So, don't forget the quotes.

With the Lookup call set up as explained, here's the updated report Preview:

 Rendered report with product descriptions added through Lookup
How cool is that huh?  Each product has gotten a description, retrieved from another database, and still in the same table data region.  Before R2 of SQL Server 2008, this wasn't possible to achieve (well, not easily anyway) and now it's actually fairly simple!

Is that all?  Ha, I was kinda hoping that you were going to ask that.  No, it's not all, there are two more new lookup functions: LookupSet and MultiLookup.

More Lookups: The LookupSet Function
In case you're wondering, in the Expression Builder the new lookup functions are located under the Miscellaneous node:

 Expression Builder: the new lookup functions are under Miscellaneous
Let's say that you want to add another detail row, this time it needs to show all colors in which the product is manufactured.  Again this additional info is coming from the AdventureWorks relational database.

I've created a dataset called dsProductColors using the following query:

select distinct LEFT(P.ProductNumber, 6) as ProductCodeWithoutColorAndSize,
                          P.Color
                      from Production.Product P
                      where P.Color is not null

Open in new window


When looking at the product codes, I noticed that for the products which are available in several colors and sizes, the last four characters represent the color and size.  Which means the first six characters define the product itself, without color or size.  That's why the query is using the Left function to create a product code of only the first six characters of the ProductNumber.  Using the distinct keyword, we remove any duplicate records.

(Please note that I'm not 100% sure if this logic applies to all products but for this demo it's fine.)

Here's what the query retrieves:

 List of product colors per product
As you can see, for some products there's more than one record.  And that's exactly what the LookupSet function was made for: it retrieves a set of data based on the key given to it.  This is different from the Lookup, where for each key value it would fetch only one value.

Again I've added an additional row inside the Details group and used a placeholder with the following expression:

=Join(
                          LookupSet(
                              Left(Fields!ProductAlternateKey.Value, 6),
                              Fields!ProductCodeWithoutColorAndSize.Value,
                              Fields!Color.Value,
                              "dsProductColors"
                          ),
                          ", "
                      )

Open in new window


The LookupSet call itself looks very similar to the Lookup, with the same four parameters.  I've used the Left function on the first parameter to apply the same logic to the ProductAlternateKey as we did with the ProductNumber.

However, there's one important difference: the call of the Join function.  This is needed because the LookupSet is returning a set, or better, a VariantArray, not just a single value.  And an array cannot be visualized without first concatenating the values somehow.  With the Join, we can concatenate the different values, using a comma as separator.

And here's the resulting report:

 Our report displaying the list of colors for each product
With the first two lookup functions covered there's one more to go.

Just One More Lookup: The MultiLookup Function
Guess what crazy request the business people have come up with this time?!  The report should have a multi-value filter on region, and for each region selected, the top of the table should list the number of shops opened in the first year in those regions.  For example, if the first shop in France was opened in 1970 and in that same year there were two other shops opened in France, the report should state “France: 3 shop(s) opened in 1970”.

Ow, and that list should be located right under the main header so deciding to use a textbox outside of the table is not a good idea :-)

Sounds like we can use the MultiLookup function for this request.  But let's first set up the filter.

I've created a dataset called dsRegions, using the following query on the data warehouse:

select DST.SalesTerritoryAlternateKey,
                          DST.SalesTerritoryCountry + ' - ' + DST.SalesTerritoryRegion as CountryRegion
                      from DimSalesTerritory DST
                      where DST.SalesTerritoryAlternateKey > 0

Open in new window


Then I've added a multi-value parameter called Regions with the Available Values coming from the dsRegions dataset.

 Regions parameter: the Available Values
This parameter can now be used in our main dataset.  Here's the updated query:

select PROD.EnglishProductName, PROD.ProductAlternateKey, PROD.ListPrice,
                          PSC.EnglishProductSubcategoryName, PC.EnglishProductCategoryName,
                          S.OrderQuantity, S.SalesAmount
                      from FactInternetSales S
                      inner join DimProduct PROD on S.ProductKey = PROD.ProductKey
                      inner join DimProductSubcategory PSC
                          on PROD.ProductSubcategoryKey = PSC.ProductSubcategoryKey
                      inner join DimProductCategory PC on PSC.ProductCategoryKey = PC.ProductCategoryKey
                      inner join DimSalesTerritory DST on S.SalesTerritoryKey = DST.SalesTerritoryKey
                      where DST.SalesTerritoryAlternateKey in (@Regions)

Open in new window


The only difference with the previous query are the two last lines: we add DimSalesTerritory to the joins and filter it on SalesTerritoryAlternateKey.

Don't forget to set up the parameter.

 Setting up the parameter on dsInternetSales
With the filter implemented, let's get started on that extra lookup.  

First we need to add the dataset containing the data that we need.  I've created a dataset called dsShopsOpenedInFirstYear, using the following query on the relational database:

with ShopOpened as
                      (
                          select T.TerritoryID, T.Name Territory, S.Name ShopName,
                              S.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
                              (/StoreSurvey/YearOpened)[1]', 'integer') AS [YearOpened] 
                          from Sales.Store S
                          inner join Sales.SalesPerson SP on S.SalesPersonID = SP.BusinessEntityID
                          inner join Sales.SalesTerritory T on SP.TerritoryID = T.TerritoryID
                      ),
                      FirstShopOpened as
                      (
                          select MIN(YearOpened) YearOpened, TerritoryID
                          from ShopOpened
                          group by TerritoryID
                      )
                      select SO.TerritoryID, 
                          SO.Territory + ': ' + CAST(COUNT(*) as varchar(100)) + 
                          ' shop(s) opened in ' + CAST(SO.YearOpened as char(4)) as ShopString
                      from FirstShopOpened FSO
                      inner join ShopOpened SO on SO.TerritoryID = FSO.TerritoryID
                          and SO.YearOpened = FSO.YearOpened
                      group by SO.TerritoryID, SO.Territory, SO.YearOpened

Open in new window


This query uses a couple of Common Table Expressions to get to the result as we need it.  The first CTE, ShopOpened, creates a list of all shops with their territory and the opening year.  The second CTE, FirstShopOpened uses the ShopOpened CTE to retrieve the first opening year for each territory.

And finally the main query uses both CTEs to create the following result:

 Number of shops opened in first year per territory
For each territory we've constructed a string that shows how many shops were opened in the first year of that region, and in what year it happened.  Coincidentally all regions had shops opened in 1970.

The TerritoryID corresponds with the SalesTerritoryAlternateKey, which is the value of our Regions parameter.

I've added an extra row under the top row in the table data region, and I'm using the following expression in that row:

=Join(
                          MultiLookup(
                              Parameters!Regions.Value,
                              Fields!TerritoryID.Value,
                              Fields!ShopString.Value,
                              "dsShopsOpenedInFirstYear"
                          ),
                          "<br>"
                      )

Open in new window


The MultiLookup takes four parameters, just like the two previous lookup functions.  They are all the same, except for the first one.  It may not be very obvious in the example here, but the Parameters!Regions.Value is in fact not just a single value.  It's an array because we've set up the parameter as being multi-valued.

And that's exactly what the MultiLookup function requires.  Here's the description for that first parameter, as stated in the Books Online:


(VariantArray) An expression that is evaluated in the current scope and that specifies the set of names or keys to look up. For example, for a multivalue parameter, =Parameters!IDs.value.

Just like the LookupSet function, MultiLookup returns a VariantArray, so we use the Join function to concatenate the values.

Interesting to note here is that I'm adding the break HTML tag as separator.  I want the result of the expression to be treated as HTML, so that each value retrieved ends up at a new line in the textbox.  To get this to work as expected, you need to tell the Placeholder that the resulting value should be treated as HTML:

 Using HTML in a Placeholder
Everything is now set up to have another report Preview.  The following screenshot shows the report with the data filtered on Canada, France and Australia:

 The final report performing three different lookups
Seems to be working fine, doesn't it?

Okay, that's it for now, have fun looking up that data!

PS: Y/N?

Valentino.

Originally appeared at my blog: http://blog.hoegaerden.be/2010/05/16/looking-up-data-on-different-sources 

References
BOL2008R2: Lookup Function
BOL2008R2: LookupSet Function
BOL2008R2: MultiLookup Function
6
18,777 Views
ValentinoVBI Consultant
CERTIFIED EXPERT
1998: C++ - SQL Server 6.5
2000-2007: C++, VB6, C#, java - SQL Server 7.0-2005
2008-...: SQL Server 2005-2016
2014-2017: MVP Data Platform

Comments (9)

Got it, thanks.   It will be linked servers or SSIS for me..
ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Author

Commented:
You're welcome :-)
Tks man..Very good article !!!
Easwaran ParamasivamSenior Software Engineer

Commented:
Nice artilce. Thanks.  

In a report I've more Shared DataSources. Call them as DB1, DB2 and DB3. Each datasource points to different databases from different servers. But the tables and their schemas are common to them. This is applicable for SPs/UDFs as well . Is there any way to fetch records from all of the above Datasources from a RDL and show in report?  

I know this is not the right place to ask the question. As your artilce is related to that I ask here. I would like to get solution for my problem. Please do suggest. Thanks in advance.
ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Author

Commented:
EaswaranP: as far as I can tell you were already given an answer to that question here: Is it possible to fetch records from more than on DB for a SSIS report?

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.