Search code examples
reporting-servicesssrs-2012

SSRS Time expression filter not working with Parameter boolean value

Background:

Trying to achieve a filter in dataset, if the user selects Day shift from the parameter, any data entry between 06:00:00 and 18:00:00 is filter in the report. Furthermore, any data entry between 18:00:00 and 06:00:00 is reflect in the report for Night shift

Process:

I have got a parameter with 2 Boolean values "True" and "False". I have labeled those values as "Day shift" and "Night shift" in the available value Parameter window option. Default value is true.

Converting datetime to time, in the dataset query

SELECT
CONVERT(time, SWITCHOFFSET(CONVERT(datetimeoffset, LastModified),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as ShiftTime
FROM table abc

Filter expression:=Fields!ShiftTime.Value

Operator: in

Value: =IIf( ( TimeValue(Fields!ShiftTime.Value) >= "06:00:00" And TimeValue(Fields!ShiftTime.Value) <= "18:00:00" ) , Parameters!ShiftType.Value(0), Parameters!ShiftType.Value(1) )

Problem: [BC30516] Overload resolution failed because no accessible 'IIf' accepts this number of arguments

Not sure which part I am going wrong with, I am thinking it is the datatype but unsure.

Solution which I built on

Solution

  • I'm not sure what report designer you are using, but using the Report Designer extension for Visual Studio gives me very different errors.

    And it gives errors both in the output window, and the expression window when building it.

    Firstly to debug this, add it as a column to your report before you try and implement it as a filter.

    Then check the relevant documentation for the function. It turns out the TimeValue function takes a string, not a DateTime, and returns a datetime.

    Then, you can't access the available parameter values from the parameter, you can only access the selected parameter values. So instead of Parameters!ShiftType.Value(0) (which has a red line under it) you should just have true (or false depending on which way around your logic is) i.e. the value of the parameter.

    Finally, I don't know what implicit conversion SSRS does from strings to a time, so I would do an explicit time compare instead of a string compare. Given we know that TimeValue returns a DateTime with the minimum date value and the time component we can compute the required limites for day/night shift.

    The following is what worked for me:

    =IIf(TimeValue(Fields!ShiftTime.Value.ToString()) >= DateTime.MinValue.AddHours(6) And TimeValue(Fields!ShiftTime.Value.ToString()) <= DateTime.MinValue.AddHours(18), true, false)

    And actually re-visiting it, thats way too convoluted, you are already returning a time value which we can compare directly as

    =IIf(Fields!ShiftTime.Value >= new TimeSpan(6,0,0) And Fields!ShiftTime.Value <= new TimeSpan(18,0,0), true, false)

    And then your filter should be:

    Filter expression: {as shown above}

    Operator: =

    Value: =Parameters!ShiftType.Value

    Note: when you say "between" I don't know whether the intention is to include both 6am and 6pm within the day shift window, but I haven't modified your logic in that regard.