SSRS 2005 - Sum if a particular field falls into specific criteria

Question

SSRS 2005 - Sum if a particular field falls into specific criteria

matt_scott1984

SSC-Addicted

Points: 469
More actions
September 14, 2011 at 4:12 am

#243692

Hi all,
I am very new to reporting services.
I have a report like the one below with 6 columns:
Name Bills Disbs WIP Exposure Client
Smith 5,000 1,000 1,000 7,000 500
Jones 10,000 5,000 5,000 20,000 1,500
Roberts 12,000 6,000 3,000 21,000 750
Beckham 15,000 5,000 20,000 40,000 1,000
Exp >25k 15,000 5,000 20,000 40,000 1,000
Exp <25k>10k 22,000 11,000 8,000 41,000 2,250
Exp <10k 5,000 1,000 1,000 7,000 500
What i am trying to acheive is the several subtotals.
Basically i need to total each column based on the Exposure value. For example, if the Exposure value is less than 10,000 i want to only total each column if the Exposure meets this criteria. Likewise, if the Exposure value is over 25,000 i want a seperate subtotal for these entries.
As an example, I have tried using the following expression but it does not work:
=Iif(Fields!Exposure.Value<10000, SUM(Fields!Disbs.Value),0)
I would be grateful for any help on this.
Thanks in advanced.

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

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

One way of doing this is to add new columns to your underlying selection query, corresponding to your conditions.

The value of the column will be zero unless the condition is true, in which case it should hold the value you wish to sum (use a CASE construction to do this).

Then you can sum these columns in your report.

No doubt there is a more elegant way of achieving this in RS - I'll leave that for someone who knows it.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 2

Phil Parkin (9/14/2011)
One way of doing this is to add new columns to your underlying selection query, corresponding to your conditions.
The value of the column will be zero unless the condition is true, in which case it should hold the value you wish to sum (use a CASE construction to do this).
Then you can sum these columns in your report.
No doubt there is a more elegant way of achieving this in RS - I'll leave that for someone who knows it.

This is my preferred method

sdvoranchik SSCrazy Points: 2800 More actions · Answer 3

You can also try changing the expression around:

=SUM(IIF(Fields!Exposure.Value<10000, Fields!Disbs.Value,0))

Does this work for you?

matt_scott1984 SSC-Addicted Points: 469 More actions · Answer 4

Thanks for the reply but this did not work. It returned 'Error'.

I have found a solution elsewhere in case your are interested:

=Sum(iif(Fields!Exposure.Value < 10000, CDbl(Fields!Disbs.Value), CDbl(0)))

I have no idea what 'CDbl' means but it appears that is vital in making the expression work.

I need the expression to work where the Exposure amount is >= 10000 but <= 25000.

I have tried:

=Sum(iif(Fields!Exposure.Value >= 10000 AND <= 25000, CDbl(Fields!Disbs.Value), CDbl(0)))

Any ideas why this is not working?

Thanks

sdvoranchik SSCrazy Points: 2800 More actions · Answer 5

CDBL is converting your field to a Double data type. If you only need whole numbers and no decimals you could use CINT instead which would just convert to Integer

sdvoranchik SSCrazy Points: 2800 More actions · Answer 6

For you second expression, in your comparison you need to state the field name for comparison in both parts so:

=Sum(iif(Fields!Exposure.Value >= 10000 AND Fields!Exposure.Value <= 25000, CDbl(Fields!Disbs.Value), CDbl(0)))

Phil Parkin SSC Guru Points: 246985 More actions · Answer 7

You may need to 'nest' your iif's:

Level 1: iif(condition, true, false)

Level 2: iif(condition, true, iif(condition2, true2, false2))

etc etc

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

matt_scott1984 SSC-Addicted Points: 469 More actions · Answer 8

matt_scott1984

SSC-Addicted

Points: 469

September 15, 2011 at 11:38 am

#1382183

Thanks again.

The expression worked fine.

KoldCoffee SSCoach Points: 18971 More actions · Answer 9

I'm in a similar situation though in SSRS 2008.

I am interested in knowing how to write an expression that says;

iif the row_description is A then return Count(item_id)

Iif the row_description is B then return AVG(price)

iif the row_description is C then return Count of C's item_ids /iif row_description is A then Count of A's item_ids.

In other words, I need a top count to be based on a different condition than the bottom count.

what approach and functions to pursue?