frequent request we come across is from companies who want to know which users are using CRM and when. The CRM platform provides the facility to gather detailed usage information by writing plug-ins, but a simpler and more general mechanism is to use the Internet Information Services (IIS) logging mechanism.
Below are some very useful links on standards for Microsoft CRM and SQL Server Reporting Services:
http://www.ssw.com.au/ssw/Standards/Rules/RulestoBetterMicrosoftCRM.aspx
http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLReportingServices.aspx
Enjoy!
Monika Borgaonkar Published Monday, January 26, 2009 10:22 AM
One of the well-known features offered in Microsoft Dynamics CRM v4 is export data to Excel as a dynamic worksheet, a dynamic pivot table, or even to a static worksheet. Users can then refresh these dynamic Excel sheets alone to get the most current data without having to pull up the web client and do a re-export.
A common question asked with respect to Excel web queries is how to protect query data from being exposed over the wire. When using Excel web queries, especially in an on-premise deployment, in spite of having configured CRM web server to allow traffic only over https, the query data exchanged, will not be protected over SSL. This is because Excel web queries are designed to directly query the SQL server mainly for performance reasons. In an on-premise deployment, since enabling https will cover all connections going over port 443 on your web server, this particular entry point remains exposed unless you have IPSEC configured. This blog discusses solutions to protect the dynamic export to Excel traffic over the wire in case of intranet scenarios.
Note that when accessing an Internet Facing Deployment from an external network or a CRM Online deployment via a dynamically exported sheet, Excel web queries do not talk directly to SQL server instead submit a fetchxml to the web server which then forms a SQL query accordingly to retrieve data from CRM database. Hence, permitting SSL only connections to the web server will protect data over the wire even for dynamic Excel sheets in case of IFD and Crm Online.
There are two solutions using which one can achieve data protection over the wire for dynamic Excel sheets. Before deciding upon which method works best for your needs, I suggest to ponder upon the caveats (also described below) associated with each method.
Solution A: Forcing CRM application to always run live queries using fetchXML
You can force an on-premise deployment to always serve dynamic Excel worksheets using the fetchXML route by adding a registry key to the machine running CRM application server role. Add a type DWORD registry key named UseWebQueryForLiveExport, with value set to 1, under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM hive.
Using the above method will now force all newly exported dynamic Excel sheets with CRM data to pull data using fetchxml and thereby not directly talk to the SQL server.
Caveats: Once the UseWebQueryForLiveExport registry key is in effect, if you want to refresh your newly exported dynamic Excel sheets with CRM data, you will require CRM Outlook client installed. This is primarily because once the above registry key is applied, all dynamically exported Excel sheets will use CRMTicket based authentication mechanism instead of integrated authentication.
Also note that once the above registry key is applied, newly exported dynamic sheets will be forced to pull data via fetchxml using ticket based authentication. Any sheets exported prior to the introduction of the UseWebQueryForLiveExport registry key will continue to pull data over integrated authentication by directly talking to SQL server hosting the CRM database and hence exposed over the wire. So you may have to ask all the users to re-export their dynamic Excel sheets.
In case you want to limit the usage of Export to Excel feature to few users, there is a special privilege “Export to Excel” which can be revoked from the role assigned to a user. This privilege is granted by default to all roles shipped out of the box. As a good practice, you want to create custom role with this privilege granted/revoked per the specific needs of your organization.
Solution B: Enabling Force Protocol Encryption on SQL Server
The second method requires very less user interaction and can be done silently without having any users to re-export their dynamic Excel sheets. This method relies on enabling SSL encryption on your SQL server. To enable encryption on the instance of SQL server hosting the CRM database, a server-side setting “Force Protocol Encryption” needs to be switched on.
A few things to be noted before enabling Force Protocol Encryption on your SQL instance:
a. You will have to acquire a Server Authentication certificate from an Enterprise Certificate Authority that your company interacts with.
Selfssl.exe utility can be used to generate a certificate for trial purposes. Selfssl.exe can be found as part of IIS6.0 resource kit http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/993a8a36-5761-448f-889e-9ae58d072c09.mspx?mfr=true . Selfssl.exe generated certificates are not to be deployed in production environments.
b. The subject property of the certificate should exactly match the fully qualified domain name of your SQL server hosting the CRM database and the intended purpose of the certificate should be Server Authentication.
c. Before applying the certificate, ensure that you have SQL services running under a domain user account credential and not a built-in account like NT AUTHORITY\Network Service. In order to apply the certificate to your SQL instance, you require to login under the same credential as your SQL service and hence this note.
d. Note that by enabling Force Protocol encryption on the SQL server, communication between all clients and SQL Server is encrypted. So before enabling this, ensure that this is indeed what your organization needs.
How to enable Force Protocol Encryption for SQL 2005: http://support.microsoft.com/kb/316898
The same steps listed in the above article work for SQL 2008 also.
Additional considerations for SQL 2008: http://msdn.microsoft.com/en-us/library/ms131691.aspx
A major advantage of this method is that in an intranet only (on-premise SKU) setup, all Excel dynamic sheets would be inadvertently protected over the wire without having the users re-export their previously created sheets. Also in other words, with this solution in place, Excel dynamic sheets will still be using integrated authentication and will not be forced to install MSCRM Outlook client.
Caveats: Though this method does not require any action from users, the SQL services indeed need to be restarted and hence a downtime will be involved. Also if you share the SQL server hosting MSCRM databases to host databases from other applications (this is not recommended from security aspect), then enabling Force protocol encryption will affect those database connections also. Enabling encryption on SQL server is known to have performance impact and hence if speed is prime criteria, then this may not be the solution to opt for.
Cheers,
The report wizard functionality of Microsoft Dynamics CRM 4.0 allows end users to quickly and easily create basic Reporting Services reports. After the user completes the wizard, CRM creates an RDL file that can then be rendered within the CRM Reporting Services viewer. A common question we get is how to change the template used by the report wizard. Well, my colleague, Brian, found a solution to this request. In this post, we discuss how to add your company logo to the report template used by the wizard.
Note: This approach is definitely unsupported, so use at your own risk!
We will go through the following steps to update the template:
Step 1 - Backup existing template
The report wizard template is located in the body field of the ApplicationBaseFile table within the _MSCRM database. We recommend that you back up this data prior to any alterations. You can do this simply by executing the following SQL statement in the _MSCRM database which creates a backup table in your _MSCRM database to store the template data:
select body into dbo.ApplicationFileBase_Backup from ApplicationFileBase
Creating your own tables within the _MSCRM database is typically frowned upon, so you could instead copy this to a backup table in another database.
Step 2 - Extract template from SQL
Unfortunately, since SQL Management Studio limits its output to 64KB, retrieving the template is not as simple as just selecting the body text and copy and pasting into your favorite XML editor. While there are a number of ways to accomplish this, we decided to write a very simple .NET application to extract the template. Create a console application in Visual Studio, and paste in the following code. Be sure to update the sqlServerName and databaseName variables with your information. Run the application and your template will be saved on the c: drive in a file called report_template.xml.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.IO;
namespace CrmExtractTemplate
{
class Program
{
static void Main(string[] args)
{
//update the sql server name
string sqlServerName = "sqlserver";
string databaseName = "organization_mscrm";
string connectionString = String.Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI", sqlServerName, databaseName);
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
string sqlText = "select body from applicationfilebase";
SqlCommand cmd = new SqlCommand(sqlText);
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
TextWriter log = TextWriter.Synchronized(File.AppendText(@"c:\report_template.xml"));
log.Write(rdr["body"].ToString());
log.Close();
}
}
}
}
Step 3 - Add your logo to the template file
The report wizard template is a custom XSL stylesheet that CRM uses to transform into a valid RDL file. To update it, you need to find the actual RDL code, which is located within the <Report> node. This area contains all of the RDL XML. The default template doesn't include a <PageHeader> node, so you need to add one with your image information.
Now that you know where in the template file to add the image, you need to determine what XML to add. The easiest way to do that is to actually create a new report in a tool like Visual Studio .NET or Business Intelligence Design Studio. Also, by creating it first in a tool, you can be sure of the placement and sizing settings.
You can add an image as an external link or embed it in the report. The code for an external image would look similar to:
<PageHeader>
<PrintOnFirstPage>true</PrintOnFirstPage>
<ReportItems>
<Image Name="image1">
<Sizing>AutoSize</Sizing>
<Width>4.01042in</Width>
<MIMEType />
<Source>External</Source>
<Style />
<Value>http://www.sonomapartners.com/images/logo.jpg</Value>
</Image>
</ReportItems>
<Height>0.79167in</Height>
<PrintOnLastPage>true</PrintOnLastPage>
</PageHeader>
However, if the image is small enough, you could also choose to embed it in the report. To do this, you would not only add the <PageHeader> node as shown before, but also add an <EmbeddedImages> node with the image. The code for the embedded image approach would look similar to:
<PageHeader>
<PrintOnFirstPage>true</PrintOnFirstPage>
<ReportItems>
<Image Name="Image15">
<Source>Embedded</Source>
<Value>sonomalogosmall</Value>
<Sizing>AutoSize</Sizing>
<Width>4.01042in</Width>
</Image>
</ReportItems>
<Height>0.79167in</Height>
<PrintOnLastPage>true</PrintOnLastPage>
</PageHeader>
<EmbeddedImages>
<EmbeddedImage Name="sonomalogosmall">
<MIMEType>image/jpeg</MIMEType>
<ImageData>
/9j/4AAQSkZJRgABAQEAYABgAAD... remaining encoded image removed for brevity
</ImageData>
</EmbeddedImage>
</EmbeddedImages>
This code can be placed anywhere within the parent <Report></Report> node in the template file.
Step 4 - Import the template file back to SQL Server
Luckily, you don't need to rely on .NET to get the file back into SQL Server. Copy your template file to the c:\drive of your SQL Server, and then from SQL Management Studio, execute the following SQL:
create table dbo.ApplicationFileBase_tempLoad
(
Body xml
)insert ApplicationFileBase_tempLoad (body)
select BulkColumn from openrowset( bulk 'C:\Report_Template.xml', Single_Blob) as Bodyupdate ApplicationFileBase
set Body = (select top 1 convert(nvarchar(max), Body) from ApplicationFileBase_tempLoad)
drop table dbo.ApplicationFileBase_tempLoad
Now when you create a new report wizard, your new logo appears in the top left corner of the report!
Meet guest blogger Jim Wang, Microsoft Dynamics CRM MVP. He is a technical consultant working for 2B.net, an UK based consulting company. He has been working on Microsoft Dynamics CRM since version 1.2.
Recently I have been asked to modify some reports. In my current project we have 4 environments (Development, Training, UAT, and Production) and we have custom reports for each environment. The reports for each environment are different because the data source is different. So the deploy administrator complain about numbers of report. They come and ask me if there is a way to have one report for all environments.
So this is how I did it:
If you have a look the CRM reports in report manager, there is a Data Source call: MSCRM_DataSource
I have noticed that actually all CRM reports use this data source, so why can’t I use it for our custom reports? I think it shouldn’t be a problem.
In Visual Studio, open the report project, Add a Shared Data Sources call: MSCRM_DataSource, it is important to keep it as same as CRM’s. The connection string just uses the development environment, e.g.: Data Source=crm;Initial Catalog=org_MSCRM
Once finished, upload the report RDL file using the Report Manager, e.g http://crm/reports.
See how Jim solves this challenge...
Cheers
The report wizard functionality of Microsoft Dynamics CRM 4.0 allows end users to quickly and easily create basic Reporting Services reports. After the user completes the wizard, CRM creates an RDL file that can then be rendered within the CRM Reporting Services viewer. A common question we get is how to change the template used by the report wizard. Well, my colleague, Brian, found a solution to this request. In this post, we discuss how to add your company logo to the report template used by the wizard.
Note: This approach is definitely unsupported, so use at your own risk!
We will go through the following steps to update the template:
Step 1 - Backup existing template
The report wizard template is located in the body field of the ApplicationBaseFile table within the _MSCRM database. We recommend that you back up this data prior to any alterations. You can do this simply by executing the following SQL statement in the _MSCRM database which creates a backup table in your _MSCRM database to store the template data:
select body into dbo.ApplicationFileBase_Backup from ApplicationFileBase
Creating your own tables within the _MSCRM database is typically frowned upon, so you could instead copy this to a backup table in another database.
Step 2 - Extract template from SQL
Unfortunately, since SQL Management Studio limits its output to 64KB, retrieving the template is not as simple as just selecting the body text and copy and pasting into your favorite XML editor. While there are a number of ways to accomplish this, we decided to write a very simple .NET application to extract the template. Create a console application in Visual Studio, and paste in the following code. Be sure to update the sqlServerName and databaseName variables with your information. Run the application and your template will be saved on the c: drive in a file called report_template.xml.
using System;using System.Collections.Generic;using System.Text;using System.Data.SqlClient;using System.Data;using System.IO;namespace CrmExtractTemplate{ class Program { static void Main(string[] args) { //update the sql server name string sqlServerName = "sqlserver"; string databaseName = "organization_mscrm"; string connectionString = String.Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI", sqlServerName, databaseName); SqlConnection conn = new SqlConnection(connectionString); conn.Open(); string sqlText = "select body from applicationfilebase"; SqlCommand cmd = new SqlCommand(sqlText); cmd.Connection = conn; cmd.CommandType = CommandType.Text; SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { TextWriter log = TextWriter.Synchronized(File.AppendText(@"c:\report_template.xml")); log.Write(rdr["body"].ToString()); log.Close(); } } }}
Step 3 - Add your logo to the template file
The report wizard template is a custom XSL stylesheet that CRM uses to transform into a valid RDL file. To update it, you need to find the actual RDL code, which is located within the <Report> node. This area contains all of the RDL XML. The default template doesn't include a <PageHeader> node, so you need to add one with your image information.
Now that you know where in the template file to add the image, you need to determine what XML to add. The easiest way to do that is to actually create a new report in a tool like Visual Studio .NET or Business Intelligence Design Studio. Also, by creating it first in a tool, you can be sure of the placement and sizing settings.
You can add an image as an external link or embed it in the report. The code for an external image would look similar to:
<PageHeader> <PrintOnFirstPage>true</PrintOnFirstPage> <ReportItems> <Image Name="image1"> <Sizing>AutoSize</Sizing> <Width>4.01042in</Width> <MIMEType /> <Source>External</Source> <Style /> <Value>http://www.sonomapartners.com/images/logo.jpg</Value> </Image> </ReportItems> <Height>0.79167in</Height> <PrintOnLastPage>true</PrintOnLastPage> </PageHeader>
However, if the image is small enough, you could also choose to embed it in the report. To do this, you would not only add the <PageHeader> node as shown before, but also add an <EmbeddedImages> node with the image. The code for the embedded image approach would look similar to:
<PageHeader> <PrintOnFirstPage>true</PrintOnFirstPage> <ReportItems> <Image Name="Image15"> <Source>Embedded</Source> <Value>sonomalogosmall</Value> <Sizing>AutoSize</Sizing> <Width>4.01042in</Width> </Image> </ReportItems> <Height>0.79167in</Height> <PrintOnLastPage>true</PrintOnLastPage> </PageHeader> <EmbeddedImages> <EmbeddedImage Name="sonomalogosmall"> <MIMEType>image/jpeg</MIMEType> <ImageData> /9j/4AAQSkZJRgABAQEAYABgAAD... remaining encoded image removed for brevity
</ImageData> </EmbeddedImage> </EmbeddedImages>
This code can be placed anywhere within the parent <Report></Report> node in the template file.
Step 4 - Import the template file back to SQL Server
Luckily, you don't need to rely on .NET to get the file back into SQL Server. Copy your template file to the c:\drive of your SQL Server, and then from SQL Management Studio, execute the following SQL:
create table dbo.ApplicationFileBase_tempLoad( Body xml)insert ApplicationFileBase_tempLoad (body) select BulkColumn from openrowset( bulk 'C:\Report_Template.xml', Single_Blob) as Bodyupdate ApplicationFileBaseset Body = (select top 1 convert(nvarchar(max), Body) from ApplicationFileBase_tempLoad)drop table dbo.ApplicationFileBase_tempLoad
Now when you create a new report wizard, your new logo appears in the top left corner of the report!
Posted by Jim Steger on November 21, 2008 | Permalink
We welcome our guest blogger David Jennaway who is the technical director at Excitation and a CRM MVP.
Reporting Services is usually considered at most as just the engine for executing and rendering reports. However, it also has its own SQL database that contains information that can be useful. In this article I’ll look at how you can use information derived from the ExecutionLog and Catalog tables to find information about who ran which report, and how long it took.
Accessing data in the ReportServer database
The recommended approach for querying the execution log is to periodically extract the data from the ReportServer database into a separate, denormalised database, then query this database. This approach is described in the SQL Server Books Online here (http://msdn.microsoft.com/en-us/library/ms155836(SQL.90).aspx ), and there are associated samples which include an SSIS package to perform the extract, and some sample reports on the extracted data (http://msdn.microsoft.com/en-us/library/ms161561(SQL.90).aspx ).
There are several advantages to this approach:
However, it is also possible to query the ReportServer database directly. This will give you data that is always up to date, but you will have to ensure users have sufficient permission on the tables, and you will have to parse the report parameters yourself.
The rest of this article is based on the denormalised database created from the SQL Server Samples.
Database Structure
The scripts described above create several tables; those that are relevant to this article are:
The following query illustrates a simple SQL query on these tables:
select r.Name as ReportName, u.UserName
, l.TimeStart, l.TimeDataRetrieval + l.TImeProcessing + l.TimeRendering as TotalTimefrom executionlogs l
join reports r on l.reportkey = r.reportkey
join users u on l.userkey = u.userkey
This produces output like the following:
| ReportName | UserName | TimeStart | TotalTime |
| My Report | MyDomain\My User | 2008-10-06 12:51:22.123 | 1023 |
| {7121cc90-d2c0-dc11-8308-0003ff562152} | NT AUTHORITY\NETWORK SERVICE | 2008-10-06 12:51:51.817 | 551 |
| ... | | | |
The first record is from a separate report on a database that has nothing to do with Dynamics CRM, while the second record is from executing the Activity report on a Dynamics CRM 4.0 Server, in a deployment where the Dynamics CRM Data Connector for Reporting Services has been installed.
From this you can probably see that, while the above query gives immediately helpful information when running non-CRM reports, it is not so useful for the CRM report. There are 2 things we have to do; get the actual user name or the user running the report, and get a usable report name.
Getting the UserName from the ExecutionParameters
The UserName information from the above query would normally identify the user that ran the report. However, if you use the Dynamics CRM Data Connector for Reporting Services, then this field will not give you this information.
However, it is possible to get the user information from the parameters passed to the report. Dynamics CRM passes several pieces of information to a report in the form of parameters, and the CRM_FullName parameter holds the name of the user executing the report.
So, we can change the above query to the following:
select r.Name as ReportName
, (select p.Value from executionparameters p where ExecutionLogID = l.ExecutionLogID and Name = 'CRM_FullName') as UserName
, l.TimeStart, l.TimeDataRetrieval + l.TImeProcessing + l.TimeRendering as TotalTime
from executionlogs l
join reports r on l.reportkey = r.reportkey
This now gives us:
| ReportName | UserName | TimeStart | TotalTime |
| {7121cc90-d2c0-dc11-8308-0003ff562152} | CRM Admin | 2008-10-06 12:51:51.817 | 551 |
| ... | | | |
Rather than using a join to get the ExecutionParameter, I used a subquery. This is mostly a matter of preference, but it makes it easier to include several parameter values on the select list.
Getting the Report Name from the MSCRM Database
Now we need the report name. If we were using CRM 3.0, this would show us the name of the report as we see it in the Dynamics CRM user interface, but things work differently in CRM 4.0. In CRM 4.0, the reports are created with a Guid for a name, and the usable name is stored in the organisation’s MSCRM database.
To get the report name in our query we will need data from another database. This can be done in a join, but I prefer to use a SQL function to do the work:
create function fExcitationGetReportName(@RSName nvarchar(425))
returns nvarchar(425)
as
begin
declare @ret nvarchar(425)
select @ret = r.name from AdventureWorksCycle_MSCRM..FilteredReport r
where @RSName = '{' + cast(r.reportid as nvarchar(100)) + '}'
return @ret
end
To avoid supportability concerns, I create this function in my RSExecutionLog database, and include the specific organisation database name in the function definition. You’ll need to replace AdventureWorksCycle_MSCRM with your CRM database name. If you have multiple organisations you could either create a function per organisation, or pass the organisation name as a parameter into the function, and use dynamically generated SQL.
Another point to make about the function is that we need to cast the reportid in CRM from a Guid to a string, and add the curly braces to match the name that is stored in Reporting Services
Once we’ve create the function, we can use it as follows:
select dbo.fExcitationGetReportName(r.Name) as ReportName
, (select p.Value from executionparameters p where ExecutionLogID = l.ExecutionLogID and Name = 'CRM_FullName') as UserName
, l.TimeStart, l.TimeDataRetrieval + l.TImeProcessing + l.TimeRendering as TotalTime
from executionlogs l
join reports r on l.reportkey = r.reportkey
Which gives the output we want:
| ReportName | UserName | TimeStart | TotalTime |
| Activities | CRM Admin | 2008-10-06 12:51:51.817 | 551 |
| ... | | | |
Further Thoughts
So far I’ve concentrated on the SQL aspects of getting the data you want from the underlying tables. Once you have this, you can present this information in your own reports. The samples that create the RSExecutionLog database include some sample reports that you can use, and the SQL within these reports can be easily modified using the techniques described above to get the report and user names.
I don’t have the space in this article to go into detail about extra things you can do with the data from ReportServer, but here are some additional ideas which may make it into a subsequent article:
Links
Report Execution Log at SQL Books Online - http://msdn.microsoft.com/en-us/library/ms159110(SQL.90).aspx
Code used in this article, and sample reports on the MSDN Code Gallery - http://code.msdn.microsoft.com/RSExecutionLogCRM40.
Cheers,
In an activity CRM grid, it is not possible to add attributes from the activity type (letter, phonecall etc) itself. The fields to and from on the entities phonecall, letter, fax are therefore not eligable for addition on the CRM grid. It would be very useful to see those though. The same is valid for the to, cc and bcc in email and required and optional attendees in appointments. In this post I won't be giving a solution to show the attributes in the grid, instead I will give a workaround by using reports.
The only attributes which you can select in the grid are the attributes which are belonging to the entity activitypointer. These include the activityid, startdate, statecode, but also the regardingobjectid. So the question is, how to get the to, from, cc etc. For this you can use the function which I have posted in my previous post. This function accepts an ActivityID and an ActivityPartyType. So what is this type? Look at this page: ActivityPartyType. You will find a list of values mapped to what kind of field you want to add to your report.
By using that function you can create your query for the report. An example would be:
In a recent upgrade environment, now running CRM 4.0, I found that reports stopped working from CRM. What was strange was that reports could still be run from the SQL Reporting Services website, just not from CRM - not even on the CRM server, so I knew it wasn't just a Kerberos authentication problem (and SQL was on the same box with CRM anyway!). After checking and re-checking all the settings in the SSRS website, the registry hive for CRM, and everywhere else I could think of, I opened a support ticket with Microsoft.
But whenever I have to ask for outside help, I feel like I need to re-double my efforts in locating the problem and fixing it myself. (I should probably just open tickets all the time!). After much searching, I found an obscure reference on a SQL forum about this error (The underlying connection was closed: A connection that was expected to be kept alive was closed by the server.) and a mention of an update for SQL 2005. I logged onto the SQL server and ran Windows Update, and there it was - KB948109. Downloading this update and installing it fixed the problem. After the update, there was an error in the event log indicating that the .NET Framework 2.0 could not recompile, but upon running a report and waiting a long time for the app to compile, reports started working again throughout the network. Yay!
Here's a link to the update for more information:
http://support.microsoft.com/kb/948109
A common customer request is to be able to schedule CRM reports and automatically send the report to one or more users by email. This can be done without the need for any code, but needs a combination of configuration within both CRM and Reporting Services.
CRM Configuration
CRM reports are designed to be run under the context of the user running the report so as to return only the data that user has permission to see. Due to this configuration, Reporting Services will not permit you to schedule CRM Reports out of the box (if you try, you'll get the error 'Subscriptions cannot be created because the credentials used to run the report are not stored') . The easiest way to resolve this issue is to download the CRM Report Scheduling Wizard from here and install it on the CRM server.
Then, navigate to Workplace, Reports in CRM, select the report you want to schedule, and go to More Actions, Schedule Report. As you will do the scheduling later within Report Manager, I'd suggest you select the following options:
Reporting Services configuration
The rest of the configuration is done via Reporting Services. First of all, you may have to configure the email properties of Reporting Services. To do this open RSReportServer.config, which by default will be in the Reporting Services\ReportServer directory under the SQL installation directory in Program Files. Within the 'RSEmailDPConfiguration' element you will need to set values in, as a minimum, the 'SMTPServer' and 'From' elements. You may need to apply other settings. For more information see the documentation for SQL 2005 or SQL 2000
Scheduling the Report in Report Manager
You can now schedule the report. Browse to Report Manager (by default it will be in the Reports virtual directory), go to the OrganisationName_MSCRM folder and the report you have scheduled (it's name will have the suffix On demand Snapshot). On the toolbar you should have a button 'New Subscription'. Click this, select 'Report Server E-mail' in the Delivered by drop down, and set all other options as required. (If 'Report Server E-mail' is not an option, go back to the instructions in the above paragraph
Posts
All Comments
