CODEWITHSUNDEEP
reporting-servicesreportbuilder3.0powerbi-paginated-reports
Susan-l3p
Susan-l3p

Reputation: 147

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.

Upvotes: 0

Views: 411

Answers (2)

Susan-l3p
Susan-l3p

Reputation: 147

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)

Upvotes: 0

Alan Schofield
Alan Schofield

Reputation: 21703

Assuming you have some expression to get the actual day name already, you can leave that bit as it is.

Now, double click the column header "cell"/textbox to get a cursor in there, then click at the end of the existing fields/expression.

Now just put a space in and then right-click and choose "Create Placeholder". The placeholder dialog will appear.

enter image description here

This placeholder acts almost like a separate textbox to you can put whatever you want in there, click the '[fc]' button next to the Value property and then set this to whatever you want, e.g. =Fields!ReportingDate.Value.

Now go to the "number" tab on the same dialog box and set the format to the required date format.

If you want to edit this later, just double click the cell again but this time right-click the placeholder and choose properties.

The other way of doing this would be to add another row to your header and set that to be the date instead but then you have to mess around removing borders etc., sometimes this can be easier if layout is an issue but you have a couple of options at least now.

Upvotes: 0

Related Questions