none
Sum expressions/totals help! RRS feed

  • Question

  • Please help/advise. I am writing a report using MS Report Builder. The SQL database the company uses stores various data for individuals.

    For each record/individual on the database, 'events' can be logged and these are assigned certain categories, for example, ‘traded’ or ‘non-traded’. Simply put, I want to be able to see what individuals/records have both ‘traded’ and ‘non-traded’ events logged in their event history.

    I currently have two calculated fields in my report (and therefore two extra columns): one which displays a score of 1 if an event is logged as ‘traded’ and another for when an event is logged as ‘non-traded’. For each individual there is a sum at the bottom to display how many events are logged for that category (the events are grouped by record/individual, this all works).

    I have then created an expression in a third column which effectively says: if (sum(traded)>0 and sum(non-traded)>0, 1, 0) basically, if an individual has events of both category, put 1 otherwise 0. This also works and a correct score of 1/0 is returned based on the sum of event type (simplified/redacted example for an individual below).

    Funding Description

    Traded Events

    Non-traded Events

     

     

     

     

     

    Non-traded

    0

    1

     

    Traded

    1

    0

     

    Traded

    1

    0

     

    Traded

    1

    0

     

    Traded

    1

    0

     

    Traded

    1

    0

     

    Totals

    5

    1

    1

    What isn’t working is the summing this final column. I basically want to be able to sum all of those 1s to get a total of individuals who have 1s logged against them because they have involvements of both category type. For some reason the only score to return in my expression is 1. The expression is as follows: 

    =Sum(IIf(Sum(Fields!Traded_Score.Value) > 0 And Sum(Fields!Non_Traded_Score.Value) > 0, 1, 0))

    Can someone advise on this please?

    Friday, November 6, 2020 12:04 PM