SSRS Custom Sum - 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

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

SSRS Custom Sum

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2013-01-08 : 07:13:47

Hi,

Ive got a matrix,

1 2 3
1 0.00023 0.00000 0.00015
2 0.00589 0.00000 0.00306
3 0.00384 0.11011 0.00087

The values are based on calculations in the textbox, Now I wanted to do a sum at the bottom of each group but SSRS doesnt allow SUM(ReportItems.Textbox.value)

So I wrote a custom code below, but it seems like its suming everything in all groups, wheras i just want to sum each group and show value at the bottom.

Public Dim TotalAmount As Double = 0

Public Function CalculateRowTotal(ThatValue As Double) As Double
Dim LocalVar as Double = ThatValue

LocalVar = Round(ThatValue, 5)

IF LocalVar > 1 THEN
LocalVar = 0
ELSEIF LocalVar < 0 THEN
LocalVar = 0
END IF

TotalAmount = TotalAmount + (LocalVar)
Return LocalVar

End Function

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-08 : 23:54:44

for totals you can just click group in matrix and you've an optionto insert subtotal. just select it and total column will be placed automatically.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2013-01-09 : 04:28:16

Yea I tried that and it doesnt like doing aggregations, hence why im going after writing the custom code

Unless you could explain another way of doing it.

quote:
Originally posted by visakh16

for totals you can just click group in matrix and you've an optionto insert subtotal. just select it and total column will be placed automatically.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-09 : 04:32:17

quote:
Originally posted by cipriani1984

Yea I tried that and it doesnt like doing aggregations, hence why im going after writing the custom code

Unless you could explain another way of doing it.

quote:
Originally posted by visakh16

for totals you can just click group in matrix and you've an optionto insert subtotal. just select it and total column will be placed automatically.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

what do you mean by it does like doing aggregation? please illustrate with an example

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2013-01-09 : 05:49:18

So,

This is my matrix:

1 2 3
1 0.00023 0.00000 0.00015
2 0.00589 0.00000 0.00306
3 0.00384 0.11011 0.00087

in the cells, there is a calculation a log(dbfield1.value) * 100 - 20 / log(dbfield2.value) etc.

I added a row below to do sum(reportitems.value)

it complains aggregations can only be done in footer etc.

quote:
Originally posted by visakh16

quote:
Originally posted by cipriani1984

Yea I tried that and it doesnt like doing aggregations, hence why im going after writing the custom code

Unless you could explain another way of doing it.

quote:
Originally posted by visakh16

for totals you can just click group in matrix and you've an optionto insert subtotal. just select it and total column will be placed automatically.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

what do you mean by it does like doing aggregation? please illustrate with an example

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-09 : 05:58:18

yes that makes sense. whats meaning of doing sum over log calculation?

I think what you should be doing is an expression like this

=IIF(Inscope("Your Matrix Group"),log(Fields!dbfield1.value) * 100 - 20 / log(Fields!dbfield2.value),log(Sum(Fields!dbfield1.value,"dataset") * 100 - 20 / log(Sum(Fields!dbfield2.value,"dataset")

also see

http://visakhm.blogspot.in/2010/01/inscope-operator-in-sql-reporting.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2013-01-09 : 06:04:37

Yea the expression is just an example.

problem is with the value its not something that can be sum'd in an expression i need to sum all the values in the cells.

So what is the best way to sum all the report items, hence why i used the custom code, but the custom code is almost there in doing it where its sum'd up everything but i want the sum of each grouping instead of the sum of everything.

so it looks like below.

1 2 3
1 0.00023 0.00000 0.00015
2 0.00589 0.00000 0.00306
3 0.00384 0.11011 0.00087
Total 0.009034 0.11011 0.00397

quote:
Originally posted by visakh16

yes that makes sense. whats meaning of doing sum over log calculation?

I think what you should be doing is an expression like this

=IIF(Inscope("Your Matrix Group"),log(Fields!dbfield1.value) * 100 - 20 / log(Fields!dbfield2.value),log(Sum(Fields!dbfield1.value,"dataset") * 100 - 20 / log(Sum(Fields!dbfield2.value,"dataset")

also see

http://visakhm.blogspot.in/2010/01/inscope-operator-in-sql-reporting.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-09 : 06:27:02

read the posted link on Inscope operator. Thats what you should use to apply correct aggregate expression for the total rows

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2013-01-09 : 06:36:45

yea thats brilliant.

So i need to put the inscope in both my custom code and the expression?

quote:
Originally posted by visakh16

read the posted link on Inscope operator. Thats what you should use to apply correct aggregate expression for the total rows

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-09 : 06:37:25

not custom code. just your expression inside textbox

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2013-01-09 : 06:47:06

Its complained again :(

Aggregate functions can be used only on report items contained in page headers etc.

quote:
Originally posted by visakh16

not custom code. just your expression inside textbox

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-09 : 22:55:22

have you not applied any group over your data? without grouping it doesnt make sense to do an aggregation.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2013-01-10 : 04:20:41

Yea there is a grouping, i cannot not have a grouping in a matrix.

quote:
Originally posted by visakh16

have you not applied any group over your data? without grouping it doesnt make sense to do an aggregation.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 04:36:04

then whats the issue? the expression if used inside a grouping will obviously have aggregate function

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2013-01-10 : 04:54:15

Ok

So where should I put the inscope function?

The expression in the field is: Code.CalculateRowTotal(ReportItems!Textboxsum.Value)

the output column expression is: Code.TotalAmount

then the custom code in properties is:

Public Dim TotalAmount As Double = 0

Public Function CalculateRowTotal(ThatValue As Double) As Double
Dim LocalVar as Double = ThatValue

LocalVar = Round(ThatValue, 5)

IF LocalVar > 1 THEN
LocalVar = 0
ELSEIF LocalVar < 0 THEN
LocalVar = 0
END IF

TotalAmount = TotalAmount + (LocalVar)
Return LocalVar

End Function

quote:
Originally posted by visakh16

then whats the issue? the expression if used inside a grouping will obviously have aggregate function

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 05:06:15

you should be using Fields collection rather than ReportItems. then only you'll be able to apply aggregation.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2013-01-10 : 05:07:46

Could you show me an example or point me in the direction?

quote:
Originally posted by visakh16

you should be using Fields collection rather than ReportItems. then only you'll be able to apply aggregation.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 05:16:11

as told earlier the expresion will be like below

IIF(Inscope("YourMatrixGroup"),SUM(Fields!Yourfield.value),SUM(Fields!YourField.value,"dataset"))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2013-01-10 : 05:31:43

Ok,

as explained its not something that can be done in the sql query, or in the "total" box

i need to sum all the values that are displayed on the rows, its one of those mathematical formulas you need to supply all the values then sum it. Cannot do a sum of the overall as it will bring a different value.

I have a mathematical expression in the rows, i just need a way to sum the report items or sum the columns on there.

quote:
Originally posted by visakh16

as told earlier the expresion will be like below

IIF(Inscope("YourMatrixGroup"),SUM(Fields!Yourfield.value),SUM(Fields!YourField.value,"dataset"))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 05:38:39

ok...but still you need to apply formula over aggregate of values in total right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2013-01-10 : 07:07:57

Ok this is the exact same issue (incase i didnt explain properly)

But they fixed it for groups going along the side, i need it for groups going along the top of the matrix.

http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/5188dc38-52aa-4703-9793-54c8c5649938

quote:
Originally posted by visakh16

ok...but still you need to apply formula over aggregate of values in total right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources