Custom SQL Reporting Overview

Last Updated: 12/09/2015 Introduced in Verision: 2.0
The primary difference between a Custom Datasource and a SQL Datasource is that a Custom Datasource is responsible for producing the data needed for reporting engine while a SQL Datasource produces a select statement to be run against the database to get the data needed for reporting.
 
While both have their place, there are a number of advantages to using SQL Datasources, the primary one is databases are tuned specifically to handle large collections of data, filtering and ordering.  SQL Datasources take full advantage of the databases 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 in this are 
- Apply (modify the CompositeSelectStatement)
- FilterApplies (evaluate if based on current datasource definition, if you are able to apply your changes to this.  An example is if you are able to modify a statement based on task data, and the current query is about account data… they you are not able to be applied)
 
Here is an example:

 

Additional Resources