Custom SQL Reporting Overview
  • Updated on 09 Dec 2013
  • 1 minute to read
  • Print
  • Dark
    Light

Custom SQL Reporting Overview

  • Print
  • Dark
    Light

Overview

You 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 whereas a custom datasource is responsible for producing the data needed for reporting engine.

While both have their place, there are a number of 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 a number of 'sub structures' 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
  • 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 datasource 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 
        { 
        }
    }
}
Was this article helpful?