Search Knowledge Base by Keyword

Report Builder

< Back

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

  1. Click the Report Builder button to bring up the Report Builder wizard
  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 page 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. Assets (Assets, Lists)
      2. Workflow (Waves, Tasks)
      3. System Events
    5. Description – Description of the report (optional)
  2. Add data columns to the report
    1. Assets
      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 an asset type (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. Workflow
      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 an asset type (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. 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 an asset type (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 (Assets 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 Asset – Select an asset 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 (Assets 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. Value – Values of the selected data column
      3. 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 (Assets 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. Assets
      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. Workflow
      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. 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 asset (Name, Lookup, Lookup Alias, Link and Linked Name fields only)
      4. Custom Color Labels – Select custom label colors for each value (Checkbox, Select and Status fields with less than 20 values)
      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 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. Click the Preview Report button to preview the report before saving
  13. 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.
  14. Click the Create Report button when finished to create the report. You will be redirected back to the Reports tab. 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 mapping in the query window
    NOTE: When using SQL functions for columns, be sure to wrap the column with an AS label (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
    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 report is created
    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 asset (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 Color Labels – Select custom label colors for each value (Checkbox, Select and Status fields with less than 20 values)
      7. Top Level Filter – Display column as a top level filter (Lookup and Lookup Alias fields only)
      8. 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 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. Click the Preview Report button to preview the report before saving
  8. Click the Create Report button when finished to create the report. You will be redirected back to the Reports tab. Click the Cancel button to cancel the operation.

Edit Report

From the Reports tab, 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 page for a Classic Report and the SQL Query Report Builder page 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. Assets (Assets, Lists)
      2. Workflow (Waves, Tasks)
      3. System Events
    5. Description – Description of the report (optional)
  2. Add data columns to the report
    1. Assets
      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 an asset type (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. Workflow
      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 an asset type (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. 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 an asset type (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 (Assets 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 Asset – Select an asset 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 (Assets 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. Value – Values of the selected data column
      3. 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 (Assets 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 custom status column and then click the Create button. Click the Close button to cancel the operation.
    3. Select the data column of the overall status from the Data Column drop down

      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)
    4. 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. Assets
      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. Workflow
      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. 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 asset (Name, Lookup, Lookup Alias, Link and Linked Name fields only)
      4. Custom Color Labels – Select custom label colors for each value (Checkbox, Select and Status fields with less than 20 values)
      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 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. Click the Preview Report button to preview the report before saving
  13. 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.
  14. Click the Update Report button to save any changes to the report. Click the Cancel button to cancel the operation.

Edit SQL Query Report

From the Report Builder page, you can edit SQL Query reports. To edit 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 mapping in the query window
    NOTE: When using SQL functions for columns, be sure to wrap the column with an AS label (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
    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 asset (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 Color Labels – Select custom label colors for each value (Checkbox, Select and Status fields with less than 20 values)
      7. Top Level Filter – Display column as a top level filter (Lookup and Lookup Alias fields only)
      8. 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 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. Click the Preview Report button to preview the report before saving
  8. 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.

Data Column

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

  1. Assets
    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 an asset type (default)
        2. Sort Alphabetically – Sorts data columns alphabetically
      2. Data columns are organized by asset type with the selected data source listed first (1)
      3. Asset types linked to the selected data source are listed subsequently in alphabetical order (2, 3, 4, etc.)
      4. Each asset type is organized by Standard columns, Lookup columns and Lookup Alias columns
  2. Workflow
    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 an asset type (default)
        2. Sort Alphabetically – Sorts data columns alphabetically
      2. Data columns are organized by workflow 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. Asset types linked to the selected data source are listed subsequently (3, 4, 5, etc.)
      5. Each asset type is organized by Standard columns, Lookup columns and Lookup Alias columns
  3. 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 an asset type (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 (Assets 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 Asset – Select an asset 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 asset (Name, Lookup, Lookup Alias Link and Linked Name fields only)

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

      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. Add Where Clause – Add new rows (columns) to the where clause
      2. Where – Name of the data column to filter the report on
      3. Operator – Select an operator (equals and not equals are currently supported)
      4. Value – Enter or select a value to filter the report
      5. Delete Row – Delete a row from the where clause
    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 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.

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 (Assets 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. Value – Values of the selected data column
    3. 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 (Assets 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. Value – Values of the selected data column
    3. 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 (Assets 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 (Assets 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. Assets
    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. Workflow
    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. 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. Assets
    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. Workflow
    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. 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. Assets
    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. Workflow
    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. 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 mapping in the query window
  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
  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 asset (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. Asset – Asset 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 Color Labels – Select custom label colors for each value (Checkbox, Select and Status fields with less than 20 values)

      1. Select a color for each value (Blue, Light Blue, Green, Red, Orange, Grey)
    7. 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.
    8. 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.