Creating a Dashboard Datasource (and List Portlet) from a SQL SELECT Statement in the Administration Console SQL Runner

You can create a dashboard datasource and a list portlet from any SQL statement that runs in the Administration Console SQL Runner.

You can choose whether the dashboard and/or the list portlet is enabled or disabled upon creation. Default choice is enabled, however you might want to disable it if it is created on a production environment and that some specific access grants should be added to it to restrict its access before it can be used by PPM Users.

Once created from the Administration Console, the datasource can be edited from PPM Workbench (to add filters and access grants) and the list portlet can be edited from the PPM Center portlet definition page, like any datasource or custom list portlet.

To create a dashboard datasource and a list portlet from the Administration Console,

  1. Log on to PPM.

  2. From the menu bar, select Open > Administration > Open Administration Console.

  3. Under the Administration Console node, select Administration Task > SQL Runner.

  4. In the SQL Statement text box, run the SQL statement that you want to create a Dashboard Datasource with.

  5. Click the Create Dashboard Datasource button.

    The Create Dashboard Datasource dialog opens.

  6. Specify names of the datasource and the list portlet that you want to create as described in the table below.

    Field/Option Description
    Datasource Name Specify a name for the datasource that you want to create.
    List Portlet Name

    Specify a name for the list portlet that you want to create.

    The List portlet creation is optional. If you keep this field empty, no list portlet will be created, and button name changes accordingly.

    Enable Datasource Select to enable the datasource upon its creation.
    Enable List Portlet

    Optional. Select to enable the list portlet upon its creation.

    If you choose to enable the List Portlet, any PPM User can add it directly to their dashboard after the portlet creation.

    Note:  

    • You CANNOT enable the list portlet but disable the datasource.
    • If you enter a name for the list portlet or datasource that already exists, you get an error message.

    For example,

  7. Click Create Dashboard Datasource and List Portlet.

    The Creation Successful confirmation pops up.

  8. Click OK.
  9. If you chose to enable the List portlet, any PPM User can now add it directly to their dashboard.

SQL Syntax Accepted and Limitation

Most of the SQL syntax is accepted as long as it is a SELECT statement.

You can use the * operator for columns, such as:

SELECT * FROM KNTA_USERS;

All columns and their types will be discovered automatically.

However, you cannot use bind variables in the SQL. If you need to use bind variables, first create the datasource with constants values set in place of variables, and then edit the datasource definition in the PPM Workbench to add the bind variables.