---
title: "Custom SQL Reporting Overview"
slug: "custom-sql-reporting-overview"
description: "This document shows how to use a SQL Datasource instead of a Custom Datasource in a Report. The document provides examples for code needed to create a select statement, and for implementing the ISqlReportDatasource itself. "
tags: ["SQL Reporting", "SDK"]
updated: 2024-11-19T20:00:08Z
published: 2024-11-19T20:00:08Z
canonical: "documentation.decisions.com/custom-sql-reporting-overview"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://documentation.decisions.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Custom SQL Reporting Overview

## Overview

Developers may choose to use a SQL Data Source as opposed to a [Custom Data Source](https://documentation.decisions.com/docs/custom-reporting-data-source-simple). 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.

```csharp
/* 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.

```csharp
/* 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)

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