Running Total of field - 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

Running Total of field

Author

Topic

nomaneagle
Starting Member

18 Posts

Posted - 2008-11-19 : 09:17:58

Im trying to get the running total of a group field but don't know how to do it in Sql Server Reporting Services. I have done it in Crystal Reports but as I am new in Sql Reporting Services, I don't know how to do it. The scenario is as follows:

    Part    QTY    Bin    QTYOH
1. Part1    2

                    01001    8
                    01002    4
                    01003    6
2. Part2    6
                    01001    2
                    01002    3
                    01003    9

Total: 8

Thats what Im trying to achieve. Total of QTY should be 8. I know that I can di it through my query but I want to do it from the report itself. Any Clue?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 09:20:39

just give =SUM(Field!QTY.value,"Your dataset name or group name here")

depending on whether you want total on table footer or group footer.

nomaneagle
Starting Member

18 Posts

Posted - 2008-11-19 : 09:24:19

Actually PART is my parent group and then BIN is the sub group of PART group. I want to get my total on the footer of PART group. But when I use SUM function then it gives me 24 as total and not 8. Any Clue? Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 10:39:29

quote:
Originally posted by nomaneagle

Actually PART is my parent group and then BIN is the sub group of PART group. I want to get my total on the footer of PART group. But when I use SUM function then it gives me 24 as total and not 8. Any Clue? Thanks

whats the scope you gave for sum()? PART group or BIN group?

nomaneagle
Starting Member

18 Posts

Posted - 2008-11-20 : 11:57:24

Actually I have tried both of them. I have done it in crystal Reports where they have a Reset option for the field on Change of group Event. But unfortunately we don't have this option in reporting Services.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 12:09:56

how is data appearing in dataset?

nomaneagle
Starting Member

18 Posts

Posted - 2008-11-26 : 10:30:19

Part1 2 01001 8
Part1 2 01002 4
Part1 2 01003 6
Part2 6 01001 2
Part2 6 01002 3
Part2 6 01003 9

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 12:14:55

try

=SUM(IIF(RowNumber("PartGroup")=1,Field!Qty.value,0)

nomaneagle
Starting Member

18 Posts

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

=IIF(RowNumber("table1_Group2")=1, Fields!qty.Value ,0)

This expression works fine and give me following values:

Part1 2
Part2 6

But when I am trying to take SUM of these values, systems gives me aggregate function error which is as follows:

=Sum(IIF(RowNumber("table1_Group2")=1, Fields!qty.Value ,0),"DataSet1")

The value expression for the textbox 'textbox17' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-28 : 11:07:27

then i think you need to do this from query behind or by using a custom code as follows

http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

nomaneagle
Starting Member

18 Posts

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

For the time being I have already done it by using my query. But sometimes additions in query make effect on the performance of the report. But thanks for your time and help anyways.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-28 : 11:22:32

quote:
Originally posted by nomaneagle

For the time being I have already done it by using my query. But sometimes additions in query make effect on the performance of the report. But thanks for your time and help anyways.

The link i gave is a substitute if you dont want to do it in query.

nomaneagle
Starting Member

18 Posts

Posted - 2008-11-28 : 16:08:26

This is working perfect for me now. Thanks for the help.

Expression=last(Code.MyFunc(Fields!displayline.Value, Fields!qty.Value),"vendorname")

And below is the code:

Dim DispLines As System.Collections.Hashtable
public total As Double
public totalsum As Double

Function MyFunc(ByVal DisplayLine As Object,ByVal QTY As Object) As Double
If (DispLines Is Nothing) Then
DispLines = New System.Collections.Hashtable
End If

If (DisplayLine Is Nothing) Then
MyFunc = total
Else

If (Not DispLines.Contains(DisplayLine)) Then
total = total + qty
DispLines.Add(DisplayLine,qty)
end if
MyFunc = total
end if
End Function

Function GetTotalQty()
for each de As System.Collections.DictionaryEntry In DispLines
totalsum = totalsum + de.Value
Next de
GetTotalQty=totalsum

End Function

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-29 : 00:24:08

Welcome

Glad that i could help you out

Dutchie75
Starting Member

22 Posts

Posted - 2008-12-05 : 12:50:55

I'm implementing this exact same solution into one of my reports (thanks to this thread). I've got the count working, but how can I get it to reset for each group? I can't find where to indicate the grouping, and so it's simply counting for the whole report. Thanks! And thanks for this solution, I've been working on this for two days, and you both have helped me so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 12:57:37

quote:
Originally posted by Dutchie75

I'm implementing this exact same solution into one of my reports (thanks to this thread). I've got the count working, but how can I get it to reset for each group? I can't find where to indicate the grouping, and so it's simply counting for the whole report. Thanks! And thanks for this solution, I've been working on this for two days, and you both have helped me so much.

can you explain it with some data? you want to reset count when? higher group?

Dutchie75
Starting Member

22 Posts

Posted - 2008-12-05 : 12:59:11

Correction. I'm not trying to sum a group field. I'm trying to sum individual rows within a group that I'm using the custom code on. But it's counting all rows for the whole report. I want a count per group. Hope this makes sense.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 13:02:45

quote:
Originally posted by Dutchie75

Correction. I'm not trying to sum a group field. I'm trying to sum individual rows within a group that I'm using the custom code on. But it's counting all rows for the whole report. I want a count per group. Hope this makes sense.

then give scope of count function as group name. then it will return you count for the group. something like

=COUNT(Field!YourField,.value,"group name here")

Dutchie75
Starting Member

22 Posts

Posted - 2008-12-05 : 13:14:26

I don't have a function to use. The code is in the detail line of the lowest group. Here's the line of code for detail:

=Code.MyFunc(Fields!Value_A.Value, Fields!Value_B.Value) - 1

I've added the -1 because otherwise the count starts on detail line 2??? It counts perfectly with the -1 added. So now, I want the count to restart for each group. Thanks.

I'm such a novice with slq rep. serv. I can do anything in Crystal, but having difficulty adapting to this new-to-me environment. Like for instance, what the heck happened to the report footer??????? I like sql though; just a learning curve.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 13:18:27

quote:
Originally posted by Dutchie75

I don't have a function to use. The code is in the detail line of the lowest group. Here's the line of code for detail:

=Code.MyFunc(Fields!Value_A.Value, Fields!Value_B.Value) - 1

I've added the -1 because otherwise the count starts on detail line 2??? It counts perfectly with the -1 added. So now, I want the count to restart for each group. Thanks.

I'm such a novice with slq rep. serv. I can do anything in Crystal, but having difficulty adapting to this new-to-me environment. Like for instance, what the heck happened to the report footer??????? I like sql though; just a learning curve.

you've report footer available in sql reoporting services. clicking on left top corner and you will get a menu,select report footer and it will show you footer.
why use custom code for count? you've standard function count() available. just use count with group name of your lowest group.

Dutchie75
Starting Member

22 Posts

Posted - 2008-12-05 : 14:39:11

I must have the cheap version of SRS because when click the report properties I only get the PAGE Header/Footer options. I guess I could just use the end of the body of the report as report footer but it's just not the same.

It's the SUM I'm looking for actually. I wish it were that easy. I can't use the SUM function because I have a one-to-many relationship to another table and that brings in duplicate records, therefore including the same records twice.

I'll keep at 'er.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources