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.

crm2011

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

Authentication

Use Windows authentication in connection string properties for all CRM reports.

Avatar for Joe D365

Joe D365

Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.