Summarizing Group Forumlas 2

[oanion]

I have the following sample database table
[ul]
[li]Client string[/li]
[li]Placeddate date[/li]
[li]Placedbalance currency[/li]
[/ul]


I have a report have set up a report that is supposed to look like the following:

Client January February Totals
BOA 1000 2000 3000
Citibank 500 1000 1500
Total 1500 3000 4500


*****The report will show all 12 months.******

I cannot seem to get the correct totals for each client name for a particular month and the row and column totals. I took the following steps to begin:

1. Group by the clientname
2. I created a formula for each month (column). The formula for January is:

*************************************
if GroupName ({table.client}) = {table.client} AND
month(placeddate} = 1.0
then
sum({table.PlacedBalance},{table.client});

***************************************

• This formula will total balances for all months for one particular client. It totally ignores the portion of the formula which specifies the month.
3. I’m not sure how to get the row and column totals since Crystal will not allow you to summarize a sum. I thought I could do a running total, by specifying to evaluate for a formula, however, it was unsuccessful.

I would be very grateful for some assistance.

Sincerely


Vasheti

 

[rosemaryl]

Dear Vaseheti,

Since you are grouping on client, you do not need to check the client as in the detail the data is evaluated for each row for that client.

A formula for each month should be created and placed in the detail section and then you would sum those.

//Example January Formula
If month({table.PlacedBalance})= 01
then {table.PlacedBalance}
else 0

Now, in the detail section click on these new formulas for each month and click sum, insert summary for Group and Grand Totals (8.5) or select for group and copy to report footer (cr10).

You will now have the data summarized by client, for each month.

You can suppress the detail section and the group header section, move the group name to the group footer and have the report display exactly as you show in your example, with the addition of a grand total row.

Regards,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[lbass]

Rosemary showed you how to complete your manual crosstab, but you could also just insert a crosstab, add {table.date} as the column field, {table.client} as the row, and {table.PlacedBalance} as the summary field. In the crosstab screen, you would then highlight the column field->group options and choose "Print on change of month". In preview, you could then format the column heading to display as the monthname.

-LB

 

[oanion]

Thanks a million for your assistance. Both solutions worked like a charm!!!!!!!!!!!