CODEWITHSUNDEEP
reporting-servicescountssrs-2008
thomsan
thomsan

Reputation: 483

How to count records of Dataset using SSRS report expression

I need to get count of the records of the dataset, where its AsssistTypeId = 1.

My Dateset is something like this,

+---------+--------+---------------+
| LobbyID | ProdID | AsssistTypeId |
+---------+--------+---------------+
| 285316  | 160    | 1             |
+---------+--------+---------------+
| 285317  | 161    | 2             |
+---------+--------+---------------+
| 285318  | 159    | 1             |
+---------+--------+---------------+
| 285319  | 160    | 1             |
+---------+--------+---------------+
| 285331  | 160    | 2             |
+---------+--------+---------------+
| 285332  | 160    | 1             |
+---------+--------+---------------+
| 285333  | 161    | 2             |
+---------+--------+---------------+
| 285334  | 160    | 1             |
+---------+--------+---------------+
| 285335  | 160    | 1             |
+---------+--------+---------------+
| 285335  | 161    | 1             |
+---------+--------+---------------+
| 285335  | 163    | 1             |
+---------+--------+---------------+

Currently I'm get count of the lobbyId distinct value as follows and it's working as expected.according to the above dataset, output for the following expression returns 9

=iif(inscope("matrix1_RowGroup3"), 
IIF(Count(Fields!LobbyID.Value) = 0, "", Count(Fields!LobbyID.Value)),
iif(inscope("matrix1_AssistedBy"),IIF(CountDistinct(Fields!LobbyID.Value) = 0, "", CountDistinct(Fields!LobbyID.Value)),
CountDistinct(Fields!LobbyID.Value)))

Now I need to get count of the LobbyId where its AsssistTypeId = 1. For the above dataset, I'm expecting AsssistTypeId = 1 count as 6 ,How can I write expression for this?

Updated: 12/8/2020

This is what I tried, but it doesn't return any result.

=
iif
(

    inscope("matrix1_RowGroup3"),   
        IIF
        (
            Fields!AsssistTypeId.Value = 1,
            IIF
            (
                Count(Fields!LobbyID.Value) = 0, "", Count(Fields!LobbyID.Value)            
            ),
            Nothing         
        ),
        IIF
        (
            inscope("matrix1_AssistedBy"),
            
            IIF
            (
                Fields!AsssistTypeId.Value = 1,
                IIF(CountDistinct(Fields!LobbyID.Value) = 0, "", CountDistinct(Fields!LobbyID.Value)),
                Nothing         
            ),
            IIF
            (
                Fields!AsssistTypeId.Value = 1,
                CountDistinct(Fields!LobbyID.Value),
                Nothing         
            )
        )
)

And I also tried this,

=iif(inscope("matrix1_RowGroup3"), 
IIF(Count(Fields!LobbyID.Value) = 0, "", Count(Fields!LobbyID.Value)),
iif(inscope("matrix1_AssistedBy"),IIF(SUM(IIF(Fields!AsssistTypeId.Value = 1,1,0)) = 0, "", SUM(IIF(Fields!AsssistTypeId.Value = 1,1,0))),
SUM(IIF(Fields!AsssistTypeId.Value = 1,1,0))))

This works, but when there have duplicates LobbyId, Its also count that Duplicates, According to the above data set, output shows as, 8 , But its should be 6

Upvotes: 2

Views: 356

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

This is untested but you could try...

=CountDistinct
    (
      IIF (
           Fields!AssistTypeId.Value = 1
           , Fields!LobbyID.Value
           , Nothing
          ) 
    , "matrix1_RowGroup3"
    )

As CountDistinct ignores null (nothing) values, we replace anything that is not the value you want to count with Nothing then do a distinct count of LobbyID from the remaining values.

You may have to change or remove the "matrix1_RowGroup3" scope, I just guessed what it might be from you existing expression. I also assumed AsssistTypeID was a typo and removed an 's'.

Upvotes: 2

Related Questions