CRM 2011/CRM 2013 Filtered View Return No Data

The only way to do SQL Query in your CRM OnPremise Report is by using FIlteredView, not direct access to the SQL Standard View or SQL Table.
But, I have experienced the Problem, here is my case.

1. If I run the report (using Filtered View Query) from the VS BIDS or Report Builder, it works.



2. Then I run the report through CRM Web, it did not work, it did not return any data nor error.



3. I try to run through the http://reportservername/reports and it is ok.



I already search to the Google and try all of the possible solution.
This link is giving me inspiration : http://anythingcrm.blogspot.com/2013/06/crm-2011-ssrs-custom-report-query.html
But, I already checked every possible solution :

Solution

Add the user to "PrivReportingGroup" group in your Active Directory.


Other things to check 

• Make sure SSRS report’s "Data Source" connection is using Windows Authentication.
• “MSCRM_DataSource” should be used as data source in SSRS (shared) and should be using Windows Authentication.
• Find “ReportingGroup” under SQL security and add this group as dbReader on CRM database.

And still no result.

I also restart the Services also no result.
I was thinking to Reinstall the Report Extension, but I was afraid it will request server restart, which is Production Server.

Using the CRM Filtered View means that you can exactly filter the data by the security based on the CRM Security Platform, when a user access the report and no data, it means that they don’t have the access to view the data.

But, I am Administrator, how can I have no data.
So, I am pretty sure that there is a problem in the connection string and the authentication, cannot recognize the CRM User Id outside the Report Server running.

Then, I try to debug,

1. I put a text box in the report to get the SystemUserId of the Caller
using the DataSet query :

SELECT        dbo.fn_FindUserGuid() AS CRMcalleruserid

2. Go Run the report from BIDS



I know that it can get the Caller ID

3. Go Run the report through CRM Web, I can know that the report cannot get the Caller ID



It seems like the report cannot detect the System User Id of the Caller and so that the Filtered View is not accessible since the report cannot validate and authenticate the Caller.

4. Run Report through Report Manager



Yes, it can detect the System User Id.

So, it is clearer that I need to make sure the custom report get the correct Connection String.

Finally, here is my Resolution, I have to manually check and change the Data Source to proper Connection String.

Resolution:

1. Go to Report Manager [http://reportservername/reports]

2. Go to the Organization_MSCRM –> Custom Reports



3. Select the Custom Reports and click Manage



4. Then I know that the Data Source is using Microsoft SQL Server not using the MSCRM Connection String



5. Change to : A Shared Data Source



6. Choose the Location to “Home –> Organization_MSCRM –> CustomReports –> MSCRM_DataSource”



7. Then don’t forget to apply



8. If after this step you still cannot produce the report data, go to the Data Source in the Organization_MSCRM



And check whether the Connection String is using correct Microsoft Dynamics CRM SQL or still SQL Server.

9. Change to this :



Keep the Name : MSCRM_DataSource
Data Source Type : Microsoft Dynamics CRM SQL
Connection string: MSCRM Data Connector Connection String
Connect using : Credentials supplied by the user running the report

10. Now test and Run the Report



And luckily it works.

Hope it helps!