How to make SSRS subscriptions Kick off on Business Day 6 of month

Found a similar question 5th BUSINESS DAY subscription SSRS But in that case a work around -schedule for 1st of month- was suggested and accepted as the answer.

I want to know if anyone has found a clever way to make SSRS subscriptions run on a specified Business Day. I.E run on Business Day 6. This is not just to prevent the report from going out on a weekend, but also because certain Finance operations related to closing the month have an agreed upon date (EX. "Will be done by BD 3") And I need my report to run after that each month.

One comment also suggested setting up a sql agent job to calculate what BD X would be each month, and insert a SQL job for that date to kick off the Report. I can see in theory how this would work - but the subscription wouldn't be managed within SSRS then and could easily be over-looked in the future.

I have been getting by with the following imperfect code:

--: returns last day of last month (DT), but only provides a result row if we are on BusinessDay X of the month.
--No rows returned prevents the report from being processed & sent. 
--SSRS Schedule must be set to run monthly in a range covering all possible Calendar Days that could be Business Day X 
declare @dt datetime = Getdate(), @BDTarget int = 6
SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1,@dt)-1, -1) as DT,sum(dimdate.BusinessDay) as BD
FROM DimDate 
where FullDate between  DATEADD(mm, DATEDIFF(mm, 0, @dt), 0) and @dt
having sum(dimdate.BusinessDay)=@BDTarget

Though recently discovered that this logic can kick off the report two days in a row, for example if we are looking for BD 6, this month July 2021 the above query returned a row on both Friday 7/9 and Sat 7/10.

Solution

You can use a data-driven subscription that runs daily. Use a query that will only give a result on the 6th business day.

e.g.

WITH cte_6th
AS
(
    SELECT *
    FROM dimDate dd
    WHERE dd.TheMonth = MONTH(current_timestamp)
    AND dd.TheYear = YEAR(current_timestamp)
    AND dd.BusinessDay = 1
    ORDER BY dd.Date
    OFFSET 5 ROWS
    FETCH NEXT 1 ROWS ONLY
)
SELECT *
FROM cte_6th c
WHERE c.Date = CAST(current_timestamp as DATE);