SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 SSRS Custom Sum
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cipriani1984
Constraint Violating Yak Guru

United Kingdom
304 Posts

Posted - 01/08/2013 :  07:13:47  Show Profile  Click to see cipriani1984's MSN Messenger address  Reply with Quote
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
52249 Posts

Posted - 01/08/2013 :  23:54:44  Show Profile  Reply with Quote
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/

Go to Top of Page

cipriani1984
Constraint Violating Yak Guru

United Kingdom
304 Posts

Posted - 01/09/2013 :  04:28:16  Show Profile  Click to see cipriani1984's MSN Messenger address  Reply with Quote
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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/09/2013 :  04:32:17  Show Profile  Reply with Quote
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/

Go to Top of Page

cipriani1984
Constraint Violating Yak Guru

United Kingdom
304 Posts

Posted - 01/09/2013 :  05:49:18  Show Profile  Click to see cipriani1984's MSN Messenger address  Reply with Quote
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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/09/2013 :  05:58:18  Show Profile  Reply with Quote
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/

Go to Top of Page

cipriani1984
Constraint Violating Yak Guru

United Kingdom
304 Posts

Posted - 01/09/2013 :  06:04:37  Show Profile  Click to see cipriani1984's MSN Messenger address  Reply with Quote
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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/09/2013 :  06:27:02  Show Profile  Reply with Quote
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/

Go to Top of Page

cipriani1984
Constraint Violating Yak Guru

United Kingdom
304 Posts

Posted - 01/09/2013 :  06:36:45  Show Profile  Click to see cipriani1984's MSN Messenger address  Reply with Quote
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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/09/2013 :  06:37:25  Show Profile  Reply with Quote
not custom code. just your expression inside textbox

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

Go to Top of Page

cipriani1984
Constraint Violating Yak Guru

United Kingdom
304 Posts

Posted - 01/09/2013 :  06:47:06  Show Profile  Click to see cipriani1984's MSN Messenger address  Reply with Quote
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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/09/2013 :  22:55:22  Show Profile  Reply with Quote
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/

Go to Top of Page

cipriani1984
Constraint Violating Yak Guru

United Kingdom
304 Posts

Posted - 01/10/2013 :  04:20:41  Show Profile  Click to see cipriani1984's MSN Messenger address  Reply with Quote
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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/10/2013 :  04:36:04  Show Profile  Reply with Quote
then whats the issue? the expression if used inside a grouping will obviously have aggregate function

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

Go to Top of Page

cipriani1984
Constraint Violating Yak Guru

United Kingdom
304 Posts

Posted - 01/10/2013 :  04:54:15  Show Profile  Click to see cipriani1984's MSN Messenger address  Reply with Quote
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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/10/2013 :  05:06:15  Show Profile  Reply with Quote
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/

Go to Top of Page

cipriani1984
Constraint Violating Yak Guru

United Kingdom
304 Posts

Posted - 01/10/2013 :  05:07:46  Show Profile  Click to see cipriani1984's MSN Messenger address  Reply with Quote
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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/10/2013 :  05:16:11  Show Profile  Reply with Quote
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/

Go to Top of Page

cipriani1984
Constraint Violating Yak Guru

United Kingdom
304 Posts

Posted - 01/10/2013 :  05:31:43  Show Profile  Click to see cipriani1984's MSN Messenger address  Reply with Quote
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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/10/2013 :  05:38:39  Show Profile  Reply with Quote
ok...but still you need to apply formula over aggregate of values in total right?



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

Go to Top of Page

cipriani1984
Constraint Violating Yak Guru

United Kingdom
304 Posts

Posted - 01/10/2013 :  07:07:57  Show Profile  Click to see cipriani1984's MSN Messenger address  Reply with Quote
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/



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000