I had an ex colleague asking me for help with question on how to schedule to a PS Query which has two date prompts (namely Start Date and End Date).
Query should be scheduled to run every month and prompt values should be based on the month it is running. e.g: Start Date prompt value should be the first day of previous calendar month and End Date prompt value should be the last day of previous calendar month.
While I'm not sure if there is any PS delivered way to do this, I can think of below options to overcome this problem.
1. Change the PS Query criteria so that it takes expression as parameter instead of prompt. Use expression LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1 for first day of previous calendar month and LAST_DAY(ADD_MONTHS(SYSDATE,-1)) for last day of previous calendar month (Of course I'm assuming it is Oracle DB). You might have to change the expression if you are on any other DB.
2. Schedule a custom AE/SQL or any other tools to update the run control parameter stored in the table PS_QUERY_RUN_PARM.
In my humble opinion first option is more elegant than the other.
If you have a better solution for this problem, please comment on this post.
Query should be scheduled to run every month and prompt values should be based on the month it is running. e.g: Start Date prompt value should be the first day of previous calendar month and End Date prompt value should be the last day of previous calendar month.
While I'm not sure if there is any PS delivered way to do this, I can think of below options to overcome this problem.
1. Change the PS Query criteria so that it takes expression as parameter instead of prompt. Use expression LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1 for first day of previous calendar month and LAST_DAY(ADD_MONTHS(SYSDATE,-1)) for last day of previous calendar month (Of course I'm assuming it is Oracle DB). You might have to change the expression if you are on any other DB.
2. Schedule a custom AE/SQL or any other tools to update the run control parameter stored in the table PS_QUERY_RUN_PARM.
In my humble opinion first option is more elegant than the other.
If you have a better solution for this problem, please comment on this post.
No comments:
Post a Comment