Previous Week data when report executed on sysdate

[Raghuvir]

hi all,

we have an requirement where we have to display data for previous week when the report is executed on the sysdate.

Could you guys help me to achieve this.

Thanks in advance.

Regards

[MFGF]

hi all,

we have an requirement where we have to display data for previous week when the report is executed on the sysdate.

Could you guys help me to achieve this.

Thanks in advance.

Regards

The appropriate solution depends on whether you are using a relational or dimensional package. If you are using a dimensional package it would also help to know the structure of a MUN for a day member and a week member.

Cheers!

MF.


Sent from my iPad using Tapatalk HD

[Raghuvir]

Hi MFGF,

We are using a relational package.

the requirement is, we are going to schedule the report to run on every monday. when it executes on monday it should be showing data for the previous week(monday to sunday)

could you guide us with the logic to be used.

Regards

[Lynn]

Something similar to the below should work for a relational source. If you run on Monday this would be fine, but re-running it any other day of the week is going to give you the 7 days immediately preceding the current date. If you use the day of week function you can figure out what day it currently is and then adjust to always get the preceding Monday to Sunday range no matter what day you run it on. It is a little more complicated to do, but will ensure you can re-run if something fails on any given Monday.

Code Select Expand


[YourDate] between _add_days(current_date,-7) and _add_days(current_date,-1)


[Raghuvir]

Something similar to the below should work for a relational source. If you run on Monday this would be fine, but re-running it any other day of the week is going to give you the 7 days immediately preceding the current date. If you use the day of week function you can figure out what day it currently is and then adjust to always get the preceding Monday to Sunday range no matter what day you run it on. It is a little more complicated to do, but will ensure you can re-run if something fails on any given Monday.

Code Select Expand


[YourDate] between _add_days(current_date,-7) and _add_days(current_date,-1)


thanks Lynn... :)