• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Creating Cross Tab Report

Explorer ,
Jul 26, 2021 Jul 26, 2021

Copy link to clipboard

Copied

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
query.jpg
TOPICS
Advanced techniques , Reporting

Views

39

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jul 26, 2021 Jul 26, 2021

Copy link to clipboard

Copied

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? 


/Charlie (troubleshooter, carehart.org)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jul 31, 2021 Jul 31, 2021

Copy link to clipboard

Copied

LATEST

Could you explain what you mean by

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation