reporting-servicescountexpressionssrs-expression

Count of specific decimal values in a column in SSRS Table


I am trying to get a sum of people of a specific age. AgeInYears is a Decimal.

I have tried a sum and get 0 every time

=SUM(IIF( Fields!AgeInYears.Value = 15, 1, 0 ) )

If I do a count I get 68 which is still incorrect

=Count(IIF( Fields!AgeInYears.Value = 15, 1, 0 ) )

I'm not sure if I am comparing to a decimal correctly. When I use my query in SQL, then use a pivot table on the data I'm able to find the answer as 47 (I'm much better with excel than SSRS). But in SSRS I can only get a 0 or 68 Value.

EDIT I am calculating the AgeInYears myself in my Query via

cast(cast(datediff(month, invl.ChildDateOfBirth, fp.enddate) as decimal) / 12 as decimal) as AgeInYears

could this be related?


Solution

  • I was unable to solve whatever issue that SSRS was giving me when I attempted to count the ages.

    Instead I have updated my query to

    Select
        cast(cast(datediff(month, invl.ChildDateOfBirth, fp.enddate) as decimal) / 12 as decimal) as AgeInYears
    From Table
    Where
        cast(cast(datediff(month, invl.ChildDateOfBirth, fp.enddate) as decimal) / 12 as decimal) > 13 
    and 
        cast(cast(datediff(month, invl.ChildDateOfBirth, fp.enddate) as decimal) / 12 as decimal) as AgeInYears < 19
    

    This ensures my data only contains ages that I want ( between 14-18. Then in the SSRS table I created a row group over AgeInYears and did a simple

    =Count(Fields!AgeInYears.Value)
    

    in the text box to get the correct count for each Age.