Creating a new query for a historical report

Last Updated : Apr 30, 2015 |
Prolog information
Metadata
Keywords:
  1. Select Query from the Edit menu.
    STEP RESULT:
    The Query Select window is displayed.
  2. Select New.
    STEP RESULT:
    A Query Assistant window is displayed. For more information, see Adding the database items and calculations for the SELECT portion of the query window.
  3. Select Historical Database.
    STEP RESULT:
    The tables that are available for the historical database display.
  4. In the Table list, highlight the names of the tables that include the database items that you want to include in the query. For historical reports, you can select up to three table names.
  5. Select Next.
    STEP RESULT:
    A Query Assistant window is displayed. For more information, see Adding the database items and calculations for the SELECT portion of the query window.
  6. In either the Database Items or Calculations box, highlight the first database item or calculation that you will reference in the query.
  7. Select Add or double-click the item. The item is listed in the Query Items box.
  8. Repeat Steps 6 and 7 until all of the database items and calculations that you believe will be referenced in the query are listed in the Query Items box.
  9. Select Next.
    STEP RESULT:
    A Query Assistant window is displayed. For more information, see Choose functions for the SELECT items window.
  10. Highlight the first Query Item that you want to assign an aggregate function.
  11. Highlight the function that you want to assign to the item.
  12. Repeat Steps 10 and 11 until you have assigned functions to the appropriate Query Items.
  13. Select Next.
  14. Do one of the following tasks:
    ADDITIONAL INFORMATION:
    If
    Then
    You did not select more than one historical database table for your query
    Go to Step 20.
    You selected the Historical Database for your query and you are using more than one table in the query
    The Specify all field joins for the tables window is displayed. For more information about this window, see Specify all field joins for the Tables window.
    Go to Step 15.
  15. Highlight an item in one of the table lists.
  16. Highlight an item in one or both of the remaining table lists.
  17. Select Join.
    STEP RESULT:
    The join clause is shown in the Join Criteria box.
  18. Repeat Steps 15 through 17 until all of the necessary join clauses are listed.
  19. Select Next.
    STEP RESULT:
    A Query Assistant window is displayed. For more information, see Enter the SQL WHERE criteria window.
  20. In the Operand1 box, highlight the first database item or calculation that you want considered in the WHERE clause.
  21. In the Operator box, highlight the appropriate operation.
  22. In the Operand2 box, highlight the second database item or calculation that you want considered in the WHERE clause.
    If you wanted the report to select the data from the table when the number of ACDCALLS is greater than the number of ACWINCALLS, you would select acdcalls in the Operand1 box, the > sign in the Operator box, and ACWINCALLS in the Operand2 box.
  23. Select AND or OR, as appropriate.
  24. Repeat Steps 20 through 23 until you have completed the query.
  25. Select Test.
    STEP RESULT:
    CMS checks the syntax of the query and returns a message with any errors. For more information, see Appendix A: Error messages.
  26. Correct any of the errors detected in the test.
  27. Select Next.
    STEP RESULT:
    A Query Assistant window is displayed. Use this window to give your query a new name.
  28. In Name, enter the name that you want to assign to the query you created.
  29. Select Done.