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] Show data in report for "sub" groups where the data is missing

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Show data in report for "sub" groups where the data is missing
 Login/Join
 
Luiz De Assis
Platinum Member
posted
Hello,

I'm creating a report where there is a "fixed" BY column" that shows a sales point range that needs to appear in every section of the report regardless of having data or not for a particular department, sub-department or class:

SALES RANGE (PP_DESC) SALES
0.01 - 50.00
50.01 - 100.00
100.01 - 150.00
150.01 - 200.00
200.01 - 250.00
|
|
|
10,000.01 - 9,999,999.99

The report is broken down by Division, Department, Sub-Department, and Class. I can get all the "sale ranges" to show at the Division level, but once I started going to the lower levels, I can only get some of the "sale ranges" to show. I've tried joins and MATCH, but can't get it to work. I've also have a DEFINE for each range using logic that includes "IS MISSING", but no luck either.

PP_DESC/A50 MISSING ON = IF ((PP_ID EQ 1) AND (SUBDPTID IS MISSING OR SUBDPTID_DESC IS MISSING)) THEN PRICE_POINT
ELSE
IF ((PP_ID EQ 2) AND (SUBDPTID IS MISSING OR SUBDPTID_DESC IS MISSING)) THEN PRICE_POINT
ELSE ....

Appreciate any suggestions you might have. Thanks

Luiz

This message has been edited. Last edited by: Kerry,
 
Posts: 117 | Location: Denver | Registered: July 27, 2005Report This Post
DavSmith
Platinum Member
posted Hide Post
Read the fabulous manual section on "Creating Financial Reports", you'll find new uses for FOR and OVER. Also, referred to as FML (and before that FRL). Here's an example:

TABLE FILE CAR
SUM CNT.SALES SALES
BY COUNTRY
FOR SALES
0     TO  9999 AS '    0- 9999' LABEL R1 OVER
10000 TO 19999 AS '10000-19999' LABEL R2 OVER
20000 TO 29999 AS '20000-29999' LABEL R3 OVER
30000 TO 39999 AS '30000-39999' LABEL R4 OVER
40000 TO 49999 AS '40000-49999' LABEL R5 
ON TABLE SET PAGE NOLEAD
END


Advantage is the missing data will always show a row.

Disadvantage is you must hardcode the ranges.

Note that ranged grouping is based on detail sales value.



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
 
Posts: 161 | Location: Dallas, TX | Registered: February 20, 2009Report This Post
Waz
Expert
posted Hide Post
If you are using the FOR statement, you can have dialog manager generate the rows, with some quick analysis of the data in a prior table file.


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
Luiz De Assis
Platinum Member
posted Hide Post
Thanks guys for the suggestions. I'll try FML first, and see if works for the report.

Waz, do you have an example of using FOR and DM together? Thanks

L
 
Posts: 117 | Location: Denver | Registered: July 27, 2005Report This Post
Waz
Expert
posted Hide Post
Here is an example expanding on Daves one.

TABLE FILE CAR
SUM MIN.SALES
    MAX.SALES
ON TABLE SAVE AS SAV_SALE
END
-RUN
-READ SAV_SALE &MIN_SALES.A6. &MAX_SALES.A6.

-TYPE Minimum Sales &MIN_SALES
-TYPE Maximum Sales &MAX_SALES

-SET &BLOCK_SIZE = 10000 ;
-SET &FOR_BLOCKS = &MAX_SALES / &BLOCK_SIZE ;
-TYPE Blocks = &FOR_BLOCKS

TABLE FILE CAR
SUM CNT.SALES SALES
BY COUNTRY
FOR SALES
-SET &Over = ' ' ;
-SET &Cntr = 0 ;
-SET &FOR_END = &FOR_BLOCKS - 1 ;
-REPEAT FOR_LOOP FOR &Cntr FROM 0 TO &FOR_END ;
-SET &From = &BLOCK_SIZE * &Cntr ;
-SET &To   = &BLOCK_SIZE * (&Cntr+1) - 1 ;
&Over &From TO &To AS '&From - &To' LABEL R&Cntr
-SET &Over = 'OVER' ;
-FOR_LOOP
ON TABLE SET PAGE NOLEAD
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
Luiz De Assis
Platinum Member
posted Hide Post
Waz,

I was able to get the logic to work using some FML code. Thank you very much for the example though.

This issue has been resolved.

Luiz
 
Posts: 117 | Location: Denver | Registered: July 27, 2005Report This Post
DavSmith
Platinum Member
posted Hide Post
Waz, Nice dynamic example!



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
 
Posts: 161 | Location: Dallas, TX | Registered: February 20, 2009Report This Post
Waz
Expert
posted Hide Post
Thanks Dave,

But it still is a quick one. Smiler


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
  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] Show data in report for "sub" groups where the data is missing

Copyright © 1996-2020 Information Builders