ssrs if exists then else expression for default parameter value

Question

I need to write an ssrs expression that checks if a parameter exists, if not it should pick another value.

I have a Year parameter in my SSRS report. The year parameter should use current year (=Year(Now())) as a default value. The problem is, january just started and we don't have data yet for 2018, so it will return an error. How do I write a default value expression that takes the current year if it exists, else the previous year?

Thanks in advance guys

>>>we don't have data yet for 2018<<< Where there is no data, in some your table? — sepupic

Alan Schofield Alan Schofield · Accepted Answer · 2018-01-24T10:40:22

It's not clear what you want to be set as the default if not data is available for the current year.

This solution tests if data exists and then give you the most recent if it doesn't. It's easy to return anything you like using this approach.

I've just created a table variable for testing against, obviously swap this out for your real table.

-- uncomment @myParameter declaration for testing
--DECLARE @myParameter int = year(getdate())

-- Just some data to test against.
DECLARE @myData TABLE (PeriodID int, SomeValue int)
    INSERT INTO @myData VALUES (2015, 8), (2016, 10), (2017, 15)

IF Exists(SELECT * FROM @myData WHERE PeriodID = @myParameter)
    BEGIN
       SELECT @myParameter as resultPeriodID
    END
ELSE
    BEGIN
       SELECT MAX(PeriodID) as resultPeriodID FROM @myData
    END

ssrs if exists then else expression for default parameter value

1 Answers