While working with CRM Reports, we faced a scenario where data was not getting retrieved from FilteredViews in our IDE. Interestingly same queries were returning data from CRM tables but nothing from FilteredViews.
E.g. “Select * from SalesOrderDetail” table retrieved rows
“Select * from FilteredSalesOrderDetail” retrieved no rows
We made several checks including:
- Report connection/data sources
- User rights in CRM
- Using the right DB and Organization
These checks yielded no luck. Eventually we found the actual cause.
Filtered views will never return records from a connection string using SQL authentication
Use Windows authentication in connection string properties for all CRM reports.