I would like to send data-driven subscription on specific date. Query for report:
SELECT PPstart, PPend, PmtDate,
PayForMonth, DateAdd(dd, - 3, PPend) AS SendRpt,
'[email protected]' AS MailTo,
Concat(PayForMonth, ' Monthly Payroll Report') AS Subject
FROM PayPeriodTable
WHERE YEAR(GETDATE()) = LEFT(PmtDate, 4)
AND PayForMonth = CONVERT(char(3), GETDATE(), 0)
Gives me:
PPstart PPend PmtDate PayForMonth SendRpt MailTo Subject 2021-10-18 2021-10-31 2021-11-05 Oct 2021-10-28 [email protected] Oct Monthly Payroll Report
Query is dataset for the subscription, and I would like it to run on SendRpt value every month. Possible without getting into exe files on report server? I do not have access to those.