Summarizing Across Groups

[jakecolman]

Crystal Report XI with Oracle 9.

I am summarizing data by date and currency. The user now wants the sum to be cumulative for a currency across dates. In other words, in the currency footer I display the sum for that currency for that date. But when I start the sum for that same currency for the next date, the sum should be cumulative across the dates. So the new sum starts with the value of the old sum. Makes sense?

How can I do this?!

Thanks!

...Jake

 

[IdoMillet]

You can use variables (or a single array variable) to achieve this. However, it wouldbe useful to get a better sense of what the information display should look like (and why).

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[jakecolman]

Ido,

OK. First I'll explain what I'm trying to do then I'll explain the various issues I am having.

I have data records with the following fields:

Payment Date, Currency, Funding Type, Amount.

In addition to these fields are some other indicative data.

The value of Funding Type tells me whether Amount is an asset amount or a liability. The report is grouped by Payment Date and Currency and has the following columns for each detail record:

Several columns of indidative data
Asset Amount - the sum of Amount from asset funding type records
Liability Amount - the sum of Amount from liability funding type records
Balance Amount - the diffence between the two prior columns.

The subtotals are displayed for each break in date/currency. For the next date/currenct, the beginning value for each Amount column should be the ending balance for that currency.

I am using the following formula for the Asset Amount column:

whileprintingrecords;
numberVar AssetBal ;
if {Command.PRODUCT CLASS} = "ASSET" then
AssetBal := AssetBal + {Command.AMOUNT}
else
AssetBal := AssetBal

I use a similar formula for the Liability Amount column.

I cannot do a simply summary of the formula since I get an error about 'non-recurring value'. How can I implemnt all these requirements?

Thanks for your help!

...Jake

 

[lbass]

I think you keep posting new threads on the same topic. I don't think you need to use variables at all here. If you create formulas like this:

//{@asset}:
if {Command.PRODUCT CLASS} = "ASSET" then {Command.AMOUNT}

...you can place this in the detail section->right click on the formula->insert summary. You can insert summary at different group levels as needed. Create a similar formula for liabilities. Then you can create a formula to subtract one from the other:

sum({@asset},{table.currency})-sum({@liability},{table.currency})

Change the group condition to calculate the difference at the date level, e.g., if your date group is monthly, it would look like:

sum({@asset},{table.date},"Monthly")-sum({@liability},{table.date},"Monthly")

If you really need to show the values as they accumulate, then you can insert running totals, e.g, where you choose sum of {Command.amount}, evaluate based on a formula:

{table.type} = {Command.Asset}

Reset on change of group or never if you want the result at the grand total level.

Your variable is a running total. If you want to do this manually with a variable, then you would add a separate variable for each group level you want to report a result for, with a separate reset formula for each group level. An inserted running total is just simpler. You would create separate ones per the level of result. You can also use them in formulas to subtract one from the other.

-LB

 

[jakecolman]

Thanks, LB, but I'm having trouble understanding this. The values for 'Asset Amount' and 'Liability Amount' must be printed on the detail line. Lets say I have 10 records for a given date/currency group with Amount values for each. Half those records represents assets and half represent liabilities. As I process each detail record, I must display the current sum of assets and liabilities as affected by the amount on the current detail record. Each detail record contributes either to the Asset Amount or the Liability Amount column. So if the detail record's Amount is an Asset, the Asset Amount column will increase but the Liability Amount column for that detail record will display the same value as the prior record. Am I explaining this ok?

Summarization of the formula, as you suggested, can only be done in a footer, not at the detail level. So this brings be back to my problem. My formula will work for each detail record. But how do I summarize at the group level and how do I make a running total for each currency that will carry across date breaks?

Am I totally missing the boat on how to approach this in Crystal?

Thanks!

...Jake

 

[lbass]

If you set up two running totals, one for assets and one for liabilities, as I suggested towards the end of my last post, you can place them in the detail section and you will see them accumulate detail by detail. You can set up multiple running totals depending upon the level in which you are evaluating them. Try them first with no resets. Then you will see that they continue to accumulate. If you create a second set of running totals which have resets on change of the date group, you will have the subtotals you want for the date group when you place them in the date group footer.

-LB

 

[jakecolman]

I'm probably being an idiot or just obtuse.

How can you place a running total in the detail section? Crystal does not allow it!

I appreciate your help and I hope you indulge me a bit more. I currently have everything working except for the ability to accumulate a total for the same currency across dates. I can total each column for a specific currency and, since I reset the total in the currency group header, it calculates new totals for the each currency within the date. What I can't figure out is how to rememebr each currency's result when I hit that same currency for the next date.

Let's assume I'm working with the Asset Amount column. I use the following formula to display the Asset Amount for each detail record:

//@Asset Balance
whileprintingrecords;
numberVar AssetBal;
numberVar FuncAssetBal;
if {Command.PRODUCT CLASS} = "ASSET" then
AssetBal := AssetBal + {Command.AMOUNT};
else
AssetBal := AssetBal;

To print the total for a currency, I have the following formula in the Currency footer:

//@Subtotal Asset Balance
whileprintingrecords;
numbervar AssetBal;
AssetBal;

I don't do any toatlling in the Payment Date footer since there is no point in totalling across currencies.

Am I going about this all wrong?

How can I remember the total for each currency so that when I break into a new Payment Date group the total for that the currency picks up where to previous total left off?

Thanks again for your help and patience.

...Jake

 

[lbass]

Of course you can place a running total in the detail section. Your variable is a form of running total, though manual.

Please provide some sample data. I think we are running around in circles here. Show data in your group sections and detail section, indicating what you would like to see. Also show your reset formula and where you have put it.

-LB

 

[jakecolman]

OK. I am definitely an idiot. The light bublb just clicked on. I now see that I can create a running total object off of using a formula that looks like what you suggested:

//{@asset}:
if {Command.PRODUCT CLASS} = "ASSET" then {Command.AMOUNT}

This gives me a proper running total without the variables. Perfect! I'm sorry it took me this long to sort out.

OK. Now how do I solve my last problem? I can reste the RT on a change of currency within a date abd that will give me proper RTs for each currency within the date. But when I switch to a new date, I want the RT to start with the value that it left off with the last time it saw that currency.

For example, the RT for USD on 5-Jan-2006 is 25. When I process a USD detail record for 6-Jan-2006 with an amount of 11, the RT should display 36.

Is this possible?

...Jake

 

[lbass]

Insert another running total without the reset on the date group. I still don't see any sample data.

-LB

 

[jakecolman]

Sample Data:
name,date,ccy,class,amount

mazda,1/1/01,USD,ASSET,10
ford,1/1/01,USD,ASSET,10
toyota,1/1/01,USD,LIABILITY,25
mazda,1/1/01,GBP,ASSET,10
ford,1/1/01,GBP,ASSET,10
toyota,1/1/01,GBP,LIABILITY,25
mazda,1/1/02,USD,ASSET,10
ford,1/1/02,USD,ASSET,10
toyota,1/1/02,USD,LIABILITY,25
mazda,1/1/02,GBP,ASSET,10
ford,1/1/02,GBP,ASSET,10
toyota,1/1/02,GBP,LIABILITY,25

Does this help?

I implemented everything you suggested and the RTs are working great. You suggested inserting another running total without the reset on date group. Does this mean adding another column to the report for that RT? If so, that won't make my user happy. He wants one column each for asset and liabiloty amounts, with those amounts reset on a currency break but the last amunt for that ccy picked yp when I do that same ccy on the next date.

 

[lbass]

Is the display you are showing based on a Group #1 on date and a group #2 on currency? This makes all the difference. It also sounds like you don't really want a reset on the asset and liability amounts. I'm guessing below what summaries you want displayed, e.g. (I'm changing the data so it's not all the same):

GH1: 1/1/01 Is this what you want for the RTs?
GH2: USD #Asset {#Liability}
D: 10 A 10
10 A 20
25 L 25
GH2: GBP
D: 5 A 5
10 L 10
50 A 55
GH1: 1/1/02
GH2: USD
D: 10 A 30
20 L 45
40 L 85
GH2: GBP
D: 25 L 110
15 A 70
25 A 95

If this is what you meant, then you need four separate RTs an asset and a liability rt for each currency. The RTs would be set up as a sum of {table.amt}, evaluate using a formula, e.g.,:

{table.class} = "Asset" and
{table.currency} = "USD} //for {#AssetUSD}

Reset never. Create the three other rts, only changing the evaluation formula. Then use field suppression to format each running total, e.g., right click on {#AssetUSD}->format field->common->suppress->x+2 and enter:

{table.currency} <> "USD"

Repeat for the other rts. Then position the two asset rts on top of each other, and the two liability rts on top of each other.

If I'm still wrong about what you are looking for, please use my example and edit it to match what you do want, and show it in the thread.

-LB

 

[jakecolman]

LB,

I am definitely going to owe you a bottle of beer when we're all done!

Your example is almost correct. The groups are correct, date and then currency. Here is what it should look like:

[tt]
GH1: 1/1/01
GH2: USD #Asset {#Liability}
D: 10 A 10 0
10 A 20 0
25 L 20 25
GH2: GBP
D: 5 A 5 0
10 L 5 10
50 A 55 10
GH1: 1/1/02
GH2: USD
D: 10 A 30
20 L 30 45
40 L 30 85
GH2: GBP
D: 25 L 55 10
15 A 70 10
25 A 95 10
[/tt]

I don't think this change in my desired output materially affects what you have suggested.

Our system supports appx 30 currencies. In any given report, some subset of those 30 currencies will appear. Would I have to use 30*2 sets of RT variables and hard-code the test for each possible currency?

 

[lbass]

GH1: 1/1/01
GH2: USD #Asset {#Liability}
D: 10 A 10 0
10 A 20 0
25 L 20 25
GH2: GBP
D: 5 A 5 0
10 L 5 10
50 A 55 10
GH1: 1/1/02
GH2: USD
D: 10 A 30
20 L 30 45
40 L 30 85
GH2: GBP
D: 25 L 55 35
15 A 70 35
25 A 95 35

Note the change in the liability column at the bottom. I think you would have to approach this as you say, with all of those RTs, unless you are willing to change the overall design, and have the currency group be the outer group, with the date the inner group. Then you could have two rts resetting on change of the currency group only, with no special evaluation formula.

-LB

 

[jakecolman]

The report has to be done in date order and then by currency within date. This way they can see their exposure on a day by day basis.

I guess I see alot of RTs in my future!

Is there any way to copy/paste an RT object so that I can minimize the grunt work?

Thanks again for all your help!

...Jake

 

[lbass]

Sorry, not that I'm aware of.

-LB

 

[jakecolman]

I started implementing your suggestion. Its a real PITA, since I have so many currencies, but it looks like it will work.

If the first record populates the Liability column, the Asset column is blank, not 0. Any idea why that is? Or how I can force it to show a zero?

 

[lbass]

You could create a formula:

if isnull({#yourrt}) then 0 else {#yourrt}

-LB