CODEWITHSUNDEEP
reporting-services
DavidE
DavidE

Reputation: 1

SSRS - Need expressions that calculate beginning and then the end of the month given a parameter date

I have a report that works perfectly. In this report, the user can input the date of the report, for example, 6/25/2015. I need two expressions that will display the beginning of the month and the end of the month for the date input by the user. For example, if the user inputs 05/01/2015, then I would want my report to display 05/01/2015 - 05/31/2015. If the user inputs 04/09/2015, I would want the report to display 04/01/2015 - 04/30/2015. I believe that I would use two text boxes, one to display the date for the beginning of the month and one to display the date at the end of the month. I have no idea what the expressions would be though.

Any thoughts? Thanks for the help.

Upvotes: 0

Views: 518

Answers (1)

Sébastien Sevrin
Sébastien Sevrin

Reputation: 5405

There are a lot of ways to do this, here is one of them:

Input example: 6/25/2015 (Parameter Name => TestDate)

First day of month ("MM/dd/yyyy" format): 

=DateSerial(Year(Parameters!TestDate.Value), Month(Parameters!TestDate.Value), 1).ToString("MM/dd/yyyy")

Builds a new date with the Year and Month of the date and 1 as Day.

Output: 06/01/2015

Last day of month ("MM/dd/yyyy" format): 

=DateSerial(Year(Parameters!TestDate.Value), Month(Parameters!TestDate.Value) + 1, 1).AddDays(-1).ToString("MM/dd/yyyy")

The idea is to:

  • Build a new date with the Year and Month of the date and 1 as Day (06/01/2015)
  • Add 1 Month (07/01/2015)
  • Remove 1 Day

Note that this could also be simplified as:

=DateSerial(Year(Parameters!TestDate.Value), Month(Parameters!TestDate.Value) + 1, 0).ToString("MM/dd/yyyy")

Because DateSerial can process out of range values and perform the requested date operations.

Output: 06/30/2015

Putting everything together:

=DateSerial(Year(Parameters!TestDate.Value), Month(Parameters!TestDate.Value), 1).ToString("MM/dd/yyyy") & " - " & DateSerial(Year(Parameters!TestDate.Value), Month(Parameters!TestDate.Value) + 1, 1).AddDays(-1).ToString("MM/dd/yyyy")

Output: 06/01/2015 - 06/30/2015

Upvotes: 1

Related Questions