Financial Date Selection 1

[BigglesUK]

Hello folks, my first post so please be gentle! Ive seen the wealth of info you have to give, unforutnatly I couldnt seem to find anything I could relate to my problem.

I am trying to generate a summary report on an our order book, grouping figures into date ranges. While this would be great it it were done in normal months, my manager would like it in Financial dates.

Now, I thought of 2 ways of doing this. In our database the info is stored in a table called LedgerPeriod. Unfortuantly I cant think of any way to get it out, as its not a direct link you can create to my main table {SALESLINE.CONFIRMEDDLV}. Plus in that table they have a StartPeriod and EndPeriod Date colum.. I would use todays currentdate, and then try and work out which period it was in for "This Month", then this period + 1 for "next month" etc etc.

The other way i though if I couldnt do this is to hard code the financial period dates for this month. Ive gone about this as:

BeforeReadingRecords;
Global DateVar Array FinancialPeriod := [
Date (2006,01,01),
Date (2006,01,30),
Date (2006,02,27),
Date (2006,04,03),
Date (2006,05,01),
Date (2006,05,29),
Date (2006,07,03),
Date (2006,07,31),
Date (2006,08,28),
Date (2006,10,02),
Date (2006,10,30),
Date (2006,11,27),
Date (2006,12,31)
];

These are the Start dates for that period, the next period begins at the next date...

Now my problem, completely out of my depth on what to do next. This is the most complicated report ive created so far and im not sure where to put this code or how to use it to get my date groups.

Please can anyone give me suggestions. Im using Crystal Reports XI.

 

[Madawc]

Do a formula field to set the Finacial field: e.g.:

If {your.Date} > (2006,01,01) 
and {your.Date} < (2006,01,30) then 01
else

and so on. If you have a top group of year -- Year({your.Date}) -- and a lower group of financial period, that should do it. Or concatonate Year+Financial period/

You can group by almost anything that is based on just one record.

Also check Help to see if there is an existing command for Financial Period: I don't know of one but it might exist.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[BigglesUK]

Hi madawc, thanks for the reply. I know how to group select them like that, I just wanted to enter them into somewhere so I didnt have to hard code the dates every time. I want it to work out which is the first financial period. So for example the current date is the 19/01/2006, it would work out that the period began on the 01/01/2006 (from the array or table or wherever) and that it ends on the 29/01/2006 (the day before the next financial period).

 

[damonkimble]

As Madawc suggests, a formula rather than an array is the way to go. What is the criterea for your financial periods?

damon

 

[BigglesUK]

Hi, ive drawn up a spreadsheet to show what im trying to
produce.

Im trying to group the items by financial period. I would like to get the financial periods put somewhere (as i said they are in table in my db already) or hardcode them into one forumula..

Still struggling

 

[Madawc]

No one's very likely to write your code for you. You've been a workable method: why not use it?

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[BigglesUK]

Hi madawc, i wasnt suggesting that!

Ive got the finished report already by hard coding dates. What i want to do is make it dynamic, so that when i run it next month i dont have to change 10 formula's dates..!

I would like them to be picked up from a list of dates.. does anyone know how i can do this?

 

[Madawc]

I don't thing it's possible. No one else has suggested a way either. You can test dates against currentdate, which would stop future dates from being used. Or get a database table created and updated with the values, if that is permitted at your site.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[GJParker]

Create a financila calendar table on your db abd link this in to the report the calendar table can be as simple as

Date          Year      Period
01/01/2006    2006      1
02/01/2006    2006      1
..
..
..
29/01/2006    2006      1
30/01/2006    2006      2

do this for each day of the financial year and link this into your report

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

You can accomplish this with arrays as follows. Create a formula to be placed in your report header:

DateVar Array Start := [
Date (2004,01,01),
Date (2004,01,30),
Date (2004,02,27),
Date (2004,04,03),
Date (2004,05,01),
Date (2004,05,29),
Date (2004,07,03),
Date (2004,07,31),
Date (2004,08,28),
Date (2004,10,02),
Date (2004,10,30),
Date (2004,11,27),
Date (2004,12,31)
];
DateVar Array End := [
Date (2004,01,29),
Date (2004,02,26),
Date (2004,04,02),
Date (2004,04,30),
Date (2004,05,28),
Date (2004,07,02),
Date (2004,07,30),
Date (2004,08,27),
Date (2004,10,01),
Date (2004,10,29),
Date (2004,11,26),
Date (2004,12,30),
Date (2005,01,28)
];
redim preserve start[ubound(start)];
redim preserve end [ubound(end)];

numbervar i;
datevar array adjst;
datevar array adjend;
redim preserve adjst[ubound(start)];
redim preserve adjend[ubound(end)];

for i := 1 to ubound(start) do(
if currentdate in start to end then (
numbervar m := ubound(adjst)-i + 2;
numbervar n;
for n := 0 to ubound(start)-1 do(
if 1 + n < m then (
adjst[1 + n] := start[i + n];
adjend[1 + n] := end[i + n]))));

"Current Period: "+ totext(adjst[1],"MM/dd/yyyy")+" to "+ totext(adjend[1],"MM/dd/yyyy")

Add as many period start and end dates as necessary for your report to the arrays. The only update you will need to do to the report is to add these array elements in this one formula as necessary. The formula creates new arrays using the entered arrays where the period containing the currentdate becomes the new first period.

Note that your report does not show any groups by periods, but instead shows a manual crosstab. For the months columns, you would write formulas like this:

//{@currentmo}:
datevar array adjst;
datevar array adjend;
numbervar i;

if {table.date} in adjst[1] to adjend[1] then
{table.sales}

//{@nextmo}:
datevar array adjst;
datevar array adjend;

if {@order date}in adjst[2] to adjend[2] then
{table.sales}

//{@nextmo+1}:
if {@order date}in adjst[3] to adjend[3] then
{table.sales} //etc.

You can right click on these formulas and insert sums on them at the "customer name" group level and the "customer group" group level. You would then suppress the details.

To get a breakout for the current period based on period day, create a formula like the following for the detail section:

numbervar periodday;
datevar array adjst;
datevar array adjend;

if {@order date} in adjst[1] to adjend[1] then
periodday := datediff("d",adjst[1],{@order date})+1 else
periodday := 0;
periodday

Then create formulas for the particular ranges you want to summarize, e.g.,:

numbervar periodday;
if periodday in 16 to 22 then {table.sales}

Again, right click on this formula and insert a summary at the group levels where you want summaries.

-LB