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

  • matt_scott1984

    SSC-Addicted

    Points: 469

    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.

  • Phil Parkin

    SSC Guru

    Points: 243261

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    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: 2780

    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

    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: 2780

    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: 2780

    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: 243261

    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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • matt_scott1984

    SSC-Addicted

    Points: 469

    Thanks again.

    The expression worked fine.

  • KoldCoffee

    SSCoach

    Points: 18971

    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?

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

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