I recently had a need to show three different view in a employee productivity report with the same data set, two summary views and one account level detail view.
For the two summary views, employees are listed regardless they have data or not in the date range. This will allow the report to do “forced ranking. I’ll blog more about “forced ranking” later. Because I had to show all the FTEs in different grouping, I had to create some dummy rows. I’ve written a post, SSRS – Drill down or Drill through 2? discussing the approach.
Now if I use the same data set to show the data in account level details, there is no need for forced ranking any more. Now I want to hide those dummy rows.
This is a simple task in SSRS. There is no need for a new data set. Create a new table in SSRS, using the same data set, with a filter.
The Expression for the table filter is this:
Note: IsNothing is VB’s equivalent to IsNull in T-SQL.
With the above filter, only rows that have a DECISION_DT will be shown in the account level detail view, and all the dummy rows will not.
This should be a simple task. But I struggled with it longer than I really should have.
Count unique number of FTEs only when the flag FTE is 1.
It turned out that all I need to do is to put the IIF function inside the COUNTDISITNCT function.
CountDistinct(IIF(Fields!FTE.Value = 1, Fields!UW_NM.Value,Nothing))
But I started in the wrong direction. I put the CountDistinct function inside the IIF function.
IIF(Fields!FTE.Value = 1, CountDistinct(Fields!UW_NM.Value), Nothing)