Grouping and Custom Code

Hello everyone,

I've got an issue where I want to sum the group values and not the details, the reason is because I am hiding duplicate records. Here's how my Layout is setup.

TH

GH1 (hidden)

GH2 (hidden)

Det (hidden)

GF2 =Code.AddValue(Fields!Quantity.Value * Fieds!Cost.Value)

GF1 =Code.ShowAndResetSubTotal()

TF =Code.GrandTotal

I have the following in my Code window.

Dim Public SubTotal as Decimal

Dim Public GrandTotal as Decimal

Function ShowAndResetSubTotal() as Decimal

ShowAndResetSubTotal = SubTotal

SubTotal = 0

End Function

Function AddValue(newValue as decimal) as Decimal

SubTotal += newValue

GrandTotal += newValue

AddValue = newValue

End Function

This gives me incorrect results and I can't figure out why. Here's how it shows on my report:

Part Number Quantity Cost Regular Subtotal Method Using Custom Code Part 1 4,000 1.49 $5,947.20 Customer 1 $11,894.40 $0.00 Part 2 10 1.01 $10.07 Customer 2 $50.34 $5,947.20 Part 3 1 0.44 $0.44 Part 4 6,050 0.25 $1,530.41 Part 5 0 1.25 $0.00 Part 6 0 1.23 $0.00 Customer 3 $42,851.86 $10.07 Part 7 16,250 0.24 $3,922.59 Customer 4 $19,612.94 $1,530.85 Part 8 17,250 0.38 $6,544.82 Part 9 27,225 0.20 $5,380.20 Customer 5 $66,891.69 $3,922.59 Grand Total $141,301.23 $0.00

The issues brought up from the duplicates is shown in the "Regular Subtotal Method" column (there are 2 detail records for Customer 1-Part 1, which is why it is doubled). I can't use a distinct on the SQL query because there are other fields (not shown) on the report that are different.

As you can see, the GF1 (Customer #) shows the subtotal from the previous group, and the Table Footer (Grand Total) shows 0. Why is this?

Jarret

Hi Jarret,

The reason for seeing 0 (I think) Is that after a group ends, Reporting Services basically creates a new instance of your custom code and therefore any saved values get cleared.

I am not sure how your GF1 shows a value though... I could be wrong, but this is the experience I have had...

Regards,
Neil

|||The way I approached it...

I ordered my duplicate values... or some way of identifying that the value was not needed, and if the previous item = that item then do not add it to the total... Then for each footer call the same code and passing in the same values.

So each footer will be identical ... passing in the value and some other way of identifying if the value is unique...

Hope this helps...

Regards,
Neil