In my UI, users can view reports
I want an option to schedule a report based on their criteria
I.e. send me a report at 10pm of data from the day
or send me the weekly report every sunday evening at 8pm
How would you suggest implementing this
I was thinking of a scheduled task to hit a console app that is running every x mins to scan the "criteria" table and send any reports that falls in this criteria
But not sure this is the best way
Can you suggest the best practice for this please
thanks
I want an option to schedule a report based on their criteria
I.e. send me a report at 10pm of data from the day
or send me the weekly report every sunday evening at 8pm
How would you suggest implementing this
I was thinking of a scheduled task to hit a console app that is running every x mins to scan the "criteria" table and send any reports that falls in this criteria
But not sure this is the best way
Can you suggest the best practice for this please
thanks
1 Answer, 1 is accepted
0
John
Top achievements
Rank 1
Rank 1
answered on 24 Mar 2014, 09:34 AM
Hi Mark,
This is something we've got planned with our product later in the year so we haven't given it a lot of thought just yet, but here's a little input that might help you.
First, I'd ensure my reports are contained within a DLL so that can be referenced by the web project, windows project and then whatever method you will use to run the schedules.
Next I'd maybe have a set of tables in the DB to store the details of reports, including the parameters and parameter types so that I could store the settings for each report requested on a schedule, but, there are many more ways of doing this so many discussions would take place in the team on this point.
Your next question might be "How often do we want to send reports?" I might suggest only allowing reports to be sent on approximate hours rather than letting users select exact times in hours and minutes. This means your schedule will only have to poll the database hourly rather than every few minutes. Of course, this is going to be something only your team can decide. You may also want to warn your users that the reports may not run exactly ON the hours if there's a large queue, it may run at a few minutes past. For date/time sensitive data you may wish to consider this in your report parameters.
I guess the final step is going to be how to actually fire something up every hour. Once again there are many options here. Maybe by far the easiest is going to be to create a simple executable to run the whole process and send out the emails and then use the windows task scheduler to run this up. However, I would be tempted to look at a windows server instead, but still using the windows task scheduler. The main benefit of the windows service is you don't need to leave a user logged on to run a WinForms app.
This has got me wondering, however, about using SQL server agent with DBMail, the tricky bit will be trying to use the reports DLL via SQL. This should be possible calling a CLR function and would be an interesting technical challenge.
I hope this helps and I'd be keen to hear how you get on.
Tony
This is something we've got planned with our product later in the year so we haven't given it a lot of thought just yet, but here's a little input that might help you.
First, I'd ensure my reports are contained within a DLL so that can be referenced by the web project, windows project and then whatever method you will use to run the schedules.
Next I'd maybe have a set of tables in the DB to store the details of reports, including the parameters and parameter types so that I could store the settings for each report requested on a schedule, but, there are many more ways of doing this so many discussions would take place in the team on this point.
Your next question might be "How often do we want to send reports?" I might suggest only allowing reports to be sent on approximate hours rather than letting users select exact times in hours and minutes. This means your schedule will only have to poll the database hourly rather than every few minutes. Of course, this is going to be something only your team can decide. You may also want to warn your users that the reports may not run exactly ON the hours if there's a large queue, it may run at a few minutes past. For date/time sensitive data you may wish to consider this in your report parameters.
I guess the final step is going to be how to actually fire something up every hour. Once again there are many options here. Maybe by far the easiest is going to be to create a simple executable to run the whole process and send out the emails and then use the windows task scheduler to run this up. However, I would be tempted to look at a windows server instead, but still using the windows task scheduler. The main benefit of the windows service is you don't need to leave a user logged on to run a WinForms app.
This has got me wondering, however, about using SQL server agent with DBMail, the tricky bit will be trying to use the reports DLL via SQL. This should be possible calling a CLR function and would be an interesting technical challenge.
I hope this helps and I'd be keen to hear how you get on.
Tony