summing on a maximum summary 2

[suburbanites]

Hi, I am using CRXI SP1

I have a running total that's a maximum summary of a variable that I need to sum/accumulate/create a runningtotal on.

How can I do this?

Thanks much for your input.

 

[Madawc]

I'm assuming you are already finding the maximum for a group, and want a sum of those values for the whole report.

Do a running total on the variable, accumulating using a formula that tests the value against the maximum.

If that doesn't work, you could probably do it with a variable that you accumulate for each group.

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

 

[suburbanites]

Hi Madawc,

Would you happen to have a sample formula?

 

[dgillz]

Place this is the group footer that you want to accumulate the total on:

WhilePrintingRecords;
Numbervar Myvar:=Myvar+Maximum({MyField},{MyGroup})

Then when you want to show the variable value:

WhilePrintingRecords;
Numbervar MyVar;


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

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[suburbanites]

Thanks so much to the both of you...That works..

 

[suburbanites]

Hi,

Now I have a new problem that I did not take into consideration..

My Maximum summary on a variable shows correctly if the amounts are positive but does not show when the amounts are negative. How do you display a maximum on a negative variable?

 

[lbass]

I think you should show the formulas you are currently using for this and also show some sample data that includes the negative amounts and indicate what the expected results would be from that set of sample data.

-LB

 

[dgillz]

maximum still works on negative numbers. The maximum of -100, -9000, -25 and -12,500 is -25. Is this not what you want?

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

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[suburbanites]

Thank you so much Lbass and Dgillz for responding. The max does work on either positive or negative. My problem actually was my one to many databases. For some reason it is not picking up the negative balances.

I have 3 databases : a master file and the actual and budget files. The master file has all the records. The actuals has the current and previous years' balances and the budget file has the budget and forecast balances.

Master file keys: Company, Accounting Unit, Account, SubAccount

Actuals key: Company, Fiscal Year, Accounting Unit, Account, SubAccount

Budget key: Company, Fiscal Year, Budget Number, Accounting Unit, Account and SubAccount.

I link the master to the actuals via the 4 keys, joined as inner, then I link the forecast to the budget via the 4 keys (no fiscal year or budget number) with right outer.

The report should show the balances for all of the previous years, all of the budget for the current year, all of the forecast for the current year and all actuals for the current year.

My parameters are as follows:

{master.chartname} = "CHART1"
and
actuals.fiscalyear} = {@Current Year} or
{actuals.fiscalyear} = ({@Current Year} - 1))
and
amster.company = {?Company} and
master.accountingunit = {?Accounting Unit})

It's picking up an inordinate number of data which affects performance and somehow it is not picking up the data from the budget file if the actuals is from the previous year.

I think my problem lies in the linking and the data selection but I have tried many ways of linking and nothing seems to work. That's why I thought about a subreport for the budget and forecast numbers but I hit a snag in totalling for the grouping in the (previous threads).

I am at a loss..please point me in the right direction..

 

[Madawc]

Try removing the year selection, and do your tests as formula fields, 'boolians' that will return True or False. Put them next to the data and 'tweak' till they work. Then restore to selection.

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

 

[suburbanites]

Hi Madawc, sorry, a bit dense this morning.. not sure what you meant by this post. Can you be more specific?

 

[Madawc]

Take each test from the selection and put it in a formula field. This will say True or False, if you do it without an IF and an answer.

Do a test version of the report in which you comment out the selection and get a look at how the tests are working.

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

 

[lbass]

Please copy your actual record selection formula as the one in your post contains errors. Also you mention linking the forecast to the budget, and there is no forecast table or mention of any forecast fields.

-LB

 

[suburbanites]

This is how my record selection looks like:

{master.chartname} = "CHART1"
and
{actuals.fiscalyear} = {@Current Year} or
{actuals.fiscalyear} = ({@Current Year} - 1))
and
(master.company = {?Company} and
master.accountingunit = {?Accounting Unit})

The forecast amounts are included in the budget file as budget number 3. Budget amounts are budget # 1.

The actuals and the previous years are in the actuals file with the corresponding years.

 

[lbass]

Sorry, that cannot be your actual formula. You have an extra paren. Your formula should look like:

{master.chartname} = "CHART1"
and
(
{actuals.fiscalyear} = {@Current Year} or
{actuals.fiscalyear} = {@Current Year} - 1
)
and
master.company = {?Company} and
master.accountingunit = {?Accounting Unit}

I was asking because it was unclear whether you were setting off the 'or' clauses properly.

How is the budget table linked? To the Master table? Why are you using a right join? I have never had occasion to use this. Are there cases where the budget or forecast are missing? Use a left outer instead then.

-LB

 

[suburbanites]

Yes, I was missing the parenthesis.
{master.chartname} = "CHART1"
and
({actuals.fiscalyear} = {@Current Year} or
{actuals.fiscalyear} = ({@Current Year} - 1))
and
(master.company = {?Company} and
master.accountingunit = {?Accounting Unit})



The budget table is linked to the actuals table using the company/accounting unit/account/subaccount fields.

I used the right outer because there might be cases where there are budget-forecest amounts and no corresponding actuals-previous year amounts.
The actuals-previous years file is a one to one relationship based on the keys company/fiscal year/accounting unit/account/subaccount.
The budget-forecast file is a many relationship as budget is company/fiscal year/budget number/accounting unit/account/subaccount. Budgets are number budget number 1 and forecasts are budget number 3.

 

[lbass]

I think you should link the budget table to the master table with a left outer from the master to the budget table.

-LB