Creating a Task Manager 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. Select Task Manager Query.
  4. On the New Query screen, enter a Name and an optional Description.
  5. 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 .Task Manager periods:

      SELECT PERIOD_ID, PERIOD_NAME FROM FCC_PERIODSWHERE APP_ID=1

    • Report Query for Task Manager and Supplemental Data Reports

      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$

      Note:

      When using $FCC_SECURITY_CLAUSE$ in the query, the FCC_TASKS must be aliased to TaskEO.

      Note:

      Because many predefined queries included with Task Manager have the Security Filter applied, you can use them as examples when building your own.

      For example, the following Report Query specifies scheduled tasks to display Task Code, Name, Assignee and Schedule for all High Priority tasks in the selected schedule.

      SELECT TaskEO.TASK_CODEAS "$TASK_CODE$" ,

      TaskEO.TASK_NAMEAS "$NAME$" ,

      ((SELECT CASE WHEN FIRST_NAME IS NULL AND LAST_NAME IS NULL THEN USER_LOGIN ELSE FIRST_NAME||' '||LAST_NAME END FROM FCM_USERS WHERE USER_ID = (coalesce(AssigneeEO.ACTIVE_USER_ID, AssigneeEO.USER_ID))))AS "$ASSIGNEE$" ,

      DeploymentEO.DEPLOYMENT_NAMEAS "$SCHEDULE$"

      FROM FCC_TASKS TaskEO

      LEFT OUTER JOIN FCC_ACCESS AssigneeEO ON (TaskEO.TASK_ID = AssigneeEO.SOURCE_ID AND AssigneeEO.ACCESS_TYPE = 'AS')

      LEFT OUTER JOIN FCC_DEPLOYMENTS DeploymentEO ON (TaskEO.SOURCE_ID = DeploymentEO.DEPLOYMENT_ID)

      WHERE (TaskEO.SOURCE_TYPE = 'DEPLOYMENT')

      AND ((((TaskEO.PRIORITY=3 )

      AND (TaskEO.SOURCE_ID=~SCHEDULE~ ))))

  6. Click Generate Query to build the query from the New Query dialog. The dialog 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. The system then generates the SQL to match the specified attributes and filters, at which time you can modify and enhance it.

    1. On the Select Type screen, select options:
      • From Query, select Template Tasks or Schedule Tasks.
      • 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.
      generate query dialog.
  7. From Select Columns, select the columns to display in the query, and then click Next.
    select columns.
  8. From Select Filters, click Create Condition or Create Condition Group and select the conditions to create the filters to create the query.
    select filters.
  9. Select OK.
  10. Optional: If you want to use the report in the future, click Generate Sample XML.
  11. Click Save.
  12. 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.

Modifying A Query

A system administrator can edit a Task Manager query or Supplemental Data query.

  1. From the Home Page, select Applications, then Reports.
  2. In Reports, select Queries if is not already selected, and then Edit from ellipses icon. next to the query you are editing.

    Note:

    If it is locked, ask the System Administrator to unlock.

  3. Select an option:
    • Task Manager Query
    • Supplemental Data Query
  4. In Edit Query, edit the information as needed.
  5. After you are done with your changes, click Save and Close.