Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Resolved] Changing BY literal value while reporting
Go
New
Search
Notify
Tools
Reply
  
[Resolved] Changing BY literal value while reporting
 Login/Join
 
Member
posted
I'm so new I squeak, but I'm trying to do an Excel BYTOC on a variable field across two appended files with different dates. With this code the TOC_DESC field to changes when the CAS_REGN_NM changes, but the TOC_DATE within TOC_DESC always has the date from the second TABLE FILE PRINT. I need to create Excel tabs for Each combination of CAS_REGN_NM and RPT_PER_END_DT. Thank you! Ron

Example Data:

CAS_REGN_NM TOC_DATE TOC_DESC RPT_PER_END_DT
Central 3/31/2009 Central - 03/31/2009 3/31/2008 0:00
Eastern 3/31/2009 Eastern - 03/31/2009 3/31/2008 0:00
Western 3/31/2009 Western - 03/31/2009 3/31/2008 0:00
Central 3/31/2009 Central - 03/31/2009 3/31/2009 0:00
Eastern 3/31/2009 Eastern - 03/31/2009 3/31/2009 0:00

Example Code:

DEFINE FILE TCAS_OFC_DMSN
TODTN/YYMD=HDATE(RPT_PER_END_DT ,'YYMD');
TODT/I8=TODTN;
TOCYR/A4=EDIT(PEDT,'9999$$$$');
TOCMO/A2=EDIT(PEDT,'$$$$99$$');
TOCDA/A2=EDIT(PEDT,'$$$$$$99');
TOC_DATE/A10=TOCMO | '/' |TOCDA | '/' | TOCYR;
TOC_DESC/A30= CAS_REGN_NM | ' - ' | TOC_DATE;
END

FILEDEF PERF002X1 DISK PERF002X1.CSV (APPEND
-RUN

TABLE FILE TCAS_OFC_DMSN
PRINT
R_FLD1
R_FLD2
CAS_REGN_NM
RPT_PER_END_DT
TOC_DATE
TOC_DESC
BY TOC_DESC NOPRINT
WHERE PRD_END_DATE EQ '&IVD_OOW_DATE';
WHERE R_FLD1 EQ 1;
ON TABLE HOLD AS PERF002X1
END

TABLE FILE TCAS_OFC_DMSN
PRINT
R_FLD1
R_FLD2
CAS_REGN_NM
RPT_PER_END_DT
TOC_DATE
TOC_DESC
BY TOC_DESC NOPRINT
WHERE PRD_END_DATE EQ '&PRD_END_DT';
ON TABLE HOLD AS PERF002X1
END

TABLE FILE PERF002X1
PRINT
TOC_DESC AS 'Region Name, Period End Date'
R_FLD1 AS '&HEAD1'
R_FLD2 AS '&HEAD2'
BY TOC_DESC NOPRINT
HEADING
ON TABLE SET PAGE-NUM OFF
ON TOC_DESC SUMMARIZE R_FLD1 R_FLD2 AS 'Total For Region '
ON TABLE SUMMARIZE R_FLD1 R_FLD2 AS 'Total '
ON TABLE PCHOLD FORMAT EXL2K BYTOC

This message has been edited. Last edited by: Ron,


WF 7.6.11
zLinux on Z10 Mainframe
Oracle DB
HTML, Excel & PDF Outputs
ronald.taffe@okdhs.org

Democracy is two wolves and a lamb voting to choose the dinner menu. Freedom is a well armed lamb quoting the constitution to them.
 
Posts: 10 | Location: Oklahoma, USA | Registered: April 10, 2009Reply With QuoteReport This Post
Member
posted Hide Post
Updated my signiture info.....


WF 7.6.11
zLinux on Z10 Mainframe
Oracle DB
HTML, Excel & PDF Outputs
ronald.taffe@okdhs.org

Democracy is two wolves and a lamb voting to choose the dinner menu. Freedom is a well armed lamb quoting the constitution to them.
 
Posts: 10 | Location: Oklahoma, USA | Registered: April 10, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
In order to get a separate tab for each unique combination of CAS_REGN_NAM (or TOC_DESC) and RPT_PER_END_DT, those two fields would have to be concatenated together and be placed as your primary sort field. That's the only way the BYTOC functionality would create a separate tab for each. Otherwise you just get a tab for each TOC_DESC.


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
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Reply With QuoteReport This Post
Member
posted Hide Post
Darin,
I have concatenated them, but the TOC_DATE is always the last accessed date.

quote:
TOC_DESC/A30= CAS_REGN_NM | ' - ' | TOC_DATE;


WF 7.6.11
zLinux on Z10 Mainframe
Oracle DB
HTML, Excel & PDF Outputs
ronald.taffe@okdhs.org

Democracy is two wolves and a lamb voting to choose the dinner menu. Freedom is a well armed lamb quoting the constitution to them.
 
Posts: 10 | Location: Oklahoma, USA | Registered: April 10, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Ron,

The sample data and code you posted is not complete.
In the data I miss things like R_FLD1 and 2, PEDT, PRD_END_DATE.
In the code I miss the values for the &'s that you use.
And that makes it a little bit harder to exactly pinpoint your problem.
The following code sort of looks like yours and provides the correct output, at least in my setup (still on 764 for this post).
-* Example master file
EX -LINES 11 EDAPUT MASTER,TCAS_OFC_DMSN,CV,FILE
FILENAME=TESTDATA, SUFFIX=FIX,$
SEGNAME=TEST_DATA, $
  FIELD=CAS_REGN_NM   , ALIAS= ,A07 ,A07, $
  FIELD=DUMMY1        , ALIAS= ,A10, A10, $
  FIELD=DUMMY2        , ALIAS= ,A01 ,A01, $
  FIELD=TOC_DESC      , ALIAS= ,A20 ,A20, $
  FIELD=RPT_PER_END_DT, ALIAS= ,HYYMDS, A15, $
  FIELD=PEDT          , ALIAS= ,A08, A08, $
  FIELD=R_FLD1        , ALIAS= ,A08, A08, $
  FIELD=R_FLD2        , ALIAS= ,A08, A08, $

-*Example Data:
EX -LINES 6 EDAPUT FOCTEMP,TCAS_OFC_DMSN,CV,FILE
Central03/31/2009 Central - 03/31/2009 3/31/2008 0:0020100101TESTDATA12345678
Eastern03/31/2009 Eastern - 03/31/2009 3/31/2008 0:0020100201DATATEST23456789
Western03/31/2009 Western - 03/31/2009 3/31/2008 0:0020100301TSTDAT  34567890
Central03/31/2009 Central - 03/31/2009 3/31/2009 0:0020100401DATTST  45678901
Eastern03/31/2009 Eastern - 03/31/2009 3/31/2009 0:0020100501TESTDAT 56789012

FILEDEF TCAS_OFC_DMSN DISK TCAS_OFC_DMSN.FTM (RECFM V LRECL 77
TYPE TCAS_OFC_DMSN.FTM

-* Example Code:
DEFINE FILE TCAS_OFC_DMSN
TODTN/YYMD=HDATE(RPT_PER_END_DT ,'YYMD');
TODT/I8=TODTN;
TOCYR/A4=EDIT(PEDT,'9999$$$$');
TOCMO/A2=EDIT(PEDT,'$$$$99$$');
TOCDA/A2=EDIT(PEDT,'$$$$$$99');
TOC_DATE/A10=TOCMO | '/' |TOCDA | '/' | TOCYR;
TOC_DESC/A30= CAS_REGN_NM | ' - ' | TOC_DATE;
END

FILEDEF PERF002X1 DISK PERF002X1.CSV (APPEND
-RUN

TABLE FILE TCAS_OFC_DMSN
PRINT 
R_FLD1
R_FLD2
CAS_REGN_NM
RPT_PER_END_DT
TOC_DATE
TOC_DESC
BY TOC_DESC NOPRINT
ON TABLE HOLD AS PERF002X1
END

TABLE FILE TCAS_OFC_DMSN
PRINT 
R_FLD1
R_FLD2
CAS_REGN_NM
RPT_PER_END_DT
TOC_DATE
TOC_DESC
BY TOC_DESC NOPRINT
ON TABLE HOLD AS PERF002X1
END

TABLE FILE PERF002X1
PRINT 
TOC_DESC AS 'Region Name, Period End Date'
R_FLD1
R_FLD2
BY TOC_DESC NOPRINT
ON TABLE SET PAGE-NUM OFF
ON TOC_DESC SUMMARIZE R_FLD1 R_FLD2 AS 'Total For Region '
ON TABLE SUMMARIZE R_FLD1 R_FLD2 AS 'Total '
ON TABLE PCHOLD FORMAT EXL2K BYTOC
END
I get 5 different tabs / worksheets with the correct names displayed on them.


GamP

- Using AS 8.2.01 on Windows 7 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Member
posted Hide Post
GamP,
Thank you so much for the fast, detailed response! That solution works fine with 1 input file. I am reading an Oracle twice with two different dates and appending the file. The simplified test data would be:


CAS_REGN_NM RPT_PER_END_DT R_FLD1

TABLE FILE 1
Central 3/31/2008 0:00 1
Eastern 3/31/2008 0:00 1
Western 3/31/2008 0:00 1

TABLE FILE 2
Central 3/31/2009 0:00 1
Eastern 3/31/2009 0:00 1
Western 3/31/2009 0:00 1


Simplified Code:

DEFINE FILE TCAS_OFC_DMSN
TODTN/YYMD=HDATE(RPT_PER_END_DT ,'YYMD');
TODT/I8=TODTN;
TOCYR/A4=EDIT(PEDT,'9999$$$$');
TOCMO/A2=EDIT(PEDT,'$$$$99$$');
TOCDA/A2=EDIT(PEDT,'$$$$$$99');
TOC_DATE/A10=TOCMO | '/' |TOCDA | '/' | TOCYR;
TOC_DESC/A30= CAS_REGN_NM | ' - ' | TOC_DATE;
END

(FILEDEF PERF002X1 DISK PERF002X1.CSV APPEND
-RUN

TABLE FILE TCAS_OFC_DMSN (read 1)
PRINT
R_FLD1
CAS_REGN_NM
RPT_PER_END_DT
TOC_DATE
TOC_DESC
BY TOC_DESC NOPRINT
ON TABLE HOLD AS PERF002X1
END

TABLE FILE TCAS_OFC_DMSN (read 2)
PRINT
R_FLD1
CAS_REGN_NM
RPT_PER_END_DT
TOC_DATE
TOC_DESC
BY TOC_DESC NOPRINT
ON TABLE HOLD AS PERF002X1
END

TABLE FILE PERF002X1
PRINT
TOC_DESC AS 'Region Name, Period End Date'
R_FLD1 AS 'Field 1 heading'
BY TOC_DESC NOPRINT
HEADING
ON TABLE SET PAGE-NUM OFF
ON TOC_DESC SUMMARIZE R_FLD1 AS 'Total For Region '
ON TABLE SUMMARIZE R_FLD1 AS 'Total '
ON TABLE PCHOLD FORMAT EXL2K BYTOC


WF 7.6.11
zLinux on Z10 Mainframe
Oracle DB
HTML, Excel & PDF Outputs
ronald.taffe@okdhs.org

Democracy is two wolves and a lamb voting to choose the dinner menu. Freedom is a well armed lamb quoting the constitution to them.
 
Posts: 10 | Location: Oklahoma, USA | Registered: April 10, 2009Reply With QuoteReport This Post
Member
posted Hide Post
Solved...... It helps the use the correct fields when formatting the date. Thanks to all who have dropped by or responded.


WF 7.6.11
zLinux on Z10 Mainframe
Oracle DB
HTML, Excel & PDF Outputs
ronald.taffe@okdhs.org

Democracy is two wolves and a lamb voting to choose the dinner menu. Freedom is a well armed lamb quoting the constitution to them.
 
Posts: 10 | Location: Oklahoma, USA | Registered: April 10, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Good for you!
Could you please change the topic to have it also say [solved]?
You can do that by going to your first post in this thread and edit it. It will allow you to change the description.
Thanks!


GamP

- Using AS 8.2.01 on Windows 7 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Resolved] Changing BY literal value while reporting

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.