Report Wizard: Query execution failed for data set ‘DSMain’

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

Advertisements
This entry was posted in Reports. Bookmark the permalink.

12 Responses to Report Wizard: Query execution failed for data set ‘DSMain’

  1. CraigP says:

    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?

    Like

  2. Bill Owens says:

    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.

    Like

  3. CraigP says:

    Bill,I seem to have the same issue with built-in reports not just custom ones…., any idea why this may be?

    Like

  4. Bill Owens says:

    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?

    Like

  5. Anonymous says:

    Thank you, Sir. Much appreciated.

    Like

  6. 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!

    Like

  7. Bill Owens says:

    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.

    Like

  8. Josee says:

    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?

    Like

  9. Bill Owens says:

    Josee,Please send me a print screen of the error and the rdl file. I’ll take a quick look.

    Like

  10. Kgopotso says:

    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??

    Like

  11. Bill Owens says:

    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?

    Like

  12. Anonymous says:

    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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s