drill down on sum

[atarrqis]

CR 11, Oracle 11
I'm having trouble getting a Qty formula to show properly, including in a drill-down. I've tried various ways - Running Totals & formulas with WhilePrintingRecords/Shared NumberVar and I can get the Qty values correct when I just show each group footer but when I hide these and drill-down the Qty value is always zero.

I have 5 groups:
DETAILS
GF5 = OrderNo
GF4 = PartNo
GF3 = P
GF2 = D
GF1 = C

Sample Data:
CAT OrderNo PartNo P D C Hrs $ Qty
A 123 abc Y 1 e 4.5 0 1
A 123 abc Y 2 e 2.5 0 1
B 123 abc Y 1 e 0 100 1
C 123 abc Y null null null null 1
C 123 abc Y null null null null 1
A 456 abc Y 1 e 4.5 0 1
A 456 abc Y 2 e 3.0 0 1
B 456 abc Y 1 e 0 250 2
C 456 abc Y null null null null 3

I have a formula in the Details for QT: if {example\\.CAT}='C' THEN {example\\.Qty}

Results I want to see on drill down:
OrderNo PartNo P D C Hrs $ QT
GF5 123 abc 7.0 100 2
GF5 456 abc 7.5 250 3
GF4 abc 14.5 350 5
GF3 Y 14.5 350 5
GF2 1 9.0 350 5
GF2 2 5.5 0 5
GF1 e 14.5 350 5

 

[kenhamady]

Try this, place the formula on the detail band. Right click the formula and select "Insert>Summary" Make sure it is set to do a SUM and then click OK. This should add a grand total. Copy the grand total from the report footer and paste it into all 5 group footers. See if that behaves the way you want.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[atarrqis]

No, it just puts zero as the sum at every level. Again, when I just SHOW a lower level, the sum formula is correct.

 

[kenhamady]

I must not be following what you are doing. If a sum of any formula is zero, then the value of the formula has to be zero. Now, I am not sure what you mean by the "SUM FORMULA". There is only the formula that you are summing on the detail band and the summary of that formula that I had you create on the group footer(s). So if the "SUM FORMULA" shows a different value then it must not be the same formula that is used in the summary you created.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[atarrqis]

Sorry, I wasn't clear. Yes, I have only one formula as i defined and I sum it for the Grand Total. I then copied this summary to all the footer groups as you suggested. If I 'show' GF5, for example, the sum is correct. If I drill-down to GF5 the sum is zero. It is also zero at the top level and every level in between.

 

[kenhamady]

Create a version of the report with one group and hide the details. Put a copy of the subtotal in both the Group Header and the group footer. Double click on either subtotal to do a drill-down. The details should appear but the subtotals shouldn't change. Let me know what happens.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[atarrqis]

Yes, this works if I’ve deleted GF1 & GF2. I think it has something to do with the fact that we can have multiple values of ‘D’ for each OrderNo. This is what I show with OrderNo 123 – it can have only one PartNo and only one P but D can be either 1 or 2. We group on D at GF2. The sum of Qty for D is the same as it is for P (GF3) – in my example = 5. It is also the same as at C (GF1). Is there someway to take the value of the sum at GF3 and make it appear for GF2 and GF1?

 

[kenhamady]

Try adding a second group, copy the subtotal to the new group and hide the details and group 2 header/footer. Double-clicking on the G1 subtotal should take you to G2, and a double click on G2 subtotal should take you to the details. The only reason a subtotal would be zero is if all the details in that group sum to zero. If that works add your groups one at a time and the pattern should continue.

>> Is there someway to take the value of the sum at GF3 and make it appear for GF2 and GF1?
Which GF3? There can be more than one GF3 in each GF2 and multiple GF2s in each GF1.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[atarrqis]

it does the same thing, it works until I add the group for GF2. I ap0preciate your help but apparently there is no way to get this to work. Thanks.