Report Totals - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Report Totals

Author

Topic

werseyjersey
Starting Member

47 Posts

Posted - 2008-10-27 : 09:05:10

I am hoping I can explain what I am trying to do but if this isn't clear please let me know:

I have a report works great! On this report I pull all FTE's for a particular position (i.e. position/name of person in position/actual hours worked/budget hours for position, etc.). There are cases where there is NOT someone in a position and I have the report displaying (Open Position) for the persons name when no one is in the position. I want to include the budgeted FTE for this position in the "summary total" but it is not displayed on the report any place else nor do I wish it to be. I also want to include in that summary total the other positions within that category that have FTE budgets but have actual people in that position. Like I said I hope that makes sense. How would I go about getting the totals into one summary total?

What makes this more complicated is the fact that I have a group by total for the dept and a Group by for the Unit the grand total should be the total for the dept "+" the total for the Unit. For example:

Dept total = .15
Unit total = 5.60
Total Grand = 5.75

How can you get two group by totals to add together and come up with the total of 5.75?

This pulls the .15 ----- =iif(Fields!PersonIdNo.Value = 0, Sum(Fields!ftebudget_position.Value), 0) (which is grouped by GL_Dept)

This pulls the 5.60 ----- =Sum(Fields!ftebudget_employee.Value) (which is grouped by Unit)

How do I combine them into the total for 5.75?

Thanks!!

werseyjersey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 02:10:50

you can use scope parameter for that. something like below

=Sum(Fields!ftebudget_employee.Value,"UnitGroupNamehere")+iif(Fields!PersonIdNo.Value = 0, Sum(Fields!ftebudget_position.Value,"GL_DeptGroupnamehere"), 0)

werseyjersey
Starting Member

47 Posts

Posted - 2008-10-28 : 09:22:25

I am now getting this error. Is it because I have it in the group for the Unit?

[rsInvalidAggregateScope] The Value expression for the textbox ‘textbox36’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

=Sum(Fields!ftebudget_employee.Value)+iif(Fields!PersonIdNo.Value = 0, Sum(Fields!ftebudget_position.Value,"Fields!GL_Dept.Value"), 0)

Does this go in a footer?

werseyjersey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 10:10:24

quote:
Originally posted by werseyjersey

I am now getting this error. Is it because I have it in the group for the Unit?

[rsInvalidAggregateScope] The Value expression for the textbox ‘textbox36’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

=Sum(Fields!ftebudget_employee.Value)+iif(Fields!PersonIdNo.Value = 0, Sum(Fields!ftebudget_position.Value,"Fields!GL_Dept.Value"), 0)

Does this go in a footer?

werseyjersey

you've to give group name (got from grouping properties window) not field name. also make sure you're using this in same contianer in which you've defined group.

werseyjersey
Starting Member

47 Posts

Posted - 2008-10-28 : 10:18:05

Same container? What do you mean? I have two groups one for Unit one for GL_Dept where does this solution belong?

I have this in the total at the bottom of the report where the group for the Unit is.
=Sum(Fields!ftebudget_employee.Value) + iif(Fields!PersonIdNo.Value = 0, Sum(Fields!ftebudget_position.Value,"list1_Details_Group"), 0)

How does it know to use the second group? The list1_Details_Group only has Unit in the listing do I need something else to tell it about the second grouping?

werseyjersey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 10:48:32

quote:
Originally posted by werseyjersey

Same container? What do you mean? I have two groups one for Unit one for GL_Dept where does this solution belong?

I have this in the total at the bottom of the report where the group for the Unit is.
=Sum(Fields!ftebudget_employee.Value) + iif(Fields!PersonIdNo.Value = 0, Sum(Fields!ftebudget_position.Value,"list1_Details_Group"), 0)

How does it know to use the second group? The list1_Details_Group only has Unit in the listing do I need something else to tell it about the second grouping?

werseyjersey

didnt understand what you're talking about. SO you want total of both groupings there?

werseyjersey
Starting Member

47 Posts

Posted - 2008-10-28 : 10:57:41

I want the total of the two together in one fields so that using my example the total would be 5.75 which the total of the
open positions (.15) (GL_Dept group) and if I put this in the group for GL_dept that is what I get .15)
occupied positions (5.60) (Unit group) and if I put this in the group for Unit that is what I get 5.60)

which totals to 5.75 this is the total I wish to display in the report for the "grand total".

Does that help? Sorry I didn't explain it correctly I guess!

werseyjersey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 11:04:27

did you try this?

=Sum(Fields!ftebudget_employee.Value,"GL_Deptgroupname") + iif(Fields!PersonIdNo.Value = 0, Sum(Fields!ftebudget_position.Value,"list1_Details_Group"), 0)

werseyjersey
Starting Member

47 Posts

Posted - 2008-10-28 : 11:16:47

Ok let's try this again I tried this:

=Sum(Fields!ftebudget_employee.Value) + iif(Fields!PersonIdNo.Value = 0, Sum(Fields!ftebudget_position.Value,"list1_Details_Group"), 0) because this is on the group "Unit" and I get 5.60 it doesn't add in the .15!

I have one group in my list1_details_group = Unit
Within in my table properties under group list, I have:
table1_Unit
table1_GLDept
table1_ClientTitle

Do I need to add something? What am I missing?

I can do screen prints if need be if you have somewhere I can send them. I need to get this done by noon today!

werseyjersey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 11:43:57

put both the grouping infos. unit & GLDept for sum. something like

=Sum(Fields!ftebudget_employee.Value,"table1_GLDept") + Sum(iif(Fields!PersonIdNo.Value = 0, Fields!ftebudget_position.Value, 0),"table1_Unit")

werseyjersey
Starting Member

47 Posts

Posted - 2008-10-28 : 11:46:22

Did that now I am getting this error again:
[rsInvalidAggregateScope] The Value expression for the textbox ‘textbox36’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

werseyjersey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 11:47:14

does that mean you're putting in a container other than table1?

werseyjersey
Starting Member

47 Posts

Posted - 2008-10-28 : 12:01:51

Not trying to be dumb -- just new to this. So I have a list1 and I have a table called table1 I am putting it in the "total" at the bottom of the table1 for what I am calling "grand total". Does that make sense?

werseyjersey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 12:07:21

is table inside list or vice versa? or are they independent?

werseyjersey
Starting Member

47 Posts

Posted - 2008-10-28 : 12:09:11

The table is inside the list.

werseyjersey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 12:11:53

and you're putting experession on table footer?

werseyjersey
Starting Member

47 Posts

Posted - 2008-10-28 : 12:13:12

no I put it in the group for Unit. Does it go with the table footer? I moved it to the footer and I still get the error.

werseyjersey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 12:15:16

quote:
Originally posted by werseyjersey

no I put it in the group for Unit. Does it go with the table footer?

werseyjersey

how can i know

. where have you put the group? in table or list?

werseyjersey
Starting Member

47 Posts

Posted - 2008-10-28 : 12:17:34

I know you don't know. Sorry just trying to understand what you are asking so I answer correctly. I have ONE group in the list which is the Unit.
Then within the table I have:
Within in my table properties under group list, I have:
table1_Unit
table1_GLDept
table1_ClientTitle

Sorry just not sure about this stuff.

werseyjersey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 12:20:06

put the expression on TABLE FOOTER and try

werseyjersey
Starting Member

47 Posts

Posted - 2008-10-28 : 12:23:12

Did it but now I get this:

=Sum(Fields!ftebudget_employee.Value,"table1_Unit") + Sum(iif(Fields!PersonIdNo.Value = 0, Fields!ftebudget_position.Value, 0),"table1_GLDept")

[rsInvalidAggregateScope] The Value expression for the textbox ‘textbox364’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.
[rsInvalidAggregateScope] The Value expression for the textbox ‘textbox364’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

I also tried the following:
=Sum(Fields!ftebudget_employee.Value,"list1_Details_Group") + Sum(iif(Fields!PersonIdNo.Value = 0, Fields!ftebudget_position.Value, 0),"table1_GLDept")

and I only get one error then:
[rsInvalidAggregateScope] The Value expression for the textbox ‘textbox364’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

Is it because I don't have the other group in the list?

werseyjersey

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources