SUM Issue

Last Post 12 Feb 2006 03:43 PM by dba123. 12 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
dba123
New Member
New Member

--
10 Feb 2006 04:53 AM
I'm trying to create a Grand Total in my footer for a column in my table. My table has a header, one group, and a footer.

In my group, the field I want to sum has this formula in it which works fine for the group level but not in the footer:

=((((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

I want to now create a total for that column. When I try pasting in that, it is way overinflated for the result. In the past, I have figured out that i had to tweak the placement of the SUM when working with expressions in the footer...sometimes the same expression in the group could be pasted in teh footer minus the SUMs and it would work.

Right now, this SUM will nto work in the footer. I have tried so many combinations. Coupled with the fact that SSRS 2005 will not allow me to just do a SUM(fieldname from group fieldname) from the footer...I've tried to qualify it but it's out of scope, I'm stuck.
ChabotWVU
New Member
New Member

--
10 Feb 2006 08:55 AM
Okay, see if this will work for you.

-Create a textbox control in the group and name the it txtSum, this will hold the information that you want to display in the footer, also hide the control, hidden property = true

-Create a textbox control in the footer and add the expression =ReportItems!txtSum.Value, this control will display the data from the txtSum control in your group
dba123
New Member
New Member

--
10 Feb 2006 09:05 AM
Yea, I know I've tried that. I even tried dragging just a textbox outside my table then setting it to ReportItems!fieldnameingroup.value and it still said out of scope...
dba123
New Member
New Member

--
10 Feb 2006 09:06 AM
I mean when you say create a textbox control "in the group" what do you m ean by that. The table control is what I'm using and it already has a textbox in my Group1
dba123
New Member
New Member

--
10 Feb 2006 09:07 AM
AHHHH! I see what you're saying...this is just like I used to do in Crystal...hold on
dba123
New Member
New Member

--
10 Feb 2006 09:09 AM
Ok, so for the hidden textbox, do I put the expression of the group detail field that already exists in my table or do I put a refrence to it ReportItems!mygroupdetailfield.valu ?
dba123
New Member
New Member

--
10 Feb 2006 09:15 AM
ok, didn't work, here's what I did

1) my current data is in my group1 in my table object
2) I dragged a textbox control from my toolbox right over an existing open field in my table's group...weird though dragging a texbox over basically a textbox but I did as you said
3) In the new texbox I just dragged into my group at the end of my report, I put this expression, which is the same that is contained in the group field that I want to sum in my footer (I named it txt_SumProjFee) :

=((((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

4) Dragged another textbox over my footer field in my table and in it put this formula in it to reference that hidden field:

=SUM(ReportItems!txt_SumProjFee.Value)

5) Previewed and got this error, same one I've been getting


[rsAggregateReportItemInBody] The Value expression for the textbox 'textbox5' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.
dba123
New Member
New Member

--
10 Feb 2006 09:16 AM
really the actual error is:

[rsReportItemReference] The Value expression for the textbox ‘textbox5’ refers to the report item ‘txt_SumProjFee’. Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.

which is also in my compiler
dba123
New Member
New Member

--
10 Feb 2006 12:44 PM
any more thoughts out there???
ChabotWVU
New Member
New Member

--
11 Feb 2006 07:59 AM
I was saying perform the sum inside your group and then just grab that value using
ReportItems!txtbox.value in your footer
dba123
New Member
New Member

--
12 Feb 2006 03:43 PM
If you read my post, that is what I did and it doesn't work!!! you cannot do that. you cannot reference a field like that in your footer in SSRS 2005. Are you using 2005 SQL Server Reporting Services, a table approach? I am doing this in a table. If you try to reference ReportItems that reside in your group from a footer, it tells you plainly that you can't because it is out of scope, that you can only add header and footer item references to another footer field. This has been my whole problem, why is this a limitation and is there a work around???
dba123
New Member
New Member

--
12 Feb 2006 03:44 PM
It still stumps me why nobody understand this and that nobody still has not come up to me and said "HEY I GET THIS ALSO AND HAVE TRIED AND GOT THE SAME ERROR". I cannot be the only one who has come across this limitation problem!
dba123
New Member
New Member

--
12 Feb 2006 04:18 PM
Please check my thread out here to help:

http://www.eggheadcafe.com/forums/F...46&INTID=9


Acceptable Use Policy
---