PPM Dashboard Datasource

If you have some Dashboard Datasources already defined in the workbench with the relevant data and filters, or if you prefer to avoid writing hard-to-maintain SQL in the Excel template, it is possible to retrieve data directly from Dashboard Datasources in the Excel template. You can retrieve the Datasource in the template by using the Datasource name.

It is possible because you can easily create a Dashboard Datasource (and a corresponding List Portlet) in a SQL statement from the Administration Console > Administration Tasks > SQL Runner menu. Input your SQL statement, and click Create Dashboard Datasource. You may still need to do some column adjustments in the workbench and create all the filters. This saves you the hassle to create all the columns with the relevant types.

In order to get data from a Dashboard Datasource, use the following placeholder: ${datasources.get('My Datasource Name').getData()}.

If you use more than one language on your PPM instance, this method only works with the datasource name defined in the language of the user running the Excel Report. Therefore, if you plan to have users running this report in multiple languages, you should rather use the method “getFromId()” that uses the datasource ID instead of the datasource name as a parameter:

${datasources.getFromId(34567).getData()}

You can get the datasource ID from database table KDSH_DATA_SOURCES by running the following SQL query on PPM database:

SELECT DATA_SOURCE_ID FROM KDSH_DATA_SOURCES WHERE DATA_SOURCE_NAME = '<Insert the name of your datasource here>'

You can also add filters to the datasource by calling the method addFilter(‘FILTER_NAME’, filter_value) as many time as needed before calling the method getData(). For example, you have a report field using PPM User validation stored in the P_USER_ID token. If you want to use the filter CREATED_BY on the PPM Dashboard Datasource listing programs, you can use the following placeholder: ${datasources.get('Program List').addFilter('CREATED_BY', P_USER_ID).getData()}.