Custom SQL Reporting Overview

Last Updated: 03/28/2018 Introduced in Version: 2.0

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:
Below is an example of where a statement is created across 2 tables.

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:

 

Additional Resources