The Dreaded The report cannot be displayed. (rsProcessingAborted)

(0) Share

Report

Posted on by Pete_N

1,703

CRM 2016 & SQl 2016

for all testing purposes I am logging on to CRM and SQL as Domain Admin. I have also taken down the firewalls on both crm server and sql server for testing

CRM: I can run the default reports with no problem. When I try to run a custom report I get the error 'The report cannot be displayed (rsProcessingAborted)

SQL: if I go onto reporting services as the domain admin I can run the report with no issues

When looking in the reporting log I can see this error entry

webserver!ReportServer_0-1!3c8!08/08/2017-10:16:32:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'MainInvoice'. ---> Microsoft.Crm.Reporting.DataExtensionShim.Common.ReportExecutionException:
System.Data.SqlClient.SqlException: The SELECT permission was denied on the object 'new_transactionsets', database 'Operations_MSCRM', schema 'dbo'. ---> Microsoft.Crm.Reporting.DataExtensionShim.Common.ReportExecutionException: The SELECT permission was denied on the object 'new_transactionsets', database 'Operations_MSCRM', schema 'dbo'.

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

ashlega 34,477 on at

Like (0)

Report

Hi,

try using Filterednew_transactionsets view instead of the new_transactionsets?

Or, possibly, see what permissions you service account has in the database:

crmtipoftheday.com/.../reflecting-current-user-ssrs

Was this reply helpful? Yes No
Pete_N 1,703 on at

Like (0)

Report

Hi,

I have tried creating a less complex report that only accesses the CRM database

I tried usine filtered view but still get the same result. I thing the error about the select permission may be a red herring as i'm running crm as Domain Admin. When logging onto reporting services, as Domain Admin, I can run the report with no issues, likewise when creating the report in VS as a lesser user, I can run the report with no issues. The only issue occours when trying to run the report from CRM. In this case I have supplied a default value so am not even parming in variables from CRM. It just doesn't make sense to me.

processing!ReportServer_0-1!a88!08/08/2017-18:45:57:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: , Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'CBI018'. ---> Microsoft.Crm.Reporting.DataExtensionShim.Common.ReportExecutionException:
System.Data.SqlClient.SqlException: The SELECT permission was denied on the object 'new_bacsreportsprocessed', database 'Operations_MSCRM', schema 'dbo'. ---> Microsoft.Crm.Reporting.DataExtensionShim.Common.ReportExecutionException: The SELECT permission was denied on the object 'new_bacsreportsprocessed', database 'Operations_MSCRM', schema 'dbo'.
--- End of inner exception stack trace ---

If the 'user' doesn't have permission for select .. how can they run a default report from crm and that work ?

Was this reply helpful? Yes No
Aric Levin - MVP 30,190 Moderator on at

Like (0)

Report

Hi Pete,

The error that you are receiving still mentions that the select permission was denied for a Non-Filtered view.

Few questions to help you/us troubleshoot...

1. Can you possibly send the a copy of the query that you have in your dataset?

2. Try running the report as non-Domain Admin user. The domain admin is not that relevant, the question is if the user is the CRM Admin (System Administrator Security Role)?

3. Does the user running the report have permission to all the entities that are part of the report?

Thanks.

Was this reply helpful? Yes No
Pete_N 1,703 on at

Like (0)

Report

The code for the non filtered report
SELECT
   s.c.value('@user-number[1]' , 'VARCHAR(6)') as 'user-number',
   h.c.value('@user-name[1]' , 'VARCHAR(100)') as 'user-name',
   s.c.value('@record-type[1]' , 'VARCHAR(6)') as 'record-type',
   CONVERT( SMALLDATETIME,s.c.value('@effective-date[1]' , 'VARCHAR(20)'))as 'effective-date',
   s.c.value('@reference[1]' , 'VARCHAR(18)') as 'reference',
   s.c.value('@payer-name[1]' , 'VARCHAR(18)') as 'payer-name',
   s.c.value('@payer-account-number[1]' , 'VARCHAR(8)') as 'payer-account-number',
   s.c.value('@payer-sort-code[1]' , 'VARCHAR(6)') as 'payer-sort-code' ,
   CONVERT( SMALLDATETIME,s.c.value('@due-date[1]' , 'VARCHAR(20)'))as 'due-date',
   s.c.value('@payment-frequency[1]' , 'VARCHAR(10)') as 'payment-frequency',
   s.c.value('@amount-of-payment[1]' , 'VARCHAR(10)') as 'amount-of-payment',
   s.c.value('@reason-code[1]' , 'VARCHAR(2)') as 'reason-code',
   s.c.value('@payer-new-name[1]' , 'VARCHAR(18)') as 'payer-new-name' ,
   s.c.value('@payer-new-account-number[1]' , 'VARCHAR(18)') as 'payer-new-account-number' ,
   s.c.value('@payer-new-sort-code[1]' , 'VARCHAR(6)') as 'payer-new-sort-code',
   CONVERT( SMALLDATETIME,s.c.value('@new-due-date[1]' , 'VARCHAR(20)'))as 'new-due-date',
   s.c.value('@new-payment-frequency[1]' , 'VARCHAR(10)') as 'new-payment-frequency',
   s.c.value('@amount-of-new-payment[1]' , 'VARCHAR(10)') as 'amount-of-new-payment',
   CONVERT( SMALLDATETIME,h.c.value('@last-payment-date[1]' , 'VARCHAR(20)'))as 'last-payment-date',
   s.c.value('@work-code[1]' , 'VARCHAR(10)') as 'work-code',
   s.c.value('@aosn[1]' , 'VARCHAR(10)') as 'aosn'
   FROM (SELECT CAST([new_xmlfile] as XML) AS Xmlreport FROM [xxx_MSCRM].[dbo].[new_bacsreportsprocessed] WHERE CAST([new_bacsreportsprocessedid] AS VARCHAR(100)) = @ReportID) d
         CROSS APPLY Xmlreport.nodes('//Data/MessagingAdvices') AS a(c)
    OUTER APPLY a.c.nodes('MessagingHeader') h(c)
   OUTER APPLY a.c.nodes('MessagingAdvice') s(c)

I have tried with a non domain user and get the same result
The domain Admin and the non domain admin have full rights to the entities. If I call up reporting services under bot the users, I can run the report without any issues. It only seems to be when trying to run the report from CRM

Was this reply helpful? Yes No
ashlega 34,477 on at

Like (0)

Report

Hi,

do you have reporting extensions set up on the server?

technet.microsoft.com/.../hh699807.aspx

Was this reply helpful? Yes No
Pete_N 1,703 on at

Like (0)

Report

HI,

I do have the reporting extensions installed.

Was this reply helpful? Yes No
Suggested answer

Aric Levin - MVP 30,190 Moderator on at

Like (0)

Report

Hi Pete,

In your query, can you make the following change:

FROM (SELECT CAST([new_xmlfile] as XML) AS Xmlreport FROM [xxx_MSCRM].[dbo].[new_bacsreportsprocessed] WHERE CAST([new_bacsreportsprocessedid] AS VARCHAR(100)) = @ReportID) d

to

FROM (SELECT CAST([new_xmlfile] as XML) AS Xmlreport FROM [xxx_MSCRM].[dbo].[Filterednew_bacsreportsprocessed] WHERE CAST([new_bacsreportsprocessedid] AS VARCHAR(100)) = @ReportID) d

Was this reply helpful? Yes No
ashlega 34,477 on at

Like (0)

Report

And what about the CRM service account(check that link I sent earlier)? I am just curious if it would have helped if you gave that account dbo permissions in the organization database temporarily..

Was this reply helpful? Yes No
Pete_N 1,703 on at

Like (0)

Report

Thank you, that seemed to crack the problem. I had tried this from an earlier suggestion , but must have done something wrong. Thank you for all your help

Was this reply helpful? Yes No

Community site session details

The Dreaded The report cannot be displayed. (rsProcessingAborted)

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Featured topics

Product updates

Community site session details

The Dreaded The report cannot be displayed. (rsProcessingAborted)

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Featured topics

Product updates