Using Saved Queries

Queries can be defined as run as often as necessary using the saved queries option. Users also have the option to apply pre-filters to the query. Queries must first be setup and run via System > Query Tool prior to using the Saved Queries option. Additional information on the process is available via the Process section below.

Configuration

Users will need access to the following security settings prior to running saved queries.

  • Tools -- report management
  • System -- query tool

Process

  1. Navigate to System > Query Tool.
  2. Setup and run the appropriate query.
    1. Additional information on queries as well as additional query examples can be found via the Using the Query Tool help page.
    2. Note: If you wish to setup a pre-filter with the query, you need to code the pre-filter information in the query itself. See the Query Example with Pre-Filter section below for details.
  3. Once the query has loaded, click the More ellipsis button and then click the Publish Data option.
  4. Select "Favorite With Preview" as Type.
  5. Enter an appropriate description and select any other settings as necessary.
  6. Click the "Save" button when complete.
  7. Navigate to Tools > Management Reporting.
  8. Select a Report Type of "Saved Queries" and click the "View" button to display your Favorite With Preview query.
  9. At this point users have the option of running the query or adding a pre-filter first. To run the query, select it and click the "Run" button. To add a pre-filter first, click the "Pre-Filter" button.
  10. At this point the system will display a blank QueryPrefilter form in the Form Builder. Additional information on the Form Builder is available via the Form Layouts help page.
  11. Add the necessary fields and parameters to the pre-filter and save the form.
  12. Exit the Form Builder, save the query, and exit the Edit Saved Query form. Note that once this is done your original Favorite With Preview will change to a Favorite. This occurs because the system cannot preview a pre-filter.
  13. Click on your favorite to run it and notice the previously created pre-filter will appear.
  14. Fill in your pre-filter and click the "View" button to run the query using the pre-filter.

Query Example

The following example demonstrates a query for active part numbers that is saved and then modified with the "Saved Queries" Report Type.

  1. Enter and run the following query - SELECT * FROM dmprod WHERE pr_active = 1
  2. Publish the results as a "Favorite with Preview".
  3. Navigate to Tools > Management Reporting.
  4. Run a Report Type of "Saved Queries".
  5. Modify the Query Text for the query in Step 1 and add a filter with a placeholder value - AND pr_caid = <<categoryid>>
  6. Click Pre-Filter and a blank QueryPrefilter form will load in the Form Builder.

Query Example with Pre-Filter

The following example demonstrates a query for to see what Bill-To Customers have been created within a given date range and then modified with the "Saved Queries" Report Type. The query contains the information needed for the pre-filter at the top with both 'DECLARE' statements.

DECLARE @Start AS DATE = <<start date>>

DECLARE @End AS DATE = <<end date>>

SELECT lo_date as 'Created Date', bi_name as 'Bill-to', bi_id as 'ID', s2_name as 'Customer Type'

FROM dxlog, dmso2, dmbill

LEFT JOIN cookiesunitedsystem.dbo.dxuser ON bi_id = us_id

WHERE lo_recid = bi_id AND bi_active = '1' AND s2_id = bi_s2id AND lo_date between @start and @end AND lo_table = 'dmbill'

FAQ & Diagnostic Tips

Can saved queries be deleted?

Yes, click the saved query in the list and hit the "Delete" button. The system will delete the saved query along with the associated Favorite.