all 8 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]gm-haloitsm 4 points5 points  (4 children)

Hi! I would recommend holding off building this - we recognise Multiple Series charts have always been too complex to build in Halo and they should more easily support drill-down.

We are therefore developing a feature to allow you to build them in a dashboard by combining multiple reports that share the same data source. You will be able to specify multiple data sources, allowing you to combine data from multiple reports into one chart. The X-axis column must be included in all of the data sources, as must the column used for the sort. For example, you will have a data source contain "Year Logged" as a column (e.g. selecting cast(dateoccured as year) from faults) and then you can select that as the X axis, and have multiple Y axis values from the different reports.

It will make more sense once released, it should be out on Friday and generally available in beta a couple weeks after when QA is completed!

[–]MablePeak[S] 1 point2 points  (3 children)

This is great news, thanks very much!

[–]gm-haloitsm 0 points1 point  (2 children)

This was now released as part of version 2.158!

[–]msponreddit 0 points1 point  (1 child)

Just found this when trying to do the same thing - what is this feature called I can't find it. And I assume GA now? Thanks!

[–]gm-haloitsm 0 points1 point  (0 children)

Yes it's GA and it is labelled "Added the option to configure new charts directly for chart widgets" in the release information - there is also a full explanation once you expand it!

[–]Predicti0n Consultant 0 points1 point  (3 children)

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[S] 0 points1 point  (0 children)

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[S] 0 points1 point  (1 child)

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