Archive

Posts Tagged ‘IIF function’

SSRS #40 – Use Table Filter to Conditionally Show Rows

March 30, 2011 Leave a comment

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.

image

The Expression for the table filter is this:

=IIF(IsNothing(Fields!DECISION_DT.Value),"HideRow","ShowRow")

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.

SSRS #37 – How to use IIF function to conditionally count unique values correctly‏

March 9, 2011 Leave a comment

This should be a simple task. But I struggled with it longer than I really should have.

Goal

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)

%d bloggers like this: