We have a number of SSRS reports setup that need to run for the current month. However, our company uses a 4-4-5 accounting calendar so typical monthly start/end dates don't line-up with the standard calendar.
For example, the beginning of the current fiscal month is July 28th and ends August 24th. Currently we are manually updating the start/end dates on the SSRS reports, but I would like to setup something in SQL that could identify which dates should be used dynamically. If setup correctly, the report would begin using new start/end dates when the fiscal month changes.
We have an existing table that's setup like this (edited SEP/4/2024 - added last 2 columns):
| Date | Fiscal Month | Year | Start of Month | End Of Month |
|---|---|---|---|---|
| 7/27/2024 | Jul-24 | 2024 | 0 | 1 |
| 7/28/2024 | Aug-24 | 2024 | 1 | 0 |
| ........ | ...... | ... | ...... | ...... |
| 8/24/2024 | Aug-24 | 2024 | 0 | 1 |
| 8/25/2024 | Sep-24 | 2024 | 1 | 0 |
So assuming we're in August, for this month, we would want to retrieve July 28th (Start Date) and August 24th (End Date) as parameters in SSRS.
The table in SQL is very basic and setup something like this:
SELECT
FC.[Date],
FC.[Month],
FC.[Year],
FC.[StartMonth],
FC.[EndMonth]
FROM [DatabaseName$Fiscal Calendar] FC
Given that we have added the "Start of Month" and "End of Month" columns to help retrieve the data, what would be the easiest way to retrieve the month start date (Jul 28) and next end of month (Aug 24) from today's date?
Assuming you [Date] column is a real Date data type and that this is SQL Server then add a dataset to the report that just gets the start and end dates based on the current date such as
SELECT MIN(fc.[Date]) as StartDate, MAX(fc.[Date]) as EndDate
FROM [DatabaseName$Fiscal Calendar] fc
JOIN (SELECT [Month], [Year] FROM [DatabaseName$Fiscal Calendar] WHERE [Date] = CAST(GetDate() AS Date)) as tday
ON fc.[Month] = tday.[Month] and fc.[Year] = tday.[Year]
Here, the subquery (tday) just gets today's row. Then we join this back to the calendar table on the same month and year and get the min and max values .
You can then use this to supply default values to your report parameters.
Note: Ideally, the month start and end date columns would be built into your calendar table then you would not need this code, especially as it seems like a common thing to do, building the calendar table once with all this info pre-calculated makes life much easier going forward.