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] Add an extra column under an across for a particular value

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Add an extra column under an across for a particular value
 Login/Join
 
Member
posted
I have an odd requirement from my users (whats new) and I am not sure how to code it. I have a report with the across and on the across total they want a metric to show up that isnt in the other across columns. I tried just hiding it but you cant hide the metric but it hides it everywhere. At least I dont think you can.


Period (label)
Period 1 Period 2 Period 3 Total
Row 1 metric 1 meric 2 metric 1 meric 2 metric 1 meric 2 metric 1 metric 2 metric 3
Row 2 metric 1 meric 2 metric 1 meric 2 metric 1 meric 2 metric 1 metric 2 metric 3

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8
Windows, All Outputs
 
Posts: 16 | Registered: April 13, 2017Report This Post
Member
posted Hide Post
Sorry my grid apparently didnt post well... it looked good in the preview...

Period Label
Period 1 Period 2 Period 3 Total
Row1 metric 1 metric 2 metric 1 metric 2 metric 1 metric 2 metric 1 metric 2 metric 3
ROw2 metric 1 metric 2 metric 1 metric 2 metric 1 metric 2 metric 1 metric 2 metric 3


WebFOCUS 8
Windows, All Outputs
 
Posts: 16 | Registered: April 13, 2017Report This Post
Virtuoso
posted Hide Post
Instead of using the ACROSS-TOTAL option, create your own total columns to append them to the original data then displayed in whished order.

See following post for sample
Manual Across

quote:

Sorry my grid apparently didnt post well... it looked good in the preview...

Use the code tag when posting sample code or layout. The last one on the ribbon:
</>


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Member
posted Hide Post
I did try that.

I made three different hold files

A) has the non totals and the 2 metrics
B) Has the totals and the 2 metrics
c) ha the totals and the extra metric

I then did a more to combine A and B and from there didnt know what to do.

I cant do a more to add C because it doesnt have the same amount of columns.

I thought about doing a join but then it would add a column to A and B and just be null.

I couldnt get the match to work. Im still relatively new and havent used that much

  
	Period (label)								
	Period 1		Period 2		Period 3		Total		
Row 1	metric 1	meric 2	metric 1	meric 2	metric 1	meric 2	metric 1	metric 2	metric 3
Row 2	metric 1	meric 2	metric 1	meric 2	metric 1	meric 2	metric 1	metric 2	metric 3



WebFOCUS 8
Windows, All Outputs
 
Posts: 16 | Registered: April 13, 2017Report This Post
Expert
posted Hide Post
Sorry Monika, I'm confused. What is the column, are the columns, that you only want to show up sometimes?
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Member
posted Hide Post
Sorry - its a bit confusing.

Here is an example using the car file:


TABLE FILE CAR
SUM
CAR.BODY.SEATS
CAR.BODY.RETAIL_COST
CAR.BODY.SALES
BY CAR.COMP.CAR
ACROSS LOWEST CAR.ORIGIN.COUNTRY
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE ROW-TOTAL
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END


In this example they only want to see the SALES in the TOTAL. Not under each country.


WebFOCUS 8
Windows, All Outputs
 
Posts: 16 | Registered: April 13, 2017Report This Post
Expert
posted Hide Post
Something like this, but you might have to play with the column titles using SUBHEAD etc. Also, the column notation (C1, C2 etc.) would need to be thought out in a preprocess maybe, to allow you the knowledge of how many across values you have.

Anyway, it's a start ...

TABLE FILE GGSALES
  SUM DOLLARS    AS 'Metric 1'
      BUDDOLLARS AS 'Metric 2'
	  UNITS NOPRINT
   BY ST
ACROSS REGION AS ''
RECAP XX1/D12c = C1 + C4 + C7 + C10; AS 'Total'
RECAP XX2/D12c = C2 + C5 + C8 + C11; AS ' '
RECAP XX3/D12c = C3 + C6 + C9 + C12; AS 'Metric 3 '
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
  grid=off, $
ENDSTYLE
END


T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Member
posted Hide Post
I have never seen the recap command. This looks like it might work. Ill try and let you know.

Thanks!


WebFOCUS 8
Windows, All Outputs
 
Posts: 16 | Registered: April 13, 2017Report This Post
Virtuoso
posted Hide Post
Tony has a good option but, as he mentioned, you need to know the number of ACROSS field to perform your COMPUTE/RECAP. Which can be a pain when it's not always the same.

quote:

I cant do a more to add C because it doesn't have the same amount of columns

Here another option. Need to have the same columns' name and format.
-* EXTRACT SOURCE DATA
DEFINE FILE GGSALES
YR /YY = DATE;
END
TABLE FILE GGSALES
SUM DOLLARS/D8C AS 'VAL'
    BUDDOLLARS/D8C AS 'BDG'
BY REGION
BY YR
ON TABLE HOLD AS EXTDATA
END
-RUN

-* CREATE YR ROW TOTAL (NEW COLUMN)
TABLE FILE EXTDATA
SUM VAL
BY REGION
BY TOTAL COMPUTE YR /YY = 9900;
ON TABLE HOLD AS YRTOT
END
-RUN

-* CREATE YR ROW VAR TOTAL (NEW COLUMN)
DEFINE FILE EXTDATA
VAR/D8C = BDG - VAL;
END
TABLE FILE EXTDATA
SUM VAR AS 'VAL'
BY REGION
BY TOTAL COMPUTE YR /YY = 9999;
ON TABLE HOLD AS VARDATA
END
-RUN

-* NMERGE DETAIL WITH TOTAL
TABLE FILE EXTDATA
SUM VAL
BY REGION
BY YR
ON TABLE HOLD AS RPTDATA
MORE
FILE YRTOT
MORE
FILE VARDATA
END
-RUN

-* PRODUCE FINAL REPORT
SET ACRSVRBTITL = ON
DEFINE FILE RPTDATA
YRX /A20V = IF YR EQ 9900 THEN 'Total'
       ELSE IF YR EQ 9999 THEN 'Var To Bdg'
       ELSE FPRINT(YR, 'YY', 'A4');
END
TABLE FILE RPTDATA
SUM VAL AS '$'
BY REGION
ACROSS YR  NOPRINT
ACROSS YRX AS ''
ON TABLE COLUMN-TOTAL AS 'Grand-Total'
ON TABLE SET PAGE-NUM NOLEAD
END
-RUN


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Member
posted Hide Post
Thanks guys! I was able to get it working, I really appreciate all the help!


WebFOCUS 8
Windows, All Outputs
 
Posts: 16 | Registered: April 13, 2017Report This Post
Virtuoso
posted Hide Post
Another solution. Use multi-set request and sequence:
  
-SET &ECHO=ALL;
-* File monika01.fex
TABLE FILE CAR
SUM 
DCOST
RCOST
SALES
BY CAR
SUM 
DCOST
RCOST
BY CAR
ACROSS SEATS
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
-*ON TABLE ROW-TOTAL
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE=ENDEFLT,
$
TYPE=REPORT, COLUMN=C1, SEQUENCE=21,
$
TYPE=REPORT, COLUMN=C2, SEQUENCE=22,
$
TYPE=REPORT, COLUMN=C3, SEQUENCE=23,
$
ENDSTYLE
END


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report 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] Add an extra column under an across for a particular value

Copyright © 1996-2020 Information Builders