Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Across with subfoot on noprint field

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Across with subfoot on noprint field
 Login/Join
 
Member
posted
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,


WebFOCUS 7.6.10
Windows
Excel, PDF, HTML
 
Posts: 19 | Location: Cincinnati OH | Registered: August 18, 2010Report This Post
Expert
posted Hide Post
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


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1948 | Location: New York | Registered: November 16, 2004Report This Post
Expert
posted Hide Post
Hmmm, that looks interesting!


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
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


WebFOCUS 7.6.10
Windows
Excel, PDF, HTML
 
Posts: 19 | Location: Cincinnati OH | Registered: August 18, 2010Report This Post
Expert
posted Hide Post
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


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
Francis, you have no idea how many columns there will be with an across.

You may be better using FML
 
Posts: 140 | Location: Adelaide South Australia | Registered: October 27, 2006Report This Post
Virtuoso
posted Hide Post
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,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Member
posted Hide Post
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.


WebFOCUS 7.6.10
Windows
Excel, PDF, HTML
 
Posts: 19 | Location: Cincinnati OH | Registered: August 18, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Across with subfoot on noprint field

Copyright © 1996-2020 Information Builders