Custom Group Sum Function a Group Behind

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

Custom Group Sum Function a Group Behind

Author

Topic

Dutchie75
Starting Member

22 Posts

Posted - 2009-01-03 : 09:05:09

I need to Sum a calculated field in my table footer. The calculated field acts as a Yes/No to answer whether or not a client has been admitted to a program. The problem is each group may have duplicates depending on it's link to another table, and so I wrote a custom Public Function to handle this. The function works EXCEPT that it displays the sum a group behind. For instance:

Group 1
Value1 CalculatedField
Client1 1
Client2 0
Client3 1
SumFooter 0

Group 2
Value1 CalculatedField
Client1 1
Client2 1
Client3 1
SumFooter 2

Group 3
Value1 CalculatedField
Client1 0
Client2 0
Client3 1
SumFooter 3

... and so on.

I've been at this for days now. I'm banging my head against the keyboard. Ugh..... Any ideas?

Here is my custom code:

Public SumAdmit As Integer

Public Function SumUp(ByVal Value As Integer)
SumAdmit = SumAdmit + Value
End Function

Public Function AdmitReset()
SumAdmit = 0
End Function

And here is where I call my code:

Group-ReferralSource (Group Header)

Client#1 Activity1 Yes/NoField = 1 '=Code.SumUp(YesNo.Value)
Activity2
Activity3

Client#2 Activity1 Yes/NoField = 1
Activity2

Total Yes/NoField '=Code.SumAdmit (Group Footer)
'=AdmitReset() (Group Footer Line 2)

Hopefully this makes sense to someone out there. My head is sore from banging it against my keyboard. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-03 : 09:10:20

i really think you can achieve this without using custome function. just put below expression in the group footer and see
=CountDistinct(IIF (Fields!YesNo.value=1,Fields!Value1.value,Nothing))

Dutchie75
Starting Member

22 Posts

Posted - 2009-01-03 : 09:59:36

OMG, it worked! You have no idea how excited I am! Talk about trying too hard. That's so easy. It wouldn't work if I actually had to sum different totals, but I'll cross that bridge another day. Hopefully, Reporting Services will think about adding DISTINCTSUM to their functions, but until then THIS problem has been solved. :) I'll celebrate by shutting this laptop down for the day and sharing drinks with friends. THANKS SO MUCH!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-03 : 10:06:04

quote:
Originally posted by Dutchie75

OMG, it worked! You have no idea how excited I am! Talk about trying too hard. That's so easy. It wouldn't work if I actually had to sum different totals, but I'll cross that bridge another day. Hopefully, Reporting Services will think about adding DISTINCTSUM to their functions, but until then THIS problem has been solved. :) I'll celebrate by shutting this laptop down for the day and sharing drinks with friends. THANKS SO MUCH!

You're welcome

even summing different totals can be achieved without using custom functions by doing a work around at your backend query. If you need assistance in it, please post your sample data with reqd output you want with an explanation of your reqmnt.

sachya45
Starting Member

25 Posts

Posted - 2009-05-01 : 10:15:43

I need to sum detail column in my table footer.The detail column has duplicate entries but i need sum of only distinct entries.
i want those duplicates to display in my Report but while calculating sum take distinct values only.
and then get the sum of each group values.I have tried runningvalue function and tried to hide duplicates and then get the sum of only displayed
values but its not working please help.....i have been trying this for long...

Thanks in advance....

my Report looks as follow:
Group1 header
group2 header
group3 header
Detail row
group 3 footer
group2 footer
group1 footer

example:
group1 001
group2 0989
group3 7890
Detail row 1722929.71
1722929.71
1722929.71
------------
group3 total 1722929.71

5276198.02
5276198.02
group3 total -----------
5276196.02

group2 -----------------
group2 total (1722929.71+ 5276198.02)

------------------------
(group1 total) total of all fields of group 2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources