Information Management - Pulling Data from Multiple Systems

In ActiveReports, a Page Report allows a report designer to create structured reports, such as tax forms, healthcare forms, government applications, legal pleadings, invoices or catalogs, and more. While designing such reports, where the data is aggregated from multiple data systems, it becomes essential to understand how to fetch data in a report.

In the past, retrieving data from multiple data systems in a Page Report was challenging. It required either manual aggregation or writing code to process the data. Report designers could use SQL Joins; however, use of SQL Joins is limited to a single SQL database and can't be used when:

1. Data comes from independent data systems.
2. Data is stored in file-based sources (such as XML, CSV, and JSON).

Information management improvements

While ActiveReports 11 introduced Lookup Function, ActiveReports 12 introduced DataSet Joins and SubReports. Since these versions were released, you can now pull data from multiple data systems within a Page Report.

In this blog, we'll learn about these features, using a form-based report as an example.

When you may need to pull data from multiple systems

Let's say a filing agency that provides financial reporting solutions and disclosure management systems would like to expedite their turnaround time for any filing (including a Form D issued by the U.S. Securities and Exchange Commission) for various companies.

This agency has the mechanism in place to maintain the data required -- the information about the company, such as the names and addresses of the company's executive officers, detailed financial data, the date of the first sale, etc. However, they are still helping their clients with manually filing the Form D with these pieces of information. This can be an error-prone, time-consuming process.

Another option is to use pre-populated forms that would omit the need to manually fill the form with the basic information. Once the form is there, they can always populate the data from their database management systems and send these pre-filled forms to their clients ahead of time -- helping them meet their regulatory deadlines on time.

Let's see the design and data requirements for creating the above form-based report using ActiveReports.

Report: design requirements

For the scope of this blog, we'll replicate the first two sections - "Item 1" and "Item 2" of the "Form D" using following report items in Page Reports:

1. TextBoxes to display the issuer information (name, jurisdiction, street address, country).
2. Table to display all the previous names of the issuer.
3. Table with a nested checkbox to display entity options and "choose one" option in the list corresponding to the issuer.

To see the complete design of form, you can download the US Securities and Exchange Commission Form. You can learn how to create a fixed page layout report in ActiveReports here.

Report: data requirements

While designing this report, the data is available to us in different XML files. This necessitates the need to fetch data in the report using multiple data sets as described below:

1. IssuerInfo: Provides personal or securities information of the issuer
2. Country: Provides distinct names of the country codes
3. PreviousNames: Provides information for previous names of the issuer
4. EntityInfo: Brings distinct options for entity types

Data binding and population

When we have the report design and data sources ready, the final step is to populate the data in the specified regions.

To get issuer data in the report, we should bind the page of the report with "IssuerInfo" dataset using FixedLayout -> DataSetName property. And use the data fields from this dataset to display information in the TextBox controls – Name of Issuer, City, Street Address, Jurisdiction etc. In order to display previous names of the issuer, Country Name and Entity options, the required report items should be bound with the respective datasets. However, doing this is not enough, as when the form is rendered, incorrect or no data appears in these report items. It happens because the dataset bound to the page, i.e., "IssuerInfo" may or may not have these bound fields.

Let's now see how this can be resolved using the Lookup Function, Dataset Joins, and SubReports to bring data from multiple data systems in a Page Report.

The Lookup function

The Lookup function is introduced in ActiveReports 11 to display data from different data systems in a bound data region. This function returns the first occurrence of the value from a collection, obtained after mapping two datasets based on a common data field. It can be used with any data source provider, given the two datasets have common fields of same data type and matching values.

In the described form-based report, we'll use the Lookup function to display the country names in a TextBox.

Here, the Lookup function is suitable to fetch data because ‘IssuerInfo’ dataset is bound to FixedPage of the report that contains data for country codes. And the country name corresponding to these code is stored in ‘Country’ dataset. The function is used as TextBox value in "Item 2" section as shown below:

The mapping for the Lookup function is defined on ‘IssuerInfo’ and ‘Country’ datasets based on the common field ‘CountryCode’. This should retrieve the value of ‘CountryName’ from ‘Country’ dataset for the matching country codes.

In another article, we discuss how to use the Lookup function in detail.

Now you may wonder, can we use Lookup function to display all previous names of the issuer, by mapping on some common field and displaying the result in a Table/List? This may not be possible because an issuer can have multiple previous names and the Lookup function only displays the first value from the collection. To display all the matching values from a collection, like multiple previous names of an issuer, ActiveReports provides another option - DataSet Joins.

Dataset Joins

Dataset Joins is introduced in ActiveReports 12 to display a set of matching data from different data systems in nested data regions.

Like the Lookup function, this feature also needs to define a mapping between two datasets – one bound on parent data region and the other bound on child data region. However, the difference lies in the way this mapping is created. While Lookup function uses an expression, DataSet Joins uses "Filters" or "DataSetParameter" properties of the child data region to define the required mapping.

For this form-based report, we use this option to list all the previous names in a Table report-item, bound to "PreviousNames" dataset as shown below:

The FixedPage is already bound to ‘IssuerInfo’ dataset as a parent data region for the report items on the page. So, we should define mapping on the child control (a Table in this case) using filters approach as shown below:

In this, the mapping is defined using CIK (Central Key Index) in both the datasets. This retrieves the previous names of the issuer (for matching CIK value) and displays it in the table.

For more details, you can read how to use dataset joins in ActiveReports 12.

The last requirement is to display the list of entity options from "EntityInfo" dataset and show the selected option applicable to the issuer. To fulfill this requirement, we should add a Table with a nested CheckBox on the design page. We'll bind it with required dataset - "EntityInfo" as shown below:

This will list the entity type options in the form during preview as shown below:

While designing the report, it may appear that we can use Lookup Function or DataSet Joins to show the selected option. This is because both "IssuerInfo" dataset (bound to FixedPage) and EntityInfo have the common field "EntityType" that can be used to create the mapping. However, this is not possible and will give incorrect results.

Let's see how we can use SubReports to achieve this requirement.

Using SubReports

The SubReport control already exists with RDL Reports in ActiveReports (it was introduced with Page Reports in ActiveReports 12). For this form-based report, we should design a new report having Table Control with a nested checkbox and bound to "EntityInfo" dataset as shown below:

Additionally, we should add a hidden parameter. We'll use it in the expression of "Checked" property of nested checkbox to display the applicable option as checked for the issuer. The value of this parameter can be fetched from the main "form-based" report.

Once the report is designed, we'll add a SubReport control to the main report to display the entity type options. Set its "ReportName" property to the report (created above) as shown below:

To select the required option in the list displayed through Subreport, a parameter is added in "Parameters" Collection editor of Subreport control as shown below:

Based on the value of the entity type from "IssuerInfo" dataset passed to the the SubReport through a parameter, the expression for "Checked" property is evaluated and the matching option appears, as selected in the form.

For more information on using SubReports, read our article, How to use SubReports in Page Reports.

Once the full report design is completed using the above options, the form will appear as shown below, displaying WYSIWYG report data from multiple sources using Page Reports.

Download the report files

Additional tutorials are available on our ActiveReports blog page.