FetchXML Builder

Some time ago Microsoft organized the code contest. Users have been asked to share their greatest assets with the community. One of the winners is James Downey. He has created the FetchXML Builder: a free user-friendly tool for building and testing FetchXML statements.

Today I have been able to use this tool in my development project. It works very intuitive, at least for people having fetchxml knowledge. I would recommend everybody to download the tool and use it to reduce the time needed for figuring out how to set up the xml. Go to the CRM Sandbox download page and scroll to "FetchXML Builder".

James, thanks for sharing!

Convergence EMEA 2006

March this year I didn't make it to the Convergence in Texas. But I'll be attending the Convergence in Munich (6-8 November)! Let me know if I can meet you there. I'll assist the MS teams at the Community Lounge and Microsoft “Ask The Expert” Pavillion.

What is Convergence 2006 EMEA?
Convergence EMEA is the Microsoft Dynamics premier event, to bring customers, partners, team members and industry experts together to share ideas and knowledge. At the first annual Convergence EMEA, customers will learn how to leverage their Microsoft investment now and in the future. In addition, they will gain a stronger sense of community through interacting with industry leaders, Microsoft team members, partners and other customers.

Who should attend Convergence 2006 EMEA?
All Microsoft Dynamics customers, including those that use:
Microsoft Dynamics AX (formerly Microsoft Business Solutions Axapta)
Microsoft Dynamics CRM (formerly Microsoft CRM)
Microsoft Dynamics GP (formerly Microsoft Business Solutions Great Plains)
Microsoft Dynamics NAV (formerly Microsoft Business Solutions Navision)

With specific roles as:
Business Decision Makers
Technical Decision Makers
IT Professionals
Information Workers

Existing Microsoft Dynamics PartnersMicrosoft Certified and Gold Certified partners, especially those with the Business Solutions competency and the IW competency.

For more information:
http://www.mseventseurope.com/convergence

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.