SSRS报表条件化默认起始日期设置：前一个工作日配置需求

Hey there, let's get that conditional default start date set up in SSRS to match your stored procedure logic. Here's a step-by-step breakdown:

Step 1: Configure Your Start Date Parameter #

First, open your report in SQL Server Data Tools (or Report Builder) and navigate to the Parameters pane:

• Create a new parameter (or edit your existing StartDate parameter)
• Set the Data type to Date/Time — this avoids messy string conversions and plays nicer with your dataset's date fields
• Adjust other settings (like visibility, allow nulls) based on your report's needs.

Step 2: Add the Conditional Default Value Expression #

Head to the Default Values tab for the parameter:

1. Select Specify values and click Add
2. Click the fx button to open the expression editor, then paste this VB.NET-based expression:

   =IIF(Weekday(Today(), vbMonday) = 1, DateAdd("d", -3, Today()), DateAdd("d", -1, Today()))
   

   Let's break this down to match your original logic:
   • Weekday(Today(), vbMonday): Treats Monday as the first day of the week (so Monday returns 1, Tuesday returns 2, ..., Sunday returns 7)
   • If today is Monday (=1), we subtract 3 days to get last Friday
   • For any other day, we subtract 1 day to get the previous workday
   • Today() gives us just the date portion (no time), which aligns with your stored procedure's CONVERT(date, getdate()) logic.

Alternative Expressions (If You Prefer SQL-Like Syntax) #

If you want something closer to your original stored procedure code, you can use these variants:

• Using DatePart (similar to SQL's DATENAME):

  =IIF(DatePart("w", Today(), vbMonday) = 1, DateAdd("d", -3, Today()), DateAdd("d", -1, Today()))
  

• Checking the weekday name (note: this depends on your report's language settings, so use with caution):

  =IIF(Format(Today(), "dddd") = "Monday", DateAdd("d", -3, Today()), DateAdd("d", -1, Today()))
  

Bonus: If You Need String-Formatted Date #

If your parameter requires a string format (like your stored procedure's nvarchar conversion), wrap the expression in Format:

=Format(IIF(Weekday(Today(), vbMonday) = 1, DateAdd("d", -3, Today()), DateAdd("d", -1, Today())), "yyyy-MM-dd")

But again, using the Date/Time parameter type is recommended for better compatibility with datasets.

Test It Out #

Save your changes and preview the report:

• On Mondays, the default StartDate should be last Friday
• On any other day, it should be the previous calendar day (which is the previous workday)

内容的提问来源于stack exchange，提问作者todd.pund