reporting-servicesreportbuilder3.0powerbi-paginated-reports

Auto populate dates of the week Paginated reports

Good day

I am very very new two paginated reports so forgive me if this is a silly question

I have a report that displays values for Mondays to Fridays based on the date selected from a date picker. So basically You select a date (Example 24 Nov) and the following table is displayed based on values pulled from SQL.

enter image description here

Now my question is how do I display the dates of the weekdays too? So if the date selected is Thursday 24 Nov, in the column headers under the week day names it should give the corresponding date i.e Monday-21/11/2022, Tuesday - 22/11/2022, etc.

Below is a little snippet of the data

enter image description here

So the date picker is based on the ReportingDate column. The rows of the matrix consist of Region and Country and the values are the sum of Monday-Friday.

Any guidance would be greatly appreciated.

Edit: The day names are not obtained via an expression in SSRS. They carry over from the column headers in the data set.

Solution

  • I managed to figure it out with the help of the following post.

    These are the steps I followed

    1. Get the date of the first day of the current week (Sunday's date) using the formula explained in the link
    DateAdd("d",1-  DatePart("w", CDate(Parameters!ReportingDate.Value)), CDate(Parameters!ReportingDate.Value))
    1. Use DateAdd to add the corresponding number of days to get to a required weekday. That is for Monday add 1, Tuesday add 2,...
    DateAdd("d",1,DateAdd("d",1-  DatePart("w", CDate(Parameters!ReportingDate.Value)), CDate(Parameters!ReportingDate.Value)))
    1. Format datetime to date and add new line to insert date below day name
    ="Monday" + Environment.NewLine + FormatDateTime(DateAdd("d",1,DateAdd("d",1-  DatePart("w", CDate(Parameters!ReportingDate.Value)), CDate(Parameters!ReportingDate.Value))), DateFormat.ShortDate)