如何编写SSRS参数默认值表达式：存在当前年则用，否则用上年

Hey there, let's sort out this default value for your SSRS Year parameter. The goal is to use the current year first, but switch to the prior year if there's no data for the current year—here's a solid way to make that happen.

Step 1: Create a Dataset to Check Existing Years #

First, you need a dataset that returns all years that actually have data in your source. Let's call this dataset ds_ExistingYears. Use a query like this (adjust the table and date column names to match your data):

SELECT DISTINCT YEAR(YourDateColumnName) AS ExistingYear
FROM YourSourceTable
ORDER BY ExistingYear DESC

This gives you a list of every year with valid data, which we'll use to check if the current year is available.

Step 2: Write the Default Value Expression #

Go to your Year parameter's properties, head to the Default Values tab, select "Specify values", then add this expression as the value:

=IIF(IsNothing(Lookup(Year(Now()), "ExistingYear", "ExistingYear", "ds_ExistingYears")), Year(Now()) - 1, Year(Now()))

Breakdown of the Expression #

Let's break this down so you know what each part does:

• Year(Now()) grabs the current calendar year (e.g., 2024 right now)
• Lookup searches our ds_ExistingYears dataset for a match with the current year. If it finds a match, it returns the year; if not, it returns Nothing
• IsNothing(...) checks if the Lookup came up empty (meaning no data for the current year)
• If the check is true (no current year data), we use Year(Now()) - 1 (the previous year). If false, we stick with the current year

Quick Notes to Avoid Issues #

• If your dataset returns years as strings instead of integers, convert the current year to a string in the Lookup: CStr(Year(Now()))
• Double-check your dataset query to make sure it's pulling all years with data—if you have filters on the dataset, they might exclude years you need to check
• Test this by temporarily setting your system clock to early January (or modifying the dataset to exclude the current year) to confirm the fallback works as expected

内容的提问来源于stack exchange，提问作者JVGBI