Selection criteria, previous month and same month last year 1

[urbanhim]

I’m trying to do a report which shows data for the last full month, plus the same month for the previous year.

At the moment I have two reports, one pulling data for last month and another pulling data for same month last year, whereby I have to manually enter the date range myself.

However, report requirements mean I need to automate this every month, and also draw comparisons on the same report (so two seperate is not an option).

Ive used this selection formula, but it brings back more than I need, it seems to be all years:

Month({ORDER_HISTORY.MODIFIED_DATE}) = Month(DateAdd("m", -1, currentdate))

Grateful for any help.

Thank you

UrbanHim
Crystal XI Report Writer
London

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]

 

[lbass]

I would try:

{table.date} in lastfullmonth or
{table.date} in dateserial(year(maximum(lastfullmonth))-1, month(maximum(lastfullmonth)),1) to
dateserial(year(maximum(lastfullmonth))-1, month(maximum(lastfullmonth))+1,1)-1

-LB

 

[urbanhim]

Thanks for this LB.

Out of interest though, do you know how the data retrieval might work for this against an Oracle table? The table that i'm pointing to has about 6 years worth of data, and over 5 million records for each month, so in total, the data is huge.

When I run this formula, it does nothing for 10 minutes, then it starts to read records, then Crystal terminates altogether after another 20 minutes.

I just wondered if there was anyway in which i can streamline this whole process!!

Thanks





UrbanHim
Crystal XI Report Writer
London

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]

 

[lbass]

I just tested this formula with the Xtreme database and the entire record selection formula passed to the SQL, meaning it is optimized. Is this your entire selection formula? Maybe there is some other part of your selection formula that is not passing.

-LB

 

[urbanhim]

This is the only selection I am using, here is the SQL (from here I can see how great your formula is too) ;-)

 SELECT "ORDER_HISTORY"."ACCOUNT_ID", "ORDER_HISTORY"."MODIFIED_DATE", 
"ORDER_HISTORY"."SITE_IDENTIFIER", "ORDER_HISTORY"."ORDER_HISTORY_ID", 
"ORDER_HISTORY"."DEAL_ID", "ORDER_HISTORY"."SITE_TYPE", 
"ORDER_HISTORY"."BUSINESS_AREA", "ORDER_HISTORY"."ACTION"
 FROM   "CONTENT"."ORDER_HISTORY" "ORDER_HISTORY"
 WHERE  (("ORDER_HISTORY"."MODIFIED_DATE">=TO_DATE ('01-04-2008 00:00:00', 
'DD-MM-YYYY HH24:MI:SS') AND "ORDER_HISTORY"."MODIFIED_DATE"<TO_DATE 
('01-05-2008 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) OR 
("ORDER_HISTORY"."MODIFIED_DATE">=TO_DATE ('01-04-2007 00:00:00', 
'DD-MM-YYYY HH24:MI:SS') AND "ORDER_HISTORY"."MODIFIED_DATE"<TO_DATE 
('01-05-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS')))

It is really frustrating, if I run the two dates as seperate reports, it does take 15 minutes or so for each report, so I would expect this to take around 30 minutes, but it just doesnt seem to work against our Oracle table.



UrbanHim
Crystal XI Report Writer
London

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]

 

[urbanhim]

Your formula clearly works anyway, i'll have to take the issue of the reports crashing up with my IT team, thanks once again.



UrbanHim
Crystal XI Report Writer
London

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]