Defining Expressions


Concept


Steps:

  1. Begin by navigating to the Records page.

     

    Click the Reporting Tools link.

    Step 1
  2. Click the Query Manager link.

    Step 2
  3. Click the Create New Query link.

    Step 3
  4. Use the Records page to view existing records. Select an existing record to create a new query.

    Step 4
  5. The first step in creating a query is to find an existing record for the query. In this example, you will locate and use the Anticipated Aid Term record.

     

    Enter the desired information into the begins with field. Enter

     a valid value
     e.g.
     "ANTC_AID_TERM"
    .

    Step 5
  6. Click the Search button.

    Step 6
  7. Click an entry in the Add Record column.

    Step 7
  8. The Query page lists all the fields for the selected record. You use this page to select the fields that you want to use in the query.

    Step 8
  9. Click the EMPLID - EmplID option.

    Step 9
  10. Click the STRM - Term option.

    Step 10
  11. Click the NET_AWARD_AMT - Net Award ... option.

    Step 11
  12. Click the Fields tab.

    Step 12
  13. Use the Fields page to view how fields are selected for output; view the properties of each field; and to change headings, order-by numbers, and aggregate values.

    Step 13
  14. In this example, want to edit the column and sort orders for the query results.

     

    Click the Reorder / Sort button.

    Step 14
  15. Use the Edit Field Ordering page to modify the order of the columns.

    Step 15
  16. You want to view the term values first, followed by the IDs, and then the amounts.

     

    Click in the New Column field.

    Step 16
  17. Enter the desired information into the New Column field. Enter

     a valid value
     e.g.
     "2"
    .

    Step 17
  18. Click in the New Column field.

    Step 18
  19. Enter the desired information into the New Column field. Enter

     a valid value
     e.g.
     "1"
    .

    Step 19
  20. You want to sort the data by term and then by ID within each term.

     

    Click in the New Order By field.

    Step 20
  21. Enter the desired information into the New Order By field. Enter

     a valid value
     e.g.
     "1"
    .

    Step 21
  22. Click in the New Order By field.

    Step 22
  23. Enter the desired information into the New Order By field. Enter

     a valid value
     e.g.
     "2"
    .

    Step 23
  24. Click the OK button.

    Step 24
  25. Next, you need to add the expression to divide the net amount by four to get a monthly amount result.

     

    Click the Expressions tab.

    Step 25
  26. Use the Expressions page to add expression criteria.

    Step 26
  27. Click the Add Expression button.

    Step 27
  28. Use the Edit Expression Properties page to select the expression type, and to enter expression text.

    Step 28
  29. Select an Expression Type from the drop-down list.

     

    Click the Expression Type list.

    Step 29
  30. If you select Character, enter the maximum length of the expression result in the Length field. If you select Number or Signed Number, enter the total number of digits in the Length field and the number of digits after the decimal point in the Decimal field.

     

    In this example, you are calculating a number.

     

    Click the Number list item.

    Step 30
  31. In the Length field, ensure that you set the integer to a large enough number so that it will not truncate the number if you have not reserved enough places. For example; if you assign Length=2 and your result is 125, it will only display 12 because you have only reserved two places.

     

    Click in the Length field.

    Step 31
  32. Enter the desired information into the Length field. Enter

     a valid value
     e.g.
     "10"
    .

    Step 32
  33. If you entered Number or Signed Number as the expression type, enter the number of digits to the right of the decimal.

     

    Click in the Decimals field.

    Step 33
  34. Enter the desired information into the Decimals field. Enter

     a valid value
     e.g.
     "2"
    .

    Step 34
  35. Select the Aggregate Function check box to create an aggregate function, such as Sum, Avg, or Count.

    Step 35
  36. Click in the Expression Text field.

    Step 36
  37. If you know the field name, you can enter it. Precede the field name with an alias (A, B, and so on). If you mistype the field, you will receive an error message. Alternatively, you can click the Add Field link to add a field to this expression.

     

    Add the additional expression text to the field name. For example *2 to multiply by two, and /3 to divide by three.

     

    Enter the desired information into the Expression Text field. Enter

     a valid value
     e.g.
     "A.NET_AWARD_AMT/4"
    .

    Step 37
  38. Click the OK button.

    Step 38
  39. You maintain expressions on the Expressions page. You can:

    • View all expressions.

    • Modify expressions by clicking the Edit button.

    • Delete expressions by clicking the Delete (-) button.

    Step 39
  40. To display the result of the calculation in the query's output, select the expression for output and change the heading.

     

    Click the Use as Field link.

    Step 40
  41. An expression can be treated just as if it were a field in the query: select it for output, change its column heading, or choose it as an “order by” column.

     

    Click the Edit button.

    Step 41
  42. Use the Edit Field Properties page to enter edits, updates, or changes to field properties.

    Step 42
  43. Click in the Heading Text field.

    Step 43
  44. Enter the desired information into the Heading Text field. Enter

     a valid value
     e.g.
     "Monthly Amount"
    .

    Step 44
  45. Click the OK button.

    Step 45
  46. Click the Save As link.

    Step 46
  47. Use the Enter a name to save this query: page to specify a name and description for the new query you created.

    Step 47
  48. Enter the desired information into the Query field. Enter

     a valid value
     e.g.
     "MONTH_AMT"
    .

    Step 48
  49. Click in the Description field.

    Step 49
  50. Enter the desired information into the Description field. Enter

     a valid value
     e.g.
     "Monthly aid amount"
    .

    Step 50
  51. Use the Query Type field to specify the type of query as User, Process, or Role. Standard queries are defined as User types, and queries that use workflow are defined as Process or Role types. For the exercise, retain the default query type.

    Step 51
  52. Use the Owner field to specify the access to this query. Private indicates that only the user ID that created the query can open, run, modify, or delete the query. Public indicates that any user with access to the records used by the query can run, modify, or delete the query. For this example, you want to make it a private query.

    Step 52
  53. Click the OK button.

    Step 53
  54. Finally, view the results of the query.

     

    Click the Run tab.

    Step 54
  55. Use the Run page to view the results of your query.

    Step 55
  56. The results display the net and monthly amount for each student and term combination.

    Step 56

You have successfully defined an expression for a query.

Table of Contents  Start Topic