Group total base on sub groups

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

Group total base on sub groups

Author

Topic

Tart_SQL
Starting Member

41 Posts

Posted - 2008-08-28 : 19:01:38

I have three groups in my report on a table, I get the sub total for each group but on the last (3rd) group I need the difference between sub totals for group 2 which consists of two distinct values (Income total and Expenditure total). The sub total textboxes are directly below each other, and I tried getting group 3 total by using the Inscope() but I get this error ‘The Value expression for the textbox ‘total5’ 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’ Is there a way I can get the difference between the two groups to be displayed in the textbox of the third group on the same table?

Tart_SQL
Starting Member

41 Posts

Posted - 2008-08-28 : 19:51:26

To explain further what I need done on the table is something like this

Income A 10
Income B 20
Income C 30
Total Income 60
Expenditure X 5
Expenditure Y 2
Expenditure Z 7
Total Expenditure 14
Net Income *46*

Here Income and expenditure are of the same group but I want to display the netcome underneath the group. Thanks.

Dance Doll
Yak Posting Veteran

54 Posts

Posted - 2008-08-29 : 09:31:51

Why not using Group Footer to display the netcome?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-29 : 10:31:03

try putting it in table footer then

Tart_SQL
Starting Member

41 Posts

Posted - 2008-08-29 : 12:30:37

quote:
Originally posted by visakh16

try putting it in table footer then

Problem is that the table footer or other group footer will try an error if it refers to a group outside its scope.

What I want is something like this.

Group 1 Income
Income A 10
Income B 20
Income C 30
Total Income 60
Expenditure X 5
Expenditure Y 2
Expenditure Z 7
Total Expenditure 14
Group 2 Net Income *46* (Group 1’s difference (Total Income – Total expenditure))

Dance Doll
Yak Posting Veteran

54 Posts

Posted - 2008-08-29 : 13:11:56

I don't see any reason to create Group 2 here.
--Create Group 1 with totals:
Total Income 60
Total Expenditure 14
And within the same group:
Caculate Net Income=46*(Sum(Total Income)-Sum(Total Expenditure))

You could make a new group footer line under current group footer and display Net Income over there.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources