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
Subscribe to:
Post Comments (Atom)
Posts
7 comments:
Bill,
I seem to have the same issue with built-in reports not just custom ones...., any idea why this may be?
CraigP
I 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?
Thank you, Sir. Much appreciated.
Bill,
Why is this the exact same blog entry as this one??
Anyway : thanx a bunch!
Josee
Thanks 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?
Josee,
Please send me a print screen of the error and the rdl file. I'll take a quick look.
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.
Post a Comment