Report Scheduling and Delivery is a wonderful feature of SQL Server Reporting Services which can be easily integrated into CRM. If you are using CRM 3.0, you should download and install Microsoft CRM 3.0 Report Scheduling Wizard to enable report scheduling. In CRM 4.0 and CRM 2011 this feature is fully integrated in CRM Reporting Services Connector component, so no additional downloads required.
Typical tasks of report scheduling: executing “heavy” analytical report at an inopportune time, daily sales summary, a weekly campaign successes summary and so on! Reporting connectors for all CRM versions, unfortunately, do not have such functionality as email snapshot delevery, but stil there is the way to implement it manually. In this post I will provide step by step instructions for CRM report scheduling and its subsequent delivery. Screenshots are made for CRM 2011 & SQL Reporting Services 2008 R2, but their differences from other versions are not significant.
1. Select report your need and press “Schedule Report”
2. Let’s schedule this report. Or you can make a one-time report snapshot by selecting “on demand”. In this case you can stop reading this post.
3. In the next step, we choose how often we want to do a snapshot of the report. In this example, once a week on Fridays.
4. Next, specify then you want to begin running the report , for example, next Monday, and when to stop doing this, for example, never!
5. Select any report parameters you want to use on the snapshots, as well as CRM filtering options. Looking ahead, I will say that you should avoid filters like “current user” and other like this, because this will not work as expected. Report will always be executed under saved credentials of its owner. So if you want managers to receive report data filtered according to their subordinates – you have to schedule this reports for every individual manager!
6. Confirm that we have done everything right and click “Create “. Pay attention to the name of the report which will be a container for the report snapshots (Snapshot Definition Name), schedule execution, as well as the fact that the system will limit a number of saved snapshots. Running ahead again: we can change this value later!
At this step we have done with CRM, but still need to configure snapshot mail-delivery. Other steps are provided for Reporting Services configuration.
1. First, we need to configure SSRS to send e-mail. To do this, run the SSRS Сonfiguration Manager and choose “E-mail Settings”. In the most of scenarios, you only required to specify the e-mail server host name and e-mail address of the sender.If your environment require a more complex setup, refer to the article: Configuring a Report Server for E-Mail Delivery.
2. Next, open the Reporting Services Web application. By default it is located at: http://ReportServer Name/Reports. In the home page you will find the report folders named as CRM organizations in your deployment . Now press the “Detail View” button and go to the directory for the organization you need.
3. Now, find the report you need and press “Manage”. Note that CRM reports in the Reporting Services are named with their GUIDs in CRM. You can use description field to find Snapshot Definition Name of your scheduled report.
4. The next 3 steps are for information only, so you can go directly to step 7. Nevertheless, I recommend you to pass them, for a better understanding of the Reporting Services.
First look at the “Data Source” section. Snapshot, in contrast to other reports, do not use shared SQL data source for the organization. Instead of this, it connects to the custom CRM report renderer. Please also note that the source uses stored credentials, and does not prompt the user to login. User name – is an ID of the CRM user, who was planning report or has published it for external use. Password – is the identifier of the organization. In the same way CRM 4.0 and CRM 2011 does Report Server user authentication during external access. From inside of the network it might be a good loophole to look into someone else’s report, so keep that in mind!
5. Next to that you should pay attention – is the processing options. CRM Scheduled Report will not be executed every time you run it. Instead of this, it will use a schedule that we provided during setup. By the way, the local scheduler a little more flexible than the one in CRM, but it is not a good idea to modify settings from here. Maybe you still want to make changes to the schedule through CRM in future.
6. The last thing you should see before going on – is the report Snapshot Options. This option is often confused with the scheduling of processing options. The difference is that it is used for reports that are configured to receive data in real time. With it, we can configure RS to save reports snapshots on the schedule, but at the same time keep the ability to receive data in real time.
On this page we are interested in “Limit the copies of report history” setting. If 8 snapshots is not enough, you can increase this value.
7. Now we are ready to create report subscription!
8. “Subscription” means “delivery of the report”. There are are two types of subscriptions: you can save generated reports in a network folder, or send it by e-mail.
Now let’s see how to configure the subscription properly.
1. Please note that CRM reports are named with their GUID . Therefore, you should remove the parameter @ReportName from subject line and enter it manually.
2. Specify the recipients of the report and, if necessary, a “reply-to” address.
3. Choose whether you want to attach the report file and, if so, what format to use. The report will be included in the message body by default, but it is often convenient to get it in Excel file format for storage and further processing.
4. Subscription processing option. Specify “When the report content is refreshed” option to receive а message then a new snapshot is created.
Also, thanks to MVP Donna Edwards for her post Creating Report Subscriptions in Microsoft Dynamics CRM 4.
Yesterday I received a new Award Kit, and with it a new annual disk to the statuette. Joining in this game was interesting, but stay in it even more fun! Thanks to all who believed in me!
This year I’ll try to pay more attention to English-speaking community.
Sometimes you do not want to give user the ability to extort to Excel arbitrary amounts of data (10 000 rows by default), but also there is no crime, if user extort some personal owning sales data for later analysis or reporting. In this case you can use reporting features of MS CRM, but this is not very convenient because it requires additional time for report development.
The way out is to use the built-in Excel data connection features! Just open your Excel workbook, go to the “Data” tab on the ribbon and push “From Web” button, to use Internet page data import feature. Type CRM URL in the New Web Query dialog, then go to the appropriate page in CRM and select table you want to import!
Your also can use direct CRM view URL to simplify the process. To find it open the view you want to use, in the Actions Toolbar select More Actions and then select Copy Shortcut and click Of Current View.
As you know, CRM can use the Microsoft Office Word “Mail Merge” feature to create documents with data from the system. For unclear reasons, this option is disabled for the majority of system objects, such as Salesorder. Nevertheless, there is a simple way to change this!
Using SQL Server Management Studio, open the your organization database and find the MetadataSchema.Entity table. This table contains settings for all objects in the system. Locate the desired object and set IsMailMergeEnabled field to True. Now restart the web server using IISRESET command. Then you will see “Mail Merge” button on the objects list tool bar, and you can create templates for objects of this type.
Another way is to customize an system object called mailmergetemplate. Open it for editing and find the attribute templatetypecode. Add a new option for your object, for example the “Order”. Save and publish your changes. Now we must return to the database and find MetadataSchema.AttributePicklistValue table. Find pick list option created in previous step – you can use it “Value” attribute to do this. Now we should change its value to the code of the required object. For example, 1088 for the object “Order” (the codes of the system objects can be found in the SDK). Then restart server by IISRESET command, and you will see that you can create templates for newly added object type. Minus of this approach is that the system will not display any Mail Merge buttons in the lists or on the object form, so you need to add this button manually using ISV.config:
<ToolBar ValidForCreate="0" ValidForUpdate="1">
var oArgs = new Object();
var oIds = new Array(1);
oIds = crmForm.ObjectId;
oArgs.TotalRecords = 1;
oArgs.SelectedRecords = 1;
oArgs.Ids = oIds;
oArgs.GridXml = "";
openStdDlg(prependOrgName("/_grid/cmds/dlg_webmailmerge.aspx?mergetype=3&objectTypeCode=") + CrmEncodeDecode.CrmUrlEncode(crmForm.ObjectTypeCode), oArgs, 600, 600);'>
<Title LCID="1049" Text="Mail Merge" />
<ToolTip LCID="1049" Text="Convert form to the Word document" />
The idea of the method is very simple: use the system function, which fills user’s form on “domain name” field change:
displayError("domainname", "onchange", e.description);
Let’s modify it and add a button through isv.config:
<ImportExportXml version="220.127.116.11" languagecode="1033" generatedBy="OnPremise">
<Title LCID="1033" Text="Update User from AD" />
<ToolTip LCID="1033" Text="Update User from AD" />
As you know, the full name format change does not affect the records that are already stored in the database. But what if:
- There was a change in reporting requirements in the system?
- You have imported organization from other deployment?
- There was a change in naming requirements for users in AD, and you have to re-import the organization to reflect the changes?
There is a way. To populate this field CRM uses a stored procedure
dbo.fn_GetFullName(<first name>, <second name>, <middle name>)
You also can use it to quickly update all records of Сontacts:
UPDATE ContactBase SET fullname = dbo.fn_GetFullName (firstname, secondname, middlename)
A similar query can be applied to records SystemUser and Lead.
Of course it’s an unsupported solution, but we use the native function isn’t it? Nevertheless, use it at your own risk!