Sub Total Expression problem

Question

Sub Total Expression problem

simon.lewis-796464

SSC Enthusiast

Points: 134
More actions
November 26, 2008 at 2:07 am

#201762

Hi
I need some help constructing/modify the below expression:
=IIf(Sum(Fields!totalmoney.Value)>105, (Sum(Fields!totalmoney.Value)-105)*0.128, 0) as a sub total in a table.
this currently gives the below result.
total money NI
bob 200 12.16
ted 104 0
subtotal 304 25.47
which is obviously incorrect.
Can anybody help me with an expression that will give the correct result ie 12.16.
thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

It appears that this formula is being used as a subtotal for the "NI" column. On the basis of that assumption, it appears you may have forgotten that the > 105 rule applies to each individual, and would NOT apply to the SUM of the totalmoney field, as while Bob's 200 qualified with 12.16 NI, Ted's 104 did NOT meet the criteria, and thus has 0 for NI. The only proper summary of NI is simply the SUM of that field. This also introduces the question of whether or not to report Ted's 104 in the summary for TotalMoney or not, as it produced 0 for NI. You can certainly include it, but it's important to know that the summary total may include totalmoney values that produced 0 NI.

Finally, the reason you can't use the SUM of totalmoney for a calculation of the subtotal of NI is because not ALL of totalmoney actually produces NI (whatever NI is ???).

Does that help?

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

simon.lewis-796464 SSC Enthusiast Points: 134 More actions · Answer 2

you are correct have you any idea of an expression that would sum only the result of the NI?

sgmunson SSC Guru Points: 110640 More actions · Answer 3

=SUM(Fields!NI)

That's assuming that NI is the name of the field in your dataset.

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110640 More actions · Answer 4

Somehow I get the feeling you don't have NI as a field in your dataset, and are instead calculating it in the report. Anyway, in that case, you might try:

=SUM(IIF(Fields!TotalMoney.Value>105,Fields!TotalMoney.Value*0.128,0))

Let me know which solution was applicable and if it worked. Thanks!

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

simon.lewis-796464 SSC Enthusiast Points: 134 More actions · Answer 5

unfortunately, totalmoney needs to be a Sum, as it is the totalmoney earned in a time period & SSRS will not allow me to have a Calculated dataset with a sum in it.

sgmunson SSC Guru Points: 110640 More actions · Answer 6

Ummm... I don't agree. You can calculate any sum you want in a T-SQL query that produces your dataset.

However, as I have no information from you on exactly what constitutes one record in your dataset, I have nothing to go on as to how to determine why you've come to the conclusion that you can't calculate the sum in your dataset. Thus I have to ask, where is the NI field coming from? Is it calculated within the report? How about the totalmoney value for each person? Is that also a sum? If they are both being calculated in your report, I would be looking to do that back in the query that produces your dataset. Something along the lines of:

SELECT SUM(money_amt_field) AS TotalMoney,

CASE

WHEN SUM(money_amt_field) > 105 THEN SUM(money_amt_field) * 0.128

ELSE 0

END AS NI

FROM yourtable

WHERE yourcondition

GROUP BY [Name]

ORDER BY whatever

You'd have to fill in for the various items such as yourtable, yourcondition, and whatever, and substitute the name of the field with the person's name in it for [Name]

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 7

Simon,

You're kinda new to the forum... take a look at the link in my signature below to get better, tested answers quicker...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

paul-924039 Old Hand Points: 338 More actions · Answer 8

Simon,

Have you tried putting the NI value as a calculated field using the same expression?

That way, NI comes through as a reliable value each time and can be subtotaled with a SUM(Fields!NI.Value) in a group or table footer.

Paul J

simon.lewis-796464 SSC Enthusiast Points: 134 More actions · Answer 9

thanks to all of you. you all pushed me in the right direction

thanks again