Global Event: OnFilterSqlStatementConstructed

The OnFilterSqlStatementConstructed Event occurs just before SQL is sent to the Data Source to retrieve data to populate the filter dropdown menu of Exago. This Event could be used to inspect, log or modify the SQL that is being used to populate the filter dropdown menu.

Signature

For custom code the args array is structured as follows:

args[] contains a string representing the filter SQL in position zero.

For .Net Assmblies the method signature is as follows:

string EventHandlerName(SessionInfo sessionInfo, string filtersSql, SqlObject sqlObject)

Expected Return

The OnFilterSqlStatementConstructed Event expects a string value to be returned.

Note

This Event will provide the SQL for the Filter Dropdown Object if that feature is being utilized. See Data Objects for more information on Filter Dropdown Objects

Example

The following example takes into account the server time zone offset and modifies the SQL generated for a filter dropdown accordingly.

//Modified SQL generated for a filter dropdown with the server time zone offset
//Uses SQL DateAdd() function to modify the SQL generated with the time zone offset
//Designed to work with DateTimeTreatedAs set to time DateTime in config file 
//Also designed to work for mssql can be modified to extend to other schema types

/*** START of Custom Code ******************************/
string filterSql = args[0] as string;
SqlObject sqlObject = args[1] as SqlObject;

if (sessionInfo.SetupData.General.DateTimeTreatedAs != WebReports.Api.wrDateTimeTreatedAs.DateTime)
   return filterSql;

Role activeRole = sessionInfo.SetupData.Roles.ActiveRole;
double offset = (double)(activeRole?.General.ServerTimeZoneOffset ?? sessionInfo.SetupData.General.ServerTimeZoneOffset ?? 0);

//Assuming only one filter is available
if (offset != 0 && sqlObject.Filters[0].DataType == Constants.DataType.DateTimeDataType)
{
   switch (sqlObject.DbConnect.DbType)
   {
      case Constants.DatabaseType.SqlServer:
      {
         //Assuming there's only one data column for the dropdown
         string columnName = sqlObject.DataColumns[0].ColumnName;
         string columnNameForReplacement = "[" + columnName + "]";
         string entityName = "[" + sqlObject.DataColumns[0].EntityDbName + "]";
         string schemaType = "dbo.";

         string oldColumnSql = schemaType + entityName + "." + columnNameForReplacement;
         string newColumnSql = "DATEADD(hour," + offset + "," + columnName + ")";
         filterSql = filterSql.Replace(oldColumnSql, newColumnSql);
         break;
      }
   }
}
return filterSql;