Hope you have table W_DAY_D then try to utilize field W_CURRENT_CAL_WEEK_CODE
or else go for variables that would flag current week.
Hi, Sir Srini.
Thank you for your reply.
May I know what does W_DAY_D contains?
Just so we're clear to the OP:
W_DAY_D is one way of doing date dimension and comes from the Oracle BI Applications product line.
It is not the ultimate answer to all questions but one way of midellimg a date dimension.
Below formula will provide you week ending date (here i am considering friday as weekending date. You can modify if you need another day as weekending date)
case when DAYOFWEEK(Date.Date) = 7 then timestampadd(sql_tsi_day,6,Date.Date) else timestampadd(sql_tsi_day,(6-DAYOFWEEK(Date.Date),Date.Date) end
Filtering your report based on current date will always hide/show data for current week. Also you can use Weekofyear function for finding the week number.