Accountacy Period Groupings

[JamesFlowers]

hi

I have a date formula made from

if {condition}='OPEN' then {instr_date} else if {condition}='CLOSED' then {close_date)

this gets me a single date in a report that a report is either opened or closed on.

But when I go to group the dates , it has to be in line with our accountacy year

which runs on periods like

04/June/2011 to 05/July/2011
06/July/2011 to 01/August/2011

so there isnt a set date period each month (but these dates dont change each year).

When it comes to the december period

11/Dec/2011 to 09/Jan/2012

it goes haywire and the rest of the years dates are put into this banding , and ignore subsequent date ranges.

I am using progress as a DB abd CR11


Many thanks in advance.

JAmes

James Flowers
Crystal Consultant

 

[RTag]

You can do this inside crystal reports using arrays , but database is much better for queries than Crystal reports.

I would create a view like this :
SELECT CASE WHEN condition = 'OPEN' THEN instr_date ELSE close_date END as RecordDate, <other columns here ...> FROM tablename

This view will get the data for the record + will identify the date (open or close)

The next thing will be to create a table to keep the accounting periods. I will create a separate record for each month and prefill the dates for any period you might need.
The structure of the table will be:
AccountingPeriodID Month Year StartDate EndDate

Then join the view and the table and identify in which accounting period your record date is falling. This will be your report SQL
SELECT rd.*, ad.AccountingPeriodID, ad.Month , ad.Year
FROM viewRecordDates rd , AccountingDates ad
WHERE rd.RecordDate BETWEEN ad.StartDate and ad.EndDate


I am not sure that the syntax is right for Progress database. You might need to change it in some places.

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[JamesFlowers]

I have mooted this point with our developers , but they want to do it in the CR as we have to pay for all alterations done on the db by the vendors



James Flowers
Crystal Consultant

 

[RTag]

You can avoid using view by using a subquery and a command .

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[lbass]

What formula did you use to create the period group? Please post it here.
Also, what is your selection formula?

-LB