Custom SQL Reporting Overview

Overview

Developers may choose to use a SQL Datasource as opposed to a Custom Datasource. A SQL Datasource produces a select statement to be run against the database to gather data used for reporting. In contrast, a custom data source is responsible for producing the data needed for the reporting engine.

While both have their place, there are several advantages to using SQL Datasources. SQL Datasources take full advantage of a database's built-in functionality to produce the data for the report.

CompositeSelectStatement

SQL Datasources rely on an object model that mirrors the structure of a SQL Select Query.  The heart of this definition is the CompositeSelectStatement class.

This object has some 'substructures' to handle all of the elements needed to construct a SQL statement, including:

• Defining which tables the data is coming from
• Defining the relationship (joins) between these tables
• Specifying which fields (and from what tables) these fields come from
• Specifying sort order
• Adding WhereConditions to filter the data

Below is a simple example of creating a simple select statement:

 CompositeSelectStatement ss = new CompositeSelectStatement(new CompositeSelectStatement.TableDefinition("my_table"));
ss.WhereConditions.WhereConditions.Add(new FieldWhereCondition("my_field", QueryMatchType.Equals, 12));
string sql = ss.GetSql();

Below is an example of where a statement is created across 2 tables.

 CompositeSelectStatement ss = new CompositeSelectStatement(new CompositeSelectStatement.TableDefinition("my_table"));
ss.WhereConditions.WhereConditions.Add(new FieldWhereCondition("my_field", QueryMatchType.Equals, 12));
ss.JoinList.Add(new CompositeSelectStatement.JoinDefinition(
    CompositeSelectStatement.JoinType.InnerJoin, 
        new CompositeSelectStatement.TableDefinition("my_other_table"), 
            new AndWhereSet(new WhereCondition[] {new FieldToFieldWhereCondition("pk1", "ak2", QueryMatchType.Equals), }) ));

string sql = ss.GetSql();

SQL Report Datasources

SQL Datasources need to implement the ISqlReportDatasource.  The key methods are:

• Apply (modifies the CompositeSelectStatement )
• FilterApplies (evaluates whether a filter will work based on a given data source definition - e.g., a query based on task data would not apply to account data)

Here is an example:

 public class MyReportDataSource : ReportFilterBase, ISqlReportDataSource
{
    public override void Apply(CompositeSelectStatement statement)
    {
    }

    public override bool FilterApplies(CompositeSelectStatement jSelect)
    {
    }

    public ReportFieldData[] ReportFields
    {
        get 
        { 
        }
    }

    public ReportFieldData[] OrderByUniqueFields
    {
        get
        {
        }
    }

    public IReportDataDriver Driver
    {
        get 
        { 
        }
    }
}