Sub Total Expression problem

Question

Post reply

Sub Total Expression problem

simon.lewis-796464

SSC Enthusiast

Points: 104
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: 110588 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)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)

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

simon.lewis-796464

SSC Enthusiast

Points: 104

December 1, 2008 at 9:20 am

#905702

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

sgmunson SSC Guru Points: 110588 More actions · Answer 3

sgmunson

SSC Guru

Points: 110588

December 1, 2008 at 9:35 am

#905723

=SUM(Fields!NI)

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

Steve

(aka smunson)

:):):)

Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110588 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)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)

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

simon.lewis-796464

SSC Enthusiast

Points: 104

December 1, 2008 at 9:46 am

#905731

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: 110588 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)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1000539 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)
Intro to Tally Tables and Functions