Accounting Period 4th Friday each month

[Brenj68]

Hi All, I'm on CR2008/ Oracle 11.

My user has requested the following report, which I'm fairly certain I've done in the past but unfortunately have no record of how I may have achieved it.

I need to calculate the total hours booked (to a project) on an accounting monthly schedule basis (not the nice easy calendar month calculation). These accounting period startdate is from the 4th Saturday (each month)to period enddate of 4th Friday (each month), and in this case is the current financial year 01 Mar 13 to 28 Feb 14. So the report will presednt a YTD total and display each full previous month as well as what has been currently booked to the point when the report is run
Just to clarify my reporting periods are:
02 Mar 2013 - 29 Mar (yes this a 5 week period)
30 Mar 2013 - 26 Apr (4th Friday end)
27 Apr 2013 - 24 May (4th Friday end), etc

Any suggestions are always appreciated

 

[hilfy]

Do you have access to create a table in the database? I think the easiest way to do this would be to create a calendar table that contains information about the accounting month start and end dates. It might have the following structure:

Calendar_Date DateTime
Cal_Month DateTime
Cal_Year DateTime
Fiscal_Month DateTime
Fiscal_Year DateTime

So, your data would look something like this:

03/01/2013, 03/01/2013, 01/01/2013, 03/01/2013, 03/01/2013
...
03/30/2013, 03/01/2013, 01/01/2013, 04/01/2013, 03/01/2015

By having the fields as actual dates, it's much easier to sort things correctly than if, for example, the Cal and Fiscal dates are in strings like MAR-2013. You can easily set the display format for the dates to give the format you're looking for without sacrificing the ability to sort correctly.

You would join this table to a date field in your data to get the calendar and fiscal information for that specific date.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Brenj68]

Hi Thanks for the response, unfortunately I do not thnk your suggestion will work for me as my system is SaaS and I have only read rights to the db, this also excludes everything and anything else that may have been an option. So my only route is using Crystal.

Thanks for the feedback

B

 

[IanWaterman]

You will have to build a huge If..then.. else formula with date ranges set

If yourdatefield between 02 Mar 2013 and 29 Mar 2013 then '2013-01'
else
If yourdatefield between 30 Mar 2013 - 26 Apr 2013 2013 then '2013-02'
else
If yourdatefield between 27 Apr 2013 - 24 May 2013 2013 then '2013-03'
else

Keep going until you get bored or think you will have left company ;-)

Ian

 

[Brenj68]

Hi Ian, I was soooo hoping that I wouldn't have to do this approach :-0 but at least there are only 12 months to set up.

Cheers

Brendan

 

[hilfy]

Unfortunately, the big If statement is also a maintenance headache because you'll need to update it on a regular basis as the years change. Another option would be to create the calendar table in an Excel spreadsheet, use it as an additional data source in the report and join it to the date fields in the data. However, this may also cause the report to take longer to run, depending on the amount of data.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Brenj68]

Hi, This is the approach I've taken but it is throwing up some unusual results: Just for reference the cmd.Hours_Date is a date/time
if totext({Command.HOURS_DATE}, "dd/mm/yyyy") in ('01/03/2012') to ('23/03/2012') then '2012/03'
else
if totext({Command.HOURS_DATE}, "dd/mm/yyyy") in ('24/03/2012') to('27/04/2012') then '2012/04'
else
if totext({Command.HOURS_DATE}, "dd/mm/yyyy") in ('28/04/2012') to ('25/05/2012') then '2012/05'
else
etc......
if totext({Command.HOURS_DATE}, "dd/mm/yyyy") in ('25/01/2014') to ('28/02/2014') then '2014/02'

This does correctly place some results in the correct accounting bucket, as can be seen when this is placed in the details section, but is not consistent. I have tried alternatives
ie the above
if ({@Hours_date_convert} in ('01/03/2012') to ('23/03/2012')) then '2012/03'
else
if ({@Hours_date_convert} in ('24/03/2012') to ('27/04/2012')) then '2012/04'
else
if ({@Hours_date_convert} in ('28/04/2012') to ('25/05/2012')) then '2012/05'
else .....
totext({@PRstartdate}, "yyyy/MM") which does correctly identify the relevant accounting period. Also even though there is timesheet/ project data going back to the beginning of last year, only 2012/03, 2012/04 and 2012/05 are being returned. What am I missing here please - think I'm going a bit stir....

 

[IanWaterman]

Using text strings to filter dates is not a good idea, do it this way

if {Command.HOURS_DATE}, "dd/mm/yyyy") in datetime(2012, 3,1,0,0,0) to datetime(2012, 3, 23, 23, 59,59) then '2012/03'
else
....

Ian

 

[Brenj68]

Please ignore last comment, user error on my behalf I had missed the "'" on each of the accounting date periods, for the subsequent months. All looking good, Thanks for your input.

B