Peoplesoft Queries - range report

Question

I'm new to Peoplesoft and their query tool. I'm used to SQL (in the Ceridian Powerquery environment and TOAD) and I'm wondering if you can create a range report (ex. 1/1/2016 to 12/31/2016)?

There are some legacy reports in this environment and it appears that you can do this, but the dates are hard coded. So I'd have to go in to edit, then criteria, then edit for the particular expression (termination date), then I'd have to change the dates in the Expression 2 field.

There must be an easier way to do this - one where I can find the report, select excel, and then it brings me to a screen with two date fields (for the start/end date).

Answer 1

Another approach is to have a "CURRENT_MONTH" table in the system, which would contain information like FISCAL_YEAR, ACCOUNTING_PERIOD, START_DATE, END_DATE, NUM_OF_DAYS, etc. If queries are built joining the CURRENT_MONTH table, then all queries that use the table can run automatically without the need for Prompts, which require manual intervention and can't be used in Scheduled Queries. So if you have a large number of reports and want to run them in an automated fashion without user intervention, this is a good approach.

Answer 2

First either edit the existing PSQuery or save a copy of it and then make the following changes:

1. Create two new prompts

a) Click on the prompts tab

b) Click add prompt

c) Choose a field name. EG: DATE_FROM

d) Press Save

e) Click add prompt

f) Choose a field name. EG: DATE_TO

2. Modify the existing criteria to use the new prompts

a) Click on the criteria tab

b) Find your existing criteria involving the date field

c) Click the Edit Button

d) Change the condition type to between (if it is not already)

e) In the Choose Expression 2 Type box, choose Expr - Expr

f) In the Define Expressions -> Expressions 1 Box.
Clear out any existing values if they exist and then click Add Prompt.
Choose the prompt for DATE_FROM (or whatever field you chose)

g) In the Define Expressions -> Expressions 2 Box.
Clear out any existing values if they exist and then click Add Prompt.
Choose the prompt for DATE_TO (or whatever field you chose in 1f)

h) Click Ok

3. Save and run the query

You should now be prompted for dates for the query to run.