Tuesday, 3 June 2014

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.

image

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

SNAGHTML958084a

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

image

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
image


2. Go Run the report from BIDS

image

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

SNAGHTML9838eaf

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

SNAGHTML98740da

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

SNAGHTML961057f

3. Select the Custom Reports and click Manage

SNAGHTML962f0d5

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

image

5. Change to : A Shared Data Source

SNAGHTML968a78a

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

SNAGHTML96821cf

7. Then don’t forget to apply

SNAGHTML96a34b2

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

SNAGHTML96ed6f9

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

9. Change to this :

image

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

SNAGHTML979b88e

And luckily it works.

Hope it helps!

2 comments:

  1. Aileen,

    Very informative article and options to try when things do go wrong. I have a situation where we want to generate a small SSRS report from a Plugin (Isolation Mode = None). Plugin fails when executing Render statement of the Reporting Service. The same technique works when executing from a Custom Workflow activity. Am I missing some security permissions or incomplete registration of SetSPNs. Thanks for your help.

    ReplyDelete

My Name is..