Post reply

Count Function

  • June 25, 2008 at 1:31 pm

    #192873

    I want to use count function on the field 'Status' - counting the records only if the value in the field is "C". I'm trying the following ways, but it just returns count of all the rows in status field.

    =Count(Fields!Status.Value = "C")

    OR

    =IIF(Fields!Status.Value = "C", Count(Fields!Status.Value), 0)

    could you please suggest?

    thanks

  • June 25, 2008 at 1:36 pm

    #834247

    Try

    =COUNT(IIF(Fields!Status.Value = "C", Fields!Status.Value, Nothing))

    [font="Comic Sans MS"]toolman[/font]
    [font="Arial Narrow"]Numbers 6:24-26[/font]

  • June 25, 2008 at 1:38 pm

    #834249

    singhs2 (6/25/2008)

    I want to use count function on the field 'Status' - counting the records only if the value in the field is "C". I'm trying the following ways, but it just returns count of all the rows in status field.

    =Count(Fields!Status.Value = "C")

    OR

    =IIF(Fields!Status.Value = "C", Count(Fields!Status.Value), 0)

    could you please suggest?

    thanks

    Try this:

    =Count(IIF(Field!Status.Value="C", 1, 0))

  • June 25, 2008 at 1:48 pm

    #834255

    =COUNT(IIF(Fields!Status.Value = "C", Fields!Status.Value, Nothing))

    worked for me, thanks

    Now I have another addition to this.

    How about if I need the two conditions-

    Fields!Status.Value = "C" AND Fields!Race.Value = "W"

    this statement is not working now!

    could you please suggest

    thanks a lot for all your help!

  • June 25, 2008 at 1:50 pm

    #834260

    Change your AND to OR. That should do it.

    [font="Comic Sans MS"]toolman[/font]
    [font="Arial Narrow"]Numbers 6:24-26[/font]

  • June 25, 2008 at 1:52 pm

    #834261

    In other words, how would I write this count in the footer of reporting services-

    select Count(*) From tableA Where status = 'C'

    and race = 'B'

  • June 25, 2008 at 2:03 pm

    #834270

    It did work! I think I didn't refresh the first time!

    =COUNT(IIF(Fields!Status.Value = "C" And Fields!Race.Value = "W", Fields!Status.Value, Nothing))

  • June 25, 2008 at 2:04 pm

    #834273

    Sorry. Didn't notice at first that you had different fields.

    Try

    =SUM(IIF(Fields!Status.Value = "C" AND Fields!Race = "W", 1, 0))

    I think that should work for you.

    Here's a couple good resources for expression writing:

    http://msdn2.microsoft.com/en-us/library/ms157328.aspx

    http://msdn2.microsoft.com/en-us/library/c157t28f(vs.71).aspx

    [font="Comic Sans MS"]toolman[/font]
    [font="Arial Narrow"]Numbers 6:24-26[/font]

  • August 26, 2011 at 11:37 am

    #1374008

    =Count(IIf(Fields!sutastat.Value = "LA",1,0))

    I only have 4 records with that status.

    This still returns all rows.

    When I try this:

    =Count(IIf(Fields!sutastat.Value = "LA", Fields!sutastat.Value,Nothing))

    I get zero

    All i'm trying to do is this

    select count(*) from employeecount where sutastat = 'la'

  • August 26, 2011 at 12:13 pm

    #1374024

    Try using SUM instead of COUNT.

    SUM(IIF(your field Value = "LA",1,0))

    This way it's summing ones or zeros instead of conditionally counting fields.

    [font="Comic Sans MS"]toolman[/font]
    [font="Arial Narrow"]Numbers 6:24-26[/font]

  • August 26, 2011 at 1:29 pm

    #1374074

    =SUM(IIF(Fields!sutastat.Value = "LA",1,0))

    Returned a zero

  • August 26, 2011 at 1:34 pm

    #1374078

    Are the values you're trying to count exactly = to LA? All upper case, no leading or trailing spaces that need to be trimmed. SSRS can be really picky about such things.

    [font="Comic Sans MS"]toolman[/font]
    [font="Arial Narrow"]Numbers 6:24-26[/font]

  • August 26, 2011 at 1:38 pm

    #1374079

    Thay may be the problem.

    I copied it from the sql table and it looks like this:

    'LA '

    so how would i trim that.

    I tried

    =sum(IIF(trim(Fields!sutastat.Value = "LA",1,0)))

    doesn't like it.

  • August 26, 2011 at 2:04 pm

    #1374097

    I got the results that i need

    =sum(IIf(trim(Fields!sutastat.Value) = "LA", Fields!totals.Value,0))

    Thanks

  • December 15, 2012 at 7:45 pm

    #1568916

    Thanks! I had the same problem

Viewing 15 posts - 1 through 14 (of 14 total)

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