BusinessObjects Board

How do I create a report using a date with minus days?

I am working on a report that needs to run daily starting Tuesday thru the following Tuesday. Each day it runs it has to go in the following sequence:

Wednesday -1
Thursday -2
Friday -3
Saturday -4
Sunday -5
Monday -6
Tuesday -7

and then it starts all over again on the following Wednesday. I am using Crystal Reports 12. Any input would be greatly appreciated.


ROCKO5960 (BOB member since 2019-09-30)

Unfortunately your request is very unclear to me. What do you mean by ‘Each day it runs it has to go in the following sequence’. Are you talking about sorting, grouping or something else?


kevlray :us: (BOB member since 2010-06-23)

So when the report runs on Tuesday it looks back -1 day, Wednesday -2 days, Thursday -3 days Friday -4 days Saturday -5 days Sunday -6 days and Monday -7 days and then on Tuesday it starts all over again. I am looking for a running total for each day. Currently, I have 7 reports to do this for me. I was looking to see if there is a way I can combine these into 1 report.


ROCKO5960 (BOB member since 2019-09-30)

I do not know if you got an answer for this. But there are probably several ways of doing it. One way is to create a formula to see what week day it is (I forget what it is called) and based on that (I think it returns a number) then you can subtract that number from the current date.

Do you have access to the Universe? You could create a filter

We use Oracle, so for some of my requirements I will utilize one of these in the Universe for the appropriate attribute:

select
add_months(trunc(sysdate,‘MM’),0) bom,
add_months(trunc(sysdate,‘MM’),0)-1 eom,
to_number(to_char(sysdate,‘IW’)) iso_week_num,
add_months(trunc(sysdate,‘IW’),0) bow,
add_months(trunc(sysdate,‘IW’),0)-3 eolw,
add_months(trunc(sysdate,‘IW’),0)-10 eopw,
add_months(trunc(sysdate-0,‘DD’),0) day0,
add_months(trunc(sysdate-1,‘DD’),0) day1,
add_months(trunc(sysdate-2,‘DD’),0) day2,
add_months(trunc(sysdate-3,‘DD’),0) day3,
add_months(trunc(sysdate-4,‘DD’),0) day4,
add_months(trunc(sysdate-5,‘DD’),0) day5,
add_months(trunc(sysdate-6,‘DD’),0) day6,
add_months(trunc(sysdate-7,‘DD’),0) day7,
add_months(trunc(sysdate-2,‘IW’),0) last_week,
add_months(trunc(sysdate-2,‘IW’),0)-3 last_week,
add_months(trunc(sysdate-2,‘IW’),0)-10 prior_week
from dual;