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     [CLOSED] ACCROSS Data Format

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] ACCROSS Data Format
 Login/Join
 
Tim A
Member
posted
Could anyone show me the code to populate ACCROSS values with 0.00 P15.2CM when there is no amount returned. I need this to default to 0.00 when there is no month value. Please see example

SUM
OPEN_RECEIVABLES/P15.2CM AS ' '

BY HIGHEST TOT_REC NOPRINT
BY ACCOUNT_NUMBER
BY DAYS_TO_PAY
BY CUSTOMER

ACCROSS HIGHEST MONTH_OF_CALENDAR NOPRINT
ACCROSS MONTH AS ' '

This populates the sum of open receivables by month across the page in EXCEL output as expected, however the field months with no open receivables are blank instead of $0.00. Users are asking for dollar value 0.00 for sorting

WF8.1
Any help would be great thanks !! - Tim A

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


WF 8.2
 
Posts: 22 | Location: US | Registered: September 28, 2017Report This Post
Waz
Expert
posted Hide Post
Try using SET NODATA

e.g.
SET NODATA = '$0.00'

TABLE FILE CAR
SUM COMPUTE VAL/P15.2CM = SALES ;
BY COUNTRY
ACROSS SEATS
END


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Tim A
Member
posted Hide Post
I tried, and no luck


WF 8.2
 
Posts: 22 | Location: US | Registered: September 28, 2017Report This Post
Tim A
Member
posted Hide Post
here is the actual code if it helps..

TABLE FILE O
SUM
OPN_REC/P15.2CM AS ' '

BY HIGHEST TOT_REC NOPRINT

BY PTRN_CD
BY DTP
BY CUSTOMER
BY DESK
BY TEAM
BY EVT_333
BY EVT_ST
BY TOT_REC/P15.2CM

ACROSS HIGHEST MOC NOPRINT
ACROSS MNTH AS ' '


HEADING
"Open Demurrage"


WF 8.2
 
Posts: 22 | Location: US | Registered: September 28, 2017Report This Post
Waz
Expert
posted Hide Post
Can you reproduce with the CAR file or one of the other sample files ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Tim A
Member
posted Hide Post
this comes back with 0 for the sales amount field under the models with no sales. I see the sales field is formatted to integer. this tells me i need to check the table file formatting for null values

TABLE FILE CAR
SUM
SALES

ACROSS MODEL

-*?FF
-*-EXIT


ON TABLE SET PAGE-NUM OFF
ON TABLE COLUMN-TOTAL AS 'TOTAL'
-*ON TABLE NOTOTAL
-*ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='Letter',
LEFTMARGIN=0.500000,
RIGHTMARGIN=0.500000,
TOPMARGIN=0.500000,
BOTTOMMARGIN=0.500000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,

$
TYPE=REPORT,
GRID=ON,
FONT='CALIBRI',
SIZE=9,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
RIGHTGAP=0.125000,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
LINES-PER-PAGE=UNLIMITED,
$
TYPE=TITLE,
COLOR='WHITE',
BACKCOLOR=RGB(255 102 0),
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=HEADING,
STYLE=BOLD+ITALIC,
SIZE=10,
$
TYPE=TABHEADING,
SIZE=10,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END


WF 8.2
 
Posts: 22 | Location: US | Registered: September 28, 2017Report This Post
Tim A
Member
posted Hide Post
I double checked the field format and the data for opn_rec is definitely P15.2CM. So, i held the end results in a table file and printed. The blank months defaulted back to the $0.00 values. very strange but that seems to work.


WF 8.2
 
Posts: 22 | Location: US | Registered: September 28, 2017Report This Post
MartinY
Virtuoso
posted Hide Post
ACROSS is just another way to perform a BY where the ACROSS "order" the field in columns when a BY it's by rows.

So lets pretend that you create your report using only BY. If you don't have data in a specific measure for one of the Month, it won't show up in the result as a row, it's the same thing with ACROSS.
It's not a question of data type.

See below 2 sample and pay attention to "Capuccino"
-* With Cappuccino displayed
DEFINE FILE GGSALES
YR    /YY = DATE;
MM    /Mt = DATE;
SALES /P15.2CM = DOLLARS;
END
TABLE FILE GGSALES
SUM SALES  AS 'Sales'
BY REGION  AS ''
BY PRODUCT AS ''
ACROSS YR  AS ''
ACROSS MM  AS ''
WHERE REGION EQ 'Northeast';
WHERE YR EQ 1996;
END
-RUN

-* Without Cappuccino displayed
DEFINE FILE GGSALES
YR    /YY = DATE;
MM    /Mt = DATE;
SALES /P15.2CM = DOLLARS;
END
TABLE FILE GGSALES
SUM SALES  AS 'Sales'
BY REGION  AS ''
BY PRODUCT AS ''
ACROSS YR  AS ''
ACROSS MM  AS ''
WHERE REGION EQ 'Northeast';
WHERE YR EQ 1996;
WHERE MM LT 'Jul';
END
-RUN

And to have the missing data displayed with a valid formatted value, below could be an option
SET NODATA = $0.00
DEFINE FILE GGSALES
YR    /YY = DATE;
MM    /Mt = DATE;
SALES /P15.2CM = DOLLARS;
END
TABLE FILE GGSALES
SUM SALES  AS 'Sales'
BY REGION  AS ''
BY PRODUCT AS ''
ACROSS YR  AS ''
ACROSS MM  AS ''
WHERE REGION EQ 'Northeast';
WHERE YR EQ 1996;
-*WHERE MM LT 'Jul';
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
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] ACCROSS Data Format

Copyright © 1996-2020 Information Builders