Solved

Dynamic Parameters for Scheduled Quick Report

  • 8 April 2021
  • 4 replies
  • 685 views
R
Rank
Userlevel 2
Badge +6

 I would like to a quick report on the 12th of the month for the prior month.  So on April 12th it will run for the month of March.  On May 12th it will run for the month of Apr and so on.  This report is also run manually by the users.  Is there a way to dynamically set the From/To date to be the prior month or do I have to create  copy of this report with the dates dynamically set in the select statement?  I obviously don’t want two exact reports because of having to remember to make all changes twice.  Below is the a screen shot of the server task schedule.

 

 

icon

Best answer by Tomas Ruderfelt 9 April 2021, 07:29

View original
This topic has been closed for comments

4 replies

Chandana Gunasekara
Rank
Userlevel 5
Badge +10

As far as I know from date and To date are static parameters that user has to enter in the scheduled report.

This might be a customization where we can hardcode these two parameters.

see whether you can use these following queries.

select Last_Day(ADD_MONTHS(sysdate,-1))+12  AS TODATE from dual;

or

select Last_Day(ADD_MONTHS(sysdate,-2))+12  AS fFROMDATE from dual;

 

Chandana
A
Rank
Userlevel 4
Badge +7

Why wouldn’t you remove both inputs altogether, and let the report’s logic pick the data for the month prior? The system knows the current system date, so it can be told to pick data for the previous month without waiting for the users to enter data.

~hope is a good thing, maybe the best of things, and no good thing ever dies...
T
Rank
Userlevel 7
Badge +19

You can do like this. The users can enter the date range wjen they run it manually  and when you schedule it you do not fill in the dates and the last month, from today, will be used.

my_date_column BETWEEN NVL(to_date('&FromDate','MM/DD/YYYY'), add_months(TRUNC(SYSDATE,'MM'), -1)) AND NVL(to_date('&ToDate','MM/DD/YYYY'), TRUNC(SYSDATE,'MM')-1)

 

R
Rank
Userlevel 2
Badge +6

Thomas,

That is the ticket.  I will add to your answer to make it a bit better:

TRUNC(My_Date_column) BETWEEN COALESCE(&absence_period.date_from, add_months(TRUNC(SYSDATE,'MM'), -1)) 
        AND COALESCE(&absence_period.date_to, TRUNC(SYSDATE,'MM')-1)

 

&absence_period.date_to (this is a generic date field, but can use a date field straight from the table and it will use the column name as the prompt for the parameter box.) Doing it this way puts a option box at the end of the parameter box that allows the user to select the date using calendar control.  Can use it with NVL also.

 

Terms & ConditionsCookie settings