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.