Use PreFiltering in Reports downloaded through Webservice

In my previous post I discussed a Mail-merge alternative: Reports exporting to Word files. The reports downloaded in that approach do not support any pre filtering as CRM reports would allow you to set. It would be great if that can be set as well. This post describes how to do that.

The reports created in CRM or for CRM do support the prefiltering. In custom created reports this is added by setting an alias for the filtered views which contain the CRMAF_ prefix. See Reporting PreFilter for details. Even though that post is written for CRM 3.0, it still is valid for CRM 4.0.

The reports which are created by using the Report Wizard do not have the CRMAF_ prefix though. Also reports which have been added to CRM and have been downloaded again do not have the prefix anymore. Instead, there is a report parameter added to the report. This parameter is @CRM_FilteredEntity. Entity would need to be replaced by the entity like @CRM_FilteredAccount or @CRM_FilteredNew_Entityname. This parameter will get the value of the filter in the format of a SELECT query. For Account the default filter would be "SELECT * FROM FilteredAccount". You can set this parameter in code and replace the default filter by your own filter. Here is a code example.

ParameterValue param = new ParameterValue();
param.Name = "CRM_FilteredAccount";
param.Label = "CRM_FilteredAccount";
param.Value = "SELECT * FROM filteredaccount WHERE accountid='" + accountId + "'";
ParameterValue[] reportParams = new ParameterValue[1];
reportParams[0] = param;

These parameters you can specify in the call to the Render method of the webservice. The example below is a modified version of my previous post.

// Download the report from the webservice in HTML4.0 format
reportData = rs.Render(rptNameFullPath, "HTML4.0", null,
"/WebApplication1/",
reportParams, new DataSourceCredentials[] { dsc }, null, out optionalString, out optionalString, out optionalParams, out w, out streamIDs);

Mail-merge alternative: Reports exporting to Word files

CRM 4.0 does offer a lot of new features regarding mail merge, but not all situations are supported by the new features. You can look into 3rd party tools like C360 / MSCRM-Addons or Temptus Wordconnect, but a totally different approach is to use the Reporting Services. This post will dive into how to use Reporting Services to generate word documents.

You can create any report in CRM by using the Report Wizard. Imagine you want a specific invoice which contains a list of the products that are included in the invoice. This report is easily created in the Report Wizard. You can also run the report from CRM, but when you look at the export button, you will see that word is no option. Now you can do two things:
1) Go to Aspose and buy their product. They will allow you to export a report to many text formats including .doc, .docx, .txt etc.
2) Create a piece of code which transforms your report to a .doc yourself. Of course we'll dive deeper on this approach.

You can create an extension to Reporting Services which does the real export to a word document, but this is relatively hard. A way easier approach is to access the ReportinService webservice and get the report in a byte array and send this byte array to the user with a content type set to "application/vnd.ms-word ".

How to do this, is to create a webform which does have nothing in the aspx itself. In the code behind, send instructions to the user that a word file will is approaching.

Response.ContentType = "application/vnd.ms-word ";
Response.AddHeader("content-disposition", "attachment; filename=YourFileName.doc");
Response.BufferOutput = true;

Then initialize the webservice and set the correct values

//Define report service
ReportingService rs = new ReportingService();

//Set Credentials
rs.Credentials = GetCredentials();

//Set URL
string reportServerPath = "http://SRSServer/ReportServer";
rs.Url = reportServerPath + "/ReportService.asmx";

//Define Reporting Services Variables
byte[] reportData;
string[] streamIDs;
string optionalString = null;
string rptNameFullPath = "/ORGNAME_MSCRM/4.0/" + "{02708d0c-28c5-dd11-9398-00155d511c04}"; // the guid is the name of the report in CRM.
ParameterValue[] optionalParams = null;
Warning[] w = null;

//If neccesary define datasource credentials. See note below for more info.
DataSourceCredentials dsc = new DataSourceCredentials();
dsc.DataSourceName = "CRM";
dsc.Password = "{E0E04CEF-04DB-DD11-9418-00155D511C04}";
dsc.UserName = "{DE9347FD-BC01-FD55-5218-045655D51C04}";

// Download the report from the webservice in HTML4.0 format
reportData = rs.Render(rptNameFullPath, "HTML4.0", null,
"/WebApplication1/",
null, new DataSourceCredentials[] { dsc }, null, out optionalString, out optionalString, out optionalParams, out w, out streamIDs);

// Offer download to user
Response.BinaryWrite(reportData);

When you do request this page in IE, then you'll get the report offered to you in a doc format because of the content type settings. Word does understand HTML and will just open the report.

Note: Make sure to check my other post around Reporting Services which might help you: Log In Name and Password required by Report Server

Log In Name and Password required by Report Server

You can view the CRM reports in Reporting Services by browsing to http://YourReportingServer/ReportServer/. There you should select your organization, select 4.0 and select a report. At that moment you might be asked for a Log In Name and a Password as seen in the image.

This behavior appears when you have installed the SRS Data Connector because your deployment contains a separate server for SQL. The user name and password which are requested are not your windows credentials. Instead, the user name is your userid and the password is the id of the organization you're working with. You can find these values in SQL by using the following SQL query.

SELECT firstname, lastname, systemuserid, organizationid FROM systemuserbase

If you are programming against the webservice of reporting service, then you can use the following C# code to get a correct DataSourceCredential. This code uses the DataSourceName which is "CRM" for CRM reports. You can find this by looking at the DataSource settings in Visual Studio when changing a CRM report.

//Initialize CRM WebService
CrmAuthenticationToken token = new CrmAuthenticationToken();
token.AuthenticationType = 0;
token.OrganizationName = "OrgName";
CrmService.CrmService service = new CrmService.CrmService();
service.CrmAuthenticationTokenValue = token;
service.UseDefaultCredentials = true;

//Get Credentials
WhoAmIRequest req = new WhoAmIRequest();
WhoAmIResponse whoami = (WhoAmIResponse)_service.Execute(req);
string userName = whoami.UserId.ToString();
string password = whoami.OrganizationId.ToString();

//Create DataSourceCredentials for SRS
DataSourceCredentials dsc = new DataSourceCredentials();
dsc.DataSourceName = "CRM";
dsc.Password = _password;
dsc.UserName = _userName;

Update:
My fellow MVP Jim Steger sent me this note:
I enjoyed your posts about SRS. One thing you might want to note is that you can get around the username/password thing by ‘publishing the report for external use’. That will change the data source auth to interactive and make it like a normal report that can be used instead of through the proxy. Obviously that may not be what someone wants, since they could run into double-hop scenarios, but I thought might be worth reminding people. Especially since the command is not available from the report grid and only when you edit the report record.

Dynamics CRM Report Modification Walkthrough

There are quite some reports in the out of the box installation of Dynamics CRM 4.0, but these sometimes do not match the exact business requirements. Also the reports which can be created by using the Dynamics CRM Report Builder Wizard cannot contain complex calculations or information of more than two entities. When you do export one of these reports and try to edit this in Visual Studio, then you will end up with errors. This post will guide you through these errors and modify a default report. For this example we will change the default “User Summary” report. This of course works for reports generated with the Report Wizard as well.

Exporting a report
The first step is to download a report from Dynamics CRM. You can do this by browsing to the Workplace and open the reports are. In the grid select the report which you want to modify and press “Edit Report”.



Clicking on “Edit Report” will open the Report Detail page. On this page click “Actions” and select “Download Report”. This will let you download the *.rdl file which is the definition of the report. Save this file to some location on your hard drive.



Opening the report in Visual Studio 2005
Keep in mind that the report must be opened in an editor which supports the rdl for SQL Server 2005. For Visual Studio this is version 2005. With 2008 you cannot change report files for CRM. When you directly open the report in Visual Studio you will see that the xml viewer will be opened.



Although you can modify a report in XML, there is an easier way to modify reports in Visual Studio. You will first need to set up a project for Reports. In Visual Studio select the “Report Server Project” from the “Business Intelligence Projects” group. If this is not available, install the “Business Intelligence Development Studio Add-In for Visual Studio 2005” from the SQL Server installation CD.



From the “Solution Explorer” right click on “Reports” and add an existing Report. From the report selection screen select the report which you have saved in the previous step.



The report will now be added to the solution under the reports tree view item. When you open this report now, you will get into a mode in which you can modify the report.



Connecting to Dynamics CRM
The report does have three tabs for defining the dataset, modifying the layout and previewing the report output. The first step in changing a report is to change the dataset. Every report which is exported from Dynamics CRM does have a hardcoded data source set. This will need to be changed to your current environment before you can modify the report. To do this click on the “Data” tab. You will get the following error message.
A connection cannot be made to the database. Set and test the connection string:



This is the message specifying the situation as I have just described. Just click “OK” and let’s change the data source. To do this click on the “…” next to the dataset pick list.



This will give you the “Dataset” detail form. On this form click on “…” next to the data source picklist.



This will give you the “Data Source” detail form. On this screen click on Edit next to the connection string.



This will give you the “Connection Properties” detail form. On this page verify that the server name is correct. Also select the correct database name. This should be in the format of “organizationname_MSCRM”.



After clicking on “OK” several times you will get back to the main screen. This will be refreshed and more information will be visible now.

Changing the Query
Now that a connection is created you can change the query. Unfortunately there is not just a single query. There are multiple Datasets with each a separate query. You’ll first need to find the correct Dataset before you start changing the query. By looking at the queries you will probably be able to determine which dataset you should use. Most likely the query starts with the declaration of a dynamic query like “Declare @SQL Varchar(4000)”.
Once you have found the query you can modify the query. I do expect that the people reading this article do have knowledge of how to change these queries, if not than you might want to find somebody else to change the query for you.
Once the query is modified and does return the attributes you want to use in your report, then there is an important step you should execute. If you miss the following steps, then you’ll get this error messsage “An error occurred during the local report processing. The definition of the report '/User Summary' is invalid.”. For some reason Visual Studio removes the “Fields” from the dataset. This basically is the mapping between the attributes returned by the query and the variables used in the report. You can verify that the list of Fields is empty by opening the Dataset detail form (click on the “…” next to the dataset pick list). On the Dataset detail form click “Fields”.



You can manually enter each of the fields and values, but you can also let Visual Studio regenerate this list. To generate this list you can click the “Refresh Fields” in the Dataset toolbar.


Clicking this toolbar button will ask you to define query parameters.



This screen doesn’t automatically fill the default values, but the values are available in the report though. You can find the values which you should fill in here on the “Report Parameters”. You can access these from the “Layout“ tab. Somewhere on the layout screen right click outside of the report somewhere on the yellow piece. This will give you a context menu where you can select the “Report Parameters”.



On the “Report Parameters” form you can select the property on the left side on the screen and at the right bottom you can find the default value for the selected property.



Copy the default value and past this into the “Parameter Value” for the “Parameter Name”.



Now click “OK” and the query is changed AND you can use the selected attributes in the report layout editor.

Changing the Layout
The layout can be changed on the “Layout” tab of the report. There are many possibilities to change the layout, but I won’t dive into the Reporting Services possibilities. I will just show how to add the field which is added to the query, but for more information you should look into Reporting Services trainings.
To add a field to the result table select the table and right click on the header. You’ll now get a context menu which will allow you to add a row group. This is the value you need to select to add a column. In the “Expression” field you should select your newly added attribute in this format “=Fields!address1_fax.Value”. For the label you could add a Textbox from the toolbox and change its properties to match the existing labels, but you could also just copy an existing label and change the text value.



Previewing the Report
To review the report, just click the “Preview” tab. If necessary you can go back to the Layout or Data tab to change the report. When you are ready, then save the report to an *.rdl file.



Updating the report in CRM
In Dynamics CRM, go back to the Reports area in the Workplace module. In the grid select the report which you have just modified. In the toolbar click on “Edit Report”.



On the Report detail form select the saved report definition file by using the browse button for the file location. Now press “Save” or “Save and Close” and your report is updated with your new definition file. From CRM you can now run the updated report.

Creating an activity report which includes the related people

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:

SELECT
activityid, activitytypecode, scheduledstart, subject, owneridname, statecodename,
regardingobjectidname,
(SELECT DBO.fn_PGGM_GetActivityPartyList(activityid, 1)) [to],
(SELECT DBO.fn_PGGM_GetActivityPartyList(activityid, 2)) [from],
(SELECT DBO.fn_PGGM_GetActivityPartyList(activityid, 5)) [required]
FROM
filteredactivitypointer

This query does select some default attributes and it adds the regarding, to, from and required fields. Add this query to the generation of a report and you'll be set to go.

Note: make sure that the function gets added to your database and assign the correct rights. See the post around the function for details.

Happy reporting!

Transform a table column into a CSV field in SQL Server

Imagine that you want to use a related table in your SQL query. You then must return only a single column or otherwise you'll get this SQL error:

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

What I like to do, is to transform the result table column into a single field separated by a ; sign. Earlier on I used to create a huge function which used cursors etc. Then a colleague of mine told me how to use C# code in SQL server which seemed to be a neater solution.

Today I have found a new approach. This uses a function again, but it is very simple. Here's the function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Ronald Lemmen
-- Create date: 4 July 2008
-- Description: Function to return the people in CSV format belonging to a activity based on the activity party type code
-- =============================================
CREATE FUNCTION fn_RL_GetActivityPartyList
(
@ActivityId uniqueidentifier,
@ActivityPartyType int
)
RETURNS nvarchar(3000)
AS
BEGIN
DECLARE @result nvarchar(3000)

SET @result = ''

SELECT @result = Coalesce(@result + ';', '') + partyidname from filteredactivityparty party where party.activityid = @ActivityId and party.participationtypemask = @ActivityPartyType

RETURN SUBSTRING(@result ,2, LEN(@result))
END
GO

The @result will get filled by the SELECT query with the values from the filteredactivityparty table separated by a ;. The first character is a ; as well and therefore I do remove this one in the RETURN statement.
After the + in the SELECT query you can place your own query. This example is just extremely useful in my next post :)

Make sure that you do grant access to the correct people in order to use this function. In the case of a CRM report, add the reporting group. See the below example, but make sure that you do change the guid to the guid which is valid for your system.

GRANT EXECUTE ON [dbo].[fn_PGGM_GetActivityPartyList] TO [PGGM-INTRA\ReportingGroup {05e0584a-1d94-424c-8014-c2f3b1b92ccc}]
GO

Happy reporting!

Tips on using the publishreports executable

Update: see the bottom for a solution to be able to use the filter in the report!

Some days ago I posted an article around how to move reports from a server to another server in the development process. Now I've been working with it, I can share some tips.

The first tip is on how to publish the reports. How does the publishreports.exe know where to find the publish.config and the reports? This wasn't completely clear to me on this page. It appears that you will need to go to the folder that you have created in the first step by using downloadreports.exe. Then you can do two things. Either call the publishreports executable on the server, by default on the location "C:\Program Files\Microsoft CRM\Reports\PublishReports.exe" or you can copy the file publishreports.exe and the file "Microsoft.CRM.Tools.Logging.dll" to the same folder and run publishreports from there. This will allow you to create an installer application because you know for sure that the publishreports executable is in your folder. You cannot be sure that the executable is in the c:\program files folder because thats a choice of the person installing CRM.

Furthermore you will notice that the publishreports will throw this error on your reports:

15:40:08 Error Publishing stopped due to following error
15:40:08 Error SRS soap exception: The report definition is not valid. Details: The element 'Report' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition' has invalid child element 'Custom' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'.
List of possible elements expected: 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Description http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Author http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:AutoRefresh http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:DataSources http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:DataSets http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Body http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:ReportParameters http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Code http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Width http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:PageHeader http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:PageFooter http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:PageHeight http://schemas/....

This error is caused due to the filter that is set in your report (by default set to last modified within the last 30 days). Each report has a layout like this:

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="CRMDEV30_MSCRM">
<ConnectionProperties>
<IntegratedSecurity></IntegratedSecurity>
<ConnectString></ConnectString>
<DataProvider></DataProvider>
</ConnectionProperties>
<rd:DataSourceID></rd:DataSourceID>
</DataSource>
</DataSources>
<PageWidth></PageWidth>
<rd:DrawGrid></rd:DrawGrid>
<InteractiveWidth></InteractiveWidth>
<rd:GridSpacing></rd:GridSpacing>
<rd:SnapToGrid></rd:SnapToGrid>
<Body>
</Body>
<rd:ReportID></rd:ReportID>
<DataSets>
<DataSet Name="CRMDEV30_MSCRM">
<Query>
<rd:UseGenericDesigner></rd:UseGenericDesigner>
<CommandText></CommandText>
<DataSourceName></DataSourceName>
</Query>
<Fields>
</Fields>
</DataSet>
</DataSets>
<Code>
</Code>
<Width></Width>
<InteractiveHeight></InteractiveHeight>
<Language></Language>
<PageHeight></PageHeight>
</Report>

In your report you will find one more node between the pageheight and the end of the report:
<Custom>
<MSCRM xmlns="mscrm"><ReportFilter><ReportEntity paramname="P1" displayname="Contacts"><fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"><entity name="contact"><all-attributes /><filter type="and"><condition attribute="modifiedon" operator="last-x-days" value="30"/></filter></entity></fetch></ReportEntity></ReportFilter></MSCRM> </Custom>

Remove this line from your report and you will be able to import the report. Keep in mind that you will have to set the default filter for your report afterwards!

Update:
One of my colleagues, Philipp Uihlein, has sent me an email regarding this blog post. He has found a way to keep the default filter in the definition! See his text for the details:

Hi Ronald,

I found out that the tool can handle the <Custom> tag when you add a namespace to the xml.

In the Account Summary.rdl for example I replaced the

<Custom> <MSCRM xmlns="mscrm">&lt;ReportFilter&gt;&lt;ReportEntity paramname="CRM_FilteredAccount"&gt;&lt;fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"&gt;&lt;entity name="account"&gt;&lt;all-attributes /&gt;&lt;filter type="and"&gt;&lt;condition attribute="modifiedon" operator="last-x-days" value="30" /&gt;&lt;/filter&gt;&lt;/entity&gt;&lt;/fetch&gt;&lt;/ReportEntity&gt;&lt;/ReportFilter&gt;</MSCRM> </Custom>

node with

<Custom xmlns="http://schemas.microsoft.com/crm/2006/WebServices" xmlns:mscrm="http://schemas.microsoft.com/crm/2006/WebServices"> <MSCRM xmlns="mscrm">&lt;ReportFilter&gt;&lt;ReportEntity paramname="CRM_FilteredAccount"&gt;&lt;fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"&gt;&lt;entity name="account"&gt;&lt;all-attributes /&gt;&lt;filter type="and"&gt;&lt;condition attribute="modifiedon" operator="last-x-days" value="30" /&gt;&lt;/filter&gt;&lt;/entity&gt;&lt;/fetch&gt;&lt;/ReportEntity&gt;&lt;/ReportFilter&gt;</MSCRM> </Custom>

and could publish the report using PublishReports.exe without problems.

Best regards,

Philipp

Copying CRM Reports

You will need to copy reports from a server to another server when you're working on a CRM project, right? From development to test, from test to staging, from staging to production. Usually I do add the reports to a release package and describe in the deployment manual how to deploy the reports and how to set the filter, categories etc.

Until today. Since today I will do this completely different. For a friend of mine I was searching for information on an error he receives with reporting:
An error has occurred during report processing. (rsProcessingAborted). Query execution failed for data set 'dsContracts'. (rsErrorExecutingCommand)
Unfortunately I did not find the anwer to that error, but I did land on this page: http://www.microsoft.com/dynamics/crm/using/deploy/changesrs.mspx

There is being described how reports can effectively be migrated from one deployment to another deployment! There are two executables which I overlooked: downloadreports.exe and publishreports.exe. By using these two executables you can download the reports and configuration settings for the reports and publish these reports and settings on another machine. That will shorten the deployment time a lot and make it less errorprone!

As you can see, everyday I learn something new. I hope this helps you as well.

Reporting services on another machine

The database server doesn't have to be running on the same server as the CRM application itself. If you follow the installation guide, then everything should work fine. But... Avanade has done this for several clients and sometimes it appears that the reporting might not work. The error message will be:

“An error has occurred. For more information, contact your system administrator”
or
"error 401 – Unauthorized access"

If this is the case in your situation as well, then take a look at the following steps.

First you have to enable Kerberos Authentication for IIS on the CRM Server.
- Open a DOS box and browse to c:\inetpub\adminscripts
- Run the command: cscript adsutil.vbs set w3svc/1/NTAuthenticationProviders "Negotiate,NTLM" (If only one website is running you can use 1 in the commandline. Otherwise you have to check the logfile directory to see what number you should use)
- IISRESET

If your services run with the "network service" account you have to open ADSIEDIT and open the propeties of the CRM server. Go to SPN settings and add the next lines:
(Computername is your CRM server and FQDN is your Fully Qualified Domain Name)

HOST/computername
HOST/computername.FQDN
HTTP/computername
HTTP/computername.FQDN

You have to do a similar action on the SQL Server, the default instance uses port 1433
MSSQLSvc/computername:1433
MSSQLSvc/computername.FQDN:1433

Finally, on the client computer check if in Internet Explorer "Integrated Windows Authentication" is activated (under the advanced settings of the internet options)

Hope this helps you to get reporting to work as well!

Reporting PreFilter

Right now I'm digging into reports more and more. I have encountered some troubles and here are some hands on experience tips.

Getting started
So... I want a report. What now?

First of all go to this url: http://www.microsoft.com/dynamics/crm/using/customizing/reporttutorial.mspx
You'll find a tutorial here named "Create a report in 15 minutes or less". This is the place to start.

Then take a look at the Report Writers Guide for additional information regarding creating CRM Reports.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/CrmSdk3_0/htm/v3d0reportwritersguide.asp

For information on how to make a report look like a crm report, look at the report style guide (pdf file in the sdk install file):
http://go.microsoft.com/fwlink/?LinkId=55779

Finally take a look at how to deal with report performance.
http://www.microsoft.com/dynamics/crm/using/customizing/reportperformance.mspx

If you want some more information on how to work with reporting services, take a look at the demo's "Introduction to SQL Server 2005 Reporting Services"
http://www.microsoft.com/technet/community/events/sql2005/SQL-06.mspx

After following those links you should be able to create great reports!

Then the real fun starts. You encounter issues. Here are some catches to think about:
- Creating a prefilter. By adding an alias to a table named CRMAF_Filtered[entityname], you can use prefiltering. A select query like "SELECT name FROM FilteredAccount AS CRMAF_FilteredAccount" is enough. This works both for reports built in VS2003 as well as VS2005.
If this doesnt work, then check that the DataProvider isn't set to OLEDB or something else. This needs to be SQL.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/ sqlserver/reporting/2003/10/reportdefinition" xmlns:rd="http://schemas.microsoft.com/ SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="CRMDEV30_MSCRM">
<ConnectionProperties>
<DataProvider>OLEDB</DataProvider>
</ConnectionProperties>
</DataSource Name="CRMDEV30_MSCRM">
</DataSources>
</Report>

- Showing the prefilter
Ok. Prefiltering works. Now we want to make the prefilter visible to the users. According to the Reporting Guide, you should add a parameter to the report with the name CRM_FilterText type string. Then you can add the value to a textbox using "Parameters!CRM_FilterText.Value". Indeed you can, but if the user selected about 10 pre filter options, then the list is unreadable. To solve this, you should add a line of code in which you replace the linefeed characters with carriagereturnlinefeed characters. Here's the code:

Public Shared Function Filter(ByVal strFilter As String) As String
Return (strFilter.Replace(vblf, vbCrLf))
End Function

Now you can add a value like this to your textbox:
= "Filter: " & vbCrLf & Code.Filter(Parameters!CRM_FilterText.Value)

- Printing landscape
The reports can be printed in landscape. Keep in mind this table:

	Height(in)	Width(in)	Height(cm)	Width(cm)
Letter	8.5	11	21.59	27.94
A4	8.26	11.69	21.00	29.70

When you want to print in landscape for European sized A4 papers, then fill in the corresponding values in the PageSize field of the report: 29,7cm;21cm. For an American letter use: 11in;8,5in. You can use cm or in just as you like.

Well. Thats about it for now.

If I encounter more tips I'll add them to this list. If you have hints to share, just comment and i'll update the post.

CrmException: Exception of type Microsoft.Crm.CrmException was thrown

Time for a new fix for a common problem.

When uploading a report you might get an error message. If this error message is:

NullReferenceException: Object reference not set to an instance of an object.]Microsoft.Crm.Reporting.SRSReport.convertDataSource(CrmContext context) +115Microsoft.Crm.Reporting.SRSReport..ctor(String name, String description, String xmlContent, String showin, String relatedentity, String category, CrmContext context, String filename, String origFilter) +136Microsoft.Crm.Reports.ReportCache.CreateSRSReport(String name, String description, String template, String showin, String relatedEntity, String category, String filename, Boolean isNewReport, String defaultFilter) +100Microsoft.Crm.Application.Platform.Report. InternalCreate(String xml) +629Microsoft.Crm.Application.Platform.Entity. Create() +109Microsoft.Crm.Application.Forms.AppForm.R aiseDataEvent(FormEventId eventId) +404Microsoft.Crm.Application.Forms.EndUserForm. Initialize(Entity entity) +56Microsoft.Crm.Application.Forms.EndUserForm. Execute(Entity entity) +13Microsoft.Crm.Web.Tools.ReportProperty. ReportPropertyPage.ConfigureForm() +202Microsoft.Crm.Application.Controls.AppPage. OnPreRender(EventArgs e) +30System.Web.UI.Control.PreRenderRecursiveInternal() +62System.Web.UI.Page.ProcessRequestMain() +1499

Then this post is not about your message. Please go to A Freaky Microsoft Dynamics CRM 3.0 Blog for a fix for this error.

Instead. If you get this message below, then read on for the solution:

[CrmException: Exception of type Microsoft.Crm.CrmException was thrown.] Microsoft.Crm.Application.Platform.Report. InternalCreate(String xml) +721 Microsoft.Crm.Application.Platform.Entity.Create() +109 Microsoft.Crm.Application.Forms.AppForm. RaiseDataEvent(FormEventId eventId) +408 Microsoft.Crm.Application.Forms.EndUserForm. Initialize(Entity entity) +57 Microsoft.Crm.Application.Forms.EndUserForm. Execute(Entity entity) +13 Microsoft.Crm.Web.Tools.ReportProperty. ReportPropertyPage.ConfigureForm() +202 Microsoft.Crm.Application.Controls.AppPage. OnPreRender(EventArgs e) +30 System.Web.UI.Control.PreRenderRecursiveInternal() +62 System.Web.UI.Page.ProcessRequestMain() +1499

I've been struggling with this error message myself and thanks to a posting on a newsgroup by LeeAC, I was able to upload my report. Here's the posting he made:

The issue lay with our RS permissions. In addition to failing to upload reports, we tried downloading them from CRM too. This gave us an NT permissions error. So, we opened up (localhost)/reports, navigated to the CRM datasource (typcially 'Organization_MSCRM), then properties, then security, and then added a user / group called NT AUTHORITY\NETWORK SERVICE, and gave them the permissions of CRM Publisher. After that it all worked fine.

Thanks

Lee

Quick create export functionality

Serveral of my customers are asking for an export functionality to Excel or CSV. Have you ever experienced that? If so, how did you create that?

Let me tell you my solution. MS CRM 3.0 does offer SQL Reporting Services for Reporting. The reports which are generated can be exported to Excel and CSV. With a little bit of tuning, you can even render a report into one of these formats, without requiring the user to press the export button. In this post I'll show you how this can be done.

Let's say you're using the Adventure Works Cycle demo. Then create a report in SRS (there's enough info on the web on how to do this) and upload this report to CRM. You can upload such a report to crm by going to the Workplace area, select Reports and click "New". Now you can access this report via CRM, but also directly. To open this report directly, go to this url (where you enter your own servername, report folder directory and report name):
http://[your server]/ReportServer/?/[your report folder]/[your report]&rs:Command=Render

Now with sending parameters, you can export this to CSV or Excel. Modify the attributes of the report to contain:
&rs:Command=Render&rs:Format=CSV
&rs:Command=Render&rs:Format=Excel

The problem with the CSV, is that default the separator is a comma. You might want to change this to semicolon (;) or tab. To change this to tab, use the format hereunder:
&rs:Command=Render&rs:Format=CSV&rc:FieldDelimiter=%09

Since we know now how to export data, we can also make this link available for users. Unfortunately the isv.config does not allow us to enter an url as describe above, but we can make a simple aspx, or even html, page which does redirect the user to that url. Just add the redirection page to the webserver and add a link in the isv.config. You have now made a very easy to modify export without coding!

Update:
Placing a code like that in the ISV.config.xml is not allowed due to the : in the query string. Instead you will need to direct your code to a intermediate page which will redirect you to the report. You can do this by using a simple html page with a meta refresh:

<html>
<head>
<meta http-equiv="refresh" content="0;URL=http://localhost/ReportServer/?/Adventure+Works+Cycle+Demo_MSCRM/Export&rs:Command=Render&rs:Format=CSV&rc:FieldDelimiter=%09" />
</head>
</html>

SRS and MSCRM (1.2) on 1 server

The people who have tried to install both SRS and MSCRM on the same server within the same website in IIS, they must know the message:

Server Error in '/Reports' Application.
---------------------------------------------------------------------------­-----

Configuration Error
Description: An error occurred during the processing of a configuration file
required to service this request. Please review the specific error details
below and modify your configuration file appropriately.

Parser Error Message: Assembly microsoft.crm.platform.types.dll security
permission grant set is incompatible between appdomains.

Source Error:

Line 5: <assemblies>
Line 6: <add assembly="Microsoft.Crm.Platform.ComProxy, Version=1.0.0000.0, Culture=neutral, PublicKeyToken=31bf3856ad34e35">
Line 7: <add assembly="Microsoft.Crm.Platform.Types, Version=1.2.3297.0, Culture=neutral, PublicKeyToken=31bf385ad364e35">
Line 8: </assemblies>
Line 9: </compilation>

In this case MSCRM is in the root of the website and reporting services in the folder "/Reports".

This can be solved by modifying the web.config of MSCRM. Add this code to the web.config just before the <appsettings>part of the file. Make that you modify the PublicKeyToken and if necessary also the versions!

<!-- Support for SQL Reporting services -->
<location path="Reportssrs">
<system.web>
<compilation debug="true" defaultlanguage="C#">
<assemblies>
<remove assembly="Microsoft.Crm.Platform.ComProxy, Version=1.0.0000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35">
<remove assembly="Microsoft.Crm.Platform.Types, Version=1.2.3297.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35">
</assemblies>
</compilation>
<pages enablesessionstate="true" validaterequest="false" enableviewstate="true">
</SYSTEM.WEB>
</location>

MS SQL Reporting Services Reports for MSCRM including basic security

Microsoft CRM is using Crystal Reports for reporting purposes. This works pretty fine as long as you do not want to modify reports or create your own reports. Crystal will then become very slow if you are working with thousands of records in your database. You could tweak Crystal, but instead, you could also take a look at MS SQL Reporting Services (SRS). Microsoft has released a report pack for MSCRM v1.2 which includes 6 reports. This report pack will give you a quick start on working with SRS Reports in MS CRM. But, when examining these reports, you will notice that the security rights which are set within MS CRM are completely gone when requesting these reports! In this posting I will show you how to create basic security for custom SRS reports.

The security in MSCRM is working with Security Descriptors. We cannot use these Security Descriptors in SRS. Therefore we will have to recreate the security settings. These settings are all stored in the database. Depending on how much security is required for your business, you can get the settings. In this example I will show you how to include settings on business unit level. Let's assume that you are working in an international company called Avanade. The headquarters are in the USA and there are several other locations in other countries including The Netherlands and France. In the end of the example, the users which reside in The Netherlands are able to see the Dutch accounts, the France users the France accounts and the headquarters will see all accounts.

For this example I do expect you to install Windows, Visual Studio, MSSQL and Reporting Services on your own. That shouldn't be too hard though :)

As soon as you have Reporting Services installed, we can start creating our report. We will do this in Visual Studio. There should be a new project type: "Business Intelligence Projects". From this project type we will select "Reports Project". Within this project we will chose to add a new report (Solution Explorer, right click on Reports and select "Add New Report"). Visual Studio will ask you to first create a Data Source. Please fill in a nice name, select SQL Server and enter your connection string. You will probably use this Data Source more often, so it would be useful to select the checkbox "Make this a shared Data Source".

Now its time for the real work. Let’s create the query. A user from the Netherlands should only see Dutch accounts. We will achieve this by using the userid. Based on this GUID we can find out in which Business Unit this person resides. If this is the top BU, then we should select all accounts, otherwise we select a filtered set of accounts. The first step is to select every account which resides in the same business unit as the user. This query will get us there.

DECLARE @owningBU UNIQUEIDENTIFIER
SELECT @owningBU = BusinessUnitId FROM SystemUserBase WHERE SystemUserId = @userid;

SELECT Name, Telephone1, Description
FROM Account
WHERE (OwningBusinessUnit = @owningBU)
ORDER BY Name

As you can see, I am using the view Account instead of the table AccountBase. This view has already the address data included, which saves me a lot of query coding.

As soon as you press enter after entering the query, you will get a lot of layout options. Currently I do not care too much about how the report looks, so let’s choose to finish this report. When viewing the report in the preview window, you should enter a guid of a user which does own some accounts. Just to be sure that you do get some results.

If you enter the userid of a user which resides in the USA Business Unit, then you will only find USA accounts. We can solve this by making sure that this query will also search for accounts which are in child business units. I have created a user defined (recursive) function which accepts a GUID and returns a table with all the child business units. Here is the function:

CREATE FUNCTION dbo.GetChildBU (@currentBU UNIQUEIDENTIFIER)
RETURNS @businessunitsTable TABLE(ID UNIQUEIDENTIFIER) AS
BEGIN
DECLARE @businessunitID UNIQUEIDENTIFIER
DECLARE BU_Cursor CURSOR FOR
SELECT BusinessUnitId FROM BusinessUnitBase WHERE ParentBusinessUnitId = @currentBU

OPEN BU_Cursor

/* Read current */
FETCH NEXT FROM BU_Cursor INTO @businessunitID

WHILE @@FETCH_STATUS = 0
BEGIN
/* Return current */
INSERT INTO @businessunitsTable
VALUES (@businessunitID)

/* Return children */
INSERT INTO @businessunitsTable
SELECT * FROM dbo.GetChildBU(@businessunitID)

/* Read next current */
FETCH NEXT FROM BU_Cursor INTO @businessunitID
END

RETURN
END

With this function in mind, you can modify your initial query to include these Business Units. This will be the result:

DECLARE @owningBU UNIQUEIDENTIFIER
SELECT @owningBU = BusinessUnitId FROM SystemUserBase WHERE SystemUserId = @userid;

SELECT Name, Telephone1, Description
FROM Account
WHERE (OwningBusinessUnit = @owningBU) OR OwningBusinessUnit IN (select * from GetChildBU (@owningBU))
ORDER BY Name

If you have added the function to the MSCRM database and modified the query for the report, then you should be able to see all the accounts in the users Business Unit and all the child Business Units.

OK, now the reports are fine, but where do we store them in CRM? It would not be logical to store some of the reports under the reports part in the Navigation bar and some modified reports in the Left pane or menu bar. You will probably want them to be in the same screen as the other reports. This you can solve by modifying the map_xml.aspx file in the Reports folder. You can copy and paste one of the other items and you will have a link to your reports. Just put them in a fancy -if possible looking like crm- datagrid and you have extended CRM with your own reports! Please keep in mind that the last modification is unfortunately not supported...

Happy reporting!