Calculating non-Db (held) data based on CR report date range

[brendanj68]

Hi,

(CR v9.0/ SQL 2000)

I need to (cumulatively) calcualte a set of monthly budget figures (which are not held on a Db or anywhere) based on a users date range selection, is this feasible or even practical? I am looking to return just the one value within the report. Thanks in advance

 

[lbass]

First you would use a date range parameter and for your record selection use: {table.date) = {?date}

Group on {table.date} by month.

Then you could hard code the budget figures using a formula like {@mobudg} to be placed in the group (month) header:

select month({table.date})
case 1 : 2000
case 2 : 1000
case 3 : 3500
case 4 : 1500
case 5 : 2500
default : 0;

...where the single digit numbers are month numbers and the four-digit numbers are budget figures.

Then create a formula {@accumbudg} to be placed in the group header:

whileprintingrecords;
numbervar accumbudg := accumbudg + {@mobudg};

Then create a final formula {@displaccumbudg} for the report footer:

Whileprintingrecords;
numbervar accumbudg;

Then suppress details and group headers and footers to display only the cumulative budget figure.

-LB

 

[brendanj68]

Works perfectly. Only needed to amend the case order as my Financial Year begins on month 4 (as in the following).

select month ({PLR____Support_All.Start_Date})
case 4 : 23000
case 5 : 9000
case 6 : 75000
case 7 : 62000
case 8 : 56000
case 9 : 182500
case 10 : 182500
case 11 : 182500
case 12 : 182500
case 1 : 182500
case 2 : 182500
case 3 : 180000
default : 0;

Thanks again

 

[brendanj68]

Hi LB,

Slight change of requirement, report needs to pull data in from previous financial year (without considering any period budget figures for that year). This has led to 'case 4', 'case 5', etc for projects in 2002 and 2003 both being accredited with the same budget figure. Tried amending
case 4/2003 : 23000
case 5/2003 : 9000
case 6/2003 : 75000 ..... as well as variations on the year suffix, but to no avail. Any way round this? Thanks once more.

B

 

[lbass]

You didn't explain how the previous year's figures were being used, but to limit the formula results to 2003, change the formula to:

if year({PLR____Support_All.Start_Date})=2003 then

select month ({PLR____Support_All.Start_Date})
case 4 : 23000
case 5 : 9000
case 6 : 75000
case 7 : 62000
case 8 : 56000
case 9 : 182500
case 10 : 182500
case 11 : 182500
case 12 : 182500
case 1 : 182500
case 2 : 182500
case 3 : 180000
default : 0;

Or you could use:

if year({PLR____Support_All.Start_Date}) = year(currentdate) then //etc.

If you need to accumulate budget figures for the previous year, copy the two formulas and change the formula and variable names. In the select/case formula, you could use:

year(currentdate)-1

-LB