LabLynx KB:manipulating the datasource object in AR6 reporting

This is an article specific to the Category:LabLynx knowledge base. Its context outside of LabLynx, Inc may not be apparent, thus why it appears inside the LabLynx KB namespace.

Manipulating the database object

Currently for BNI, the only access to the main report data in script is through the dataset assigned to the report’s datasource object in the report processing code-behind. In other words, the data is retrieved before the report is even run. You can manipulate the datasource object to add sorting and filtering by extracting the dataview object, adding filter and sort options, and then re-assigning the report datasource object to the dataview.

Example:

public void ActiveReport_ReportStart()

{
                System.Data.DataSet ds = (System.Data.DataSet) rpt.DataSource;
                System.Data.DataView dv = ds.Tables[0].DefaultView;
                dv.Sort = "basetestid,paramorder,reportorder";
                rpt.DataSource = dv;
}

This does not give you access to the report datasource SQL statement like we had in AR2. That however IS available with the new ELab.Net and ELabWebService

As far as raw data queries in script, the named item m_SQLConnString provides the connection string that makes life a bit easier… especially when moving rpxs from dev to test to production.

Support for changing where the data is retrieved

An update for AR6 reporting was just completed, adding support for changing where the data is retrieved. As part of the new Report Designer, you can change the default "Report data via" setting from DataSet to DataSource. With DataSource selected, the connection string and raw SQL is assigned to the report’s datasource object, and the data is retrieved as part of the process in running the report (the same as AR2). This means, like in AR2, you can intercept the SQL and modify on-the-fly in script before it is run and data for the report is retrieved.

In script you can extract and parse the SQL WHERE clause for use in additional queries, etc. This update also provides support for selecting stored procedures or tables as well as views as the main data source for the report. Additionally, the search feature allows you to type in part of the desired database object to filter the list in the "Select" drop-down list. While the Report Designer is updated universally for all at this point, it is backward compatible with the older ELabWebService and when detected, locks out the "Type" drop-down list (so views are the only option) and the "Report" data radio buttons are fixed on DataSet.

Extracting SQL from datasource when DataSource is selected

Here is an example script for extracting the SQL from the datasource when DataSource is selected:

private string _sql = string.Empty;

public void ActiveReport_DataInitialize()
{
    DataDynamics.ActiveReports.DataSources.SqlDBDataSource myds = (DataDynamics.ActiveReports.DataSources.SqlDBDataSource)rpt.DataSource;
    _sql = myds.SQL;
}

public void PageHeader1_BeforePrint()
{
    this.Label1.Text = _sql;
}