In running a report you get the following error:
Here are examples of Incident reports that you could see this error with:
Reports\Incident Management\Incidents\Alert Manager\Problem Assets\Count Incidents by Operating Systems
Reports\Incident Management\Incidents\Helpdesk\Service Level\Incidents by Overall Satisfaction
Reports\Incident Management\Incidents\Helpdesk\Incident Activity Trend\ -- Day of Month
-- Day of Week
-- Incidents by Hour
This error indicates that a parameters SQL command, used to dynamically generate the selectable choices, failed.
Reports that have a drop-down box parameter use the SQL command to select two columns, the first to select the value displayed for selection and the second column to be used in the report query instead of the displayed value. For many reports the value to display and the value to use in the reports query are the same so the same column was selected twice and the same column was used for sorting. Due to a syntax difference in SQL 2005, when trying to sort by the one column, SQL Server is unable to differentiate between the two selected columns.
To resolve the issue the columns selected must each have a unique name so that the SQL will be able to know which selected column to sort by.
Here are the steps you want to follow to fix the issue:
1. Clone the Report
2. Edit the Cloned Report
3. Look under the Global Parameters section4. Look for a parameter that has a query that looks similar to this example:SELECT DISTINCT [OS Revision], [OS Revision] FROM Inv_AeX_AC_Identification i WHERE [OS Revision] <> '' UNION SELECT '-- Any --', '%' ORDER BY [OS Revision](Running this query in the Sever Management Console it will return a ambiguous message for OS Revision)
5. Edit the query to allow SQL to be able to distinguish between the two columns with the same name in the select statement. Here is an example on what you can do below:
SELECT DISTINCT [OS Revision], [OS Revision] as Col2 FROM Inv_AeX_AC_Identification i WHERE [OS Revision] <> '' UNION SELECT '-- Any --', '%' ORDER BY [OS Revision]
SQL Server 2005
Notification Server 6.0
Helpdesk 6.0.297 (sp4)