Creating a Supplemental Data Query

Creating a query is the first step in creating a custom report.

To create queries:

1. From the Home Page, select Application, and then select the report (for example, Non-Consolidation Reports).
2. From the Queries tab, select New.
3. On the New Query screen, enter a Name and an optional Description.

4. From Type, select an option:

   • Parameter Query

     A Parameter Query type is used to present a list of options that you can specify for this parameter's value. Parameter Query allows you to present a list of options used when filling in a parameter's value for a Report Query, where the list of options is not a simple Attribute already defined, but is instead a complex query that you need to define.

     This parameter query example provides a list of all Supplemental Data Manager Query periods:

     SELECT PERIOD_ID, PERIOD_NAME FROM FCC_PERIODSWHERE APP_ID=1

   • Report Query

     Select the records to be included in the report. You can apply a security filter, so users see only the data that they are authorized to see based on their roles and the reports to which they are assigned. To apply a Security Filter to a report query, add the following syntax to the end of the query WHERE CLAUSE statement.

     $FCC_SECURITY_CLAUSE$

5. Click Generate Query to build the query. The Generate Query window assists you in creating a query against the database by allowing you to select any existing attribute in the product to be queried and/or filtered against. This window has three sections, Select Type, Select Columns, and Select Filters. The system then generates the SQL to match the specified attributes and filters, at which time you can modify and enhance it.

   1. In Select Type, select the following:
      • From Module, select Supplemental Data Manager.
      • From Query, select Workflow.
      • Optional: Select the Apply Security checkbox to automatically apply the user security filter to the generated query. This applies the Security token to the query, to be filled in with the correct SQL when the report is generated.
   2. Click Next.
      
      
   3. In Select Columns, select the columns to display in query, and then click Next.
      
      
   4. In Select Filters, click Create Condition or Create Condition Group and select the conditions to create the filters to create the query.
      
      
6. Click OK.

   The Generate Query window closes and the generated SQL query is displayed in the Query field in the New Query window.

7. Optional: If you want to use the report in the future, click Generate Sample XML.
8. Click Save.
9. To test the query for errors:
   • Click Validate from the New Query dialog.

     

     The query displays in the Queries tab.

     Note:

     You can easily delete a query, or duplicate a query using the Action menu.
   • Click Validate and Explain Plan to validate the query and also generate the execution plan for this query. A .txt file that uses the same name as the query is generated. You can view or download the generated plan.

     Open this file to view the generated plan which contains the sequence of steps that is performed to run this query. If the performance of a query is suboptimal, you can use this plan to understand the cause of the problem and determine the best way to improve the query performance.

Modifying A Query

A system administrator can edit a Supplemental Data Query.

1. From the Home Page, select Applications, then Non-Consolidation Reports.
2. From the Queries tab, select Edit from next to the query you are editing.

   Note:

   If it is locked, contact your System Administrator to unlock.
3. In Edit Query, edit the information as needed.
4. After you are done with your changes, click Save and Close.

See also Data Tables Guide - Supplemental Data Manager guide for details on Supplemental Data Manager Tables and their columns, primary keys, indexes, and foreign keys, if applicable.