Looking Up Data On Different Sources
Almost a year ago I wrote a small blog post to ask everyone to vote on a certain Connect item related to the linking of two datasets in a Reporting Services report. At this moment there have been 87 positive votes on the request.
Now that SQL Server 2008 R2 has been RTM’ed, it’s time to demonstrate new functionality in SSRS that allows the retrieval of data from another dataset, a dataset not linked to your data region. It’s not completely the same as actually joining two datasets, but it’s better than nothing – and it works!
In this article I’ll 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
Using that dataset, I’ve set up a Table as shown in following screenshot.
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:
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
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
So, what does the Placeholder’s expression look like? Here it is:
=Lookup( Fields!ProductAlternateKey.Value, Fields!ProductNumber.Value, Fields!Description.Value, "dsProductInfo" )
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.TextRuns’ 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:
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:
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
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:
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" ), ", " )
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:
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
Then I’ve added a multi-value parameter called Regions with the Available Values coming from the dsRegions dataset.
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)
Don’t forget to set up the parameter.
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)', '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
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:
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>" )
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,
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:
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:
Seems to be working fine, doesn’t it?
Okay, that’s it for now, have fun looking up that data!
- Silly SQL #1: The Movie! [SSIS]
- Silly SQL #1: OLE DB Destination [SSIS]
- Deploying PDFs, and more [SSRS]
- T-SQL Tuesday 50: Automation, Automation, Automation!
- The "Select ALL" parameter option [SSRS]
- Book: SQL Server 2012 Reporting Services Blueprints
- SQL Server Days 2013 – Data Cleansing: Download
- SQL Server Days 2013
- Exploring the System.Object Package Variable [SSIS]
- T-SQL Tuesday 46: Contraptions!
- December 2014 (1)
- August 2014 (1)
- May 2014 (1)
- January 2014 (1)
- December 2013 (1)
- November 2013 (2)
- October 2013 (1)
- September 2013 (2)
- July 2013 (2)
- June 2013 (2)
- May 2013 (3)
- March 2013 (3)
- February 2013 (2)
- January 2013 (2)
- December 2012 (2)
- November 2012 (3)
- October 2012 (2)
- August 2012 (2)
- July 2012 (2)
- June 2012 (2)
- May 2012 (2)
- April 2012 (3)
- March 2012 (4)
- February 2012 (4)
- January 2012 (2)
- December 2011 (2)
- November 2011 (2)
- October 2011 (1)
- September 2011 (3)
- August 2011 (2)
- June 2011 (2)
- May 2011 (3)
- April 2011 (3)
- March 2011 (3)
- February 2011 (2)
- January 2011 (5)
- December 2010 (1)
- November 2010 (3)
- October 2010 (3)
- September 2010 (2)
- August 2010 (4)
- July 2010 (2)
- June 2010 (4)
- May 2010 (6)
- April 2010 (3)
- March 2010 (3)
- February 2010 (11)
- January 2010 (9)
- December 2009 (2)
- November 2009 (3)
- October 2009 (3)
- September 2009 (4)
- August 2009 (6)
- July 2009 (2)
- June 2009 (3)
- May 2009 (7)
- April 2009 (3)
- March 2009 (3)
- February 2009 (5)
- January 2009 (4)
- December 2008 (2)
- November 2008 (3)
- October 2008 (1)
- September 2008 (1)
- August 2008 (4)
- July 2008 (3)