Reporting Dashboards does not validate or correct SQL statements. All errors reported by the database are reported in the OnBase Diagnostics Console.
Queries are executed against the database using a read-only viewer account. Statements that attempt to modify the database will result in errors.
The following syntax limitations and rules are applied to all custom added to Reporting Dashboards:
-
Only statements that return a single result set are supported (e.g., statements cannot include multiple GO statements to perform evaluation queries).
-
Expressions must return at least one output column.
-
Literal column values in the SQL query should be cast to the desired type in the SQL text.
-
Output column names from a query must begin with an alphabetic character or an underscore character (_).
-
Output column names can only contain alphanumeric characters and underscores (_).
-
Output column names cannot contain spaces.
-
Comments are not supported in the custom SQL editor and may cause issues if included.
Output column names can be changed to user-friendly values during configuration of the display columns for the data provider. These restrictions only apply to the underlying executed by Reporting Dashboards.
The following data types are supported by Reporting Dashboards but are transformed into the corresponding data type.
For Oracle databases, all SQL functions or expressions that are expected to return a numeric value (e.g., COUNT) must be explicitly CAST to NUMBER(38,0) in order to ensure that Reporting Dashboards returns the column as numeric. For example, SELECT CAST(COUNT(*) as NUMBER(38,0)) "YourColumn" FROM some.dbtable. This condition only applies to Oracle databases.
|
Data Type |
Transformation |
|---|---|
|
boolean |
boolean |
|
byte |
boolean |
|
date |
date-time |
|
datetime |
date-time |
|
decimal |
currency |
|
double |
decimal |
|
long/int (any size, including unsigned versions) |
numeric |
|
NULL |
string |
|
single |
decimal |
|
string |
string |
|
time |
date-time |
|
timestamp |
date-time |
All date-time values are returned in the same timezone as they were stored on the database server. To convert these values to the local time of the user accessing a dashboard, select Display all date/time columns in local time when configuring the data provider.