Expression Problem - GrandTotal

In my SSRS 2005 report I have a column in my table that I cannot get a SUM in my table footer (I'm not referring to report footer!!) due to the way it is calculated. Here's the low down:

I will focus just on the columns in my table unique to this problem. Remember, I'm using a Table Control in SSRS 2005, not just a Body section!!!

In my table I have:

Commission % ProjGross ProjFee

.25 1000 ProjGross * Commission %

.50 2000 ProjGross * Commission %

.7 3000 ProjGross * Commission %

...

ProjFee = ProjGross x Comission %

the values above are in a Group. The column's group values render fine and are correct using this formula in my ProjFee's group textbox:

=(Round((((SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!NewCCs_Check.Value)) / Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day")) * (Sum(Fields!TotalPostingDays.Value, "TotalPostingDays") - Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day"))) +

(SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!OldPDs_Check.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!OldCCs_Check.Value) + SUM(Fields!NewCCs_Check.Value)))) * (Fields!FeeSchedule.Value / 100)

So, now for the problem. I cannot just create an expression in my table footer like the one this below because it would only take the first commission %25 and wouldn't represent a true some of the column ProjFee. YOu can't total up Commission % or you'd get a wacky % somthing like 500%!

=ProjGross * Commsion %

I don't want to do this totaling in SQL first either...and can't; there's a lot more behind this calculation which is why and the SQL would be horrendous. I'm not quite sure how I'm gonna handle the Commission % in my table footer Total for this column.

Since I cannot just reference the Group column's name in my footer and do a SUM on that field (it's out of scope) I don't know what else to try

check this out:

http://www.photopizzaz.biz/footersum.jpg

http://www.photopizzaz.biz/footersum2.jpg

http://www.photopizzaz.biz/footersum3.jpg

All I want to do is get my Footer GT to work. The formulas I have in both the group field and footer field for ProjGross are as follows:

Group:

=(Round((((SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!NewCCs_Check.Value)) / Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day")) * (Sum(Fields!TotalPostingDays.Value, "TotalPostingDays") - Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day"))) +

(SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!OldPDs_Check.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!OldCCs_Check.Value) + SUM(Fields!NewCCs_Check.Value)))) * (Fields!FeeSchedule.Value / 100)

Footer (right under group)

=sum((((Fields!PostedAmount_InHouse.Value + Fields!NewPDs_Check.Value + Fields!NewCCs_Check.Value) / Fields!CurrentPostingDay.Value)

* (Fields!TotalPostingDays.Value - Fields!CurrentPostingDay.Value)

+ (Fields!PostedAmount_InHouse.Value + Fields!OldPDs_Check.Value + Fields!NewPDs_Check.Value + Fields!OldCCs_Check.Value + Fields!NewCCs_Check.Value))

* (Fields!FeeSchedule.Value)) / 100

I’ve played around with moving the SUM in the Footer field since that works sometimes but no luck so far. If I could only refrence the Group field and just do SUM(groupfieldname) but that’s something I have asked and nobody seems to know why you can’t do that in a SSRS 2005 table when you’re trying to do that in a Footer field to sum a Group field by referencing the Group field’s label name. SSRS 2005 gives you an error when you try doing that saying that you must stay in scope when referencing names and you can’t reference a Group name forma footer textbox in a table. That would sure make my life easier than trying to fiddle around with the SUM I have in my footer textbox to try to get it to work

|||am I just not explaining myself or is SSRS 2005 ending up to be just like Crystal where you have to figure out quirky ways to get things done? The fact of the matter is, I can't simply do a sum on a column by referencing a group's textbox name from the table footer expression. If you are dealing with very complex expressions and you m ust only use an expression and cannot reference a group field directly from the footer, you are left with what I have tried to do above...which is a nightmare because my GT is not coming out and I don't know if it can because of the Commission % (Feesched) being a % and you can's sum a % in the footer like this for an overall GT