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.

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

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"
)

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

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"
    ),
    ", "
)

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

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)

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

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>"
)

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!

Valentino.

References

BOL2008R2: Lookup Function

BOL2008R2: LookupSet Function

BOL2008R2: MultiLookup Function

Share

Tags: , , , ,

  1. Andrew’s avatar

    Thanks for your blog post.

    It seems as though only one level of lookup is supported.

    How would I go about matching another key pair?

    Reply

  2. Valentino Vranken’s avatar

    Hello Andrew,

    I see what you mean, it looks indeed like this won’t be possible. The only way that I can think of to get this accomplished – and still use the lookup functions – is by somehow adding the different key values together into one concatenated value.

    Thanks for your comment!
    Valentino.

    Reply

  3. Luke’s avatar

    Hi Valentino

    Thanks for a helpful post.

    This question relates to your report layout rather than data sources…

    In your first screenshot the groups are displayed on top of each other. That is, [EnglishProductCategoryName] is stacked on top of [EnglishProductSubcategoryName], which is stacked on top of the data row.

    I’m using SSRS 2008 R2 and haven’t been able to replicate that, and it’s driving me nuts! The only way I can seem to organise the groups is by having a new column for each group, not a new row like yours.

    Would you mind outlining please how you built the report layout in your screenshots above?

    Many thanks in advance,
    Luke.

    Reply

    1. Valentino Vranken’s avatar

      Hi Luke, thanks for adding a comment here!

      (Luke initially emailed me with the same question, to which I responded. But I wanted to be able to post the solution for everyone so I asked Luke to put the question as a comment to my article.)

      Here’s what I answered:

      1. To start, drag a Table from the Toolbox onto the report.
      2. Now, drag ProductAlternateKey from the dataset into the Detail-level cell of the first column. Do the same for the EnglishProductName, but then into the second column.
      3. Now set up grouping on the Detail level. One way to do that is through the Row Groups pane at the bottom of the Design window. At the moment it only shows one item called Details. Hit the small arrow to open up the context menu and select Group Properties. Click the Add button under the Group Expressions label and group on ProductAlternateKey. Close the properties window
      4. Set up the third remaining column by selecting OrderQuantity from the left-click context menu. (That probably didn’t make much sense. When you hover the mouse pointer above the empty cell, you see a small icon of a table appear. Click that to get a list of all the fields in the dataset and select OrderQuantity.)

      Grouping on the Detail level has now been set up completely. You can render the report to have a look at the current result. Next up is adding the group levels above the detail level.

      1. With the table selected, we’ll again use the Row Groups pane. Click the dropdown to get the context menu and select Add Group > Parent Group. Choose the [EnglishProductSubcategoryName] field to group by and also activate the “Add group header” checkbox.
      2. That will create a new column in front of the three we already set up. But that’s not what we want. So, right-click the grey top of the new column and choose Delete Columns.
      3. In the new header row that’s currently empty, select the [EnglishProductSubcategoryName] field. To get the header to display over all three columns, you can select all three cells of the row, right-click and choose Merge Cells.

      That’s one level of grouping added. To add the next one, the system is exactly the same. Only difference is that now you need to add a Parent Group to the “EnglishProductSubcategoryName” group in the Row Groups pane. And obviously you now need to group on [EnglishProductCategoryName].

      Have fun, and thanks again for your question!
      Valentino.

      Reply

  4. Linda’s avatar

    Thank you soo much for asking this Andrew and Valentino for answering this!!! This article has been very educational and I can’t wait to try implementing these techniques at work here. It is very hard to find information on more complex forms. I’m currently struggling to set up a report for the boss where he can see an employees hours (sum and avg) for a pay-to-date and year-to-date(fiscal) range and while I’ve got a good start I’m struggling to get a date parameter working that will take the current date, figure out the pay period range and give me the sum for that period as well as their year-to-date sum/avg. I’m going to search for more articles from you but if you know of any articles I can reference I would really appreciate if you could point me to it. I’m even willing to buy a book but all the books I’ve looked at only have reports as a chapter or two and usually involved the Report Wizard or very basic reports. If you know of a book that has a lot of information on reports please let me know and I’ll hunt it down and buy it (company will cover it :) I love my company)

    Reply

    1. Valentino Vranken’s avatar

      Hi Linda,

      I’m glad you liked the article!

      Good books are indeed hard to find. But there is actually one that I would recommend, even though it’s still on my reading list at the moment (where’s the time when you need it…). Looking at the authors and the chapter titles, it really can’t be bad!

      The book I’m referring to is called “Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports”, written by Robert Bruckner (Principal Engineer at the SSRS team) and Paul Turley (a SolidQ MVP), and published by Wrox.

      Thanks for your feedback!
      Valentino.

      Reply

  5. Easwaran’s avatar

    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.

    Reply

  6. pdf book’s avatar

    Thіs piece of writing is reallʏ a good one it helps
    new web people, who aгe wishing in favor of blogging.

    Reply

© 2008-2017 BI: Beer Intelligence? All Rights Reserved