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

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

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.

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:

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)

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.

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:

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:

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!

Valentino.

References

BOL2008R2: Lookup Function

BOL2008R2: LookupSet Function

BOL2008R2: MultiLookup Function