The Custom SQL Query data provider type allows you to write and execute custom SQL 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. For SQL syntax, parameterizations, and macros specific to Reporting Dashboards, see About the Custom SQL Query Data Provider.
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.
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 needs to be performed against the live database or a database of similar size, 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.
The Create Data Provider Wizard is used to create new data providers. To create a new data provider:
-
From the Dashboard Viewer, click Administration from the bottom of the Dashboard Gallery screen, and then click the Create new Data Provider button from the Data Provider Administration ribbon of the Unity Client:
-
At the How would you like to create the data provider? page:
-
Click Create new Data Provider to create a completely new data provider.
-
Click Copy an existing Data Provider to use an existing data provider as the template for a new data provider. The process is the same as creating a completely new data provider except the options are pre-configured with the information from the data provider that was copied. The pre-configured options can be changed or edited to reflect the requirements of the new data provider, unless otherwise noted.
-
Click Convert reports from Report Services to use an export file from Report Services to create a custom SQL data provider.
-
-
If you clicked Create new Data Provider, the What would you like to name this data provider? page is displayed.
If you clicked Copy an existing Data Provider, the Select the data provider to copy from page is displayed. You can search for a data provider to copy by typing the name of the data provider into the Search bar in this page. Select the data provider to copy, then click Next. The What would you like to name this data provider? page is displayed.
Note:When a data provider is copied, the options in the remaining pages are pre-configured with the information from the data provider that was copied. Unless otherwise noted, the pre-configured options can be changed or edited to reflect the requirements of the new data provider.
if you clicked Convert reports from Report Services, the Select Reports to Convert page is displayed. Click Browse to select a Report Services export file (.xml) to import. The file is imported as a Custom SQL data provider. Multiple Report Services export files can be added on this screen.
To edit the details of each individual import, double-click on the file in the list. The What would you like to name this data provider? page is displayed.
- Type a name for the data provider in the Name field. The name should be unique and allow the data provider to be easily distinguished from other data providers.
- Type a brief description of the data provider in the Description field. The description should briefly explain what the data provider is and what type of data a user can expect to retrieve from it.
- Select Log data provider execution to include the elapsed time of data retrieval and runtime parameter information for this data provider in the History log. Data requests for the data provider are always logged in the history even if this option is deselected.
-
Click Next. Unless you are converting data from Report Services, the Select the type of data you would like to report on page is displayed. If you are converting data from Report Services, the Edit SQL query page is displayed.
Note:
If you copied an existing data provider this page is not displayed. You cannot change the data provider type for copied data providers.
Note:Depending on the modules licensed for your system, one or more types of data providers may be available for configuration. This image only displays the basic data providers available to all Reporting Dashboards installations.
- Select Custom SQL Query.
-
Click Next. The Edit SQL query dialog box is displayed.
-
Type or paste the SQL query into the data-entry field provided. For SQL syntax, parameterizations, and macros specific to Reporting Dashboards, see About the Custom SQL Query Data Provider.
CAUTION:
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.
Tip: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 needs to be performed against the live database or a database of similar size, 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.
To display debugging information for the replacement of macros and parameters in the SQL query entered, right-click the information icon and select Show debug output:
The debugging information is displayed below the data-entry field in the Debug Output pane:
To show/hide line numbers in the SQL editor pane, right-click the information icon and select Enable editor line numbers.
- Select Display all date/time columns in local time to convert all date-time values returned to the local time of the user accessing a dashboard.
- From the Database drop-down menu, select the database you would like to use. If any external databases have been configured, they are available for selection here. For more information, see Configuring an External Database for use with Reporting Dashboards.
-
Click Next after ensuring that the SQL entered is valid. The Configure Runtime Parameters dialog box is displayed if parameters need to be configured.
Tip:
To reduce the processing requirements of Reporting Dashboards, it is a best practice to configure required runtime parameters for every Data Provider, or configure parameters with default values to be used if a value is not entered.
Note:If there are any errors with the runtime parameters, these are displayed in the yellow warning pane above the parameters list. The Next button is not enabled until all errors are corrected.
-
Select Allow the consolidation of parameters on a dashboard to treat the same parameters from different data providers as one parameter in the Input Parameters dialog box.
Note:
In order to be consolidated, parameters must share the same data provider type, parameter ID, and have the same reverse-fill sources.
- Select Allow using default values at runtime to allow the parameters to be filled with a default value if the user does not explicitly select or enter a value.
- Select Always prompt for parameter values at runtime to prompt for parameters even if default values have been configured.
-
To configure a runtime parameter, select it in the parameters list and click Configure. The Edit SQL Parameter dialog box is displayed.
- Type a user-friendly name for the parameter in the Caption field. This is how the user will know what the parameter is for. The caption is limited to 60 characters.
-
Select the data type of the parameter from the Data Type drop-down list. The data type must be valid for the SQL parameter being configured (for example, do not use Numeric9 as the data type when the SQL parameter being configured contains letters).
Note:
The Date Time Range data type is only valid after a SQL BETWEEN clause.
-
Select Allow dynamic logical operator in order to allow the logical operator (for example <, >, =) used to evaluate this parameter to be changed. Dynamic logical operators allow for the comparison of values in a method chosen by the user. By default, the selected logical operator is used when the data provider is accessed.
Note:
If Allow dynamic logical operator and Allow multiple selection are both selected, the only logical operators available are IN and NOT IN, which allow the user to search for values within (when using IN) or not within (when using NOT IN) the parameter.
-
Select Allow wildcard operators to allow Contains, Begins with, and Ends with operators to be used to evaluate this parameter. This option can only be selected if Allow dynamic logical operator is also selected.
Note:
Queries that search for terms using the Ends with operator need to ensure that trailing spaces are taken into account. Use the RTRIM function in the SQL query to remove trailing spaces.
- Type a brief description of the data the parameter represents in the Help Text field. This text is displayed when a user hovers the cursor over the parameter name in the output display. The help text is limited to 255 characters.
-
To configure the runtime parameter to use a drop-down list, select the list type from the Value select list drop-down list. This list includes configured global select lists.
Note:
The data type of the Value select list must match the data type of the runtime parameter.
To create a new select list to add to the Value select list drop-down list, or to edit an existing select list already available in the Value select list drop-down list, follow these additional steps:
-
Click the add/edit button at the right of the Value select list drop-down list.
Select New to add a new select list.
If you are editing an existing select list, make sure the select list you want to edit is selected in the Value select list drop down, then click the add/edit button and select Edit.
The Manage Select Lists dialog box is displayed.
-
If this is a new select list, the Select List Name dialog box is displayed over the Manage Select Lists dialog box.
- Type a name for the select list in the field provided. This is the name that is displayed in the Value select list drop-down list on the Edit SQL Parameter dialog box.
- Click OK. The new select list is added and selected, but requires list values before it can be used.
-
In the right pane, select a source of the values from the Select list source drop-down list.
The list source options available include the following:
List Source
Description
Manually enter values
Type the values for the select list in the grid that is displayed. The Label is how the item is displayed in the drop-down list and the Value is the value that is included in the data query. To add a new value, press the Enter key. A new blank row is added to the grid. To change an existing Label or Value, select it in the grid and type the new value.
Note:Manually entered values are limited to 255 characters.
Get values from a SQL query
Enter the SQL used to build the label/value pairs for the drop-down list from the data that is returned. To validate the SQL entered, click Validate SQL at the bottom of the data-entry field. The SQL statement entered must return exactly two columns, one for the Label and the other for the Value(the Label is how the item is displayed in the drop-down list and the Value is the value that is included in the data query).
CAUTION:SQL statements should only be edited or written by a certified database administrator. Poorly constructed SQL statements may cause reports to return unexpected results or could cause Reporting Dashboards to close unexpectedly, and 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.
- Click Close. You are returned to the Edit SQL Parameter dialog box.
-
Select the list from the Value select list drop-down list.
Note:
The data type of the select list must match the data type of the runtime parameter.
-
Click the add/edit button at the right of the Value select list drop-down list.
- Select Restrict user input to select list values to restrict user input for the parameter to only the options in the drop-down list. If this option is deselected, users can type a value for the parameter or select it from the drop-down list.
- Select Allow multiple selection to allow users to select more than one value from the drop-down list. If this option is deselected, users can only select one value from the select list. This option is only available if Restrict user input to select list values is also selected.
-
Select Collapse multiple selection to allow users to collapse the multiple selection list for this parameter. This option is only available if Allow multiple selection is also selected.
Note:
Without Allow dynamic logical operator enabled, the Allow multiple selection option is only valid in an IN SQL clause.
- Select Input required to require users to enter a value for that parameter. This option is selected by default, but can be deselected if a parameter is optional.
- Click OK on the Edit SQL Parameter dialog box to save the configuration and return to the Configure Runtime Parameters dialog box.
- Configure any other runtime parameters.
-
Click Next. The Choose the display columns for the data provider dialog box is displayed.
Note:
If the Restrict user input to select list values option was selected in the Edit SQL Parameter dialog box, you are required to select a default value for the select list prior to selecting display columns for the data provider. Select a value from the drop-down select menu and click Next. The Choose the display columns for the data provider dialog box is displayed.
The display columns available depend on the data provider type being configured. If the data provider being configured allowed you to pre-select the display columns available, only the display columns selected earlier in the configuration process are available.
-
Select a display column to include it in the results, or deselect it to hide it.
Note:
In order for users to be able to open a document directly from a dashboard, the system Keyword Type Document ID must be included as a display column.
Click None to deselect all display columns or click All to select all display columns.
Tip:A preview of the type of data that is returned with the display columns selected is displayed in the preview pane at the right of the list of display columns. If the data returned is not acceptable, select different display columns or click Previous to reconfigure previous aspects of the data provider.
-
To reorder the display columns, drag-and-drop the column headings in the preview pane to put them in the desired order.
-
To rename a display column, double-click the name in the list, and type the new name in the field provided. Click anywhere outside the field to save the changes.
Note:
Display column names must not be blank or end with a space.
- If you are converting a report, click Finish. You are returned to the Select Reports to Convert dialog box. Double click on other reports being converted to configure them. Once all reports are configured, click Finish to complete the creation of the data provider. If you are configuring any other type of data provider, continue to the next step.
-
Click Next. The Who should have access to the data provider? dialog box is displayed.
-
To grant access to a new user, click Add. The Select Users dialog box is displayed. Only users and users groups with access to Reporting Dashboards are listed.
Tip:
To show only users, select Specific User from the drop-down list, and to show only User Groups, select User Group from the drop-down list. To find a specific user or User Group, type the first few letters of the name or the full name in the Find... field and the list is filtered accordingly. To show only previously selected users, select Only show selected items.
-
To grant access to the dashboard or report to a user or User Group, move the cursor over that user or User Group's row and select the check box that is displayed.
To remove a user or User Group's access, deselect the check box beside their name.
Note:Access cannot be changed for the owner.
-
Click OK to save your user selections.
CAUTION:
When any dashboard or report is accessed by a user, data for all configured items (such as Document Types or Workflow queues) is returned, even if the user accessing the dashboard or report does not have access to those items in OnBase.
-
To remove users or User Groups, select the users or User Groups to remove in the list and click Remove, or right-click the user's name and select Remove. You are not prompted to confirm this action.
Note:
Access for the user account that created the data provider cannot be removed or changed. The data provider creator always has full access to the data provider.
-
To change a user or User Group's access level, right-click the user's name in the list and select Full Control. A check mark will appear next to Full Control to indicate that user or User Group has Full Control privileges. If the check mark is not present, the user or User Group only has Execute Only privileges. Full Control allows the user to configure the settings for the data provider, including user access. Execute Only allows the user to use, but not configure, the data provider.
Note:
The owner is the user who created the data provider. Access cannot be changed for the owner.
- Click Next. The Summary dialog box is displayed. Review the information in the main pane to confirm that the data provider is correctly configured. If not, click Previous to return to the various configuration dialog boxes so that changes can be made.
- Click Finish. The data provider is saved and is available for use with dashboards.