Custom SQL Reporting Overview

Overview

Developers may choose to use a SQL Data Source as opposed to a Custom Data Source. A SQL Data Source 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. Using SQL Data Sources allows users to take full advantage of a database's built-in functionality to produce the data for the report.

CompositeSelectStatement

SQL Data Sources rely on an object model that mirrors the structure of a SQL Select Query.  The CompositeSelectStatement class has 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 that utilizes the WHERE clause. 

/* Define a table and give it an optional alias */
TableDefinition myTable = new TableDefinition("entity_account", "t1");
/* Define which fields you'd like to query for */
myTable.Fields.Add(new FieldDefinition("account_id"));
myTable.Fields.Add(new FieldDefinition("email_address"));
/* 
 * Begin Creating the actial select statment 
 * This generates the following : 
 * SELECT account_id, email_address FROM entity_account as t1
 */
CompositeSelectStatement css = new CompositeSelectStatement(myTable);
/* Add a WHERE clause */
css.WhereConditions.WhereConditions.Add(new FieldWhereCondition("account_id", QueryMatchType.Equals, "ADMINISTRATOR ACCOUNT"));
/* Get the query for debug purposes. */
string sqlQuery = css.GetSql();

Below is an example of a SELECT statement that uses INNER JOIN.

/* Define Table 1, and set which fields we want to return. */
TableDefinition t1 = new TableDefinition("account_group_join", "t1");
t1.Fields.Add(new FieldDefinition("group_id"));
/* Define table 2, and set no fields to return.  This will be our joined table. */
TableDefinition t2 = new TableDefinition("entity_account", "t2");
t2.AllFields = false;
/* Create the : SELECT t1.group_id FROM account_group_join */
CompositeSelectStatement css = new CompositeSelectStatement(t1);
/* Adds the Join : INNER JOIN entity_account AS t2 */
css.JoinList.Add(new JoinDefinition(JoinType.InnerJoin, t2, new AndWhereSet(new WhereCondition[]{ 
	/* adds the: ON t1.account_id = t2.account_id */
	new FieldToFieldWhereCondition("t1.account_id", "t2.account_id", QueryMatchType.Equals)
})));
/* Optional : add a where condition */
css.WhereConditions.WhereConditions.Add(new FieldWhereCondition("group_id", QueryMatchType.Equals, "ADMINISTRATORS GROUP"));
/* Get the query for debug purposes. */
string sqlQuery = css.GetSql();

SQL Report Data Sources

SQL Data Sources implement the ISqlReportDatasource with the following key methods:

• 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)

 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 
        { 
        }
    }
}