Scenario: I have a CRM 4.0 environment and have installed the SSRS Data Connector. I also created a report that uses filtered views and uploaded the report to CRM. I now created a custom user in Active Directory, added the user to CRM and gave the user the CRM System Administrator role.
In SQL Server Reporting Services, I scheduled the report to run as the custom user I had created earlier and noticed the report ran but returned no data. The same report when run with my credentials returned data.
After validating that the CRM role and SQL credentials for the custom user were the same as mine, I gave the report one more try but still no data. After additional researching and digging around and with the help of the genius minds around me, we finally found the issue:
The report I created used Filtered Views and returned pick-list text values. In CRM 4.0, Filtered Views when joining to the StringMap table, not only join to the attribute value, attribute text and objecttypecode but also to the Language Id as given below.
//Snippet of the FROM Clause of the Account Filtered View
SELECT * FROM dbo.Account a
LEFT OUTER JOIN dbo.SystemUserBase AS u
ON u.SystemUserId = dbo.fn_FindUserGuid()
AND u.IsDisabled = 0
LEFT OUTER JOIN dbo.UserSettingsBase AS us
ON us.SystemUserId = u.SystemUserId
LEFT OUTER JOIN dbo.StringMap AS sm
ON sm.AttributeName = 'accountcategorycode'
AND sm.ObjectTypeCode = 1
AND sm.AttributeValue = a.AccountCategoryCode
AND sm.LangId = us.UILanguageId
The above statement returned no data as the UserSettingsBase table row for my custom user had Language Id set to 0(default) while the StringMap wanted 1033 as the Language Id.
The Language Id is one of the values that get’s set the first time the user logs into CRM and since my custom user had never logged into CRM, that value was never updated to 1033.
Once I logged in as the custom user, Voila – the report started to display data.
