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]Report Consolidation

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED]Report Consolidation
 Login/Join
 
Silver Member
posted
Hi Guys,

I am wondering if i can consolidate the following reports as 1,

1) Gender vs State
2) Gender vs Type
3) Gender vs Race

The common totalling field is Male and Female.

Anyone can enlighten me on how to consolidate the report into 1 so that user can have different view instead of going through 3 reports.

Thanks in advance.

This message has been edited. Last edited by: Kevin Tong,


WebFOCUS 7.6.9
Windows 2003, all output
 
Posts: 32 | Location: Malaysia | Registered: December 29, 2009Report This Post
Expert
posted Hide Post
What this looks like is a three dimensional report, or is it four.

Could you use a report that utilises ACROSS ?

Are these three reports large ?

How are they going to be used, could you utilise OLAP ?


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
Silver Member
posted Hide Post
Waz thanks for replying.

It should be a 3 dimensional report, have tried using OLAP, but result isn't what i wanted though. Maybe i am not too familiar with it as in dimension reports, i can't find the flexibility way to view it as dimension by dimension , e.g. during report run, default is by Gender vs State when i choose a different dimension will oni show Gender vs Type and so on...

The data would be quite large as it comes from a credit card database for a bank.

Any guidance appreciated...thx

Below is what i have tried...

-OLAP ON
-* File GenderTest.fex

JOIN
LEFT_OUTER CUST.CUST.IPKIPN IN CUST TO UNIQUE APPL.APPL.ANKIPN IN APPL TAG J0
AS J0
END

JOIN
LEFT_OUTER CUST.CUST.IPKIPN IN CUST TO UNIQUE EMP.EMP.IPKIPN IN EMP TAG J1
AS J1
END

DEFINE FILE CUST
MALE/I7 = IF IPSEX EQ 1 THEN + 1 ELSE + 0;
FEMALE/I7 = IF IPSEX EQ 2 THEN + 1 ELSE + 0;
APPTYPE/A20 = IF J0.APPL.ANATP1 EQ 'B' THEN 'BASIC' ELSE IF J0.APPL.ANATP1 EQ 'E' THEN 'EXTRA' ELSE ' ';
END

OLAP DIMENSIONS
-* DIMENSIONS FILE CUST
State: 'CUST.CUST.IPLSTE'
Race: CUST.CUST.IPRACE;
Application Type: J0.APPL.APPTYPE;
END

TABLE FILE CUST
SUM
'CUST.CUST.MALE' AS 'Male'
'CUST.CUST.FEMALE' AS 'Female'
BY 'CUST.CUST.IPLSTE' AS 'State'
BY 'CUST.CUST.IPRACE' AS 'Race'
BY 'J0.APPL.APPTYPE' AS 'Application Type'

This message has been edited. Last edited by: Kevin Tong,


WebFOCUS 7.6.9
Windows 2003, all output
 
Posts: 32 | Location: Malaysia | Registered: December 29, 2009Report This Post
Expert
posted Hide Post
So you have Sex, two values.
Application Type, three values (Assuming its the define)
State, Assumed this is the state in the country, not state of play.
And Race, ??

What if the last TABLE FILE is

TABLE FILE CUST
SUM
'CUST.CUST.MALE' AS 'Male'
'CUST.CUST.FEMALE' AS 'Female'
BY 'CUST.CUST.IPLSTE' AS 'State'
BY 'CUST.CUST.IPRACE' AS 'Race'
ACROSS 'J0.APPL.APPTYPE' AS 'Application Type'


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

Thanks for replying.

Yeah you are right, State as in states in a country and Race as in Chinese, Indian, Malay and etc on the ethnic group/tribe.

I have tried ACROSS but it looks abit weird and with too many Male and Female totalling columns and it looks abit scattered.

Will it be better to stick with all BY and educate users on to use the OLAP options to remove the Drill Down fields if they dun wish to be there?

Thanks.


WebFOCUS 7.6.9
Windows 2003, all output
 
Posts: 32 | Location: Malaysia | Registered: December 29, 2009Report This Post
Expert
posted Hide Post
The other thing, if you have it, would be active reports. Its similar to OLAP, not sure about high record numbers though. We don't have it here.


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
Expert
posted Hide Post
There are a couple of MacGyver technique examples that you might be able to use to accomplish your task.
MacGyver Examples
Look at the last two examples specifically. You'd have to create a phony sort field that would either have the state, the type, or the race in it depending on the value of the MacGyver counter. Then have counter fields for male and female. Your end report would look something like this:
DEFINE FILE ...
MAC_DESC/A10=IF MAC_CNTR EQ 1 THEN 'RACE' ELSE IF MAC_CNTR EQ 2 THEN 'TYPE' ELSE IF MAC_CNTR EQ 3 THEN 'STATE' ELSE ' ';
MAC_SORT/Ann=IF MAC_CNTR EQ 1 THEN RACE ELSE IF MAC_CNTR EQ 2 THEN APP_TYPE ELSE IF MAC_CNTR EQ 3 THEN STATE ELSE ' ';
MALE/I4=IF GENDER EQ 'M' THEN 1 ELSE 0; 
FEMALE/I4=IF GENDER EQ 'F' THEN 1 ELSE 0;
TABLE FILE ...
SUM MALE FEMALE
BY MAC_CNTR NOPRINT
BY MAC_DESC
BY MAC_SORT
IF MAC_CNTR LE 3
END

You still have to create your MacGuyver file and add the join.

Just an idea.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Guru
posted Hide Post
TABLE FILE CUST
SUM CUSTOMER
BY RACE
ACROSS SEX
ACROSS APPTYPE
WHERE COUNTRY EQ '&COUNTRY' OR '&COUNTRY' EQ 'ALL'
WHERE STATE EQ '&STATE' OR '&STATE' EQ 'ALL'
END


Here, by giving only the basic info, you can control the report format while users dictate the scope of the view. You are not limited to one geographic location.

Hua


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Silver Member
posted Hide Post
Thx Ginny for your suggestion, I am trying the MacGyver technique but I am stuck as it keeps saying (FOC1109) DATA IS NOT PRESENT FOR FOCUS FILE : MACGYVER

My codes might not be correct as I am playing around with it. Can you please have a look and enlighten me on how it should be...

MacGyver File DefinitionCodes

FILENAME=macgyver, SUFFIX=FOC , $
SEGMENT=SEG1, SEGTYPE=S0, $
FIELDNAME=CONTROL, ALIAS=BLANK, USAGE=A1, $
SEGMENT=SEG2, SEGTYPE=S0, PARENT=SEG1, $
FIELDNAME=FIELD, USAGE=A20, $
FIELDNAME=MAC_CNTR, ALIAS=ORDER, USAGE=I4, $


Codes

JOIN CLEAR *
JOIN IPKN IN CUST TO ANKIPN IN APPL TAG J0 AS J0
JOIN J0.ANKIPN IN CUST TO IPKIPN IN EMP TAG J1 AS J1
JOIN BLANK WITH J1.IPKIPN IN CUST TO BLANK IN MACGYVER AS AJ

DEFINE FILE CUST
MAC_DESC/A10=IF MAC_CNTR EQ 1 THEN 'RACE' ELSE IF MAC_CNTR EQ 2 THEN 'TYPE' ELSE IF MAC_CNTR EQ 3 THEN 'STATE' ELSE ' ';
MAC_SORT/A20=IF MAC_CNTR EQ 1 THEN CUST.CUST.IPRACE ELSE IF MAC_CNTR EQ 2 THEN J0.APPL.ANATP1 ELSE IF MAC_CNTR EQ 3 THEN CUST.CUST.IPLSTE ELSE ' ';
MALE/I4=IF IPSEX EQ 1 THEN 1 ELSE 0;
FEMALE/I4=IF IPSEX EQ 2 THEN 1 ELSE 0;
END

TABLE FILE CUST
SUM MALE FEMALE
BY MAC_CNTR NOPRINT
BY MAC_DESC
BY MAC_SORT
IF MAC_CNTR LE 3


Thank you guys for helping...


WebFOCUS 7.6.9
Windows 2003, all output
 
Posts: 32 | Location: Malaysia | Registered: December 29, 2009Report This Post
Expert
posted Hide Post
Did you create file Macgyver focus file ?

The link that Ginny gave you shows you how.


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
Expert
posted Hide Post
You can create your own with this.

EX -LINES 7 EDAPUT MASTER,fseq,CV,FILE
FILE=FSEQ, SUFFIX=FIX
  SEGNAME=SEG1
   FIELD=CONTROL, BLANK , A1, A1, $
  SEGNAME=SEG2, PARENT=SEG1, OCCURS=VARIABLE
   FIELD=WHATEVER, , A1, A1, $
   FIELD=COUNTER, ORDER, I4,  I4,$

-SET &CNT_MAX = 100 ;

FILEDEF FSEQ DISK fseq.mas (LRECL &CNT_MAX

TABLE FILE FSEQ 
PRINT COUNTER
      COMPUTE BLANK/A1 = ' ' ;
WHERE RECORDLIMIT EQ &CNT_MAX
ON TABLE HOLD AS FSEQ FORMAT FOCUS INDEX BLANK
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
Virtuoso
posted Hide Post
Kevin, your code structure looks fine to me except for the non-existence of the "BLANK" field which is required for the JOIN to work (it's all part of the MacGyver technique).

Waz's code illustrates the concept very clearly:

quote:
COMPUTE BLANK/A1 = ' ' ;


If you still want to use your "macgyver" masterfile try adding the following:

BLANK/A1 = " ";


right below your DEFINE FILE CUST structure and see how it goes. It should behave similarly to the example Waz provided you with.

- Neftali.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
Perhaps my "explanation" above was not very clear ... this is what you may need to do:

JOIN CLEAR *
JOIN IPKN IN CUST TO ANKIPN IN APPL TAG J0 AS J0
JOIN J0.ANKIPN IN CUST TO IPKIPN IN EMP TAG J1 AS J1
JOIN BLANK WITH J1.IPKIPN IN CUST TO BLANK IN MACGYVER AS AJ
-*   ^^^^^ This field is required to join to the MG master

DEFINE FILE CUST
BLANK/A1 = " ";   <-- Definition of the field your JOIN needs
MAC_DESC/A10=IF MAC_CNTR EQ 1 THEN 'RACE' ELSE IF MAC_CNTR EQ 2 THEN 'TYPE' ELSE IF MAC_CNTR EQ 3 THEN 'STATE' ELSE ' ';
MAC_SORT/A20=IF MAC_CNTR EQ 1 THEN CUST.CUST.IPRACE ELSE IF MAC_CNTR EQ 2 THEN J0.APPL.ANATP1 ELSE IF MAC_CNTR EQ 3 THEN CUST.CUST.IPLSTE ELSE ' ';
MALE/I4=IF IPSEX EQ 1 THEN 1 ELSE 0;
FEMALE/I4=IF IPSEX EQ 2 THEN 1 ELSE 0;
END

TABLE FILE CUST
SUM MALE FEMALE
BY MAC_CNTR NOPRINT
BY MAC_DESC
BY MAC_SORT
IF MAC_CNTR LE 3
END


- Neftali.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Silver Member
posted Hide Post
Hi Neftali,

Thanks for providing me the pointer. But i still get this message (FOC1109) DATA IS NOT PRESENT FOR FOCUS FILE : MACGYVER

MacGyver File Definition

FILENAME=macgyver, SUFFIX=FOC , $
SEGMENT=SEG1, SEGTYPE=S0, $
FIELDNAME=CONTROL, ALIAS=BLANK, USAGE=A1, $
SEGMENT=SEG2, SEGTYPE=S0, PARENT=SEG1, $
FIELDNAME=FIELD, USAGE=A20, $
FIELDNAME=MAC_CNTR, ALIAS=ORDER, USAGE=I4, $


Codes

JOIN CLEAR *
JOIN IPKN IN CUST TO ANKIPN IN APPL TAG J0 AS J0
JOIN J0.ANKIPN IN CUST TO IPKIPN IN EMP TAG J1 AS J1
JOIN BLANK WITH J1.IPKIPN IN CUST TO BLANK IN MACGYVER AS AJ

DEFINE FILE CUST
BLANK/A1 = ' ';
MAC_DESC/A10=IF MAC_CNTR EQ 1 THEN 'RACE' ELSE IF MAC_CNTR EQ 2 THEN 'TYPE' ELSE IF MAC_CNTR EQ 3 THEN 'STATE' ELSE ' ';
MAC_SORT/A20=IF MAC_CNTR EQ 1 THEN CUST.CUST.IPRACE ELSE IF MAC_CNTR EQ 2 THEN J0.APPL.ANATP1 ELSE IF MAC_CNTR EQ 3 THEN CUST.CUST.IPLSTE ELSE ' ';
MALE/I4=IF IPSEX EQ 1 THEN 1 ELSE 0;
FEMALE/I4=IF IPSEX EQ 2 THEN 1 ELSE 0;
END

TABLE FILE CUST
SUM MALE FEMALE
BY MAC_CNTR NOPRINT
BY MAC_DESC
BY MAC_SORT
IF MAC_CNTR LE 3

Seems like it is not able to join with the Macgyver file, is there anything i am missing? 1st time trying out with the Macgyver technique...


WebFOCUS 7.6.9
Windows 2003, all output
 
Posts: 32 | Location: Malaysia | Registered: December 29, 2009Report This Post
Expert
posted Hide Post
Kevin, the master file MACGYVER, requires a FOCUS file,MACGYVER, to be created.

Did you do this ?

If you use the code I posted, and change COUNTER to MAC_CNTR, and put this before your join, then it should work.

If not, then please post your code.


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
Silver Member
posted Hide Post
Thanks guys for the effort and help....I will try again and tag this as close.


WebFOCUS 7.6.9
Windows 2003, all output
 
Posts: 32 | Location: Malaysia | Registered: December 29, 2009Report This Post
Virtuoso
posted Hide Post
Sorry for the late reply, but some variation of this code might work. I assume column IPSEX is in table EMP, and therefore requires the second JOIN?

SET ASNAMES = ON
-*
JOIN CLEAR *
JOIN IPKN IN CUST TO ANKIPN IN APPL TAG J0 AS J0
JOIN J0.ANKIPN IN CUST TO IPKIPN IN EMP TAG J1 AS J1
-*
TABLEF FILE CUST
 COUNT IPKN/I9 AS 'CUST_CNT'
 BY IPLSTE
 BY IPRACE
 BY APPTYPE
 BY IPSEX
 ON TABLE HOLD AS DATAHOLD
END
-RUN
-*
DEFINE FILE DATAHOLD
 DIMENSION/A5 = 'STATE';
 DIM_SORT/I1  = 3 ;
END
-*
TABLE FILE DATAHOLD
 SUM CUST_CNT
 BY DIM_SORT
 BY DIMENSION
 BY IPLSTE AS 'CATEGORY'
 BY IPSEX
 ON TABLE HOLD AS DIMHOLD
END
-*
APP FILEDEF DIMHOLD DISK dimhold.ftm (APPEND
-*
DEFINE FILE DATAHOLD
 DIMENSION/A5 = 'RACE';
 DIM_SORT/I1  = 1 ;
END
-*
TABLE FILE DATAHOLD
 SUM CUST_CNT
 BY DIM_SORT
 BY DIMENSION
 BY IPRACE
 BY IPSEX
 ON TABLE SAVB AS DIMHOLD
END
-*
DEFINE FILE DATAHOLD
 DIMENSION/A5 = 'TYPE';
 DIM_SORT/I1  = 2 ;
END
-*
TABLE FILE DATAHOLD
 SUM CUST_CNT
 BY DIM_SORT
 BY DIMENSION
 BY APPTYPE
 BY IPSEX
 ON TABLE SAVB AS DIMHOLD
END
-RUN
-*
DEFINE FILE DIMHOLD
 GENDER/A6 = IF IPSEX EQ 1 THEN 'MALE' ELSE IF IPSEX EQ 2 THEN 'FEMALE' ELSE '???';
END
-*
TABLE FILE DIMHOLD
 SUM CUST_CNT AS ''
 BY DIM_SORT NOPRINT
 BY DIMENSION
 BY CATEGORY
 ACROSS GENDER AS ''
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Silver Member
posted Hide Post
Thank you Dan for your help...


WebFOCUS 7.6.9
Windows 2003, all output
 
Posts: 32 | Location: Malaysia | Registered: December 29, 2009Report 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]Report Consolidation

Copyright © 1996-2020 Information Builders