| Author |
 Topic  |
|
|
cipriani1984
Constraint Violating Yak Guru
United Kingdom
300 Posts |
 Posted - 01/08/2013 : 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
India
48012 Posts |
Posted - 01/08/2013 : 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
United Kingdom
300 Posts |
Posted - 01/09/2013 : 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
India
48012 Posts |
Posted - 01/09/2013 : 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
United Kingdom
300 Posts |
Posted - 01/09/2013 : 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
India
48012 Posts |
Posted - 01/09/2013 : 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
United Kingdom
300 Posts |
Posted - 01/09/2013 : 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
India
48012 Posts |
Posted - 01/09/2013 : 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
United Kingdom
300 Posts |
Posted - 01/09/2013 : 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
India
48012 Posts |
Posted - 01/09/2013 : 06:37:25
|
not custom code. just your expression inside textbox
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
cipriani1984
Constraint Violating Yak Guru
United Kingdom
300 Posts |
Posted - 01/09/2013 : 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
India
48012 Posts |
Posted - 01/09/2013 : 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
United Kingdom
300 Posts |
Posted - 01/10/2013 : 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
India
48012 Posts |
Posted - 01/10/2013 : 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
United Kingdom
300 Posts |
Posted - 01/10/2013 : 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
India
48012 Posts |
Posted - 01/10/2013 : 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
United Kingdom
300 Posts |
Posted - 01/10/2013 : 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
India
48012 Posts |
Posted - 01/10/2013 : 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
United Kingdom
300 Posts |
Posted - 01/10/2013 : 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
India
48012 Posts |
Posted - 01/10/2013 : 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
United Kingdom
300 Posts |
|
| |
Topic |
|
SSRS Custom Sum
