Round & display to nearest 1000 without using Variables

[TeamGD]

I need to redesign a report so that it does not use variables. The need for removing variables is because I must insert a chart, where the layout of the chart shows the numbers from a formula containing variables. Crystal Reports does not allow the chart layout to show formulas that contain variables. This is an assumption based on my understanding of charts & variables – if someone knows how to show variables in charts, then please let me know how, and the rest of this thread is irrelevant.

I use variables because I need to round and display numbers to the nearest 1000, in two different Group Footers. The round and display to the nearest 1000 is not required at the Detail level.

The database field that needs to be rounded and displayed to the nearest 1000 is called Budget. The two group fields are Division and Branch.

In Group Footer #2 I display the following formula to give me the first amount rounded to nearest 1000:

@BudgetBranch
whileprintingrecords;
numbervar BudgetBranch := round((Sum ({Budget}, {Branch}) / 1000),0);

To display the sum of this BudgetBranch in Group Footer #1 I use the technique of initialising, calculating and then displaying a variable. That is, in Group Header #1 I initialise a variable using the following formula:

@BudgetDivisionInit
whileprintingrecords;
numbervar BudgetDivision := 0

In Group Footer #2 I calculate the variable:

@BudgetDivisionCalc
whileprintingrecords;
numbervar BudgetBranch;
numbervar BudgetDivision := BudgetDivision + BudgetBranch

Finally in Group Footer #1 I display the variable to give me the second amount rounded to nearest 1000:

@BudgetDivisionDisp
whileprintingrecords;
numbervar BudgetDivision

All of this works fine. But now, in Group Footer #1 I want a Chart that shows the value of @BudgetDivisionDisp in the layout. However this formula field is not available as an option to show in the chart layout. I have assumed that it is not available because it contains a variable. So, my question is “how can I round & display amounts to nearest 1000 without using Variables?”

Note I do not perform the division by 1000 and rounding in a formula at the Detail level, and then just use the Sum function in Group Footer, because this does give incorrect rounding results at the Group Footer levels.

Thanking you in advance.

 

[dgillz]

round({AnyNumberField},-3)

Will display the results roundeed to the nearest 1000.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[TeamGD]

Thanks for the reply dgillz

Your response would improve my formula in @BudgetBranch. However it does not remove the need for variables in the formula @BudgetDivisionDisp – which is my aim.

 

[Reebo99]

I think the problem isn't because you are using variables, but the evaluation time.

WhilePrintingRecords is normally the last thing a report does, so you can't generally use the results of such formulas for anything other than display. Reebo
Scotland (Going mad in the mist!)

 

[TeamGD]

Thanks Reebo.

Yes, I need to concentrate on changing the evaluation time from whileprintingrecords, as opposed to removing the variables.

Having said that, I'm using the Sum function in the variable calculations. The only evaluation time once Sum is used is whileprintingrecords.

I'm going back to the drawing board, and rethinking the design of this report. In the meantime, should anyone have any further suggestions/comments ... please post them.

gmhm

 

[lyanch]

I am confused.. why are you using variables? You only need to create two formulas..


@BudgetBranch

round((Sum ({Budget}, {Branch}) / 1000),0)

@DivisionBranch

round((Sum ({Budget}, {division}) / 1000),0)

And use the formula for calculation, display and charting?

Lisa

 

[TeamGD]

Lisa

If I do not use variables, then Budget amount for each Division (@BudgetDivisionDisp) will be displayed incorrectly under certain conditions as per the following example.

Assume 3 records in the database:

Budget Branch Division
2896548 1 A
14676800 1 A
45499 2 A

@BudgetBranch, <round((Sum ({Budget}, {Branch}) / 1000),0)>, would correctly display

Branch Budget
1 17573
2 45

Branches 1 and 2 are both in Division A, so @BudgetDivisionDisp (using the variable calculations in my original thread request) would correctly display:

Division Budget
A 17618

If, I used <round((Sum ({Budget}, {Division}) / 1000),0)>, as you have suggested, instead of @BudgetDivisionDisp, the following would be incorrectly be displayed:

Division Budget
A 17619

Hope this clears up the confusion.

If you have any suggestions that may lead to me being able to show the Division’s Budget in a chart … please let me know.

gmhm