I am trying to create a report with Overtime (OT) and Double time (DT) Across month. On a group break I need one line to total OT and DT. Below that I need a second line with the total Premium Hours (OT + DT).

......................JANUARY....FEBRUARY...MARCH
PLANT................ OT...DT.....OT...DT...OT...DT
001..................100...20....200...10..200...20
002..................200...20....150...25..200...20
003..................300...30....150...20..200...20
Total A..............600...70....500...55..600...60
Total A Premium..670.........555.......660

Here is my code so far:

DEFINE FILE VIEW_DW_FACT_ETIME_PAYROLL_HOURS
OT/D12 = IF TIME_TYPE = 'OT' THEN HOURS_WORKED ELSE 0;
DT/D12 = IF TIME_TYPE = 'DT' THEN HOURS_WORKED ELSE 0;
PREM/D12 = IF TIME_TYPE = 'OT' OR TIME_TYPE = 'DT' THEN HOURS_WORKED ELSE 0;
GROUP_NEW/A20V = GROUP || ' Premium'
END

TABLE FILE VIEW_DW_FACT_ETIME_PAYROLL_HOURS
SUM
OT
DT
PREM NOPRINT
BY GROUP_NEW NOPRINT
BY GROUP NOPRINT
BY PLANT
ACROSS 'DW_DIM_TIME.DW_DIM_TIME.FISCAL_MONTH' NOPRINT AS ''
ACROSS 'DW_DIM_TIME.DW_DIM_TIME.CALENDAR_MONTH' AS ''
ON GROUP RECOMPUTE AS 'Total'
???ON GROUP_NEW ?????


I cannot figure out how to get the Total A Premium line to show what I want. Can anybody point me in the right direction?

Thanks,
Swamp

This message has been edited. Last edited by: Kerry,

Hi Swamp,

Can you please try the following code from our FOCUS expert:

DEFINE FILE EMPDATA
OT/I6=SALARY/1000;
DT/I6=SALARY/2000;
MONTH/M=HIREDATE;
END
TABLE FILE EMPDATA
SUM
OT
DT
BY DIV
BY DEPT
ON DIV RECAP BOTH/I9=OT+DT; NOPRINT
ON DIV SUBFOOT
" TOTAL  FOR  OT IS <ST.OT , FOR DT IS <ST.DT  "                
" TOTAL  PREMIUM  <BOTH"
ACROSS MONTH
END

Cheers,

Kerry

Hmmm, that looks interesting!

Kerry,


I need the subtotals to line up for the twleve months of the year and the SUBFOOT does not do that as far as I know.

Swamp

You line them up by using stylesheet commands - POSITION for PDF, HEADALIGN=BODY for HTML and Excel. Check the documentation: Creating Reports With WebFOCUS Language > Laying Out the Report Page > Positioning a Report Component

Francis, you have no idea how many columns there will be with an across.

You may be better using FML

You might consider adding a Total Premium column (TP) to your output. Then SUBTOTAL becomes an easy solution.

DEFINE FILE EMPDATA
 OT/I6 = SALARY / 1000 ;
 DT/I6 = SALARY / 2000 ;
 TP/I6 = OT + DT ;
 MONTH/Mtr = HIREDATE ;
END

TABLE FILE EMPDATA
 SUM
  OT
  DT
  TP
 BY DIV NOPRINT
 BY DEPT
 ON DIV SUBTOTAL
 ON DIV SUBFOOT
  " "
 ACROSS MONTH
 ON TABLE SET STYLE *
  ACROSSCOLUMN=TP, BACKCOLOR=SILVER, $
  TYPE=SUBTOTAL, BACKCOLOR=LIGHT BLUE, $
  TYPE=GRANDTOTAL, STYLE=BOLD, $
 ENDSTYLE
END

This message has been edited. Last edited by: Dan Satchell,

With twelve months in the year the two columns of data per month means I have 24 columns in the report. I do not want to add a third column per month because the 12-month report would then be a totally unmanageable 36 columns. I am trying to use FML (thanks OPALTOSH) because that seems the best avenue to pursue based on everyone's suggestions.