Help! Simple report in CR7 with Complicated Dates!

[cheerfulskeptic]

Hi,
I wrote a simple report for a client to show sales per month for a specified year.
the report simply sums up each month and puts running totals for each month side by side.
Now, the complicated thing is that their manufacturing calendar is differnet, and we need to go by that, so for example,
Sales for Jan should be counted Jan 1 - 26
Feb: Jan 27 - Feb 28
Mar: Mar 1 - Mar 31
etc etc
(ie, nonstandard date ranges may apply).
what should I do ? should i create a database table with these date ranges stored, as in year, fld_janstart, fld_janend, fld_febstart, fld_febend, etc. ?
if so then how would i link this table to the other tables?
right now the fields I have in the report

tblAccounts.AccountID
Invoices.accountID
Invoices.invoicequantity (this is being summed).
invoices.invoicedate

Help!

 

[cheerfulskeptic]

The only solution i have to this is to hard-code the variables for start/end dates like JanS02 := 1/1/2003
JanE02 := 1/28/2003
etc
etc
for 2002, 2003 and 2004
But thats not practical I think because the client wants to be able to enter their own dates and change them around for the upcoming 2004, 2005, etc.

 

[nagornyi]

If they need to be able to change those limits, the limits should be parameters.
{?month_1_start}
{?month_2_start}
{?month_3_start}
etc.
then you can create the formula

if {Date}>={?month_1_start} and {Date}<{?month_2_start} then '01' else
if {Date}>={?month_2_start} and {Date}<{?month_3_start} then '02' else
...etc...
else '12'

Then you can create crosstab and put this formula to column. The crosstab will automatically segregate the data by the 'user defined' months.

 

[Ngolem]

All you need to do is Group by &quot;Specified Order&quot;

Create a group based on the date...then right click on the group in the grey margin on the left and select &quot;Change Group&quot; now make the group sort by &quot;Specified order&quot;...here you have formulas that allow you to create &quot;bins&quot; for the date ranges and then sort them in the appropriate order.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[lbass]

Another approach would be to create a master formula {@monthranges} as in:

whileprintingrecords;
stringvar Jan := if {table.date} in Date(2003, 01, 01) to Date(2003, 01, 26) then &quot;Jan&quot;;
stringvar Feb := if {table.date} in Date(2003, 01, 27) to Date(2003, 02, 28) then &quot;Feb&quot;; //etc.

You could either build in the date ranges for different years or change the dates in this formula each year.

Then for the running totals, use the three-formula method as in:

{@reset} to be placed in the months group header if you are grouping:
whileprintingrecords;
stringvar Jan := &quot;&quot;;
stringvar Feb := &quot;&quot;; //etc.
numbervar Janamt := 0;
numbervar Febamt := 0; //etc.

{@monthamts} for the details section:
evaluateafter({@monthranges});
whileprintingrecords;
stringvar Jan;
stringvar Feb;
numbervar Janamt;
numbervar Febamt;

if Jan = &quot;Jan&quot; then Janamt := Janamt + {table.Amount};
if Feb = &quot;Feb&quot; then Febamt := Febamt + {table.Amount};

Then create 12 formulas for display like:

{@Jan} to be placed in the group (or report) footer:
whileprintingrecords;
numbervar Janamt;

The only formula which needs to be updated yearly or expanded to include other years so that the correct month ranges are used in the report is {@monthranges}.

-LB

 

[cheerfulskeptic]

now that's what i also had in mind (the last option). this is a great idea, I think i'll go with that. thanks all!