Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Custom SSRS report ERR...
Microsoft Dynamics CRM (Archived)

Custom SSRS report ERROR when more that 20 records

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hello.

I have a custom SQL report with pre-filtering.

The problem I have now is that the report it's not working with more that 20 records. the Error i get is "Reporting Error: The report cannot be displayed. (rsProcessingAborded). "

How can i resolve this?

SELECT 	  
	 CRMAF_Filteredbs_mops.bs_mopsid AS 'MOPS ID'
	,CRMAF_Filteredbs_mops.bs_statusmopsname AS 'Status (MOPS)'
	,(select t.bs_location from dbo.Filteredbs_pop t where t.bs_mops = CRMAF_Filteredbs_mops.bs_mopsid) AS 'Location (Core Machine)'
	,(select t.bs_location from dbo.Filteredbs_energydivision t where t.bs_mops = CRMAF_Filteredbs_mops.bs_mopsid) AS 'Location (Energy Division)'
	,(select t.bs_location from dbo.Filteredbs_usedcoremachine t where t.bs_mops = CRMAF_Filteredbs_mops.bs_mopsid) AS 'Location (Used Core Machine)'
	,(select t.bs_location from dbo.Filteredbs_usedenergydivision t where t.bs_mops = CRMAF_Filteredbs_mops.bs_mopsid) AS 'Location (Used Energy Division)'
	,CRMAF_Filteredbs_mops.bs_date AS 'Date'
	,CRMAF_Filteredbs_mops.bs_accountname AS 'Account'
	,CRMAF_Filteredbs_mops.bs_cciccodename AS 'CCIC Code'
	,CRMAF_Filteredbs_mops.bs_cciccategoryname AS 'CCIC Category'
	,(select a.bs_industryname from dbo.FilteredAccount a where a.accountid = CRMAF_Filteredbs_mops.bs_account) AS 'Industry (Account)'
	,CRMAF_Filteredbs_mops.bs_pwcname AS 'PWC'
	,CRMAF_Filteredbs_mops.bs_pwcsegmentname AS 'PWC Segment'
	,CRMAF_Filteredbs_mops.bs_saletypename AS 'Sale Type'
	,CRMAF_Filteredbs_mops.bs_salestypename AS 'Sales Type'
	,CRMAF_Filteredbs_mops.bs_salesmanname AS 'Salesman'
	,CRMAF_Filteredbs_mops.bs_paymenttypename AS 'Payment Type'
	,CRMAF_Filteredbs_mops.bs_manufacturername AS 'Manufacturer'
	,CRMAF_Filteredbs_mops.bs_cmandpsdmaintanance AS 'CM and PSD Maintanance'
	,CRMAF_Filteredbs_mops.bs_familyname AS 'Family'
	,CRMAF_Filteredbs_mops.bs_modelname AS 'Model'
	,CRMAF_Filteredbs_mops.bs_departmentname AS 'Department'
	,CRMAF_Filteredbs_mops.bs_newname AS 'New'
	,CRMAF_Filteredbs_mops.bs_usedname AS 'Used'
	,CRMAF_Filteredbs_mops.bs_ordernumber AS 'Order Number'
	,CRMAF_Filteredbs_mops.bs_name AS 'Serial No.'
	,CRMAF_Filteredbs_mops.bs_tginvoicedate AS 'TG Invoice Date'
	,CRMAF_Filteredbs_mops.bs_tginvoicenumber AS 'TG Invoice Number'
	,CRMAF_Filteredbs_mops.bs_localinvoicedate AS 'Local Invoice Date'
	,CRMAF_Filteredbs_mops.bs_localinvoiceref AS 'Local Invoice Ref.'
	,CRMAF_Filteredbs_mops.bs_deliverydate AS 'Delivery Date'
	,CRMAF_Filteredbs_mops.bs_invoiceamount AS 'Invoice Amount'
	,CRMAF_Filteredbs_mops.bs_coremachinename AS 'Core Machine'
	,CRMAF_Filteredbs_mops.bs_energydivisionname AS 'Energy Division'
	,CRMAF_Filteredbs_mops.bs_usedcoremachinesname AS 'Used Core Machine'
	,CRMAF_Filteredbs_mops.bs_usedenergydivisionname AS 'Used Energy Division'
	,CRMAF_Filteredbs_mops.bs_tlcaddsmark AS 'TLC + ADDS + MARK'
	,CRMAF_Filteredbs_mops.bs_gpeur AS 'GP [EUR]'
	,CRMAF_Filteredbs_mops.bs_gppercent AS 'GP [%]'
	,CRMAF_Filteredbs_mops.bs_totaldn AS 'Total DN'
	,(select t.bs_name from dbo.Filteredbs_attachmentpops t where t.bs_attachmentonpopsid = CRMAF_Filteredbs_mops.bs_mopsid) AS 'Attachment POPS'
	,(select t.bs_attachmentonpopsidname from dbo.Filteredbs_attachmentpops t where t.bs_attachmentonpopsid = CRMAF_Filteredbs_mops.bs_mopsid) AS 'Attachment on POPS'
	,null AS 'Name (Type)'
	,CRMAF_Filteredbs_mops.bs_epptypename AS 'EPP Type'
  	,CRMAF_Filteredbs_mops.bs_eppregdate AS 'EPP Reg. Date'
	,CRMAF_Filteredbs_mops.bs_msooreso AS 'MSO or ESO'
	,CRMAF_Filteredbs_mopsadditionalitem.bs_name AS 'Name (Add. Item)' 
	,CRMAF_Filteredbs_mopsadditionalitem.bs_typename AS 'Type (Add. Item)'
	,CRMAF_Filteredbs_mopsadditionalitem.bs_cost AS 'Cost (Add. Item)'	
    	,CRMAF_Filteredbs_mopsadditionalitem.bs_description AS 'Description (Add. Item)'
	,CRMAF_Filteredbs_mops.bs_costofadditionalitems AS 'Total (Add. Item)' 
	,null AS 'Name (Marketing Support)'
	,null AS 'Type (Marketing Support)'
	,null AS 'Paid (Marketing Support)'
	,null AS 'Aggrement Amount Neg (Marketing Support)'
	,null AS 'Total (Marketing Support)'
FROM Filteredbs_mopsadditionalitem AS CRMAF_Filteredbs_mopsadditionalitem RIGHT JOIN Filteredbs_mops AS CRMAF_Filteredbs_mops ON CRMAF_Filteredbs_mopsadditionalitem.bs_mops = CRMAF_Filteredbs_mops.bs_mopsid
UNION

SELECT 	 
	 CRMAF_Filteredbs_mops.bs_mopsid AS 'MOPS ID'
	,CRMAF_Filteredbs_mops.bs_statusmopsname AS 'Status (MOPS)'
	,(select t.bs_location from dbo.Filteredbs_pop t where t.bs_mops = CRMAF_Filteredbs_mops.bs_mopsid) AS 'Location (Core Machine)'
	,(select t.bs_location from dbo.Filteredbs_energydivision t where t.bs_mops = CRMAF_Filteredbs_mops.bs_mopsid) AS 'Location (Energy Division)'
	,(select t.bs_location from dbo.Filteredbs_usedcoremachine t where t.bs_mops = CRMAF_Filteredbs_mops.bs_mopsid) AS 'Location (Used Core Machine)'
	,(select t.bs_location from dbo.Filteredbs_usedenergydivision t where t.bs_mops = CRMAF_Filteredbs_mops.bs_mopsid) AS 'Location (Used Energy Division)'
	,CRMAF_Filteredbs_mops.bs_date AS 'Date'
	,CRMAF_Filteredbs_mops.bs_accountname AS 'Account'
	,CRMAF_Filteredbs_mops.bs_cciccodename AS 'CCIC Code'
	,CRMAF_Filteredbs_mops.bs_cciccategoryname AS 'CCIC Category'
	,(select a.bs_industryname from dbo.FilteredAccount a where a.accountid = CRMAF_Filteredbs_mops.bs_account) AS 'Industry (Account)'
	,CRMAF_Filteredbs_mops.bs_pwcname AS 'PWC'
	,CRMAF_Filteredbs_mops.bs_pwcsegmentname AS 'PWC Segment'
	,CRMAF_Filteredbs_mops.bs_saletypename AS 'Sale Type'
	,CRMAF_Filteredbs_mops.bs_salestypename AS 'Sales Type'
	,CRMAF_Filteredbs_mops.bs_salesmanname AS 'Salesman'
	,CRMAF_Filteredbs_mops.bs_paymenttypename AS 'Payment Type'
	,CRMAF_Filteredbs_mops.bs_manufacturername AS 'Manufacturer'
	,CRMAF_Filteredbs_mops.bs_cmandpsdmaintanance AS 'CM and PSD Maintanance'
	,CRMAF_Filteredbs_mops.bs_familyname AS 'Family'
	,CRMAF_Filteredbs_mops.bs_modelname AS 'Model'
	,CRMAF_Filteredbs_mops.bs_departmentname AS 'Department'
	,CRMAF_Filteredbs_mops.bs_newname AS 'New'
	,CRMAF_Filteredbs_mops.bs_usedname AS 'Used'
	,CRMAF_Filteredbs_mops.bs_ordernumber AS 'Order Number'
	,CRMAF_Filteredbs_mops.bs_name AS 'Serial No.'
	,CRMAF_Filteredbs_mops.bs_tginvoicedate AS 'TG Invoice Date'
	,CRMAF_Filteredbs_mops.bs_tginvoicenumber AS 'TG Invoice Number'
	,CRMAF_Filteredbs_mops.bs_localinvoicedate AS 'Local Invoice Date'
	,CRMAF_Filteredbs_mops.bs_localinvoiceref AS 'Local Invoice Ref.'
	,CRMAF_Filteredbs_mops.bs_deliverydate AS 'Delivery Date'
	,CRMAF_Filteredbs_mops.bs_invoiceamount AS 'Invoice Amount'
	,CRMAF_Filteredbs_mops.bs_coremachinename AS 'Core Machine'
	,CRMAF_Filteredbs_mops.bs_energydivisionname AS 'Energy Division'
	,CRMAF_Filteredbs_mops.bs_usedcoremachinesname AS 'Used Core Machine'
	,CRMAF_Filteredbs_mops.bs_usedenergydivisionname AS 'Used Energy Division'
	,CRMAF_Filteredbs_mops.bs_tlcaddsmark AS 'TLC + ADDS + MARK'
	,CRMAF_Filteredbs_mops.bs_gpeur AS 'GP [EUR]'
	,CRMAF_Filteredbs_mops.bs_gppercent AS 'GP [%]'
	,CRMAF_Filteredbs_mops.bs_totaldn AS 'Total DN'
	,(select t.bs_name from dbo.Filteredbs_attachmentpops t where t.bs_attachmentonpopsid = CRMAF_Filteredbs_mops.bs_mopsid) AS 'Attachment POPS'
	,(select t.bs_attachmentonpopsidname from dbo.Filteredbs_attachmentpops t where t.bs_attachmentonpopsid = CRMAF_Filteredbs_mops.bs_mopsid) AS 'Attachment on POPS'
	,null AS 'Name (Type)'
	,CRMAF_Filteredbs_mops.bs_epptypename AS 'EPP Type'
  	,CRMAF_Filteredbs_mops.bs_eppregdate AS 'EPP Reg. Date'
	,CRMAF_Filteredbs_mops.bs_msooreso AS 'MSO or ESO'
	,null AS 'Name (Add. Item)' 
	,null AS 'Type (Add. Item)'
	,null AS 'Cost (Add. Item)'	
        ,null AS 'Description (Add. Item)'
	,null AS 'Total (Add. Item)'  
	,CRMAF_Filteredbs_mopsmarketingsupport.bs_name AS 'Name (Marketing Support)'
	,CRMAF_Filteredbs_mopsmarketingsupport.bs_typename AS 'Type (Marketing Support)'
	,CRMAF_Filteredbs_mopsmarketingsupport.bs_paid AS 'Paid (Marketing Support)'
	,CRMAF_Filteredbs_mopsmarketingsupport.bs_agreedammountneg AS 'Aggrement Amount Neg (Marketing Support)'
	,CRMAF_Filteredbs_mops.bs_marketingsupporttotal AS 'Total (Marketing Support)'
	,(select top 1 t.bs_name from dbo.FilteredOpportunity o  inner join dbo.FilteredOpportunityProduct op on o.opportunityid = op.opportunityid
							   inner join dbo.Filteredbs_tap t on op.bs_taps = t.bs_tapid	
							   inner join dbo.Filteredbs_pop p on o.opportunityid = p.bs_opportunity					   
	  where t.bs_statusname = 'Winner' and p.bs_popid = CRMAF_Filteredbs_mops.bs_coremachine) AS 'TAPS Name (Winner)'
	,(select top 1 op.baseamount from dbo.FilteredOpportunity o  inner join dbo.FilteredOpportunityProduct op on o.opportunityid = op.opportunityid
							   inner join dbo.Filteredbs_tap t on op.bs_taps = t.bs_tapid	
							   inner join dbo.Filteredbs_pop p on o.opportunityid = p.bs_opportunity					   
	  where t.bs_statusname = 'Winner' and p.bs_popid = CRMAF_Filteredbs_mops.bs_coremachine) AS 'TAPS Amount'
FROM Filteredbs_mops AS CRMAF_Filteredbs_mops LEFT JOIN Filteredbs_mopsmarketingsupport AS CRMAF_Filteredbs_mopsmarketingsupport ON CRMAF_Filteredbs_mopsmarketingsupport.bs_mops = CRMAF_Filteredbs_mops.bs_mopsid

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Custom SSRS report ERROR when more that 20 records

    Hi.

    First thx for the help. Sorry for the delayed response.

    I have checked the permisions and all looks fine. The query execution through SQL executes fine with no errors.
    I have checked the exception stacktrace and it say something like "System.Data.SqlClient.SqlException: Must declare the variable "@bs_mopsid0". -"

  • Suggested answer
    Kishor Kumar Profile Picture
    Kishor Kumar 3,702 on at
    RE: Custom SSRS report ERROR when more that 20 records

    Check this link

    support.microsoft.com/.../2590774

    patricda.wordpress.com/.../crm-2011-rsprocessing-aborter-error-resolved

  • Aileen Gusni Profile Picture
    Aileen Gusni 44,522 on at
    RE: Custom SSRS report ERROR when more that 20 records

    Hi Matej,

    Try to see the event viewer, is there any query timeout exceeding the threshold.

    Have you tried execute this query through SQL execution only?

    Thanks

  • Wayne Walton Profile Picture
    Wayne Walton 13,726 on at
    RE: Custom SSRS report ERROR when more that 20 records

    rsProcessingAborted is almost always a permissions issue of some sort.  I would start with record access and go from there.  Turn on SQL Profiler when running the report to get more details.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans