Master Detail

In Focus since 1993. WebFOCUS 7.7.03 Win 2003

November 26, 2007, 06:05 PM

Darin Lee

depends on the quantity and type of data. you haven't included any of that so I can only generalize--

One way that we do this is to put all of the fields for each "master" record in a subhead with a BY on a unique field for each record. Then printing the "detail" fields pertaining to that "master" record under each record. You can also accomplish something similar with FOLD-LINE but the GUI didn't handle this very well several versions back when i was playing with it and so I never did anything with it in GUI then. We have several using this technique as well that have been hand-written. Maybe it's handled better now in the GUI.

Regards,

Darin

In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat

November 27, 2007, 09:45 AM

Tom Brittnacher

Prarie, I did some research on compound reports, not sure if that would help me out.

Darin Lee, I tried FOLD-LINE, but that seems to only be for PDF. I also tried SUBHEAD, but I want the master record to have column headings, and be in distinct columns so it can be exported to Excel.

The data I am working with has monetary transaction records, and there could be one or more comments attached to each record, typically 2 comments at least.

So...
ACCOUNTNUMBER POSTDATE AMOUNT
12345 11/26/2007 25.00
user123 11/27/2007 this is my comment
user123 11/27/2007 this is another comment

November 27, 2007, 09:47 AM

Francis Mariani

Can't you just stick the Master data in a SUBHEAD and make the detail records the rows of the report?

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

November 27, 2007, 10:12 AM

jgelona

When you analyze the question, this is just basic FOCUS. You have a file with a parent records (master) and child records (detail). You don't say what the data source is, but it should be irrelevant if you have properly described the data in your .mas file. Consider the following master. Now this is a flat file, but you can do the same thing with a FOCUS data base or relational tables. HREC is the Header and is identified with 'H' in column 1. DREC is the Detail and identified with 'D' in column 1. This master has the relationship you described. For every header, there can be 0 to many detail rows.

FILE=FMFCEXT ,SUFFIX=FIX
$
$ Financial Management Foster Care Extracts
$
 SEGNAME=HREC,$
   FIELD=RECTYPE     , ALIAS=H, A1  , A1  , $
   FIELD=RPT_DATE    ,        , MYY , A6  , $
   FIELD=RSRC_NAME   ,        , A30 , A30 , $
   FIELD=CONTRACT_NO ,        , A6  , A6  , $
   FIELD=COUNTY_ID   ,        , A2  , A2  , $
   FIELD=ADDRVER     ,        , A1  , A1  , $
   FIELD=ADDRESS     ,        , A40 , A40 , $
   FIELD=CITY        ,        , A13 , A13 , $
   FIELD=STATE       ,        , A2  , A2  , $
   FIELD=ZIP         ,        , A10 , A10 , $
   FIELD=ADFILLER    ,        , A19 , A19 , $
   FIELD=RSRC_SSN    ,        , A9  , A9  , $
   FIELD=SUPL_SW     ,        , A1  , A1  , $
   FIELD=APPROVED_BY ,        , A10 , A10 , $
   FIELD=H1FILL      ,        , A7  , A7  , $
   FIELD=RSRC_ID     ,        , A8  , A8  , $
   FIELD=CHECK_SEQ   ,        , A5  , A5  , $
   FIELD=H2FILL      ,        , A30 , A30 , $
 SEGNAME=DREC,PARENT=HREC,$
   FIELD=RECTYPE     , ALIAS=D, A1  , A1  , $
   FIELD=CASE_ID     ,        , A10 , A10 , $
   FIELD=PERS_NO     ,        , I3  , A3  , $
   FIELD=CMD_CASE_ID ,        , A7  , A7  , $
   FIELD=CMD_PERS_NO ,        , I3  , A3  , $
   FIELD=CARE_TYPE   ,        , A2  , A2  , $
   FIELD=UNIT_CODE   ,        , A2  , A2  , $
   FIELD=CLIENT_LNM  ,        , A12 , A12 , $
   FIELD=CLIENT_FNM  ,        , A7  , A7  , $
   FIELD=CLIENT_MNM  ,        , A1  , A1  , $
   FIELD=CLIENT_BDT  ,        , YYMD, A8  , $
   FIELD=CLIENT_SSN  ,        , A9  , A9  , $
   FIELD=BEGIN_DAY   ,        , I2  , A2  , $
   FIELD=END_DAY     ,        , I2  , A2  , $
   FIELD=TOTAL_DAYS  ,        , I2  , A2  , $
   FIELD=PAY_RATE    ,        , I5  , A5  , $
   FIELD=CLAIM_AMT   ,        , I7  , A7  , $
   FIELD=ADJ_SW      ,        , A1  , A1  , $
   FIELD=ADJ_DT      ,        , MYY , A6  , $
   FIELD=DFILL1      ,        , A35 , A35 , $
   FIELD=IVESTS      ,        , A2  , A2  , $
   FIELD=LICENSED    ,        , A1  , A1  , $
   FIELD=DCN         ,        , A9  , A9  , $
   FIELD=DFILL2      ,        , A63 , A63 , $

Your basic fex will look something like this:

TABLE FILE FMFCEXT
PRINT CLIENT_SSN TOTAL_DAYS PAY_RATE CLAIM_AMT
   BY RSRC_ID NOPRPINT
   BY CHECK_SEQ NOPRINT NOSPLIT
   ON CHECK_SEQ SUBHEAD
"<RSRC_NAME>"
"<ADDRESS>"
"<CITY <STATE>  <ZIP></1>"
   ON TABLE SET ALL ON
END

Once this is working, then you can add other fields, formatting, etc.

In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.

November 27, 2007, 10:49 AM

Tom Brittnacher

I understand what you are saying jgelona, but I still have the issue I mentioned above about the report not fitting into columns.

November 27, 2007, 11:21 AM

Francis Mariani

TABLE FILE CAR
PRINT
LENGTH
WIDTH
HEIGHT
WEIGHT
WHEELBASE
BY COUNTRY NOPRINT
BY CAR NOPRINT
ON CAR SUBHEAD
"COUNTRY <+0> CAR"
"<COUNTRY<CAR"
ON TABLE SET STYLE *
TYPE=SUBHEAD, HEADALIGN=BODY, $ 
END

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

November 27, 2007, 01:21 PM

GinnyJakes

Tom,

quote:
I understand what you are saying jgelona, but I still have the issue I mentioned above about the report not fitting into columns.

Are your comments too wide? Is that the problem?

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

November 27, 2007, 01:32 PM

Tom Brittnacher

Thanks Francis for your detailed posting, that makes perfect sense and that is exactly what I am going to use.

This issue is resolved.

November 27, 2007, 01:37 PM

Francis Mariani

Glad it worked out.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server