The Custom SQL Query data provider type allows you to write and execute custom data access statements and stored procedures. Database modifications cannot be made using the Custom SQL Query data provider type because the SQL is executed against the configured database with read-only access. Statements that attempt to modify the database will result in errors.
The information in this chapter is a guide to the SQL syntax, parameterization, and macros information that is unique to Reporting Dashboards and that can be used in SQL statements written for the Custom SQL Query data provider type. This chapter is not intended to provide instructions on creating complete, valid SQL statements, which is beyond the scope of this documentation.
SQL statements should only be edited or written by a certified database administrator. Poorly constructed SQL statements may return unexpected results or could cause the client to close unexpectedly. Queries that are not written with the index schemas and data distributions in mind can become long-running queries that consume substantial database server resources and adversely affect the performance of the entire system. In extreme cases, data could also be removed from the database or otherwise corrupted.
For details on creating a Custom SQL Query data provider in Reporting Dashboards, see Adding a Custom SQL Query Data Provider Type. This chapter includes the following supplemental information:
As a best practice, all SQL queries created for use with Reporting Dashboards should be first run in a profiler by a certified database administrator, to review the database optimizer access plan. This analysis should be performed against a test database of similar size to the production database, with similar data distribution, for all tables involved in the query. The DBA should verify that the query is well formed and not I/O intensive. The data access plans should also be documented to provide a baseline in case database performance degrades at a later time.