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] ACROSSTITLE ISSUE IN Excel 2007 (XLSX)

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] ACROSSTITLE ISSUE IN Excel 2007 (XLSX)
 Login/Join
 
Silver Member
posted
Hi All,

The ACROSSTITLE is not displaying properly for all the rows in a report. The below mentioned code is giving proper report in EXL2K format and the same is not working fine in XLSX(Excel 2007).


EXL2K Code:

**********

TABLE FILE CAR
PRINT
DEALER_COST
ACROSS CAR AS 'Car Type'
ON TABLE ROW-TOTAL AS 'Test'
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLESHEET *
TYPE=ACROSSTITLE,BORDER=LIGHT,COLOR=GREEN,STYLE=BOLD,JUSTIFY=CENTER,$
ENDSTYLE
END

XLSX Code:

**********


TABLE FILE CAR
PRINT
DEALER_COST
ACROSS CAR AS 'Car Type'
ON TABLE ROW-TOTAL AS 'Test'
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLESHEET *
TYPE=ACROSSTITLE,BORDER=LIGHT,COLOR=RED,STYLE=BOLD,JUSTIFY=CENTER,$
ENDSTYLE
END

Please see the across title is not working for the first row data "TRIUMPH".

Please help me on this.


Regards,
Suresh

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


WebFOCUS 8.0.05 - BI Portal, Developer Studio, Dev/App Studio, Excel, PDF, Active Formats and HTML5, Adobe Flex Builder, VBA
 
Posts: 47 | Location: Bengaluru, India | Registered: December 04, 2014Report This Post
Virtuoso
posted Hide Post
Suresh,
Try:
 
TABLE FILE CAR
PRINT
DEALER_COST
ACROSS CAR AS 'Car Type' ACROSS-TOTAL AS Test
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLESHEET *
TYPE=ACROSSTITLE,BORDER=LIGHT,COLOR=RED,STYLE=BOLD,JUSTIFY=CENTER,$
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
Silver Member
posted Hide Post
Hi Danny,
Thanks for your code and it looks good now.
Is it possible to display the across title only for the data section (in our case till the "TRIUMPH")?. I want to display this title only for the data sections from "ALFA ROMEO" till "TRIUMPH" and not for the Test column.

Can you please tell me how can we achieve this?.

Regards,
Suresh


WebFOCUS 8.0.05 - BI Portal, Developer Studio, Dev/App Studio, Excel, PDF, Active Formats and HTML5, Adobe Flex Builder, VBA
 
Posts: 47 | Location: Bengaluru, India | Registered: December 04, 2014Report This Post
Virtuoso
posted Hide Post
Sureshkuamr

Here is the revised code...

TABLE FILE CAR
PRINT
DEALER_COST
ACROSS CAR AS 'Car Type' ACROSS-TOTAL AS ''
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLESHEET *
TYPE=ACROSSTITLE,BORDER=LIGHT,COLOR=RED,STYLE=BOLD,JUSTIFY=CENTER,$
ENDSTYLE
END 


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Silver Member
posted Hide Post
Hi Chuck,
I have tried your revised code and it works fine for the across title. Find the below scenarios which i am trying to implemented in my report.

I have a BY field amount value and it is displaying as first column in my report. I wanted this first column amount also to be summed with the across amounts.
In the below case (RETAIL_COST + DEALER_COST ) and the final total amount should be "5,765".

RETAIL_COST = 3,139
DEALER_COST = 2,626

Final total should be 5,765 ( But i am getting only 2,626) as total. Please run the below code for report.

Sample Code

TABLE FILE CAR
PRINT
DEALER_COST
BY RETAIL_COST
ACROSS CAR AS 'Car Type' ACROSS-TOTAL AS ''
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLESHEET *
TYPE=ACROSSTITLE,BORDER=LIGHT,COLOR=RED,STYLE=BOLD,JUSTIFY=CENTER,$
ENDSTYLE
END

Note : If we use ROW-TOTAL i am getting the total as we expected, but the formatting is not happening in ACROSSTITLE.

Thanks for your help in advance.

Regards,
Suresh


WebFOCUS 8.0.05 - BI Portal, Developer Studio, Dev/App Studio, Excel, PDF, Active Formats and HTML5, Adobe Flex Builder, VBA
 
Posts: 47 | Location: Bengaluru, India | Registered: December 04, 2014Report This Post
Virtuoso
posted Hide Post
Does this may suits your need ?
TABLE FILE CAR
SUM COMPUTE TOTAL_COST /D9 = DEALER_COST + RETAIL_COST;
BY RETAIL_COST

PRINT DEALER_COST
BY RETAIL_COST
ACROSS CAR AS 'Car Type'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLESHEET *
TYPE=ACROSSTITLE,BORDER=LIGHT,COLOR=RED,STYLE=BOLD,JUSTIFY=CENTER,$
ENDSTYLE
END


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
Silver Member
posted Hide Post
Hi MartinY,

The code which you have shared is giving the correct results. But i wanted to display the TOTAL_COST at the last column in my report after the ACROSS columns printed.

Currently the column TOTAL_COST is coming 2nd column in the report.
Can you please help me to get this done?.

Regards,
Suresh


WebFOCUS 8.0.05 - BI Portal, Developer Studio, Dev/App Studio, Excel, PDF, Active Formats and HTML5, Adobe Flex Builder, VBA
 
Posts: 47 | Location: Bengaluru, India | Registered: December 04, 2014Report This Post
Virtuoso
posted Hide Post
What you are trying to do is to mimic the ACROSS-TOTAL feature.
But since you want to display the RETAIL_COST as the BY field and DELEAR_COST as the measure (PRINT) to have the total of both in a separate column,
you will need to "create" that total and include it as a fake CAR value that will then be positioned as the last one.
DEFINE FILE CAR
TOT_COST /D7 = DEALER_COST + RETAIL_COST;
END
TABLE FILE CAR
SUM TOT_COST AS 'DEALER_COST'
BY RETAIL_COST
BY TOTAL COMPUTE POS /I2  = 2;
BY TOTAL COMPUTE CAR /A16 = '';
ON TABLE HOLD AS TOTREC FORMAT FOCUS
END
-RUN

TABLE FILE CAR
SUM DEALER_COST
BY RETAIL_COST
BY TOTAL COMPUTE POS /I2 = 1;
BY CAR
ON TABLE HOLD AS DETREC FORMAT FOCUS
END
-RUN

TABLE FILE DETREC
SUM DEALER_COST
BY RETAIL_COST
BY POS
BY CAR
ON TABLE HOLD AS ALLREC FORMAT FOCUS
MORE
FILE TOTREC
END
-RUN

TABLE FILE ALLREC
SUM DEALER_COST
BY RETAIL_COST
ACROSS POS NOPRINT
ACROSS CAR AS 'Car Type'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLESHEET *
TYPE=ACROSSTITLE,BORDER=LIGHT,COLOR=RED,STYLE=BOLD,JUSTIFY=CENTER,$
ENDSTYLE
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
Silver Member
posted Hide Post
Hi MartinY,

I have tried your code and it worked fine for me.

I have another scenario where i need to use only the ROW-TOTAL to get the total. Find the below code for both EXL2K and XLSX versions.


EXL2K Code

TABLE FILE CAR
SUM
DEALER_COST
BY CAR
SUM
RETAIL_COST AS ''
BY CAR
ACROSS CAR AS 'Car Type' ROW-TOTAL AS 'Total'
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLESHEET *
TYPE=ACROSSTITLE,BORDER=LIGHT,COLOR=GREEN,STYLE=BOLD,JUSTIFY=CENTER,$
ENDSTYLE
END
-EXIT

XLSX Code

TABLE FILE CAR
SUM
DEALER_COST
BY CAR
SUM
RETAIL_COST AS ''
BY CAR
ACROSS CAR AS 'Car Type' ROW-TOTAL AS 'Total'
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLESHEET *
TYPE=ACROSSTITLE,BORDER=LIGHT,COLOR=GREEN,STYLE=BOLD,JUSTIFY=CENTER,$
ENDSTYLE
END
-EXIT

Can you please tell me why the across title is not coming properly for XLSX alone?.

Thanks a lot for your help in advance.

Regards,
Suresh


WebFOCUS 8.0.05 - BI Portal, Developer Studio, Dev/App Studio, Excel, PDF, Active Formats and HTML5, Adobe Flex Builder, VBA
 
Posts: 47 | Location: Bengaluru, India | Registered: December 04, 2014Report This Post
Virtuoso
posted Hide Post
I can't explain and you probably need to contact TechSupport for that since it may be related to internal code on how ROW-TOTAL react with EXL2K vs XLSX

Myself I would use that version of the code
TABLE FILE CAR
SUM DEALER_COST
BY CAR
SUM RETAIL_COST AS ''
BY CAR
ACROSS CAR AS 'Car Type' ACROSS-TOTAL AS 'Total'
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLESHEET *
TYPE=ACROSSTITLE,BORDER=LIGHT,COLOR=GREEN,STYLE=BOLD,JUSTIFY=CENTER,$
ENDSTYLE
END

since it gives the same layout in either EXL2K or XLSX.
And even if it seems that you don't want to see "Car Type" header above the Total column, at the end it's the Car Type's Total for which make senses to have it under the same header as other car's types.


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
  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] ACROSSTITLE ISSUE IN Excel 2007 (XLSX)

Copyright © 1996-2020 Information Builders