I've got a question and it may sound dumb but am figuring it out as I go...
In SSRS there is an option to have a data driven query and in that you can edit the dataset to read parameters of the report who to send to ect., ect.,
Is there a way to have the query read an output of a subquery and if it doesn't equal the output it doesn't send but if it does, it does trigger the report sending?
In this particular example, the report needs to be triggered to send on the 3rd business day of the month. I have a query that reads the third business day written up but I am not sure how to get it into the query and read as if the date = 2023/01/04 then trigger report and send it off, otherwise do nothing, checking daily if it is that date.
In my business day query it has the columns, Date - which is the date, DayOfWeek - which is the numeral day of the week 2-6(for weekdays), Year, Month, Day, and Working day of the month(which is all 3s being the third business day.)
Should I have the query set to reading if workingdayofmonth = 3 then trigger the report? Would that be the easiest? I am not entirely sure how to code it as such into the SSRS data driven query.
Thank you for your time and help!
CodePudding user response：
If you are using Enterprise edition, you can setup a data driven subscription.
I don't use Enterprise so I can't give a working exmaple but essentially, you create a dataset for the subscription that will only return data if your conditions are met.
As you previous question (linked here for other users reference) got you a calendar view that gives you the days the report needs to run, you can use that view, something like
SELECT * FROM myCalendarView WHERE TheDate = CAST(GetDate() AS Date)
The subscription will attempt to run everyday (or whatever the schedule is) but it will not produce anything unless the query above returns a resultset.
Take a look at this post which is similar to what you are attempting.