Report based on past 3 months

system · August 20, 2009, 2:12am

HI

I have a requirement to build a report that fetches data for the last 3 months. I dont want to create a report every month that fetches data for last 3 months. I want to create a report and schedule it so that when ever it runs it fetches data for the last 3 months. For example if I run my report today it should fetch data for May, June, July and when it runs in Sept it should fetch data for June, July, August.
Is this possible?

Cheers

Aussie2582 (BOB member since 2008-04-16)

system · August 20, 2009, 2:23am

Yes, it is possible.

You need to use in the queries of the report conditions that use your database system date (it’s SYSDATE in Oracle) and some date functions.

Have a look at this series of blogs of Dave Rathbun:

Marek Chladny (BOB member since 2003-11-27)

system · August 21, 2009, 12:13am

Hi Marek,

Can you please give me more explanation on this. What I understood is that I have to create queries based on sysdate. Can you please give me some examples!

Cheers

Aussie2582 (BOB member since 2008-04-16)

system · August 21, 2009, 3:20am

Hi,

Please specify which database you use because syntax is different from one database to another.

Marek Chladny (BOB member since 2003-11-27)

system · August 21, 2009, 6:00am

Oracle.

Aussie2582 (BOB member since 2008-04-16)

system · August 21, 2009, 6:06am

Hi ,

You need to create two objects in the universe. Say
x=sysdate and y=sysdate-30

In report conditons you need to use these two objects

between X and Y

Tony Joe (BOB member since 2006-12-05)

system · August 21, 2009, 5:18pm

The beginning of the reporting period can be found as

ADD_MONTHS (TRUNC (SYSDATE,'mm'), -3)

and the end of the reporting period as

TRUNC (SYSDATE,'mm') - 1/86400

So you need to create a condition in a report that uses the above syntaxes. Something along the lines:

table.transaction_date BETWEEN ADD_MONTHS (TRUNC (SYSDATE,'mm'), -3)
                           AND TRUNC (SYSDATE,'mm') - 1/86400

Marek Chladny (BOB member since 2003-11-27)

system · August 24, 2009, 1:57am

Thanks, but it looks like my issue is more complicated than I expected.

I defined below objects

vol1=table1.cur_vol where table1. month=to_char(add_months(sysdate,-3),'YYYYMM') for May and vol2=table1.cur_vol2 where table1.month=to_char(add_months(sysdate,-2),'YYYYMM') for June and so on

But if I drag drop 2 or more months into my report my report , it fetches no data. It works fine if my report has one month. Say if I select Customer name and May volume as report objects it works fine but if I add JUne and/or July vol to my report it fetches no data!

Any ideas?

Aussie2582 (BOB member since 2008-04-16)

system · August 24, 2009, 2:45am

Hi,

Can you explain why do you need more objects? Is it not just one measure?

Marek Chladny (BOB member since 2003-11-27)

system · August 24, 2009, 3:59am

I want Customer name, may volume, june and july volumes in my report.
may, june and july are 3 different measures.

Aussie2582 (BOB member since 2008-04-16)

system · August 24, 2009, 4:04am

It can be only one measure if you also use a “month name” dimension with it. Something like:

customer name…month name…volume
XYZ…may…10
XYZ…june…12
XYZ…july…16

Marek Chladny (BOB member since 2003-11-27)

system · August 24, 2009, 4:36am

But how can I create that measure object as it depends on different months? For May it depends on 200906, for June on 200907…

Aussie2582 (BOB member since 2008-04-16)

system · August 24, 2009, 4:45am

You could create 3 separate query objects and merge them in the report.

Or you could create 3 queries and union them within the one query def.

At the moment you are saying where Month=June AND Month=July. That can never return data.

I would go for a Union and thefore you will 6 date objects in your universe.

weaver (BOB member since 2008-10-17)

system · August 24, 2009, 9:22pm

Hi,

If you applied the condition that I mentioned above then the report would always retrieve data only for the last 3 finished month. So for instance, if you ran the report now it would fetch data for May, June, July. Next month, the same condition will fetch data for June, July, August. Etc. etc.

Do you have a calendar table (date dimension) in your database? If yes then join it to your transaction table. Create a “month name” object from the calendar table. Then create a measure “volume” from the transaction table.

So then, when you select “month name” and “volume” objects and use the dynamic date condition mentioned above, you will get data (the measure) for 3 months as you need it.

Marek Chladny (BOB member since 2003-11-27)