There is an automated way of binding the current record context to reports in CRM using “magic” prefix CRMAF_ in CRM 4.0. In theory it should work like a charm, and in most of the times it does. But there are some pitfalls and CRMAF prefix appears to be ignored.  I personally prefer working in Visual Studio (Microsoft Business Intelligence Studio), so the article refers to this environment.

1. How to use CRMAF prefix binding.

• create a custom report
• create a well-formed SQL using CRMAF_ prefix
• deploy it to CRM and binding the context of single record should happen automatically.

2. Common problems

• no rows are returned
• not an expected row is returned
• other

3. Best practice

1. Check if SQLquery  is well formed. Always use filtered views if possible – you keep the user rights context correct. It should read similar to this (you can use exactly this one for testing):

   SELECT accountid, name FROM FilteredAccount
   AS CRMAF_FilteredAccount

2. Check if the SQL query returns expected results. Make sure there are records in corresponding table(s) e.g. Accounts. Copy and paste SQL query into SQL Server Management Studio and execute the query against your CRM DB (e.g. MicrosoftCRM_MSCRM).
3. Drag and drop several fields to Report Layout Pane and preview the report. Visual Studio does not provide any context binding in Preview so you always get the first record from the query results. You may try passing a condition into SQL query. Paste in the GUID for your account (e.g. retrieved from DB) and pressing the button View Report will display the report for corresponding account. Just remember to remove it when done with testing!

   SELECT accountid, name FROM FilteredAccount
   AS CRMAF_FilteredAccount
   WHERE
   accountid = 'D54D44AD-36D0-DC11-AA32-0003FF33509E'

4. When uploading the report to CRM make sure you selected:
   • Existing File for report type
   • File location is correct to your custom report
   • Categories input box is filled in with corresponding report area
   • Related Record Types input box is set to appropriate record: e.g. Accounts
   • Display In input box is filled in with Forms for related record types as well – so that the report is displayed in the context of the entity record if that is what you want.
   • Language pick appropriate one
5. Save and test on a given entity

4. Tricks – when CRMAF prefix is not working

Of course there are reports much more complicated than the example above and using CRMAF gets tricky when using advanced SQL queries. Following example is definitely not the most sophisticated one but in general it covers the principle.

SELECT     name, 'do no email' AS Expr1, donotemailname
FROM         FilteredAccount AS CRMAF_FilteredAccount
UNION
SELECT     name, 'do not fax' AS Expr1, donotfaxname
FROM         FilteredAccount AS CRMAF_FilteredAccount

Union in query breaks Visual Studio Query design feature – you cannot see and manipulate tables in visual manner – clicking and selecting columns in design pane. Alright it doesn’t matter that much. What is worse – is that you might expect binding to be working the same way it did for single query. Not really. It returns all the account records not just the one that should treated in context… What?!

Brief look at the actual SQL issued by CRM against the DB shows that in case of a single select (As designed in Visual Studio)

Query as designed:

SELECT     name, 'do no email' AS Expr1, donotemailname
FROM         FilteredAccount AS CRMAF_FilteredAccount

it looks like this (As actual query against DB – binding is visible):

Query as interpreted:

SELECT     name, 'do no email' AS Expr1, donotemailname
FROM
(SELECT account0.* FROM FilteredAccount AS account0
  WHERE
(account0.accountid = N'{D14D44AD-36D0-DC11-AA32-0003FF33509E}'))
AS CRMAF_FilteredAccount

Whereas UNION, or better to say multiple instances of magic CRMAF_ binding formula are just matched once and ignored later as seen here:

Query as designed:

SELECT     name, 'do no email' AS Expr1, donotemailname
FROM         FilteredAccount AS CRMAF_FilteredAccount
UNION
SELECT     name, 'do not fax' AS Expr1, donotfaxname
FROM         FilteredAccount AS CRMAF_FilteredAccount

Query as interpreted:

SELECT     name, 'do no email' AS Expr1, donotemailname
FROM (SELECT account0.* FROM FilteredAccount AS account0 WHERE
(account0.accountid = N'{D14D44AD-36D0-DC11-AA32-0003FF33509E}'))
AS CRMAF_FilteredAccount
UNION
SELECT     name, 'do not fax' AS Expr1, donotfaxname
FROM         FilteredAccount AS CRMAF_FilteredAccount

If you think you can cheat it with something simlar to this, forget it, it is ignored completely…:

SELECT name, Expr1, donotemailname
FROM
(SELECT name, 'do no email' AS Expr1, donotemailname
    FROM FilteredAccount
 UNION
 SELECT name, 'do not fax' AS Expr1, donotfaxname
    FROM FilteredAccount AS FilteredAccount_1
)
AS CRMAF_FilteredAccount

What should you do then? Break it into pieces!.

1. Make one simple dataset where you retrieve only accountid similar to 1st example. Be sure it is the first one in datasets order. You can modify the XML code of the report to do that if you happen to have the previus dataset in place already.

SELECT accountid, name FROM FilteredAccount
AS CRMAF_FilteredAccount

2. Add new report parameter e.g. CRM_accountid and set its default value to the dataset / valuefield of the dataset / valuefield from step 1

3. Create (Modify) the 2nd dataset with the “complicated” query and use the parameter inside:

SELECT name, Expr1, donotemailname
FROM
(SELECT accountid, name, 'do no email' AS Expr1, donotemailname
 FROM FilteredAccount
 UNION
 SELECT accountid, name, 'do not fax' AS Expr1, donotfaxname
 FROM FilteredAccount AS FilteredAccount_1) AS fac
WHERE (accountid = @CRM_accountid)

4. Enjoy