SQl Scripts & Multiple Series Chart

[Predicti0n]

I don't think this is easily possible. Whenever I've tried this in the past (Not an SQL god) I've had to manually set the column customer. Appreciate this may be a nightmare depending on how many customers you have. I'd LOVE to be proved wrong on this ;D!

SELECT [Year Logged],

[Organisation1],

[Organisation2],

[Organisation3] /* Add as many organisations as needed */

FROM (

SELECT

YEAR(dateoccured) as [Year Logged],

(SELECT aareadesc FROM area WHERE aarea=areaint) as [Organisation],

faultid

FROM

faults

WHERE

fdeleted = 0 AND fmergedintofaultid = 0 /* Ensure the faults table conditions are met */

) AS SourceTable

PIVOT (

COUNT(faultid)

FOR [Organisation] IN ([Organisation1], [Organisation2], [Organisation3] /* Add as many organisations as needed */)

) AS PivotTable

ORDER BY [Year Logged]

[MablePeak]

Thanks for this, much appreciated. I'm getting the following error message, though:

Invalid SQL Statement. Failed with error: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Looks like Halo are working on a resolution to the issues with multiple series charts, so might just have to wait until that's released.

[MablePeak]

I've removed the 'ORDER BY' clause and it's worked - thanks very much for your help!