Posted by David Jennaway at Wednesday, 15 October 2008
There is a problem with the CRM 4.0 Report Wizard that can result in an error like the following:
An error has occurred during report processing.Query execution failed for data set ‘DSMain’.The column ‘accountid’ was specified multiple times for ‘account0’. The column ‘accountid’ was specified multiple times for ‘opportunity1’.
Explanation of the problem
The ultimate cause is how the Report Wizard stores the Filtering Criteria for reports based on the account entity. The Report Wizard stores the query for any criteria as a combination of all fields in the account entity, and all fields in the related primary contact. When the report is run, the SQL query attempts to use the results of the following (or similar) as a table alias:
select DISTINCT account0.*, accountprimarycontactidcontactcontactid.* from FilteredAccount as account0 left outer join FilteredContact as accountprimarycontactidcontactcontactid on (account0.primarycontactid = accountprimarycontactidcontactcontactid.contactid) where (account0.statecode = 0)
This returns two fields called accountid (one from the account entity, and one from the contact), which breaks the main SQL query for the report, and gives the error above.
Resolution
The way to resolve this is to ensure that, when you create the report with the Report Wizard, you do not specify any criteria for the account entity. This will cause the Report Wizard to store the query as solely against the account entity. Once you’ve created the report, you can happily edit the default filter to whatever you want, and the report will work fine – the key factor is not having any criteria when you first create the report.
Unfortunately there’s not an easy way to fix existing reports with this problem – it should be possible to edit the data in the DefaultFilter column in the reportbase table, but this is unsupported. I’d suggest in this scenario that you’re best off recreating the report from scratch
Thank you for this!Is this a bug in reports? or is it something to do with the setup of the CRM/reports?any ideas how to completely fix this issue rather than the work around?
LikeLike
CraigP.This issue is with the Report Wizard and how it creates the RDL. There are several “bugs” with teh report wizard. This work around just means that you need to do this when you create the report with the report wizard. You can create reports easily with VS and by-pass this issue all together.
LikeLike
Bill,I seem to have the same issue with built-in reports not just custom ones…., any idea why this may be?
LikeLike
CraigPI would have to look at it in order to understand what is going on with your report. So at the moment, no I do not know. I have created many custom reports and have not ran into this issue. Did you create the report from scratch?
LikeLike
Thank you, Sir. Much appreciated.
LikeLike
Bill, Why is this the exact same blog entry as < HREF="http://mscrmuk.blogspot.com/2008/10/report-wizard-query-execution-failed.html" REL="nofollow">this<> one??Anyway : thanx a bunch!
LikeLike
Dear Bert-Jan DiederingIt is the same. If you notice at the top of most of my posts I give a link and credit to the person who wrote the article. I subscribe to over 60 different blogs and filter out the posts that are pertinent to my clients or something I want to have in my tool box. What I am trying to do is collect information in one spot so all of my CRM clients can subscribe to two blogs as needed (My technical and Non-Technical). That way it helps me disseminate information through one channel. I would say about 90% of the articles in my blog are links to other blogs and their articles. You will find as you look around that this is not an uncommon practice.
LikeLike
JoseeThanks Bill. However, I am getting the same error message for a custom report running on a custom entity. The custom entity does not have a direct mapping to the account, and has no criteria on the account entity. Any ideas?
LikeLike
Josee,Please send me a print screen of the error and the rdl file. I’ll take a quick look.
LikeLike
Kgopotso Riba..
I am recieving this error “An error has occurred during report processing. Query execution failed for dataset 'DSMain'.” This is after I added the twelveth field and more on mr Report, And Again if I run it and include selection Criteria, I am not Using Account or Opportunity entities, Im using contact entities and the rest are custom entities, Is there a maximum number of Columns to be added to the Table Report??
LikeLike
Kgopotso, 2 things. When you create the report are you clearing the filter instead of just using the defualt. If so, try clearing the filter. Second, does this happen when you place any field as the 12 column, or just a specific field. If it is a specific field, what is it?
LikeLike
Oh man, what a pain in the ass. So what now? I have about 200 reports that I need to go back and recreate…. They were working fine until the other day, that's what's weird to me. They were working beautifully.
Also what I'm doing is deleting the filtering fields, saving the reports and adding the fields again, and it seems to work, instead of recreating from scratch because then you have to add the report output columns and so on. Anyone has any suggestions on “attacking” 200 reports at once, they're all different…. I don't know. How could this corruption happen?
They were beautiful until the other day.
LikeLike