Custom drill through

Custom drill through can be set up by Infor professional consultants or CPM administrators familiar with creating SQL queries. It requires detailed knowledge of the financial information that ties into the data loaded into the CPM model.

The queries provide drill through capability to custom tables that have the transaction details behind the data loaded into the CPM model. When you click a cell, the system determines the members in the specific dimensions that are related to that cell, and the view in use. Use these values in a query template, leading to retrieving data from a relational table that is relevant to the selected cell.

Dimension members from the selected data cell in the CPM grid are passed to the custom query template as parameters. You can use the CPM model's hierarchy functions to run queries on consolidated members so the drill through results include values from all the descendants as well as the consolidated member. Multiple levels of drill through are supported.

The basic process for setting up custom drill through:

  • Identify the custom tables that have the details behind the CPM data.
  • Define a view that shows the desired CPM data.
  • Create a SQL query template to pull information from the custom tables based on the data cell in the view.
  • Add the query to the SQLDRILL_QUERY_TEMPLATE table in the CPM model.
  • Complete the query record by specifying the name and type associated with the query.
  • If querying a data source separate from the CPM model, define a data source connection using the Manage Data Sources page, and set up leaf level drill through. By default, CPM connection information is used for executing the query against the source database. To execute the query using the connection information specified in the data source for the source database as a proxy user, the SQLDrillQuery.aspx file must be modified. Open the SQLDrillQuery.aspx file and change the value for _useProxyUser to true. See Knowledge Base article 1450997 for more detailed information.
  • Add a page that uses the reporting and data entry template with the view, drill through icon, drill through query name, and the SQL drill data source name (if applicable).