Creating Cross Tab Report

[Tayyab Hussain]

Hello Community Members,

I've written a query that generates Cross Tab or Pivot. Image Below. My Query is as follows

SELECT * FROM

(

SELECT TOP(100) PERCENT production.Line_FK_ID,

Lines.LineName,production.OP_FK_ID,

Operations.OperationName,

[Shift].ShiftID,[Shift].ShiftName,

production.op_Time_From,

production.Production

FROM production

JOIN Operations

on Operations.opID=production.OP_FK_ID

JOIN Lines

on Lines.lineID=production.Line_FK_ID

JOIN [Shift]

on [Shift].ShiftID=production.Shift_FK_ID

WHERE

ShiftID in (1)

and op_date in ('2021-07-08')

Order by production.op_Time_From

) Report

PIVOT

(

Sum (Production)

FOR OP_TIME_FROM

IN (

[06:30:00],[07:30:00],[08:30:00],[09:40:00],[10:30:00],[11:30:00],[13:00:00],[13:30:00],[14:30:00]

)

) As Pivot_Report

I want to make PDF report in which Production Line Wise for 6 operations for each hour. I am unable to Pivot the Hours Horizontly. The CFDUMP is as follows

[Charlie Arehart]

Please clarify. Do you mean you want to create output that DOES look like the dump (hours horizontally), or which inverts things (hours vertically)? Or do you mean something else by pivot? (I do see the sql doing a pivot, of course.)

Also, how are you wanting the output to appear? As html? As a PDF? As a spreadsheet? Cf supports them all, and with multiple options for each. What have you tried? 

Finally, what version of CF are you using? 

[BKBK]

Could you explain what you mean by

• Production Line Wise for 6 operations for each hour. 
• Pivot the Hours Horizontly