top

back.gif home.gif print.gif

Pre-Built Query Runner

Use this tool to run pre-defined queries helpful to the Sales and Marketing teams.  Please note that users cannot build their own queries through this utility.  If you would like a new query added or an existing query modified, please contact Support.  

About the report format

Because it's not unusual for character fields to contain commas or quotes, query results are delimited with a pipe "|" and output as .txt files (rather than comma-delimited .csv files).  Steps for opening pipe-delimited .txt files in Excel® are located below.

 

Follow these steps:

 

Running Queries

From the Sales/Marketing window, in the Reports section:

  1. Click Open Pre-Built Query Runner.  

  2. If desired, enter search criteria to locate the query.

  1. Name:  Enter part of the query name.  Use asterisks as wild cards.

OR

  1. Category:  The type of data queried.  For example:

Directory - Queries such as User Lists (contacts) and Agreements (client trading partners).  

Sales and Marketing - Active Retailer and Supplier Lists.  

See Common Queries for brief descriptions and a list of fields.

  1. Click Find.

  2. Select the desired query.  

  3. If a query requires that you enter Parameters, these display below.  (Otherwise no fields appear and you can skip to the next step.)  Complete the fields to specify your search.

Not all parameters are required, such as multiple Supplier ID / Retailer ID fields, but the more parameters you complete the more concise the results will be.  Errors appear if you try to run the query with a missing parameter.

Each parameter has an "operator" that describes how the query will be performed.

=  Data is an exact match.  For example, a numeric Supplier ID (Business Unit ID) such as 35624.  

>=  Greater than or equal to.  For example, a "From" Creation date.  Entering 01/01/2012 will list all data from January 1st forward.

<=  Less than or equal to.  For example, all data on or before 02/22/2012.  

in  Within a range, with delimiters (commas) separating the values.  For example, a range of store numbers.  Entering 101,120 (without spaces) will include store 101, 102, 103, up to 120.

  1. Run the query.

    • Click Display to review the results onscreen.  (Button will not appear for large queries, such User Lists.  See the onscreen "Can Display? column.)

and/or

    • Click Report to receive the results in a delimited .txt file.  Reports are sent directly to your Prescient e-mail address.

____________________________________________________________

Opening

Pipe-delimited attachments

Reports run from the Pre-Built Query Runner are pipe "|" delimited, and output as .txt files.  Do the following to open these files in Excel.    

  1. Open the e-mail and save the .txt file to your computer.

  2. Open Excel.

  3. Click File > Open.

  4. In the Files of type drop-down, select Text Files (*.prn; *.txt; *.csv)

  5. Select the .txt file and click Open.  

The Text Import Wizard displays.

  1. Ensure Delimited is checked and click Next.

  2. In the Delimiters section, select Other and enter the pipe symbol in the box to the right.  

Pipe is the | character above the \ (backslash) on your keyboard.

  1. Click Next, then Finish.

Your query results open, and can be saved as .csv or .xlsx.

____________________________________________________________

(Top)