Archive

Archive for March, 2009

CRM Resource Center – Updated RSS Feeds

March 18, 2009 Leave a comment

RSS feeds are available for the Resource Center, so you can stay informed of the latest articles, diagrams, and videos being published. To subscribe to the feed you’d like, simply click the link below and then click the “Subscribe to this feed” link on the page.

CRM 4.0 (On Premise) in English: http://rc.crm.dynamics.com/rc/regcont/en_us/op/feed.xml

CRM Online in English: http://rc.crm.dynamics.com/rc/regcont/en_us/live/feed.xml

This RSS feed is available in English only.

CRM 4.0 (On Premise) is available in 25 languages. Pick the language you prefer from the following table.

Language RSS URL
Arabic http://rc.crm.dynamics.com/rc/regcont/ar_sa/op/feed.xml
Brazilian Portuguese http://rc.crm.dynamics.com/rc/regcont/pt_br/op/feed.xml
Chinese (Hong Kong) http://rc.crm.dynamics.com/rc/regcont/zh_hk/op/feed.xml
Chinese (PRC) http://rc.crm.dynamics.com/rc/regcont/zh_cn/op/feed.xml
Chinese (Taiwan) http://rc.crm.dynamics.com/rc/regcont/zh_tw/op/feed.xml
Czech http://rc.crm.dynamics.com/rc/regcont/cs_cz/op/feed.xml
Danish http://rc.crm.dynamics.com/rc/regcont/da_dk/op/feed.xml
English http://rc.crm.dynamics.com/rc/regcont/en_us/op/feed.xml
Finnish http://rc.crm.dynamics.com/rc/regcont/fi_fi/op/feed.xml
French http://rc.crm.dynamics.com/rc/regcont/fr_fr/op/feed.xml
German http://rc.crm.dynamics.com/rc/regcont/de_de/op/feed.xml
Greek http://rc.crm.dynamics.com/rc/regcont/el_gr/op/feed.xml
Hebrew http://rc.crm.dynamics.com/rc/regcont/he_il/op/feed.xml
Hungarian http://rc.crm.dynamics.com/rc/regcont/hu_hu/op/feed.xml
Italian http://rc.crm.dynamics.com/rc/regcont/it_it/op/feed.xml
Japanese http://rc.crm.dynamics.com/rc/regcont/ja_jp/op/feed.xml
Korean http://rc.crm.dynamics.com/rc/regcont/ko_kr/op/feed.xml
Norwegian http://rc.crm.dynamics.com/rc/regcont/nb_no/op/feed.xml
Polish http://rc.crm.dynamics.com/rc/regcont/pl_pl/op/feed.xml
Portuguese http://rc.crm.dynamics.com/rc/regcont/pt_pt/op/feed.xml
Russian http://rc.crm.dynamics.com/rc/regcont/ru_ru/op/feed.xml
Swedish http://rc.crm.dynamics.com/rc/regcont/sv_se/op/feed.xml
Turkish http://rc.crm.dynamics.com/rc/regcont/tr_tr/op/feed.xml

Renee Wesberry

Advertisements

CRM and The Power of Choice

March 18, 2009 Leave a comment

The Microsoft Dynamics CRM – The Danish blog has run a three part series titled the Power of Choice.

Have you heard the concept before? ‘The Power of Choice’ – what is that? In this three part blog post I will give you my angle on the great opportunity for businesses to have IT in their own way – the best.

The first blog post is titled ‘What’ and will zoom in on what Microsoft means by ‘The Power of Choice’, more precisely what I mean and see as content in this area of wisdom.

The second blog post is titled ‘Why’ and will give you some USP’s (Unique Selling Points) for customers and partners, users, administrators and sponsors.

The third and final blog post is titled ‘How’ and will give you starters for your own business or for your customers. It will list some resources for trying out and if you are new to Microsoft Dynamics CRM in general, it will also point you to some places for trying out this great piece of business value extending and people performance accelerating software.

Cheers,

Martin Houlberg Jensen

Update Rollup 3 for Microsoft Dynamics CRM 4.0

March 18, 2009 Leave a comment

The Microsoft Dynamics CRM Sustained Engineering team released Microsoft Dynamics CRM 4.0 Update Rollup 3 on Thursday, March 12, 2009.

Below are the links to the release and related information about the Rollup. Please see the Knowledge Base (KB) article for more details about the Update Rollup 3 content and instructions.

Install Details about Update Rollup 3

 

  • Update Rollup 1 and Update Rollup 2 are not prerequisites for installing Update Rollup 3
  • The Update Rollup 3 client can be deployed before the server is upgraded to Update Rollup 3
  • Update Rollup 3 can be uninstalled
  • Incorporated installer feedback on Update Rollup 2, so now we don’t over write your modified web.config file. See http://support.microsoft.com/kb/968793 for details.

How to avoid a required reboot when installing a patch for the CRM Outlook Client

  • Before starting the update process, go to Options from the CRM menu. On the “General” tab, uncheck the bottom checkbox that says “Always run the…Host process” and then click “Ok” button.
  • When manually checking for updates, go ahead and close Outlook by choosing Exit from Outlook’s File menu. This will close Outlook along with the CRM add-in and the Hoster process.
  • Now check for updates by selecting All Programs from the Start menu. Then select Microsoft Dynamics CRM 4.0 and choose Update.
  • Restart Outlook after the patch is installed.

Note: If the user doesn’t want to make the permanent change of always having the Hoster process exit when Outlook exits, they can close the Hoster process manually after Outlook has been closed by right clicking on the Dynamics icon in the Notification Area of the Task Bar.

Making Update Rollup 3 available to your clients via AutoUpdate

You can find more information about AutoUpdate in Eric Newell’s blog entry at http://blogs.msdn.com/crm/archive/2008/05/08/crm-client-autoupdate.aspx and the Microsoft Dynamics CRM 4.0 Operating and Maintaining Guide, part of the Microsoft Dynamics CRM 4.0 Implementation Guide.

If you have a direct internet connection from your client machines, you can avoid some of the configuration steps and use the LinkId directly. Below are the necessary steps to configure the AutoUpdate for Update Rollup 3.

Note: These are steps 5, 6 and 7 of Eric’s blog.

The steps below are for the English version of the product. The PatchId and LinkId values will be different for every localized version of CRM 4.0. There is a table at the bottom of this post that lists the correct PatchId and LinkId for each language.

1. Create the configuration XML file and save it.

<ClientPatches>

   <Create>

      <!— *** UR3 PATCH –>

        <ClientPatchInfo>

<!— *** The PatchId is different for every Language. Please see the table at the end for correct Link ID to use –>

          <PatchId>{8B092580-482E-4EE3-98B1-28FB589B3012}<PatchId>

          <Title>Update Rollup 3 for Microsoft Dynamics CRM 4.0 (KB 961768)</Title>

          <Description>Update Rollup 3 for Microsoft Dynamics CRM 4.0 (KB 961768)</Description>

          <!— *** This will make it Mandatory –>

          <IsMandatory>true</IsMandatory>

          <IsEnabled>true</IsEnabled>

          <ClientType>OutlookLaptop, OutlookDesktop</ClientType>

<!— *** The LinkId is different for every Language. Please see the table at the end for correct Link ID to use –>

<!– & in xml documents must be escaped using &amp; –>

          <LinkId>145425&amp;clcid=0x409</LinkId>

      </ClientPatchInfo>

   </Create>

</ClientPatches>

2. From the command prompt, go to the directory where the ClientPatchConfigurator.exe is located ([ServerInstallDir]\Tools and type microsoft.crm.tools.clientpatchconfigurator.exe [configfile].xml

3. Once the patch has been uploaded, launch the Outlook client

The dialog should now appear saying that “Update Rollup 3 for Microsoft Dynamics CRM 4.0 (KB 961768)” is available. If the <IsMandatory> is set to false, the client will only see the update if the user selects “Check for Updates” via the CRM Menu in the Outlook client.

Link and Patch IDs for Update Rollup 3:

Arabic

LinkID: 145425&clcid=0x401

PatchID: {E7CEBD6B-0E64-4130-B85D-F3A6E140B70B}

Chinese (Simplified)

LinkID: 145425&clcid=0x804

PatchID: {F60413F2-ECE0-4CD0-8925-09FC20623FFD}

Chinese (Hong Kong)

LinkID: 145425&clcid=0xc04

PatchID: {DC9B0ECB-CEF9-48E4-846E-384BFB993166}

Chinese (Traditional)

LinkID: 145425&clcid=0x404

PatchID: {E80BD416-07D3-4EDD-B81B-63C5C82B5671}

Czech

LinkID: 145425&clcid=0x405

PatchID: {09F81FB4-4369-41E9-B62F-08A1D7A2A40D}

Danish

LinkID: 145425&clcid=0x406

PatchID: {EA701D64-67B6-4E81-943E-AA1B803FB961}

Dutch

LinkID: 145425&clcid=0x413

PatchID: {B60D0F28-6B7E-4B79-AF12-DAD12F5820AD}

English

LinkID: 145425&clcid=0x409

PatchID: {8B092580-482E-4EE3-98B1-28FB589B3012}

Finish

LinkID: 145425&clcid=0x40b

PatchID: {A4C13D7F-75F1-42CC-BA98-CD42A135554F}

French

LinkID: 145425&clcid=0x40c

PatchID: {ACDAF77D-0D53-4390-A547-174B66FC51EA}

German

LinkID: 145425&clcid=0x407

PatchID: {0A59EBEC-0870-409A-B751-713CEDF9B59F}

Greek

LinkID: 145425&clcid=0x408

PatchID: {70BD553C-0AC9-40F7-A72F-6EC96D95DD86}

Hebrew

LinkID: 145425&clcid=0x40d

PatchID: {05F2A180-E338-4FB3-B48A-4C06737E5365}

Hungarian

LinkID: 145425&clcid=0x40e

PatchID: {19CA36A3-3A60-484F-AFF1-3C56347A07A7}

Italian

LinkID: 145425&clcid=0x410

PatchID: {7C2556F1-285C-4470-A473-3C6334F36C5A}

Japanese

LinkID: 145425&clcid=0x411

PatchID: {633DE31B-158A-4923-8B27-B3825626E567}

Korean

LinkID: 145425&clcid=0x412

PatchID: {8A3A317B-0780-44AC-8023-37BCFDF3FDF2}

Norwegian

LinkID: 145425&clcid=0x414

PatchID: {824DC47E-7BF1-48BF-A29C-160702D5D02F}

Polish

LinkID: 145425&clcid=0x415

PatchID: {A6012D95-B878-41F3-A27E-40F753CCC0B9}

Portuguese (Brazil)

LinkID: 145425&clcid=0x416

PatchID: {6E78D992-34E4-4B97-94AF-FE60C923EB00}

Portuguese (Portugal)

LinkID: 145425&clcid=0x816

PatchID: {C0956419-3589-4E4C-BE3B-089E63CDD213}

Russian

LinkID: 145425&clcid=0x419

PatchID: {2AC4EAC6-B1E0-453B-A038-F01FBCCEB74E}

Spanish

LinkID: 145425&clcid=0xc0a

PatchID: {57C540B7-1D7C-4B7D-BB93-36F1991483B2}

Swedish

LinkID: 145425&clcid=0x41d

PatchID: {27563C10-CE6C-4028-B44E-9A5003BD95E2}

Turkish

LinkID: 145425&clcid=0x41f

PatchID: {755633BF-DE12-47A5-AD87-9265C6CA317F}

 

Cheers,

 

Matt Brown

Best Practices for CRM Memory Usage

March 18, 2009 1 comment

Are you trying to improve the memory usage of your .net application? I’ve spent some time recently trying to improve memory usage of our .net code. In this blog, I would like to share techniques for debugging memory issues as well as useful coding practices that would help.

.Net Framework does a great job of hiding the complexity of memory management from application developers. However, you will find that you need to get a deep understanding of how the .net garbage collector (GC) works in order to be able to understand and improve your application’s memory usage.

There are many excellent articles and resources on the web that you can use to learn about troubleshooting .net GC issues. I’ve included a few links that I found helpful at the end of the blog.

Overview

 

The articles listed at the end of the blog do a great job of explaining how the GC works and how to diagnose memory issues. I’d like to provide a quick overview of the process here and encourage you to read the reference articles for more details.

You will need to understand the types of objects being instantiated by your application. You can gather this data either by adding your own instrumentation to your application, or thru’ windbg/sos debugger commands, or by using profiler tools available from various vendors.

You will also need to understand what type of GC heap allocations your application is causing. Short-lived objects use the Gen-0 heap, long lived objects such as caches use Gen-2 heap and large objects (>80K bytes in size) use the large object heap (LOH). Excessive use of Gen-2 and LOH heaps is typically bad for GC performance since these heaps are garbage collected relatively infrequently.

The above data points will in turn help identify where you can focus your optimization efforts.

Best Practices to follow

 

Here, I’d like to talk a little bit about coding practices and patterns that we found helpful in improving our memory usage. The exact bottlenecks for your application might be different, but the following practices will definitely help.

1) Dispose objects – Improving Managed Code Performance has additional details on the Dispose/Finalize pattern. If your code is creating an instance of a type that is disposable (implements IDisposable), ensure your code is disposing the object (either by wrapping its usage inside an using statement, or by calling Dispose explicitly in a finally block).

Disposing objects correctly gives you a lot of benefits including:

     a. Ensuring scarce unmanaged resources such as sql connections, handles etc are released for reuse as early as possible.

     b. Prevents long buildups in the finalizer queue (which is processed by a single thread) and can improve performance of GC.

2) Use StringBuilder class – If you have code that concatenates dynamically generated strings (example: creating xml input/output), use StringBuilder class rather than direct string concatenation. In addition to reducing the number of allocations, it will also help reduce the number of large object allocations (which are very expensive) if you are dealing with large xml strings for example.

3) Refactor web services into smaller,standalone dlls – If you have asmx style web services ([WebMethod] entrypoints), it is common to have the code that surfaces the webmethods in the same dll as the underlying implementation/business logic. If you expose a lot of web methods, this assembly can get pretty large.

We were noticing that for many incoming web requests, the asp.net runtime was allocating large byte[] (rooted under an instance of System.Security.Policy.Evidence class). The contents of the byte[] match the contents of our web service assembly (which was pretty big). Under load, this was causing heavy use of the Large Object Heap (LOH) and the associated side-effects.

Luckily, there is an easy workaround. The workaround is to refactor the code such that the webservice methods are in smaller, standalone dlls and seperate the implementation into other dlls. The following article also discusses a similar issue/workaround.

4) Initialize dictionaries/hashtables to reasonable sizes – If your code maintains dictionaries/hash tables with a large number of entries, pay attention to the initial size of the dictionary when you are instantiating it. As you add items into the dictionary, the dictionary has to be resized once its capacity is exceeded.

The resizing algorithm used by the .net classes is fairly sophisticated (doubles the current size and adjusts it to nearest prime number), however each resize will trigger a copy of the dictionary. This can get expensive if a resize is triggered on a dictionary with a large number of entries since it means an allocation on the LOH.

5) XmlSerializer Usage – If your code uses XmlSerializer class, be very careful on the constuctors you are using to instantiate the instances. Some constructores result in a new temporary assembly getting generated for each instance and you will easily run into out of memory situations due to the assembly leak. The MSDN documentation of XmlSerializer has more information on this. You can workaround this by maintaining your own cache of the xmlserializer instances.

If you have a favorite tip or technique related to optimizing .net memory usage, I’d welcome you to post a comment.

Useful Articles about .Net GC

Cheers,

Jagan Peri

Support for COUNT(DISTINCT) in Fetch XML queries

March 18, 2009 1 comment

We have made a couple of enhancements to the aggregate query feature of Fetch XML. These updates have shipped for version 4.0 as part of UR2 and for version 3.0 as documented in KB Article 960079. So far we only supported the count aggregate function which essentially did a count(*) on the base table of the entity.

We have enhanced this functionality to add a new aggregate operation countcolumn which now performs the SQL count operation for a given column. It also support the distinct keyword which only counts unique columns. If the distinct keyword is not specified with a countcolumn aggregate, its value defaults to false.

Here is a table that summarizes the Fetch aggregate types and its equivalent SQL translation:

CRM Fetch XML SQL

<attribute name=’address1_city’ aggregate=’count’ alias=’citycount’ />

SELECT COUNT(*) AS ‘citycount’ FROM Account

<attribute name=’address1_city’ aggregate=’countcolumn’ alias=’citycount’ />

SELECT COUNT(address1_city) AS ‘citycount’ FROM Account

<attribute name=’address1_city’ aggregate=’countcolumn’ alias=’citycount’ distinct=’true’ />

SELECT COUNT(DISTINCT address1_city) AS ‘citycount’ FROM Account

                                                     Key: Existing Feature, New Feature

Here are a couple of examples for using these features:

Query to return a count of all Accounts which have a county specified in their address.

   1: Query to return a count of all Accounts which have a county specified in their address.
   2: <fetch aggregate='true'>
   3:     <entity name='account'>
   4:         <attribute name='address1_county' aggregate='countcolumn' alias='countyAccounts' />
   5:     </entity>
   6: </fetch>

Query to return a count of states in which we have an account. This uses the distinct keyword which counts a state only once.

   1: <fetch aggregate='true'>
   2:     <entity name='account'>
   3:         <attribute name='address1_stateorprovince' aggregate='countcolumn' alias='countStates' distinct='true' />
   4:     </entity>
   5: </fetch>

Thanks

Noor Merchant

Announcing SDK Update v4.0.8

March 18, 2009 Leave a comment

We’ve just published version 4.0.8 of the CRM SDK! The online content is here and the download is here. Here’s what you’ll find in this release:

New Topics and Samples for Authentication with CRM Online

Read three new topics about using Windows Live ID to authenticate with CRM Online. A detailed walkthrough is provided for the Server-to-Server scenario along with helper code to make this easier. Samples for other online auth scenarios will be coming in the next update. Also new in this are updated assemblies for CRM Online. (Yes, we know the product name isn’t “Live” but we didn’t want to risk breaking your projects by changing the folder names.) Look here for the new information:

SDK\Server\Helpers\CS | VB\CrmOnlineAuth

SDK\bin\Live

SDK\bin\64bit\Live

Microsoft Dynamics CRM Online Authentication

Walkthrough: Server-to-Server Authentication with CRM Online

WindowsLiveIdTicketAcquirer Helper Code

Updated Support Statement

We’ve made it clear that Ajax is supported: Support for the .NET Framework Versions

Updated Best Practices for Custom Code

Updated information about where you should put custom code. Read about this here: ISV Extensibility Best Practices

Added the ISV Readiness Classes

We’ve added the IsvReadiness helper classes, which give you a programmatic means to change parts of the Microsoft Dynamics CRM customization XML files. Read about them here: Microsoft.Crm.Sdk.IsvReadiness Classes

Plug-in Updates

We’ve updated the Plug-in Registration tool to version 2.2. Refer to the included Readme file for more information about software updates and new features.

Added new information about when plug-ins and workflows are executed in the pipeline. Read about this here: Event Execution Pipeline

Clarified the information about child pipelines and added a list of messages that a child pipeline can process: Parent and Child Pipelines

New Data Binding Sample

We’ve added a new project that demonstrates data binding an ASPX DataGrid control with CRM data. You can find this in the download package here: SDK\Server\FullSample\DataBinding\CS | VB\WebFormSample

Upgrade Issues

Added a table of state changes that are handled differently for plug-ins that were upgraded from Microsoft Dynamics CRM 3.0 callouts: Upgrade Issues for Workflow and Callouts

Added a topic that shows the differences between Microsoft Dynamics CRM 3.0 code and Microsoft Dynamics CRM 4.0 code: Upgrading Your Existing Code

Other New Samples

We’ve added new samples for some areas and converted others to Visual Basic. Here’s a list of the new samples in the docs:

Errata

  1. We’ve clarified where you can find the EmailProviders assembly in: About the Assemblies
  2. The metadata browser only works for the on-premise product: Using the Metadata Browser
  3. Updated sample code to use correct date manipulation methods in: How To Convert a Fax to a Task
  4. We made an error in the Release History table on the first page of the SDK. It incorrectly states that the Import/Export samples were removed. You can ignore this!

Feedback

We love to hear from you! Send us your thoughts, requests, questions or bugs. Send Feedback.

Cheers,

Amy Langlois

Microsoft Dynamics CRM Pre-Filtering Tips

March 18, 2009 Leave a comment

CRM Pre-Filtering is a very useful option that can be enabled on CRM reports to make them context sensitive and to allow the report to be filtered using the Advanced Find functionality.  Although this is a great feature, it is often an area that is not fully understood which can lead to someone encountering unexpected results.

How is it Enabled?

Automatic Prefiltering (CRMAF_)

There are 2 ways to enable the CRM Pre-Filtering functionality. The easiest option is the CRMAF_ method which simply requires aliasing the filtered views with a name that starts with “CRMAF_”.   A query such as “Select name from FilteredAccount” can simply be changed to “Select name from FilteredAccount as CRMAF_Account”.  Aliasing the Filtered View with a prefix of CRMAF_ will allow CRM to recognize that you would like to enable this entity for pre-filtering.

When you enable the CRM Pre-filtering functionality using the CRMAF_ method, CRM will take a query such as the following and modify it when it is uploaded into CRM:

SELECT name, accountnumber
FROM FilteredAccount as CRMAF_Account

Becomes:

SELECT name, accountnumber
FROM (@P1) as CRMAF_Account

Then CRM will pass a query to the P1 parameter depending on how the report is being filtered. For example: If you are running the report from the Reports area and use the Pre-filtering functionality to filter to only show Accounts that are Active, the resulting query would be something like:

SELECT name, accountnumber
FROM (select FilteredAccount.* from FilteredAccount where statecode = 0) as CRMAF_Account

If you are within a specific Account and run the report, the resulting query would be something like:

SELECT name, accountnumber
FROM (select FilteredAccount.* from FilteredAccount where AccountId = ‘<CurrentAccountId>’) as CRMAF_Account

When you are looking at a list of Accounts with 3 selected and choose the option to run the report against the selected records, the resulting query would be something like:

SELECT name, accountnumber
FROM (select FilteredAccount.* from FilteredAccount where AccountId in (‘<1stAccountId>’, ‘<2ndAccountId>’, ‘<3rdAccountId>’) as CRMAF_Account

Explicit Filtering

The CRMAF_ method works in most cases but for complex queries such as queries using UNION’s, you may need to use the 2nd option referred to as Explicit Filtering. The CRM SDK contains information about how to use both methods under the Report Writers Guide section:

http://www.microsoft.com/downloads/details.aspx?FamilyID=9c178b68-3a06-4898-bc83-bd14b74308c5&DisplayLang=en

Under the Writing Reporting Services Reports section, you can reference the Using Filters in a Report section.  Another posting to the CRM Team Blog contained a great explanation of how CRM Pre-Filtering is enabled: Inside Report Prefiltering.

A potential problem is that when you use the CRMAF_ method, CRM needs to add the parameter for you as opposed to you creating the parameter yourself when using Explicit Filtering. With a more complex query such as a query using UNION statements, this can lead to unexpected results as CRM may only add the parameter to the first query.

For example, suppose you had a query such as the following:

SELECT name, accountnumber
FROM FilteredAccount as CRMAF_Account where address1_stateorprovince = ‘FL’
UNION
SELECT name, accountnumber
FROM filteredAccount as CRMAF_Account where address1_stateorprovince = ‘CA’

When you upload the report, CRM may just filter the first query using the parameter which would cause the CRM filtering to not be applied to the second query causing unexpected results:

SELECT name, accountnumber
FROM (@P1) as CRMAF_Account where address1_stateorprovince = ‘FL’
UNION
SELECT name, accountnumber
FROM FilteredAccount as CRMAF_Account where address1_stateorprovince = ‘CA’

For example:

In the scenario above, running the report from the Reports area and choosing to filter where Annual Revenue > 1,000,000, the resulting query would be something like:

SELECT name, accountnumber
FROM (select FilteredAccount.* from FilteredAccount where AnnualRevenue > 1000000) as CRMAF_FilteredAccount where address1_stateorprovince = ‘FL’
UNION
SELECT name, accountnumber
FROM FilteredAccount as CRMAF_Account where address1_stateorprovince = ‘CA’

This would mean it would return any Accounts in Florida with a Annual Revenue of  $1,000,000 or any Account in California (not just ones over $1,000,000). If you download the report from CRM and open it in Visual Studio, you will see the original version of the report that you uploaded into CRM.  If you download the report from SRS directly, you would see that CRM had modified the query but did not place the parameter where you wanted it to exist:

SELECT name, accountnumber
FROM (@P1) as CRMAF_FilteredAccount where address1_stateorprovince = ‘FL’
UNION
SELECT name, accountnumber
FROM (@P1) as CRMAF_FilteredAccount where address1_stateorprovince = ‘CA’

To resolve this issue you can use Explicit filtering. This requires you to create the parameter yourself but you are able to place it anywhere in your query you want. You need to use dynamic SQL to do this as shown in the CRM SDK. Instead of a query such as the following:

select name, accountnumber from FilteredAccount as CRMAF_Account
You would create a parameter called CRM_FilteredAccount and the query would be:
DECLARE @SQL nvarchar(4000)
SET @SQL = ‘
SELECT name, accountnumber FROM (‘+@CRM_FilteredAccount+’) AS FA’
EXEC (@SQL)

If you are looking for more examples of Explicit Filtering, you can look at any of the standard CRM reports.  All of the standard CRM reports use the Explicit Filtering method. 

Common Issues Related to CRM Pre-Filtering

Reports displaying less data than expected

Potential Cause 1

   – A default filter is enabled (ex. Modified in the Last 30 Days).  You can view the Default Filter by selecting a report in the Reports area and clicking Edit Default Filter from the More Actions menu.  Try clearing the default filter to see if the issue no longer occurs.  When an entity is enabled for CRM Pre-Filtering, a default filter is enabled to only display records for that entity that have a Modified Date within the last 30 days.

Potential Cause 2

   – A default filter is enabled on an entity that is not available for Advanced Find.  Do not enable CRM Pre-Filtering on an entity that is not available for Advanced Find as a default filter will be enabled (Modified in the Last 30 Days) but it will not appear in the user interface.  For Example:  If you are creating a report that queries Opportunities and Opportunity Products, do not enable Pre-Filtering on the Opportunity Product entity as it is not available for Advanced Find.  This can also occur if you created a report using the Report Wizard without the Update Rollup 2 being applied to the CRM server.  A hotfix was created to address that issue:

           -Only records that were changed in the past 30 days are displayed for the related record type when you create a report by using the Report Wizard in Microsoft Dynamics CRM 4.0. Read more at http://support.microsoft.com/kb/956079.

Reports displaying more data than expected

The CRMAF_ method is being used but Explicit Filtering is necessary to correctly enable CRM Pre-Filtering (see Explicit Filtering under the How is it Enabled section under the CRM Pre-Filtering topic).  Try the Explicit Filtering option.

Thanks,

Dana Martens