Lookup Function

Data sources for business applications reside in different data systems like CRM, Accounting and Financial, HRM, SCM, Ticket Management, Inventory Management Systems, and a host of others depending on the industry. When you design reports, it's not uncommon to need to aggregate data from multiple systems at the report level. ActiveReports has long supported multiple data sets in RDL and Page reports, but each data region could only be attached to one data set. This made it impossible to create a tabular report with related data from different data sets if you couldn't use SQL joins. That is why we added the Lookup function. Our Lookup function is similar to the Excel VLookup function. If you are accustomed to using that, you already have a leg up. You can use the Lookup function in an expression to get related data from different datasets that share a common field. Essentially, you use an expression in a table cell to join the datasets on a common field to retrieve related data, like a JOIN in a SQL query, except that it doesn't require SQL. You can use it with any type of data source, so long as both the source and lookup datasets have a field that shares the same data type. The basic syntax for the function is like this:

= Lookup(SourceExpression, DestinationExpression, ResultExpression, LookupDataset)

For more information, see Multiple Datasets in a Data Region. The Lookup function returns one result value (the first value returned by the expression) and is suitable for 1:1 relations. You can use it in an expression with any data-bound control. You can also use it in property value expressions and calculated field expressions.

Use Case

Let's say we want to make a table of information from the Claims table of a CMS database in Oracle. The Problem: We want the report to be easy to understand, but the PatientID field is not user friendly. Naturally we want to use the PatientName field instead. However, the PatientName field is in an HMS database in Salesforce. These are two separate transactional systems. To solve this problem, you would typically either write code to process the data before it gets to the report or create an ETL process to design a data warehouse or reporting database. Now, we can use the Claims dataset as the table's default dataset (specify it in the DataSetName property), and use the Lookup function to grab the PatientName value from the other datasource. Here is the expression I used in the PatientName cell's Value property:

=Lookup(Fields!PatientID.Value, Fields!PatientID.Value, Fields!PatientName.Value, "Patients")

Translated into plain English, that would be:

Look up the PatientID value from Claims in the Patients dataset and return the related PatientName value.

So in this example:

• the PatientID from Claims is the SourceExpression
• the PatientID from Patients is the DestinationExpression
• the PatientName from Patients is the ResultExpression
• "Patients" is the LookupDataset

=Lookup(SourceExpression, DestinationExpression, ResultExpression, LookupDataset)

Here is how our report looks at run time. The key to ensuring that the Lookup function works is that the PatientID has the same data type and matching values in both datasets. If we had a PatientID in Claims and no corresponding PatientID in Patients, the PatientName result would be null for that row. Conversely, if there is more than one record with the same PatientID in Patients, it returns the PatientName from the first occurrence of that record. Once you use the new Lookup function, you'll wonder how you ever lived without it.