Report models have been widely used to help business users to create reports without knowledge of complex data relationships. A report model maps underlying database tables, views, and pre-defined queries into data entities that are meaningful to business users. Report models are often used as data sources for reports created with SQL Server Reporting Services (SSRS) and Report Builders (RB). It can be an easy task for a report writer to design reports using RB and a report model as data source. This is especially true when a single dataset is created out of a report model and used for a report. However, a simplicity and easy-use comes with less flexibility.

A generally easy join using SQL Server connection as the data source and a query as dataset can be a challenging task using report model as the data source. Take the following dataset as an example (Table 1). Our goal is to create a report that displays latest transaction of each employee (data rows in yellow). It is an easy report by using SQL Server connection as the data source. However, there is no single easy way to do it through a report model. This article shows how to use the report function lookup() as a mean to bridge two datasets to create such a report using a report model.

Table 1. Employee Activity

Step 1. Create a dataset EmpAct

Load the Report Builder application. In the report Data pane on the left side of the Report Builder right click Data Sources. Then click Add Data Sources. In the Data Source Properties window type in EmpHistory in the name text box. Select option Use a shared connection or report model. Browse the report model that contains Employee History data as shown above. That creates a data source named EmpHistory.

click ok twice to exit the dataset creation process. 

Step 2. Create dataset MaxEffDate

Right click Data Source EmpHistory and select Add Dataset. Select the Entity that contains employee history data (PHIS Action). Click New Field button that locates between Entities pane and Fields pane. In the Field name text box type maxDate. Type MAX(ActDate) in the Formula box as shown below. This creates a user defined field maxDate.

Then select EMPID and maxDate fields from the fields pane to create a dataset MaxEffDate as follows

Step 3. Create an Employee Activity Report

To create a report, in the Report Builder design window, select Insert menu and click Table button. This brings up the New Table or Matrix window. Select EmpAct dataset and click Next. In the Available Fields pane drag EMPID, ActDate, and Activity fields to Values pane followed by clicking Next twice. Click Finish to compete the following report using dataset EmpAct as data source.

Step 4. Create a Filter

Right click any column of the report and select Tablix Properties. In the Tablix Properties window select Filters and click Add button. In the Expression box choose ActDate field by clicking Fx button.

In the Value box, click Fx button. The Expression Window shows up as follow.

In the Expression window find the function Lookup() from the Common Functions category and Miscellaneous item. Either choose fields from the Category box and Fields item or simply type in following expression:

=Lookup(Fields!EMPID.Value,Fields!EMPID.Value,Fields!maxDate.Value,"maxEffDate")

In the above expression, Lookup compares the value of EMPID of dataset EmpAct to EMPID in each row of the dataset called "MaxEffDate" and, when a match is found, returns the value of the maxDate field for that row.

Conclusion

The Report Builder function, Lookup(), can be used to compare data from one dataset to another dataset. It essentially serves as a join to bridge two independent datasets. This can be very powerful during report development using Report Builder and report models as data sources.