Hello!
I’ve tried going through previous questions and answers relating to the same topic, but none have come up trumps for me unfortunately.
I’ve tried rewriting my SQL scripts to produce data which can be read in different ways, hoping that the graph would react differently, I then selected “Stack” under “stacking & null value” but the graph still display 3 bars, instead of placing one on top of the other, can somebody please point me in the right direction?
SQL 1 (Figures by columns):
WITH CTE AS
(
SELECT
SUM(ISNULL([AMT] * ER.Rate, 0)) AS [SalesValue]
,SUM(ISNULL([TARGET] * ER.Rate, 0)) AS [TargetValue]
FROM CS_LOCAL_SALESTOTALS_AND_TARGETS Sales
LEFT JOIN SAFEDB02.CAM.dbo.Exchange_Rates ER
ON Sales.Entity = ER.Entity
WHERE Sales.FLAG = ‘T’
)
SELECT
[TargetValue] / 100 * 80 AS [Bar1]
,([TargetValue] / 100 * 100) - ([TargetValue] / 100 * 80) AS [Bar2]
,([TargetValue] / 100 * 110) - ([TargetValue]) as [Bar3]
FROM CTE
SQL 2 (Figures by Rows):
WITH CTE AS
(
SELECT
SUM(ISNULL([AMT] * ER.Rate, 0)) AS [SalesValue]
,SUM(ISNULL([TARGET] * ER.Rate, 0)) AS [TargetValue]
FROM CS_LOCAL_SALESTOTALS_AND_TARGETS Sales
LEFT JOIN SAFEDB02.CAM.dbo.Exchange_Rates ER
ON Sales.Entity = ER.Entity
WHERE Sales.FLAG = ‘T’
)
SELECT
‘Bar1’ AS [Series]
,[TargetValue] / 100 * 80 AS [Value]
FROM CTE
UNION ALL
SELECT
‘Bar2’ AS [Series]
,([TargetValue] / 100 * 100) - ([TargetValue] / 100 * 80) AS [Value]
FROM CTE
UNION ALL
SELECT
‘Bar3’ AS [Series]
,([TargetValue] / 100 * 110) - ([TargetValue])
FROM CTE
Just to note, I add a field for GETDATE() AS [time] so that the data works with the Chart plugin