How to count records of Dataset using SSRS report expression

user_9509662 0
Viewed 114

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

ssrs-2008 count reporting-services
1 Answers

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'.

user_17753890
Related