Knowledge Base > KB10411 - Special Query Keywords for Local Web Reports
Contributor: Rob Pinion
Last Updated: Dec 05, 2023 4:37pm

Queries for the Local Web Reports can be customized to automatically determine the user's site number and to prompt the user for additional data on the fly.

Note that in all examples, tables names and field names are bogus, since this is a public site.


Limit Results to the User's Site:


If you wish to limit the results of a query to the records matching the user's site, you may place the special keyword SITECODE where the site code is to be inserted into the query.

For example, consider the following query: select field1 as Position_Number, field2 as Position_Desc from library.table where pos_site_cd='SITECODE'

If Mary Smith's HRMS user account profile shows the her primary site is Mayberry Elementary, and Mayberry Elementary is site number 123, when Mary runs a local web report based on the above query, only position records with the site code 123 will appear in the report.

Mary is not prompted to enter anything. Her site number is automatically determined and inserted into the query. Typically, such local reports will be most useful to principals and site-based secretaries.

Make sure the appropriate role is selected on the Local Web Report so that proper access can be gained to the report. Principal accounts are typically assigned the HiringAgent role.

If the user has one of the following roles, the user will be prompted to enter a site number, and the site number entered will be substituted for every occurrence of SITECODE in the query.
The SITECODE keyword should not be used on reports that are available to Anonymous users as there will be no way to determine the user's site.



Prompting the User for Data When a Local Web Report is Launched:


Users can be prompted to enter data into a query when a Local Web Report is run.
Asking the user for this data is accomplished by inserting a specially-formatted prompt statement into your query.
By following a few conventions when writing your prompts, a single Local Web Report can produce a nearly infinite combination of results.

Formatting conventions for user prompts:
  1. Start each prompt with the keyword PROMPTSTART. It must be upper case. This is not a SQL keyword, but is specific to this feature within HRMS.
  2. End each prompt with the keyword PROMPTEND. Again, it must be upper case.
  3. Between the PROMPTSTART and PROMPTEND keywords, write your prompt.
  4. Your prompt should not contain parenthesis, quotes, apostrophes, or most other special characters, though periods, commas, and slashes may be used.

Example: select field1 AS ETHNICITY, UPPER(field2) AS LAST, UPPER(field3) AS FIRST, UPPER(field4) AS MIDDLE from library.table where field1 = 'PROMPTSTART Enter an Ethnicity Code B C A or I. PROMPTEND' AND upper(field2) LIKE 'PROMPTSTART Last Name starts with - user upper case please. PROMPTEND%'

If the user's first response is B and second response is J the following query will be submitted:
field1 AS ETHNICITY, UPPER(field2) AS LAST, UPPER(field3) AS FIRST, UPPER(field4) AS MIDDLE from library.table where field1 = 'B' AND upper(field2) LIKE 'J%'


Prompting for a value once and inserting the response it multiple times:


If you include a prompt command multiple times in the exact same way , the system will prompt the user once for that input and will insert the response into the query as many times as you repeat the prompt command.

For example, in the query below, the system prompts the user twice: first, to enter an ethnicity code and second, to enter the starting character of the first and last name. Even though there are three prompt commands below, the last two are exactly the same, and the system only prompts for that answer once.

Example: select field1 AS ETHNICITY, UPPER(field2) AS LAST, UPPER(field3) AS FIRST, UPPER(field4) AS MIDDLE from library.table where field1 = 'PROMPTSTART Enter an Ethnicity Code B C A or I. PROMPTEND' AND UPPER(field2) LIKE 'PROMPTSTART First and Last Name both start with - user upper case please. PROMPTEND%' AND UPPER(field3) LIKE 'PROMPTSTART First and Last Name both start with - use upper case please. PROMPTEND%'

If the user's first response is B and second response is J the following query will be submitted. Notice that the user's J answer has been inserted twice:
field1 AS ETHNICITY, UPPER(field2) AS LAST, UPPER(field3) AS FIRST, UPPER(field4) AS MIDDLE from library.table where field1 = 'B' AND upper(field2) LIKE 'J%' AND upper(field3) LIKE 'J%'


Prompting for dates and changing the case on the user input:


See the sample below for a good way of prompting the user to enter a date or converting the value entered to upper case.

Example: select * from library.table1 join library.table2 on field1= field2 where field3 ='SITECODE' and field4 = UPPER('PROMPTSTART Please Enter C for Classified or L for Licensed PROMPTEND')and field5 >= 'PROMPTSTART Please Enter a Start Date in the following format MM/DD/YYYY PROMPTEND'


Prompting for SSN:


See the sample below for a good way of prompting the user to enter a Social Security Number. In this case, you may want to make the report available to Anonymous users.

Example: select field1 from library.table where field3 = 'PROMPTSTART Please enter your Social Security Number without dashes PROMPTEND'


Prompting for a combination of partial SSN and Name:


See the sample below for a good way of prompting the user to enter a partial Social Security Number and last name. In this case, you may want to make the report available to Anonymous users.

Example: select field1 from library.table1 join library.table2 on field4ssn = field5ssn where right(trim(field4ssn),5) = 'PROMPTSTART Please enter the last five digits of Social Security Number without dashes. PROMPTEND' and upper(trim(fieldlastname)) = upper('PROMPTSTART Please enter your last name. PROMPTEND')


Using special commands with the Query Builder


The Query Builder does not prompt you to insert special commands. However, you will typically want to use the Query Builder to generate your base query, then insert the special commands in place of hard-coded values, such as substituting SITECODE for a hard-coded site number in the WHERE clause.

Potential Large Record Sets


If you prompt for a value used with the LIKE % syntax, and the user does not enter a value when prompted, the number of record returned may be large and could impact your system's performance while the report is being generated. It is recommended that you be judicious in the use of the LIKE % syntax, especially when making reports available to Anonymous users and those users with limited knowledge of HRMS.