Search Knowledge Base by Keyword

Report Builder

< Back

From the Reports Configuration page, you can build new reports. To build a report:

  1. Click the New Report button to bring up the New Report dialog
  2. Select the type of report to build

    1. Classic Report – Report built from the UI
    2. SQL Query – Report built from a SQL query
  3. Click the Next button when finished to build the report. You will be redirected to the Report Builder with Classic Report selected and the SQL Query Report Builder with SQL Query selected. Click the Close button to cancel the operation.
    1. The new report function is only available to users in the Administrator security group

New Classic Report

From the Report Builder page, you can build new Classic reports. To build a Classic report:

  1. Enter the properties of the new report

    1. Name – Name of the report (required)
    2. Status – Choose if the report is enabled (required)
    3. Published – Choose if the report is published (optional)
    4. Data Source – Name of the source data table for the report (required)
      NOTE: Changing the data source after adding columns will remove any existing columns from the report.

      1. Data Types (Primary, Secondary)
      2. Orchestration (Waves, Tasks)
      3. Event Logs (Exception Log, Login History, Session Termination, System Events, T-Comm History, User Experience Audit)
    5. Description – Description of the report (optional)
  2. Add data columns to the report
    1. Data Types
      1. From the Add Column drop down, select Data Column to bring up the Data Column dialog
      2. Select the data columns of the new report from the Data Columns drop down and then click the Add button. Click the Close button to cancel the operation.

        1. Use the menu to change the sort order of the Data Columns drop down

          1. Sort by Order – Sorts data columns in the order they appear on data types (default)
          2. Sort Alphabetically – Sorts data columns alphabetically
      3. Added data columns are displayed below the Add Column button

        NOTE: The first column must be from the selected data source.
    2. Orchestration (Waves)
      1. From the Add Column drop down, select Data Column to bring up the Data Column dialog
      2. Select the data columns of the new report from the Data Columns drop down and then click the Add button. Click the Close button to cancel the operation.

        1. Use the  menu to change the sort order of the Data Columns drop down

          1. Sort by Order – Sorts data columns in the order they appear on waves, tasks, and data types (default)
          2. Sort Alphabetically – Sorts data columns alphabetically
      3. Added data columns are displayed below the Add Column button

        NOTE: The first column must be from the selected data source.
    3. Event Logs (System Events)
      1. From the Add Column drop down, select Data Column to bring up the Data Column dialog
      2. Select the data columns of the new report from the Data Columns drop down and then click the Add button. Click the Close button to cancel the operation.

        1. Use the  menu to change the sort order of the Data Columns drop down

          1. Sort by Order – Sorts data columns in the order they appear on event logs (default)
          2. Sort Alphabetically – Sorts data columns alphabetically
      3. Added data columns are displayed below the Add Column button

        NOTE: The first column must be from the selected data source.
  3. Add a calculation column to the report (Data Types only)
    1. From the Add Column drop down, select Calculation Column to bring up the Calculation Column dialog
    2. Enter the properties of the calculation column and then click the Add button. Click the Close button to cancel the operation.

      1. Column Name – Name of the column (required)
      2. Function – Select the calculation to be performed (only COUNT is currently supported) (required)
      3. Linked Data Type – Select a data type linked to the selected data source (required)
    3. Added calculation columns are displayed below the Add Column button at the end of the column list

      NOTE: Only one calculation column on a report is currently supported.
  4. Add custom status columns to the report (Data Types only)
    1. From the Add Column drop down, select Custom Status Column to bring up the New Custom Status dialog
    2. Select the data column of the new custom status from the Data Column drop down
    3. Enter the properties of the custom status column and then click the Create button. Click the Close button to cancel the operation.

      1. Column Label – Name of the column (required)
      2. Values Type – Type of values (Fixed Values, Range Values) (required)
      3. Value – Values (or value ranges) of the selected data column
      4. Status – Select RAG status of each value to include in the custom status (at least one status is required)
        NOTE: From the RAG Status module, you can manage RAG statuses.
    4. Added custom status columns are displayed in yellow below the Add Column button at the end of the column list
  5. Add an overall status column to the report (Data Types only)
    1. From the Add Column drop down, select Overall Status Column to bring up the Overall Status dialog
    2. Enter the properties of the overall status column and then click the Update button. Click the Close button to cancel the operation.

      1. Status – Select custom statuses to aggregate to determine overall status (at least one status is required)
      2. Column Label – Name of the column (required)
    3. Added overall status column is displayed in green below the Add Column button at the end of the column list. Custom statuses included in the overall status are dog eared in green.
  6. Add custom SQL columns to the report
    1. Data Types
      1. From the Add Column drop down, select Custom SQL Column to bring up the New SQL Column dialog

        1. Enter the SQL query of the custom SQL column and then click the Create button. Click the Close button to cancel the operation.

          1. SELECT Statement – Enter SELECT statement of the custom SQL query (required)
          2. JOIN Statement – Enter JOIN statement of the custom SQL query
          3. WHERE Clause – Enter WHERE clause of the custom SQL query
          4. GROUP BY Statement – Enter GROUP BY statement of the custom SQL query
        2. Added custom SQL columns are displayed in blue below the Add Column button at the end of the column list
      2. To configure custom status, select Custom Status from the menu to bring up the Edit Custom Status dialog

        1. Edit the properties of the custom status and then click the Update button. Click the Close button to cancel the operation.

          1. Value – Values of the selected data column
          2. Status – Select RAG status of each value to include in the custom status
            NOTE: From the RAG Status module, you can manage RAG statuses.
    2. Orchestration (Waves)
      1. From the Add Column drop down, select Custom SQL Column to bring up the New SQL Column dialog

        1. Enter the SQL query of the custom SQL column and then click the Create button. Click the Close button to cancel the operation.

          1. SELECT Statement – Enter SELECT statement of the custom SQL query (required)
          2. JOIN Statement – Enter JOIN statement of the custom SQL query
          3. WHERE Clause – Enter WHERE clause of the custom SQL query
          4. GROUP BY Statement – Enter GROUP BY statement of the custom SQL query
        2. Added custom SQL columns are displayed in blue below the Add Column button at the end of the column list
      2. To configure custom status, select Custom Status from the  menu to bring up the Edit Custom Status dialog

        1. Edit the properties of the custom status and then click the Update button. Click the Close button to cancel the operation.

          1. Value – Values of the selected data column
          2. Status – Select RAG status of each value to include in the custom status
            NOTE: From the RAG Status module, you can manage RAG statuses.
    3. Event Logs (System Events)
      1. From the Add Column drop down, select Custom SQL Column to bring up the New SQL Column dialog

        1. Enter the SQL query of the custom SQL column and then click the Create button. Click the Close button to cancel the operation.

          1. SELECT Statement – Enter SELECT statement of the custom SQL query (required)
          2. JOIN Statement – Enter JOIN statement of the custom SQL query
          3. WHERE Clause – Enter WHERE clause of the custom SQL query
          4. GROUP BY Statement – Enter GROUP BY statement of the custom SQL query
        2. Added custom SQL columns are displayed in blue below the Add Column button at the end of the column list
      2. To configure custom status, select Custom Status from the  menu to bring up the Edit Custom Status dialog

        1. Edit the properties of the custom status and then click the Update button. Click the Close button to cancel the operation.

          1. Value – Values of the selected data column
          2. Status – Select RAG status of each value to include in the custom status
            NOTE: From the RAG Status module, you can manage RAG statuses.
  7. Drag and drop to reorder columns
  8. To configure column settings, select Settings from the menu to bring up the Column Settings dialog

    1. Configure the column settings and then click the Save button. Click the Close button to cancel the operation.

      1. Override Column – Override the default column name
      2. Visible – Whether the column is visible by default. Use column visibility to view the column on the report.
      3. Hyperlink – Display a hyperlink to the data record (Name, Lookup, Lookup Alias, Link, and Linked Name fields only)
      4. Custom Label Colors – Select custom label colors for each value (Checkbox, Select, and Status fields with less than 20 values only)
      5. Top Level Filter – Display column as a top level filter (Lookup and Lookup Alias fields only)
      6. Custom Filter – Create a custom filter for the column
  9. To delete a column, select Delete Column from the menu

    1. Click the Delete button to confirm the delete operation. Click the Cancel button to cancel the operation.
  10. Configure the settings of the report (optional)
    1. Click the Report Settings button to bring up the Report Settings dialog
    2. Configure the settings of the report and then click the Save button. Click the Close button to cancel the operation.

      1. Pin Left Columns – Select the number of left columns to pin (None, 1, 2, or 3)
      2. Pin Right Columns – Select the number of right columns to pin (None, 1, 2, or 3)
      3. Filter Type – Select the filter type (Simple Filter)
      4. Limit Preview Results – Select the number of rows to display in the report preview (only in the preview)
      5. Column Sort Order – Configure a column sort order (default sort order is on the Name field ascending)
      6. Apply Global Where Clause – Add a where clause for additional filtering on the report
      7. Require Top Level Filter – Choose if selecting a top level filter is required before a report can be run
  11. Assign action or form buttons to the report (optional)
    1. Click the  button to bring up the Assign Buttons to Report dialog
    2. To assign a button, select a type from the Type drop down, select an action or form button from the Button drop down, and then click the Assign button. Click the Close button to cancel the operation.

      NOTE:
      From the Action Buttons module you can manage action buttons. From the Form Builder module you can manage forms.

      1. Edit the properties of the button
        1. Position – Position of the button on the toolbar
        2. Status – Choose if the button is enabled
    3. To unassign a button, select a button (row) to unassign

      1. Click the Unassign button on the toolbar

        1. The unassign button is grayed out unless a button (row) is selected

        2. The unassign function is only available to users in the Administrator security group
      2. Click the Unassign button to confirm the unassign operation. Click the Cancel button to cancel the operation.
  12. Subscribe recipients to the report (optional)
    1. Click the  button to bring up the Subscribe Recipients to Report dialog
    2. To subscribe a report recipient, select a recipient from the Recipients drop down, and then click the Save button. Click the Close button to cancel the operation.

      NOTE:
      From the Report Recipients tab you can manage subscriptions to report recipients.
    3. To unsubscribe a report recipient, deselect a recipient from the Recipients drop down, and then click the Save button. Click the Close button to cancel the operation.
  13. Click the Preview Report button to preview the report before saving
  14. Click the Report Query button to bring up the Report Query dialog to view and copy the query. The Report Query button is not available until the report has been previewed.
  15. Click the Create Report button when finished to create the report. You will be redirected back to the Reports Configuration page. Click the Cancel button to cancel the operation.

New SQL Query Report

From the Report Builder page, you can build new SQL Query reports. To build a SQL Query report:

  1. Enter the properties of the new report

    1. Name – Name of the report (required)
    2. Status – Choose if the report is enabled (required)
    3. Published – Choose if the report is published (optional)
    4. Data Source – Name of the source for the report (SQL Query)
    5. Description – Description of the report (optional)
  2. Enter the main query of the new report in the query window. SQL Query reports can access the connected ETL database and the ReadyWorks database.
    NOTE: When using SQL functions for columns, be sure to use aliases signified by the keyword AS (See Line 10 in sample query below).

    1. Use the editing buttons to format the query
      1. Undo – Undo the last edit
      2. Redo – Redo the last edit that was undone
      3. Block Outdent – Remove an indent from a line or block
      4. Block Indent – Set a line or block in from the margin
      5. Comment – Comment out a line or block
      6. Clear – Clear the query
      7. Add SQL – Add sample SQL to the query
    2. Select the Primary Key of the query

      NOTE: Columns with blank values cannot be used as the Primary Key. It is best practice to use the Id, Name, or a column that contains unique values and contains no blank values as the Primary Key.
    3. Click the button to add a helper query. SQL Query reports can access the connected ETL database and the ReadyWorks database.
    4. Click the name of a query to expand or minimize the query window
    5. Click the button to run the query and preview the report before saving
    6. The button is grayed out until the query has been previewed successfully
    7. Click the button to bring op the Settings dialog
      1. Configure the settings and then click the Save button. Click the Close button to cancel the operation.

        1. Format Column Names – Choose if column names are formatted
          1. Unformatted – lu__migration_status
          2. Formatted – Migration Status
        2. Report UI – Choose the display of the Report UI (One Page, Tabs)
          NOTE: Changing the display will result in the loss of any unsaved changes.
    8. Click the button to expand the query to full window mode. Click the button to exit full window mode.
    9. Use the menu to manage queries
       

      1. Ignore from Execution – Ignore a query from execution
      2. Rename Query – Rename a query
      3. Move Up – Move a query up in the order
      4. Move Down – Move a query down in the order
      5. Delete Query – Delete a query
  3. To configure column settings, select Settings from the menu to bring up the Column Settings dialog

    1. Configure the column settings and then click the Save button. Click the Close button to cancel the operation.

      1. Remove from Table – Remove column from the displayed results (column will remain in the query output)
      2. Visible – Whether the column is visible by default. Use column visibility to view the column on the report.
      3. Hyperlink – Display a hyperlink to the data type (Name, Lookup, Lookup Alias, Link, and Linked Name fields only)
      4. Lookup – Mark column as a lookup (lu__, lum__, lua__, and luam__ columns will be checked automatically)
      5. Custom Status (RAG Status) – Display RAG status for each value
        1. Enter the properties of the custom status column

          1. Value – Values of the selected data column
          2. Status – Select RAG status of each value to include in the custom status
            NOTE: From the RAG Status module, you can manage RAG statuses.
        2. Custom status columns are displayed in yellow
      6. Custom Status using Ranges (RAG Status) – Display RAG status for value ranges
        1. Enter the properties of the custom status column

          1. Value – Value range of the selected data column
          2. Status – Select RAG status of each value range to include in the custom status
            NOTE: From the RAG Status module, you can manage RAG statuses.
          3. Click the Add Values button to add a value range to the custom status
          4. Click the Delete button to delete a value range (row)
        2. Custom status columns are displayed in yellow
      7. Custom Label Colors – Select custom label colors for each value (Checkbox, Select, and Status fields with less than 20 values only)
      8. Top Level Filter – Display column as a top level filter (Lookup and Lookup Alias fields only)
      9. Custom Filter – Create a custom filter for the column
  4. To configure overall status column settings, select Settings from the menu of an overall status column (displayed in green) to bring up the Overall Status dialog

    NOTE: Click the Add SQL button and select Overall Status Column from the drop down to add an overall status column to the query.

    1. Enter the properties of the overall status column and then click the Update button. Click the Close button to cancel the operation.

      1. Status – Select custom statuses to aggregate to determine overall status (at least one status is required)
      2. Column Label – Name of the column (from the query)
    2. Overall status column is displayed in green. Custom statuses included in the overall status are dog eared in green.
  5. Configure the settings of the report (optional)
    1. Click the Report Settings button to bring up the Report Settings dialog
    2. Configure the settings of the report and then click the Save button. Click the Close button to cancel the operation.

      1. Pin Left Columns – Select the number of left columns to pin (None, 1, 2, or 3)
      2. Pin Right Columns – Select the number of right columns to pin (None, 1, 2, or 3)
      3. Column Sort Order – Configure a column sort order (default sort order is on the Name field ascending)
      4. Require Top Level Filter – Choose if selecting a top level filter is required before a report can be run
  6. Assign action or form buttons to the report (optional)
    1. Click the  button to bring up the Assign Buttons to Report dialog
    2. To assign a button, select a type from the Type drop down, select an action or form button from the Button drop down, and then click the Assign button. Click the Close button to cancel the operation.

      NOTE:
      From the Action Buttons module you can manage action buttons. From the Form Builder module you can manage forms.

      1. Edit the properties of the button
        1. Position – Position of the button on the toolbar
        2. Status – Choose if the button is enabled
    3. To unassign a button, select a button (row) to unassign

      1. Click the Unassign button on the toolbar

        1. The unassign button is grayed out unless a button (row) is selected

        2. The unassign function is only available to users in the Administrator security group
      2. Click the Unassign button to confirm the unassign operation. Click the Cancel button to cancel the operation.
  7. Subscribe recipients to the report (optional)
    1. Click the button to bring up the Subscribe Recipients to Report dialog
    2. To subscribe a report recipient, select a recipient from the Recipients drop down, and then click the Save button. Click the Close button to cancel the operation.

      NOTE:
      From the Report Recipients tab you can manage subscriptions to report recipients.
    3. To unsubscribe a report recipient, deselect a recipient from the Recipients drop down, and then click the Save button. Click the Close button to cancel the operation.
  8. Click the Preview Report button to preview the report before saving
  9. Click the Create Report button when finished to create the report. You will be redirected back to the Reports Configuration page. Click the Cancel button to cancel the operation.

Edit Report

From the Reports Configuration page, you can edit reports built with the Report Builder. To edit a report:

  1. Select a Report Builder report (row) to edit
  2. Click the Edit Report button on the toolbar to bring up the Report Builder wizard

    1. The edit button is grayed out unless a report (row) is selected

    2. The edit function is only available to users in the Administrator security group
  3. You will be redirected to the Report Builder for a Classic Report and the SQL Query Report Builder for a SQL Query

Edit Classic Report

From the Report Builder page, you can edit Classic reports. To edit a Classic report:

  1. Edit the properties of the report

    1. Name – Name of the report (required)
    2. Status – Choose if the report is enabled (required)
    3. Published – Choose if the report is published (optional)
    4. Data Source – Name of the source data table for the report (required)
      NOTE: Changing the data source after adding columns will remove any existing columns from the report.

      1. Data Types (Primary, Secondary)
      2. Orchestration (Waves, Tasks)
      3. Event Logs (Exception Log, Login History, Session Termination, System Events, T-Comm History, User Experience Audit)
    5. Description – Description of the report (optional)
  2. Add data columns to the report
    1. Data Types
      1. From the Add Column drop down, select Data Column to bring up the Data Column dialog
      2. Select the data columns of the new report from the Data Columns drop down and then click the Add button. Click the Close button to cancel the operation.

        1. Use the menu to change the sort order of the Data Columns drop down

          1. Sort by Order – Sorts data columns in the order they appear on data types (default)
          2. Sort Alphabetically – Sorts data columns alphabetically
      3. Added data columns are displayed below the Add Column button

        NOTE: The first column must be from the selected data source.
    2. Orchestration (Waves)
      1. From the Add Column drop down, select Data Column to bring up the Data Column dialog
      2. Select the data columns of the new report from the Data Columns drop down and then click the Add button. Click the Close button to cancel the operation.

        1. Use the menu to change the sort order of the Data Columns drop down

          1. Sort by Order – Sorts data columns in the order they appear on waves, tasks, and data types (default)
          2. Sort Alphabetically – Sorts data columns alphabetically
      3. Added data columns are displayed below the Add Column button

        NOTE: The first column must be from the selected data source.
    3. Event Logs (System Events)
      1. From the Add Column drop down, select Data Column to bring up the Data Column dialog
      2. Select the data columns of the new report from the Data Columns drop down and then click the Add button. Click the Close button to cancel the operation.

        1. Use the  menu to change the sort order of the Data Columns drop down

          1. Sort by Order – Sorts data columns in the order they appear on event logs (default)
          2. Sort Alphabetically – Sorts data columns alphabetically
      3. Added data columns are displayed below the Add Column button

        NOTE: The first column must be from the selected data source.
  3. Add a calculation column to the report (Data Types only)
    1. From the Add Column drop down, select Calculation Column to bring up the Calculation Column dialog
    2. Enter the properties of the calculation column and then click the Add button. Click the Close button to cancel the operation.

      1. Column Name – Name of the column (required)
      2. Function – Select the calculation to be performed (only COUNT is currently supported) (required)
      3. Linked Data Type – Select a data type linked to the selected data source (required)
    3. Added calculation columns are displayed below the Add Column button at the end of the column list

      NOTE: Only one calculation column on a report is currently supported.
  4. Add custom status columns to the report (Data Types only)
    1. From the Add Column drop down, select Custom Status Column to bring up the New Custom Status dialog
    2. Select the data column of the new custom status from the Data Column drop down
    3. Enter the properties of the custom status column and then click the Create button. Click the Close button to cancel the operation.

      1. Column Label – Name of the column (required)
      2. Values Type – Type of values (Fixed Values, Range Values) (required)
      3. Value – Values (or value ranges) of the selected data column
      4. Status – Select RAG status of each value to include in the custom status (at least one status is required)
        NOTE: From the RAG Status module, you can manage RAG statuses.
    4. Added custom status columns are displayed in yellow below the Add Column button at the end of the column list
  5. Add an overall status column to the report (Data Types only)
    1. From the Add Column drop down, select Overall Status Column to bring up the Overall Status dialog
    2. Enter the properties of the overall status column and then click the Update button. Click the Close button to cancel the operation.

      1. Status – Select custom statuses to aggregate to determine overall status (at least one status is required)
      2. Column Label – Name of the column (required)
    3. Added overall status column is displayed in green below the Add Column button at the end of the column list. Custom statuses included in the overall status are dog eared in green.
  6. Add custom SQL columns to the report
    1. Data Types
      1. From the Add Column drop down, select Custom SQL Column to bring up the New SQL Column dialog

        1. Enter the SQL query of the custom SQL column and then click the Create button. Click the Close button to cancel the operation.

          1. SELECT Statement – Enter SELECT statement of the custom SQL query (required)
          2. JOIN Statement – Enter JOIN statement of the custom SQL query
          3. WHERE Clause – Enter WHERE clause of the custom SQL query
          4. GROUP BY Statement – Enter GROUP BY statement of the custom SQL query
        2. Added custom SQL columns are displayed in blue below the Add Column button at the end of the column list
      2. To configure custom status, select Custom Status from the menu to bring up the Edit Custom Status dialog

        1. Edit the properties of the custom status and then click the Update button. Click the Close button to cancel the operation.

          1. Value – Values of the selected data column
          2. Status – Select RAG status of each value to include in the custom status
            NOTE: From the RAG Status module, you can manage RAG statuses.
    2. Orchestration (Waves)
      1. From the Add Column drop down, select Custom SQL Column to bring up the New SQL Column dialog

        1. Enter the SQL query of the custom SQL column and then click the Create button. Click the Close button to cancel the operation.

          1. SELECT Statement – Enter SELECT statement of the custom SQL query (required)
          2. JOIN Statement – Enter JOIN statement of the custom SQL query
          3. WHERE Clause – Enter WHERE clause of the custom SQL query
          4. GROUP BY Statement – Enter GROUP BY statement of the custom SQL query
        2. Added custom SQL columns are displayed in blue below the Add Column button at the end of the column list
      2. To configure custom status, select Custom Status from the menu to bring up the Edit Custom Status dialog

        1. Edit the properties of the custom status and then click the Update button. Click the Close button to cancel the operation.

          1. Value – Values of the selected data column
          2. Status – Select RAG status of each value to include in the custom status
            NOTE: From the RAG Status module, you can manage RAG statuses.
    3. Event Logs (System Events)
      1. From the Add Column drop down, select Custom SQL Column to bring up the New SQL Column dialog

        1. Enter the SQL query of the custom SQL column and then click the Create button. Click the Close button to cancel the operation.

          1. SELECT Statement – Enter SELECT statement of the custom SQL query (required)
          2. JOIN Statement – Enter JOIN statement of the custom SQL query
          3. WHERE Clause – Enter WHERE clause of the custom SQL query
          4. GROUP BY Statement – Enter GROUP BY statement of the custom SQL query
        2. Added custom SQL columns are displayed in blue below the Add Column button at the end of the column list
      2. To configure custom status, select Custom Status from the menu to bring up the Edit Custom Status dialog

        1. Edit the properties of the custom status and then click the Update button. Click the Close button to cancel the operation.

          1. Value – Values of the selected data column
          2. Status – Select RAG status of each value to include in the custom status
            NOTE: From the RAG Status module, you can manage RAG statuses.
  7. Drag and drop to reorder columns
  8. To configure column settings, select Settings from the menu to bring up the Column Settings dialog

    1. Configure the column settings and then click the Save button. Click the Close button to cancel the operation.

      1. Override Column – Override the default column name
      2. Visible – Whether the column is visible by default. Use column visibility to view the column on the report.
      3. Hyperlink – Display a hyperlink to the data record (Name, Lookup, Lookup Alias, Link, and Linked Name fields only)
      4. Custom Label Colors – Select custom label colors for each value (Checkbox, Select, and Status fields with less than 20 values only)
      5. Top Level Filter – Display column as a top level filter (Lookup and Lookup Alias fields only)
      6. Custom Filter – Create a custom filter for the column
  9. To delete a column, select Delete Column from the menu

    1. Click the Delete button to confirm the delete operation. Click the Cancel button to cancel the operation.
  10. Configure the settings of the report (optional)
    1. Click the Report Settings button to bring up the Report Settings dialog
    2. Configure the settings of the report and then click the Save button. Click the Close button to cancel the operation.

      1. Pin Left Columns – Select the number of left columns to pin (None, 1, 2, or 3)
      2. Pin Right Columns – Select the number of right columns to pin (None, 1, 2, or 3)
      3. Filter Type – Select the filter type (Simple Filter)
      4. Limit Preview Results – Select the number of rows to display in the report preview (only in the preview)
      5. Column Sort Order – Configure a column sort order (default sort order is on the Name field ascending)
      6. Apply Global Where Clause – Add a where clause for additional filtering on the report
      7. Require Top Level Filter – Choose if selecting a top level filter is required before a report can be run
  11. Assign action or form buttons to the report (optional)
    1. Click the  button to bring up the Assign Buttons to Report dialog
    2. To assign a button, select a type from the Type drop down, select an action or form button from the Button drop down, and then click the Assign button. Click the Close button to cancel the operation.

      NOTE:
      From the Action Buttons module you can manage action buttons. From the Form Builder module you can manage forms.

      1. Edit the properties of the button
        1. Position – Position of the button on the toolbar
        2. Status – Choose if the button is enabled
    3. To unassign a button, select a button (row) to unassign

      1. Click the Unassign button on the toolbar

        1. The unassign button is grayed out unless a button (row) is selected

        2. The unassign function is only available to users in the Administrator security group
      2. Click the Unassign button to confirm the unassign operation. Click the Cancel button to cancel the operation.
  12. Subscribe recipients to the report (optional)
    1. Click the  button to bring up the Subscribe Recipients to Report dialog
    2. To subscribe a report recipient, select a recipient from the Recipients drop down, and then click the Save button. Click the Close button to cancel the operation.

      NOTE:
      From the Report Recipients tab you can manage subscriptions to report recipients.
    3. To unsubscribe a report recipient, deselect a recipient from the Recipients drop down, and then click the Save button. Click the Close button to cancel the operation.
  13. Click the Preview Report button to preview the report before saving
  14. Click the Report Query button to bring up the Report Query dialog to view and copy the query. The Report Query button is not available until the report has been previewed.
  15. Click the Update Report button to save any changes to the report. Click the Cancel button to cancel the operation.
  16. Click the Run Report button to run the report

Edit SQL Query Report

From the Report Builder page, you can edit SQL Query reports. To edit a SQL Query report:

  1. Edit the properties of the report

    1. Name – Name of the report (required)
    2. Status – Choose if the report is enabled (required)
    3. Published – Choose if the report is published (optional)
    4. Data Source – Name of the source for the report (SQL Query)
    5. Description – Description of the report (optional)
  2. Edit the main query of the report in the query window. SQL Query reports can access the connected ETL database and the ReadyWorks database.
    NOTE: When using SQL functions for columns, be sure to use aliases signified by the keyword AS (See Line 10 in sample query below).

    1. Use the editing buttons to format the query
      1. Undo – Undo the last edit
      2. Redo – Redo the last edit that was undone
      3. Block Outdent – Remove an indent from a line or block
      4. Block Indent – Set a line or block in from the margin
      5. Comment – Comment out a line or block
      6. Clear – Clear the query
      7. Add SQL – Add sample SQL to the query
    2. Select the Primary Key of the query

      NOTE: Columns with blank values cannot be used as the Primary Key. It is best practice to use the Id, Name, or a column that contains unique values and contains no blank values as the Primary Key.
    3. Click the button to add a helper query. SQL Query reports can access the connected ETL database and the ReadyWorks database.
    4. Click the name of a query to expand or minimize the query window
    5. Click the button to run the query and preview the report before saving
    6. Click the button to bring op the Settings dialog
      1. Configure the settings and then click the Save button. Click the Close button to cancel the operation.

        1. Format Column Names – Choose if column names are formatted
          1. Unformatted – lu__migration_status
          2. Formatted – Migration Status
        2. Report UI – Choose the display of the Report UI (One Page, Tabs)
          NOTE: Changing the display will result in the loss of any unsaved changes.
    7. Click the button to expand the query to full window mode. Click the button to exit full window mode.
    8. Use the menu to manage queries
       

      1. Ignore from Execution – Ignore a query from execution
      2. Rename Query – Rename a query
      3. Move Up – Move a query up in the order
      4. Move Down – Move a query down in the order
      5. Delete Query – Delete a query
    9. Click the  button to save (Quick Save) the query. A Quick Save does not run the query.
  3. To configure column settings, select Settings from the menu to bring up the Column Settings dialog

    1. Configure the column settings and then click the Save button. Click the Close button to cancel the operation.

      1. Remove from Table – Remove column from the displayed results (column will remain in the query output)
      2. Visible – Whether the column is visible by default. Use column visibility to view the column on the report.
      3. Hyperlink – Display a hyperlink to the data record (Name, Lookup, Lookup Alias, Link, and Linked Name fields only)
      4. Lookup – Mark column as a lookup (lu__, lum__, lua__, and luam__ columns will be checked automatically)
      5. Custom Status (RAG Status) – Display RAG status for each value
        1. Enter the properties of the custom status column

          1. Value – Values of the selected data column
          2. Status – Select RAG status of each value to include in the custom status
            NOTE: From the RAG Status module, you can manage RAG statuses.
        2. Custom status columns are displayed in yellow
      6. Custom Status using Ranges (RAG Status) – Display RAG status for value ranges
        1. Enter the properties of the custom status column

          1. Value – Value range of the selected data column
          2. Status – Select RAG status of each value range to include in the custom status
            NOTE: From the RAG Status module, you can manage RAG statuses.
          3. Click the Add Values button to add a value range to the custom status
          4. Click the Delete button to delete a value range (row)
        2. Custom status columns are displayed in yellow
      7. Custom Label Colors – Select custom label colors for each value (Checkbox, Select, and Status fields with less than 20 values only)
      8. Top Level Filter – Display column as a top level filter (Lookup and Lookup Alias fields only)
      9. Custom Filter – Create a custom filter for the column
  4. To configure overall status column settings, select Settings from the  menu of an overall status column (displayed in green) to bring up the Overall Status dialog

    NOTE:
    Click the Add SQL button and select Overall Status Column from the drop down to add an overall status column to the query.

    1. Enter the properties of the overall status column and then click the Update button. Click the Close button to cancel the operation.

      1. Status – Select custom statuses to aggregate to determine overall status (at least one status is required)
      2. Column Label – Name of the column (from the query)
    2. Overall status column is displayed in green. Custom statuses included in the overall status are dog eared in green.
  5. Configure the settings of the report (optional)
    1. Click the Report Settings button to bring up the Report Settings dialog
    2. Configure the settings of the report and then click the Save button. Click the Close button to cancel the operation.

      1. Pin Left Columns – Select the number of left columns to pin (None, 1, 2, or 3)
      2. Pin Right Columns – Select the number of right columns to pin (None, 1, 2, or 3)
      3. Column Sort Order – Configure a column sort order (default sort order is on the Name field ascending)
      4. Require Top Level Filter – Choose if selecting a top level filter is required before a report can be run
  6. Assign action or form buttons to the report (optional)
    1. Click the  button to bring up the Assign Buttons to Report dialog
    2. To assign a button, select a type from the Type drop down, select an action or form button from the Button drop down, and then click the Assign button. Click the Close button to cancel the operation.

      NOTE:
      From the Action Buttons module you can manage action buttons. From the Form Builder module you can manage forms.

      1. Edit the properties of the button
        1. Position – Position of the button on the toolbar
        2. Status – Choose if the button is enabled
    3. To unassign a button, select a button (row) to unassign

      1. Click the Unassign button on the toolbar

        1. The unassign button is grayed out unless a button (row) is selected

        2. The unassign function is only available to users in the Administrator security group
      2. Click the Unassign button to confirm the unassign operation. Click the Cancel button to cancel the operation.
  7. Subscribe recipients to the report (optional)
    1. Click the  button to bring up the Subscribe Recipients to Report dialog
    2. To subscribe a report recipient, select a recipient from the Recipients drop down, and then click the Save button. Click the Close button to cancel the operation.

      NOTE:
      From the Report Recipients tab you can manage subscriptions to report recipients.
    3. To unsubscribe a report recipient, deselect a recipient from the Recipients drop down, and then click the Save button. Click the Close button to cancel the operation.
  8. Click the Preview Report button to preview the report before saving
  9. Click the Update Report button to save (Quick Update or Full Update) any changes to the report. A Quick Update saves the Report Properties (i.e., Name, Status, Published, Description, Settings) and does not run the query. A Full Update saves the the Report Properties, Report Settings, and SQL Query and runs the query. A Full Update can take longer to save depending on the query. Click the Cancel button to cancel the operation.
  10. Click the Run Report button to run the report

Data Column

From the Report Builder, you can add data columns to a report. To add data columns:

  1. Data Types
    1. From the Add Column drop down, select Data Column to bring up the Data Column dialog
    2. Select the data columns of the new report from the Data Columns drop down and then click the Add button. Click the Close button to cancel the operation.

      1. Use the  menu to change the sort order of the Data Columns drop down

        1. Sort by Order – Sorts data columns in the order they appear on data types (default)
        2. Sort Alphabetically – Sorts data columns alphabetically
      2. Data columns are organized by data type with the selected data source listed first (1)
      3. Data types linked to the selected data source are listed subsequently in alphabetical order (2, 3, 4, etc.)
      4. Each data type is organized by Standard columns, Lookup columns, and Lookup Alias columns
  2. Orchestration (Waves)
    1. From the Add Column drop down, select Data Column to bring up the Data Column dialog
    2. Select the data columns of the new report from the Data Columns drop down and then click the Add button. Click the Close button to cancel the operation.

      1. Use the  menu to change the sort order of the Data Columns drop down

        1. Sort by Order – Sorts data columns in the order they appear on waves, tasks, and data types (default)
        2. Sort Alphabetically – Sorts data columns alphabetically
      2. Data columns are organized by orchestration type (Wave, Task) with the selected data source listed first (1)
      3. Waves or tasks associated with the selected data source are listed second (2)
      4. Data types linked to the selected data source are listed subsequently (3, 4, 5, etc.)
      5. Each data type is organized by Standard columns, Lookup columns, and Lookup Alias columns
  3. Event Logs (System Events)
    1. From the Add Column drop down, select Data Column to bring up the Data Column dialog
    2. Select the data columns of the new report from the Data Columns drop down and then click the Add button. Click the Close button to cancel the operation.

      1. Use the  menu to change the sort order of the Data Columns drop down

        1. Sort by Order – Sorts data columns in the order they appear on event logs (default)
        2. Sort Alphabetically – Sorts data columns alphabetically
      2. Data columns are organized by system events (1)

Calculation Column

From the Report Builder, you can add calculation columns to a report (Data Types only). To add calculation columns:

  1. From the Add Column drop down, select Calculation Column to bring up the Calculation Column dialog
  2. Enter the properties of the calculation column and then click the Add button. Click the Close button to cancel the operation.

    1. Column Name – Name of the column (required)
    2. Function – Select the calculation function to be performed (only COUNT is currently supported) (required)
    3. Linked Data Type – Select a data type linked to the selected data source (required)

Column Settings

From the Report Builder, you can configure column settings. To configure column settings:

  1. From the menu, select Settings to bring up the Column Settings dialog
  2. Configure the column settings and then click the Save button. Click the Close button to cancel the operation.

    1. Override Column – Override the default column name
      1. Name that overrides the default column name is listed in brackets
    2. Visible – Whether the column is visible by default. Use column visibility to view the column on the report.
      1. Columns with Visible set to No are signified by an icon of an eye with a slash through it
    3. Hyperlink – Display a hyperlink to the data record (Name, Lookup, Lookup Alias, Link, and Linked Name fields only)

      1. Columns with a hyperlink are signified by a link icon
    4. Custom Label Colors – Select custom label colors for each value (Checkbox, Select, and Status fields with less than 20 values only)

      1. Select a color for each value (Blue, Light Blue, Green, Red, Orange, Grey)
    5. Top Level Filter – Display column as a top level filter (Lookup and Lookup Alias fields only)

      1. Columns with a top level filter are signified by a slider icon
      2. Top level filters are displayed at the top of reports

        NOTE: Configure Require Top Level Filter under Report Settings to require selecting a top level filter before a report can be run.
    6. Custom Filter – Create a custom filter for the column

      1. Enter values for the custom filter (one per row)
      2. Columns with a custom filter are signified by a filter icon

Report Settings

From the Report Builder, you can configure the report settings. To configure the report settings:

  1. Click the Report Settings button to bring up the Report Settings dialog
  2. Configure the settings of the report and then click the Save button. Click the Close button to cancel the operation.

    1. Pin Left Columns – Select the number of left columns to pin (None, 1, 2, or 3)
    2. Pin Right Columns – Select the number of right columns to pin (None, 1, 2, or 3)
    3. Filter Type – Select the filter type (Simple Filter)
    4. Limit Preview Results – Select the number of rows to display in the report preview (only in the preview)
    5. Column Sort Order – Configure a column sort order (default sort order is on the Name field ascending)

      1. Add Column – Add new rows (columns) to the sort order
      2. Column – Name of the data column to sort on
      3. Order – Sort order of the column (ascending or descending)
      4. Delete Row – Delete a row from the sort order
    6. Apply Global Where Clause – Add a where clause for additional filtering on the report

      1. Click the Add Where Clause button to add a condition to the where clause
      2. Use the Column Name drop down to select the data column to filter the report on
      3. Use the Condition drop down to select the condition of the where clause
        1. = (Equals) – Number equals the value
        2. != (Does Not Equal) – Number does not equal the value
        3. > (Greater Than) – Number is greater than the value
        4. < (Less Than) – Number is less than the value
        5. Between – Number, date, or date time is between the values
        6. In – String matches one of the values (multiple)
        7. Not In – String does not match any of the values (multiple)
        8. Start With – String starts with the value
        9. Ends With – String ends with the value
        10. Contains – String contains the value
        11. Does Not Contains – String does not contain the value
        12. Is – String is empty or a valid email
        13. Is Not – String is not empty or is not a valid email
        14. REGEX – String matches regular expression
      4. Use the Value drop down (or text box) to select (or enter) the value to filter the report
        NOTE: Leave the value blank for Undefined values.
      5. Click the Add Where Clause button to add additional conditions to the where clause
        1. Select the join operator of the conditions
          1. AND – Match all conditions
          2. OR – Match any condition
      6. Click the Add Parenthesis and Close Parenthesis buttons to define the order to evaluate the conditions
      7. Click the Delete button to delete a condition (row)
    7. Require Top Level Filter – Choose if selecting a top level filter is required before a report can be run

Assign Buttons

From the Report Builder, you can assign action or form buttons to the report. To assign buttons:

  1. Click the  button to bring up the Assign Buttons to Report dialog
  2. To assign a button, select a type from the Type drop down, select an action or form button from the Button drop down, and then click the Assign button. Click the Close button to cancel the operation.

    NOTE:
    From the Action Buttons module you can manage action buttons. From the Form Builder module you can manage forms.

    1. Edit the properties of the button
      1. Position – Position of the button on the toolbar
      2. Status – Choose if the button is enabled
  3. To unassign a button, select a button (row) to unassign

    1. Click the Unassign button on the toolbar

      1. The unassign button is grayed out unless a button (row) is selected

      2. The unassign function is only available to users in the Administrator security group
    2. Click the Unassign button to confirm the unassign operation. Click the Cancel button to cancel the operation.

Subscribe Recipients

From the Report Builder, you can subscribe recipients to the report. To subscribe recipients:

  1. Click the  button to bring up the Subscribe Recipients to Report dialog
  2. To subscribe a report recipient, select a recipient from the Recipients drop down, and then click the Save button. Click the Close button to cancel the operation.

    NOTE:
    From the Report Recipients tab you can manage subscriptions to report recipients.
  3. To unsubscribe a report recipient, deselect a recipient from the Recipients drop down, and then click the Save button. Click the Close button to cancel the operation.

Report Query

From the Report Builder, you can view the report query. To view the report query:

  1. Click the Report Query button to bring up the Report Query dialog
  2. Click the Copy Query button to copy the query to the clipboard
  3. Click the Show Links View button to view the report links view. Click the Hide Links View button to hide the report links view

New Custom Status

From the Report Builder, you can add custom status columns to a report (Data Types only). To add custom status columns:

  1. From the Add Column drop down, select Custom Status Column to bring up the New Custom Status dialog
  2. Select the data column of the new custom status from the Data Column drop down
  3. Enter the properties of the custom status column and then click the Create button. Click the Close button to cancel the operation.

    1. Column Label – Name of the column (required)
    2. Values Type – Type of values (Fixed Values, Range Values) (required)
    3. Value – Values (or value ranges) of the selected data column
    4. Status – Select RAG status of each value to include in the custom status (at least one status is required)
      NOTE: From the RAG Status module, you can manage RAG statuses.
  4. Added custom status columns are displayed in yellow below the Add Column button at the end of the column list

Edit Custom Status

From the Report Builder, you can edit custom status column settings (Data Types only). To edit custom status column settings:

  1. From the menu, select Edit Status to bring up the Edit Custom Status dialog
  2. Edit the properties of the custom status column and then click the Update button. Click the Close button to cancel the operation.

    1. Column Label – Name of the column (required)
    2. Values Type – Type of values (Fixed Values, Range Values) (required)
    3. Value – Values (or value ranges) of the selected data column
    4. Status – Select RAG status of each value to include in the custom status (at least one status is required)
      NOTE: From the RAG Status module, you can manage RAG statuses.

New Overall Status

From the Report Builder, you can add an overall status column to a report (Data Types only). To add an overall status column:

  1. From the Add Column drop down, select Overall Status Column to bring up the Overall Status dialog
  2. Enter the properties of the overall status column and then click the Update button. Click the Close button to cancel the operation.

    1. Status – Select custom statuses to aggregate to determine overall status (at least one status is required)
    2. Column Label – Name of the column (required)
  3. Added overall status column is displayed in green below the Add Column button at the end of the column list. Custom statuses included in the overall status are dog eared in green.

Edit Overall Status

From the Report Builder, you can edit overall status column settings (Data Types only). To edit overall status column settings:

  1. From the  menu, select Edit Status to bring up the Overall Status dialog
  2. Edit the properties of the overall status column and then click the Update button. Click the Close button to cancel the operation.

    1. Status – Select custom statuses to aggregate to determine overall status (at least one status is required)
    2. Column Label – Name of the column (required)

New SQL Column

From the Report Builder, you can add custom SQL columns to a report. To add custom SQL columns:

  1. Data Types
    1. From the Add Column drop down, select Custom SQL Column to bring up the New SQL Column dialog

      1. Enter the SQL query of the custom SQL column and then click the Create button. Click the Close button to cancel the operation.

        1. SELECT Statement – Enter SELECT statement of the custom SQL query (required)
        2. JOIN Statement – Enter JOIN statement of the custom SQL query
        3. WHERE Clause – Enter WHERE clause of the custom SQL query
        4. GROUP BY Statement – Enter GROUP BY statement of the custom SQL query
      2. Added custom SQL columns are displayed in blue below the Add Column button at the end of the column list
    2. To configure custom status, select Custom Status from the menu to bring up the Edit Custom Status dialog

      1. Edit the properties of the custom status and then click the Update button. Click the Close button to cancel the operation.

        1. Value – Values of the selected data column
        2. Status – Select RAG status of each value to include in the custom status
          NOTE: From the RAG Status module, you can manage RAG statuses.
  2. Orchestration (Waves)
    1. From the Add Column drop down, select Custom SQL Column to bring up the New SQL Column dialog

      1. Enter the SQL query of the custom SQL column and then click the Create button. Click the Close button to cancel the operation.

        1. SELECT Statement – Enter SELECT statement of the custom SQL query (required)
        2. JOIN Statement – Enter JOIN statement of the custom SQL query
        3. WHERE Clause – Enter WHERE clause of the custom SQL query
        4. GROUP BY Statement – Enter GROUP BY statement of the custom SQL query
      2. Added custom SQL columns are displayed in blue below the Add Column button at the end of the column list
    2. To configure custom status, select Custom Status from the menu to bring up the Edit Custom Status dialog

      1. Edit the properties of the custom status and then click the Update button. Click the Close button to cancel the operation.

        1. Value – Values of the selected data column
        2. Status – Select RAG status of each value to include in the custom status
          NOTE: From the RAG Status module, you can manage RAG statuses.
  3. Event Logs (System Events)
    1. From the Add Column drop down, select Custom SQL Column to bring up the New SQL Column dialog

      1. Enter the SQL query of the custom SQL column and then click the Create button. Click the Close button to cancel the operation.

        1. SELECT Statement – Enter SELECT statement of the custom SQL query (required)
        2. JOIN Statement – Enter JOIN statement of the custom SQL query
        3. WHERE Clause – Enter WHERE clause of the custom SQL query
        4. GROUP BY Statement – Enter GROUP BY statement of the custom SQL query
      2. Added custom SQL columns are displayed in blue below the Add Column button at the end of the column list
    2. To configure custom status, select Custom Status from the menu to bring up the Edit Custom Status dialog

      1. Edit the properties of the custom status and then click the Update button. Click the Close button to cancel the operation.

        1. Value – Values of the selected data column
        2. Status – Select RAG status of each value to include in the custom status
          NOTE: From the RAG Status module, you can manage RAG statuses.

Edit SQL Column

From the Report Builder, you can edit custom SQL column settings. To edit custom SQL column settings:

  1. Data Types
    1. From the menu, select Edit Column to bring up the Edit SQL Column dialog

      1. Edit the SQL query of the custom SQL column and then click the Update button. Click the Close button to cancel the operation.

        1. SELECT Statement – Enter SELECT statement of the custom SQL query (required)
        2. JOIN Statement – Enter JOIN statement of the custom SQL query
        3. WHERE Clause – Enter WHERE clause of the custom SQL query
        4. GROUP BY Statement – Enter GROUP BY statement of the custom SQL query
    2. To configure custom status, select Custom Status from the menu to bring up the Edit Custom Status dialog

      1. Edit the properties of the custom status and then click the Update button. Click the Close button to cancel the operation.

        1. Value – Values of the selected data column
        2. Status – Select RAG status of each value to include in the custom status
          NOTE: From the RAG Status module, you can manage RAG statuses.
  2. Orchestration (Waves)
    1. From the menu, select Edit Column to bring up the Edit SQL Column dialog

      1. Edit the SQL query of the custom SQL column and then click the Update button. Click the Close button to cancel the operation.

        1. SELECT Statement – Enter SELECT statement of the custom SQL query (required)
        2. JOIN Statement – Enter JOIN statement of the custom SQL query
        3. WHERE Clause – Enter WHERE clause of the custom SQL query
        4. GROUP BY Statement – Enter GROUP BY statement of the custom SQL query
    2. To configure custom status, select Custom Status from the menu to bring up the Edit Custom Status dialog

      1. Edit the properties of the custom status and then click the Update button. Click the Close button to cancel the operation.

        1. Value – Values of the selected data column
        2. Status – Select RAG status of each value to include in the custom status
          NOTE: From the RAG Status module, you can manage RAG statuses.
  3. Event Logs (System Events)
    1. From the menu, select Edit Column to bring up the Edit SQL Column dialog

      1. Edit the SQL query of the custom SQL column and then click the Update button. Click the Close button to cancel the operation.

        1. SELECT Statement – Enter SELECT statement of the custom SQL query (required)
        2. JOIN Statement – Enter JOIN statement of the custom SQL query
        3. WHERE Clause – Enter WHERE clause of the custom SQL query
        4. GROUP BY Statement – Enter GROUP BY statement of the custom SQL query
    2. To configure custom status, select Custom Status from the menu to bring up the Edit Custom Status dialog

      1. Edit the properties of the custom status and then click the Update button. Click the Close button to cancel the operation.

        1. Value – Values of the selected data column
        2. Status – Select RAG status of each value to include in the custom status
          NOTE: From the RAG Status module, you can manage RAG statuses.

Edit SQL Custom Status

From the Report Builder, you can configure column settings. To configure column settings:

  1. Data Types
    1. From the menu, select Custom Status to bring up the Edit Custom Status dialog
    2. Edit the properties of the custom status and then click the Update button. Click the Close button to cancel the operation.

      1. Value – Values of the selected data column
      2. Status – Select RAG status of each value to include in the custom status
        NOTE: From the RAG Status module, you can manage RAG statuses.
  2. Orchestration (Waves)
    1. From the menu, select Custom Status to bring up the Edit Custom Status dialog
    2. Edit the properties of the custom status and then click the Update button. Click the Close button to cancel the operation.

      1. Value – Values of the selected data column
      2. Status – Select RAG status of each value to include in the custom status
        NOTE: From the RAG Status module, you can manage RAG statuses.
  3. Event Logs (System Events)
    1. From the menu, select Custom Status to bring up the Edit Custom Status dialog
    2. Edit the properties of the custom status and then click the Update button. Click the Close button to cancel the operation.

      1. Value – Values of the selected data column
      2. Status – Select RAG status of each value to include in the custom status
        NOTE: From the RAG Status module, you can manage RAG statuses.

SQL Settings

From the Report Builder, you can configure the settings. To configure the settings:

  1. Click the button to bring op the Settings dialog
  2. Configure the settings and then click the Save button. Click the Close button to cancel the operation.

    1. Format Column Names – Choose if column names are formatted
      1. Unformatted – lu__migration_status
      2. Formatted – Migration Status
    2. Report UI – Choose the display of the Report UI (One Page, Tabs)
      NOTE: Changing the display will result in the loss of any unsaved changes.

SQL Query

From the Report Builder, you can manage the report queries. To manage the report queries:

  1. Edit the main query of the new report in the query window. SQL Query reports can access the connected ETL database and the ReadyWorks database.
  2. Use the editing buttons to format the query
    1. Undo – Undo the last edit
    2. Redo – Redo the last edit that was undone
    3. Block Outdent – Remove an indent from a line or block
    4. Block Indent – Set a line or block in from the margin
    5. Comment – Comment out a line or block
    6. Clear – Clear the query
    7. Add SQL – Add sample SQL to the query
  3. Select the Primary Key of the query

    NOTE: Columns with blank values cannot be used as the Primary Key. It is best practice to use the Id, Name, or a column that contains unique values and contains no blank values as the Primary Key.
  4. Click the button to add a helper query. SQL Query reports can access the connected ETL database and the ReadyWorks database.
  5. Click the name of a query to expand or minimize the query window
  6. Click the button to run the query and preview the report before saving
  7. Click the button to bring op the Settings dialog
    1. Configure the settings and then click the Save button. Click the Close button to cancel the operation.

      1. Format Column Names – Choose if column names are formatted
        1. Unformatted – lu__migration_status
        2. Formatted – Migration Status
      2. Report UI – Choose the display of the Report UI (One Page, Tabs)
        NOTE: Changing the display will result in the loss of any unsaved changes.
  8. Click the button to expand the query to full window mode. Click the button to exit full window mode.
  9. Use the menu to manage queries
     

    1. Ignore from Execution – Ignore a query from execution
    2. Rename Query – Rename a query
    3. Move Up – Move a query up in the order
    4. Move Down – Move a query down in the order
    5. Delete Query – Delete a query
  10. Click the  button to save (Quick Save) the query. A Quick Save does not run the query.

SQL Preview

From the Report Builder, you can view the results of a report query or set of queries. To view the results:

  1. Click the  button to run the query and preview the output before saving
  2. View the results of the query in the Preview window

SQL Column Settings

From the Report Builder, you can configure column settings. To configure column settings:

  1. From the menu, select Settings to bring up the Column Settings dialog
  2. Configure the column settings and then click the Save button. Click the Close button to cancel the operation.

    1. Remove from Table – Remove column from the displayed results (column will remain in the query output)
    2. Visible – Whether the column is visible by default. Use column visibility to view the column on the report.
      1. Columns with Visible set to No are signified by an icon of an eye with a slash through it
    3. Hyperlink – Display a hyperlink to the data record (Name, Lookup, Lookup Alias, Link, and Linked Name fields only)

      1. Select the properties to build the hyperlink
        1. Page – Page of the hyperlink (Assetview, Waveview, Taskview, Userview)
        2. Data Type – Data type of the hyperlink (e.g., Computer, User, Department)
        3. Column Id – Column to use as the Id of the object
      2. Columns with a hyperlink are signified by a link icon
    4. Lookup – Mark column as a lookup (lu__, lum__, lua__, and luam__ columns will be checked automatically)
    5. Custom Status (RAG Status) – Display RAG status for each value
      1. Enter the properties of the custom status column

        1. Value – Values of the selected data column
        2. Status – Select RAG status of each value to include in the custom status
          NOTE: From the RAG Status module, you can manage RAG statuses.
      2. Custom status columns are displayed in yellow
    6. Custom Status using Ranges (RAG Status) – Display RAG status for value ranges
      1. Enter the properties of the custom status column

        1. Value – Value range of the selected data column
        2. Status – Select RAG status of each value range to include in the custom status
          NOTE: From the RAG Status module, you can manage RAG statuses.
        3. Click the Add Values button to add a value range to the custom status
        4. Click the Delete button to delete a value range (row)
      2. Custom status columns are displayed in yellow
    7. Custom Label Colors – Select custom label colors for each value (Checkbox, Select, and Status fields with less than 20 values only)

      1. Select a color for each value (Blue, Light Blue, Green, Red, Orange, Grey)
    8. Top Level Filter – Display column as a top level filter (Lookup and Lookup Alias fields only)

      1. Columns with a top level filter are signified by a slider icon
      2. Top level filters are displayed at the top of reports

        NOTE: Configure Require Top Level Filter under Report Settings to require selecting a top level filter before a report can be run.
    9. Custom Filter – Create a custom filter for the column

      1. Enter values for the custom filter (one per row)
      2. Columns with a custom filter are signified by a filter icon

SQL Report Settings

From the Report Builder, you can configure the report settings. To configure the report settings:

  1. Click the Report Settings button to bring up the Report Settings dialog
  2. Configure the settings of the report and then click the Save button. Click the Close button to cancel the operation.

    1. Pin Left Columns – Select the number of left columns to pin (None, 1, 2, or 3)
    2. Pin Right Columns – Select the number of right columns to pin (None, 1, 2, or 3)
    3. Column Sort Order – Configure a column sort order (default sort order is on the Name field ascending)

      1. Add Column – Add new rows (columns) to the sort order
      2. Column – Name of the data column to sort on
      3. Order – Sort order of the column (ascending or descending)
      4. Delete Row – Delete a row from the sort order
    4. Require Top Level Filter – Choose if selecting a top level filter is required before a report can be run

SQL Overall Status

From the Report Builder, you can configure overall status column settings. To configure overall status column settings:

  1. From the  menu of an overall status column (displayed in green), select Settings to bring up the Overall Status dialog
  2. Enter the properties of the overall status column and then click the Update button. Click the Close button to cancel the operation.

    1. Status – Select custom statuses to aggregate to determine overall status (at least one status is required)
    2. Column Label – Name of the column (from the query)
  3. Overall status column is displayed in green. Custom statuses included in the overall status are dog eared in green.