How to Calculate Week Start and End Dates from a Week Parameter in Bold Reports

Bold Reports® allows you to calculate the start and end dates of a selected ISO week. This is useful for weekly reports and comparisons because users can filter by a consistent full-week period without manually entering dates. Configure the parameters and dataset filter below to apply the selected year and week.

Applies to: Web Report Designer, Standalone Report Designer

Step-by-Step Instructions

Step 1: Create the Year Parameter

Create a parameter that allows users to select the reporting year.

Click the Parameters icon in the configuration panel and create a new parameter named Year. Set its data type to Date/Time, then click Save.
Click the parameter’s Settings icon, expand Miscellaneous, and click Set Attributes.
In the Attributes editor, add a custom property named DatePickerType and set its value to Year. This restricts the date picker to year selection only.

Step 2: Create the Week Parameter

Create a parameter that allows users to select an ISO week number.

Create a new parameter named Week and set its data type to Text.
Under Available Values, select Specify.
Choose the Dataset and assign fields to the Value and Label. Use the same text for each value and label. The expressions below expect the value to use this exact Week n format.

Step 3: Create the WeekStart Parameter

Create a parameter that calculates the first day of the selected week.

Create a parameter named WeekStart and set its data type to Date/Time.
Under Default Values, select Specify and click the Expression icon.

Enter this expression to calculate the Monday of the selected ISO week. It returns a Date/Time value; apply a display format separately if you want to show only the month and day.

=DateAdd(
  "d",
  (CInt(Replace(Parameters!Week.Value, "Week ", "")) - 1) * 7,
  DateAdd(
    "d",
    0 - (DatePart("w", DateSerial(Year(Parameters!Year.Value), 1, 4), FirstDayOfWeek.Monday) - 1),
    DateSerial(Year(Parameters!Year.Value), 1, 4)
  )
)

Step 4: Create the WeekEnd Parameter

Create a parameter that calculates the last day of the selected week.

Create a parameter named WeekEnd and set its data type to Date/Time.
Under Default Values, select Specify and click the Expression icon.

Enter this expression to calculate the Sunday of the selected ISO week:

=DateAdd(
  "d",
  6,
  DateAdd(
    "d",
    (CInt(Replace(Parameters!Week.Value, "Week ", "")) - 1) * 7,
    DateAdd(
      "d",
      0 - (DatePart("w", DateSerial(Year(Parameters!Year.Value), 1, 4), FirstDayOfWeek.Monday) - 1),
      DateSerial(Year(Parameters!Year.Value), 1, 4)
    )
  )
)

Because WeekStart and WeekEnd are calculated, hide them or make them read-only if those options are available for the parameters in your report.

Step 5: Filter the Dataset by the Calculated Dates

Use the calculated parameters to filter the dataset. For a date-only column, add this condition to the dataset query:

WHERE OrderDate BETWEEN @WeekStart AND @WeekEnd

If Date contains times, use an exclusive upper bound so rows later on Sunday are included. For SQL Server, use:

WHERE OrderDate >= @WeekStart
  AND OrderDate < DATEADD(day, 1, @WeekEnd)

Use the parameter syntax and date-add function supported by your data source. Alternatively, add a dataset filter on OrderDate using WeekStart and WeekEnd as the range values.

Preview

Preview the report using the actual dataset and select a year and week with known matching records. Verify that WeekStart and WeekEnd recalculate when either selection changes, that rows on Monday and Sunday are included, and that rows outside the selected week are excluded. Test a year boundary and a year with ISO week 53 when available in the data.

Year Selection: The date picker allows users to select only the year.

Dynamic Week Calculation: The start and end dates are calculated automatically based on the selected year and week.

You can download the sample report used in this article here.

Limits

ISO years have either 52 or 53 weeks. Week 53 is valid only in ISO years that contain it; remove or disable that option for years without a 53rd week.

Was this article useful?

Dislike

Comments (0)