Getting Data from PPM using SQL Query

The first step to get the data into our Excel report is to come up with a SQL query that will return the data that is needed. In this example, the following SQL query (also available in Samples/1 – Your First Report/4 – SQL to retrieve resources details.sql) returns the expected results:

SELECT 
  USERNAME,
  FULL_NAME,
  START_DATE,
  END_DATE,
  DEPARTMENT_MEANING DEPARTMENT,
  MANAGER_FULL_NAME MANAGER,
  RESOURCE_CATEGORY_MEANING CATEGORY,
  roles.ROLE_NAME ROLE
FROM KNTA_USERS_V u,
  RSC_RESOURCES r,
  RSC_ROLES roles
WHERE RESOURCE_FLAG = 'Y'
AND u.ENABLED_FLAG = 'Y'
AND r.user_id       = u.user_id
AND r.PRIMARY_ROLE_ID  = roles.ROLE_ID (+);

Use it in the template to retrieve the data, using the following placeholder:

${ppmdb.execQuery('SELECT u.USER_ID, USERNAME, FULL_NAME, START_DATE, END_DATE, DEPARTMENT_MEANING DEPARTMENT, MANAGER_FULL_NAME MANAGER,  RESOURCE_CATEGORY_MEANING CATEGORY,  roles.ROLE_NAME ROLE FROM KNTA_USERS_V u,  RSC_RESOURCES r,  RSC_ROLES roles WHERE RESOURCE_FLAG = \\'Y\\' AND u.ENABLED_FLAG = \\'Y\\' AND r.user_id = u.user_id  AND r.PRIMARY_ROLE_ID  = roles.ROLE_ID (+)')}

Consider the following when executing SQL queries in the template:

  • Any SQL query inserted in the template must be on a single line
  • The SQL query is delimited by single quotes (‘). Every single quote used in the query must be escaped by prefixing two backslashes (\\)
  • You can pass parameters to a SQL query by inserting a question mark (?) in the query where the parameters stand, and pass as parameters values as extra parameters of the execQuery() method.

    For example, if you have a report field using a validation returning the resource categories and pass it in a token P_RESOURCE_CATEGORY, you could filter results in the SQL using the following code:

    ${ppmdb.execQuery('SELECT * FROM knta_users_v WHERE RESOURCE_CATEGORY_CODE = ?', P_RESOURCE_CATEGORY)}

    More information can be found on the web site of JETT, the third-party library used by Excel reports to fill in templates: http://jett.sourceforge.net/misc/jdbc_executor.html

    In order to iterate over each result returned by this SQL, use the <jt:forEach> template tag at the start and end of the line where resources info will go. (http://jett.sourceforge.net/tags/forEach.html ). This tag stores the result of the SQL query in a variable resource, and then use placeholders in each cell to display values of the different columns returned by the SQL query. For example, because the resource full name is returned in the column FULL_NAME, use the placeholder ${resource.FULL_NAME} in the cell where you want to insert the resource full name.

    The corresponding template is available in Samples/1 – Your First Report/5 – Template with SQL query.xlsx.

    After replacing the template on PPM Server with this template and re-running the report, the produced Excel report can be found in Samples/1 – Your First Report/6 - Created Report with SQL query.xlsx.