Incorporating field data from a related table from a 2nd database into a Reporting Services report

Problem

How does one build a report based on data from one database, and incorporate lookup data from a related table from another database?

The 1st database holds facts data, the other database has a dimension table. The report is grouped by a category code that is used in the facts table, but the descriptive name of the categories (dimension) is stored in another database. The 2nd database does not necessarily reside on the same SQL server as the 1st database. Both databases and reporting services are SQL 2008 R2 versions.

Solution

There is more than one solution to this problem. 

When both databases reside on the same server and will continue to do so, then you can design the report with a single Data source and single Dataset, the query specifies the database names.

If the 2^nd database resides on another server, you could add a linked server to the 1^st server and still use the same solution as mentioned above.

If the 2^nd database resides on another server and you do not have the rights to add a linked server on the 1^st server, although you would expect other developers have experienced the same problem and also felt the need for a linked server.

When an IIS server is available web services can be created to load data from both databases.

With no IIS server available and no admin rights on the SQL servers I opted for the solution with 2 datasets and 2 data sources. The report will use a lookup expression in one of the tablix cells to read the data from the related dimension table.

Walkthrough

• Create a report server project with VS 2008.
• Create 2 Shared Data sources
• 1^st datasource connects to facts tables and is called ‘dsrc_fact’
• 2^nd datasource connects to dim tables and is called ‘dsrc_dim’

 

• Add a new report
• Use the shared datasource ‘dsrc_fact’
• Create a query that selects the necessary fields from the fact table.
• SELECT DivisionId, CustomerNr, Surname, Firstname, Street, Pcode, City, Country FROM dbo.Customer
• DivisionId is the key to the Dimension table in the second database.
• Create a Tabular report, grouped by the DivisionId.
• Call the report ‘CustomerByDivision’

• In the Report Data we see one Data source and one Dataset.

• Because I used the report wizard, my dataset is called DataSet1, rename it to ‘dset_Customer’.
• In the Report Data pane, add a 2^nd Data source
• Use the shared data source ‘dsrc_Dim’ and give it the same name again.
• In the Report Data pane, add a 2^nd dataset
• Call the dataset: ‘dset_Division’
• Use a dataset embedded in the report
• Data source: dsrc_Dim
• Query type: text
• Query:SELECT DivisionId, DivisionName FROM  dbo.Division
• Now there are 2 data sources and 2 datasets in the Report Data pane.

•  In the report group header we can add an expression to lookup the corresponding Division name in the 2^nd datasource.
• Right-click on the group header cell next to the DivisionId cell, select ‘Expression…’ from the dropdown menu.

• In the expression dialog box, type the following expression
• =Lookup(Fields!DivisionId.Value, Fields!Id.Value, Fields!Name.Value, "dset_Division")
• Reminder: the lookup function can be found in the Category ‘Common Functions/Miscellaneous’, Item ‘Lookup’.  The first lookup argument refers to the key field from the 1^st dataset, the 2^nd lookup argument refers to the corresponding key field from the 2^nd dataset, the 3^rd argument is the field value from the 2^nd dataset that needs to be returned by the Lookup function, and the last argument is the name of the dataset where Lookup needs to look.
• In the preview of the report the result is shown. Alongside each Division Id we see the corresponding division name.

Remark

With this easy solution, sorting can only be done on the Division Id , not on the Division name.