CODEWITHSUNDEEP
sql-serverstored-proceduresreporting-services
JVGBI
JVGBI

Reputation: 575

ssrs if exists then else expression for default parameter value

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

Upvotes: 1

Views: 2258

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21738

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

Upvotes: 0

Related Questions