CRM report scheduling can be done in 2 ways, namely,
1. Standard Subscription
2. Data-Driven Subscription
Scheduling with Standard Subscription:
As a prerequisite for Scheduling with Standard Subscription, you must capture Snapshot first. A Snapshot contains layout information of a report and results that were captured at a specific point of time. Report snapshots are processed for schedule and then saved to a report server. When you select a report snapshot for viewing, the report will be retrieved from the report server database. Report snapshot is a pre-executed report.
Whenever a user wants to schedule a report to static recipients (e-mailing to certain specific users statically) then standard subscription will be used.
Steps to Create Report Snapshot:
Let’s consider an existing CRM report named as “Account Details”
Now create the snapshot of this report so that the report has data up to this specific time only.
1. From the Available Reports view in your CRM, select Account Details report and click “SCHEDULE REPORT” button.
You will be directed to Select On-demand or Scheduled Snapshot window.
2. A window pops up. Select “on a schedule” radio button and click Next.
You will be directed to Select Frequency window.
3. Select the frequency of scheduling the report. For instance, let’s go for monthly schedule (1st day of every month at 11AM) and click Next.
You will be directed to Select Start and End Dates window.
4. Then select Start Date and End Date. If you don’t want to end the schedule, then select “No end date” radio button. Click Next.
You will be directed to Define Report Parameters window.
5. If the report has parameters, then enter values for parameters accordingly. To modify default filter used to create the snapshots, click Edit Filter.
You will be directed to Modify Filter Criteria window.
6. Use date parameter such as ‘Last x days’ that will be repeated for each subsequent snapshot. Click Save.
7.A final review will summarize the selections. Click Create.
8. Now Report Snapshot is created with timestamp.
Steps to Schedule Emailing of the Report Snapshot:
1. To set up the email schedule, you must navigate to the CRM report server and select the organization.
2. Usually snapshots will be available in custom folder. So, open the Custom Reports.
3. Hover over any snapshot report to see drop down menu and select “Subscribe.”
4. For the first time, a subscription is set up for this report, you must select “New Subscription,” otherwise for modification select the report and edit. Select Render format from drop down like PDF, word, excel, etc. If we tick the check box Include Link, then only CRM users can access.
Click on “Select Schedule” button to schedule the email.
Report snapshot was created to run at 11:00 AM, now let’s send the email at 11:02 AM. First snapshot will be executed after which report scheduling will work. For this reason, you must give time difference to report snapshot and scheduling the email. Select end date also, if necessary. Click Ok.
Scheduling with Data-Driven Subscription:
Reporting Services data-driven subscriptions allow you to customize and automate the distribution of a report based on dynamic subscriber data. It allows distributing of reports to a specific group of recipients based on predefined criteria.
Steps:
1. Go to the SQL Server Reporting Services folder where you want to create data-driven subscription. Choose the SSRS report and choose Manage from context menu.
2. When the report metadata is displayed, go to “Data sources” tab. Provide user details under custom data source and check Test Connection button to verify whether connection exists or not and click “Apply.”
3. Once connection exists, go to Subscriptions tab. If you are dealing with a new report, you will find an empty subscriptions list.
.
4. Click on “New Data-driven Subscription” button to create a new data-driven subscription in Reporting Services.
5. Provide description for this subscription, then choose a delivery extension.
6. Provide connection string and user credentials and click Next.
7. Specify a command or query that returns a list of recipients and optionally returns fields used to vary delivery settings and report parameter values for each recipient:
Here is the source code of sql Query returning the list of recipients for this Reporting Services data-driven subscription tutorial.
8. There is a time-out period which will fail the process in case the data source command fails to respond within given period of time in seconds.
Before you continue to next step, press "Validate" button to execute the data source command and populate a sample result set for future data-driven subscription steps. If the Validate button displays an error message, you should correct SQL command or SQL query that you have entered the above text area before continuing with next steps and click on Next.
9. In Data driven subscription, you will get email recipients from database based on specified criteria (see SQL query).
Select To, report format, include report and subject from drop down.
And click on next.
10. If there are any parameters in report, you must pass field values to those parameters.
11. Now select “on a schedule…” radio button for this subscription.
12. Mention the day and time for email subscription.
Click Finish.