I have this Microsoft SQL Server query that generates a pivot table.
"SELECT Program,
ISNULL([1], 0) AS [1], ISNULL([2], 0) AS [2], ISNULL([3], 0) AS [3], ISNULL([4], 0) AS [4], ISNULL([5], 0) AS [5],
ISNULL([6], 0) AS [6], ISNULL([7], 0) AS [7], ISNULL([8], 0) AS [8], ISNULL([9], 0) AS [9], ISNULL([10], 0) AS [10],
ISNULL([11], 0) AS [11], ISNULL([12], 0) AS [12], ISNULL([13], 0) AS [13], ISNULL([14], 0) AS [14], ISNULL([15], 0) AS [15],
ISNULL([16], 0) AS [16], ISNULL([17], 0) AS [17], ISNULL([18], 0) AS [18], ISNULL([19], 0) AS [19], ISNULL([20], 0) AS [20],
ISNULL([21], 0) AS [21], ISNULL([22], 0) AS [22], ISNULL([23], 0) AS [23], ISNULL([24], 0) AS [24],
ISNULL([1], 0) + ISNULL([2], 0) + ISNULL([3], 0) + ISNULL([4], 0) + ISNULL([5], 0) + ISNULL([6], 0) +
ISNULL([7], 0) + ISNULL([8], 0) + ISNULL([9], 0) + ISNULL([10], 0) + ISNULL([11], 0) + ISNULL([12], 0) +
ISNULL([13], 0) + ISNULL([14], 0) + ISNULL([15], 0) + ISNULL([16], 0) + ISNULL([17], 0) + ISNULL([18], 0) +
ISNULL([19], 0) + ISNULL([20], 0) + ISNULL([21], 0) + ISNULL([22], 0) + ISNULL([23], 0) + ISNULL([24], 0) AS Total
FROM (
SELECT
ISNULL(Count, 0) AS Count,
Program,
DATEPART(HOUR, Time) AS Hour
FROM
my_table
WHERE
Time >= DATEADD(HOUR, -24, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
AND Time < DATEADD(HOUR, 7, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
) r
PIVOT(
SUM(Count)
FOR Hour IN (
[1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
[11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
[21], [22], [23], [24]
)
) AS pivot_table;"
This generates the pivoted data below.
I would like to add this pivot table to an Ignition Report. I am having trouble accomplishing this. Would it be simpler to just query the data (without pivoting it) into a CrossTab and then pivot in the CrossTab?
New to reports - any advice helps. Thanks!