Hello!
I have created a compound report that includes 9 fml reports and the output is posted to 9 DAT files. All reports are generated from the same HOLD file. Everything works fine, except that when I try to use PICKUP to bring the lines over my main FML, and do some calculation via RECAP, the posted lines are not showing. I have used post & pickup many times and it always worked. Any suggestions? Part of the code is below. Thanks!
Luiz

APP HOLD LUIZ_NEW

-INCLUDE 002_qrt_main_crosschannel

-* STEP A

-SET &STDCSTQ = 'QWHLA';
FILEDEF QWHLA DISK E:\ibi\apps\LUIZ_NEW\QWHLA.DAT
-SET &TITLETEXT = 'QWHLA';

TABLE FILE HOLDCC2
SUM
Q1_PRI AS ''
Q2_PRI AS ''
Q3_PRI AS ''
Q4_PRI AS ''

Q1_ACT AS ''
Q2_ACT AS ''
Q3_ACT AS ''
Q4_ACT AS ''

Q1_BUD AS ''
Q2_BUD AS ''
Q3_BUD AS ''
Q4_BUD AS ''

FOR
MARGIN_TYPE
'1CHANNEL EXP' AS 'WHL-Step A' LABEL R1 POST TO QWHLA
WHERE COST_POOL_OWNER EQ 'GRP_CONSUMER';
WHERE BU_EVP EQ 'GRPBMG';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE PCHOLD FORMAT EXL2K NOBREAK
-*ON TABLE HOLD AS QWHLA
END

-* GROUP CONSUMER: STEP B
-SET &STDCSTQ = 'QWHLB';
FILEDEF QWHLB DISK E:\ibi\apps\LUIZ_NEW\QWHLB.DAT
-SET &TITLETEXT = 'QWHLB';
TABLE FILE HOLDCC2
SUM
Q1_PRI AS ''
Q2_PRI AS ''
Q3_PRI AS ''
Q4_PRI AS ''

Q1_ACT AS ''
Q2_ACT AS ''
Q3_ACT AS ''
Q4_ACT AS ''

Q1_BUD AS ''
Q2_BUD AS ''
Q3_BUD AS ''
Q4_BUD AS ''

FOR
MARGIN_TYPE
'1CHANNEL EXP' AS 'WHL-Step B' LABEL R1 POST TO QWHLB
WHERE COST_POOL_OWNER EQ 'GRP_CONSUMER';
WHERE BU_EVP EQ 'GRPWHL';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE PCHOLD FORMAT EXL2K NOBREAK
-*ON TABLE HOLD AS QWHLB
END

-* GROUP BMG: STEP C
-SET &STDCSTQ = 'QWHLC';
FILEDEF QWHLC DISK E:\ibi\apps\LUIZ_NEW\QWHLC.DAT
-SET &TITLETEXT = 'QWHLC';
TABLE FILE HOLDCC2
SUM
Q1_PRI AS ''
Q2_PRI AS ''
Q3_PRI AS ''
Q4_PRI AS ''

Q1_ACT AS ''
Q2_ACT AS ''
Q3_ACT AS ''
Q4_ACT AS ''

Q1_BUD AS ''
Q2_BUD AS ''
Q3_BUD AS ''
Q4_BUD AS ''

FOR
MARGIN_TYPE
'1CHANNEL EXP' AS 'WHL-Step C' LABEL R1 POST TO QWHLC
WHERE COST_POOL_OWNER EQ 'GRPWHL';
WHERE BU_EVP EQ 'GRP_CONSUMER' OR BU_EVP EQ 'GRPBMG';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
-*ON TABLE HOLD AS QWHLC
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END

-*==== PICKUP===============

FILEDEF QWHLA DISK E:\ibi\apps\LUIZ_NEW\QWHLA.DAT
FILEDEF QWHLB DISK E:\ibi\apps\LUIZ_NEW\QWHLB.DAT
FILEDEF QWHLC DISK E:\ibi\apps\LUIZ_NEW\QWHLC.DAT

TABLE FILE HOLDCC2
SUM
Q1_PRI AS ''
Q2_PRI AS ''
Q3_PRI AS ''
Q4_PRI AS ''

Q1_ACT AS ''
Q2_ACT AS ''
Q3_ACT AS ''
Q4_ACT AS ''

Q1_BUD AS ''
Q2_BUD AS ''
Q3_BUD AS ''
Q4_BUD AS ''

FOR
MARGIN_TYPE
DATA PICKUP FROM QWHLA R1
AS 'WHL-A' LABEL R1 NOPRINT OVER
DATA PICKUP FROM QWHLB R1
AS 'WHL-B' LABEL R2 NOPRINT OVER
DATA PICKUP FROM QWHLC R1
AS 'WHL-C' LABEL R3 NOPRINT OVER
RECAP R4/D12.2BM=( (R1 + R2) - R3 );
AS 'WHL CROSS-CHANNEL'
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE PCHOLD FORMAT EXL2K
END
-EXIT

Luiz,

Easiest check first, do you have any data in the DAT files in your application folder? I have a feeling that this is a FILEDEFing problem.

If not then try and place a -RUN after each FILEDEF just to force them to be actioned before progressing.

I also note that you have the FILEDEFS a second time - any "real" reason for this other than belt and braces? Try removing the second FILEDEFs and see what happens then.

T

Tony,



Thanks for your quick response. All DAT files show only the first column of my FML even after I included RUN after each fex. Example:

R1 , 1548.15,

When I run the “post” part of my fex I get everything; however, my pickup still doesn’t work (I have removed all FILEDEF).

Anything else I should try? Thanks

Luiz,

Change the NOBREAK on your first ON TABLE PCHOLD FORMAT EXL2K to OPEN NOBREAK.

Btw, you realise that you are only getting the first three holds? The final PICKUP section is not rendering a result because of the first compound EXL2K.

T

Tony,
I've gotten the same results after applying the changes you've suggested. The Excel output (post) gives me all the data I need, but for whatever reason it's not writing to the DAT files (except for the first column). Thanks

Luiz,

I know it's a bit much to ask, but have you scrolled all the way to the right? The label field can be quite wide!

This code gives all the data and simulates what you are doing

APP FILEDEF FMLDAT1 DISK FMLPOSTS/FMLDAT1.TXT
APP FILEDEF FMLDAT2 DISK FMLPOSTS/FMLDAT2.TXT
APP FILEDEF FMLDAT3 DISK FMLPOSTS/FMLDAT3.TXT
-RUN
DEFINE FILE GGSALES
  QUARTER/Q = DATE;
END

TABLE FILE GGSALES
SUM DOLLARS
    UNITS
ACROSS QUARTER
FOR REGION
'Midwest' AS 'Midwest' LABEL R1 POST TO FMLDAT1
WHERE DATE FROM 01011996 TO 31121996
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE HOLD
-*ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
END
-RUN

TABLE FILE GGSALES
SUM DOLLARS
    UNITS
ACROSS QUARTER
FOR REGION
'Northeast' AS 'Northeast' LABEL R2 POST TO FMLDAT2
WHERE DATE FROM 01011996 TO 31121996
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE HOLD
-*ON TABLE PCHOLD FORMAT EXL2K NOBREAK
END
-RUN

TABLE FILE GGSALES
SUM DOLLARS
    UNITS
ACROSS QUARTER
FOR REGION
'Southeast' AS 'Southeast' LABEL R3 POST TO FMLDAT3
WHERE DATE FROM 01011996 TO 31121996
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE HOLD
-*ON TABLE PCHOLD FORMAT EXL2K CLOSE
END
-RUN

TABLE FILE GGSALES
SUM DOLLARS
    UNITS
ACROSS QUARTER
FOR REGION
DATA PICKUP FROM FMLDAT1 R1
AS 'WHL-A' LABEL R1 OVER
DATA PICKUP FROM FMLDAT2 R2
AS 'WHL-B' LABEL R2 OVER
DATA PICKUP FROM FMLDAT3 R3
AS 'WHL-C' LABEL R3 OVER
RECAP R4/D12.2BM=((R1 + R2) - R3);
AS 'WHL CROSS-CHANNEL' 
WHERE DATE FROM 01011996 TO 31121996
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE PCHOLD FORMAT EXL2K 
END
-RUN

T

This message has been edited. Last edited by: Tony A,

Tony,
Unfortunately, your solution didn't work. When I had 'ON TABLE HOLD' I got 0 records for my pickup report. I've also tried to give the hold file the same name as my 'POST TO' file i.e., QBMGA, QBMGB, etc, and that generated error (FOC147) FML PICKUP DATA IS MISSING THE TERMINATOR OF '$': R1 , 1626.57,
Thanks

Luiz,

The ON TABLE HOLD is not for anything other than to prevent the report being written to the application. The important bit is the POST TO ddname. By HOLDing the data to the same name as your POST file you effectively overwrite the contents and negate the ability to read the data back in using FML.

When you say the code didn't work, did you post the entire code without change? Because it functions perfectly OK in the environments I have access to. If you do not have the APP folder FMLPOSTS then just change the filedefs to -

FILEDEF FMLDAT1 DISK FMLDAT1.TXT
FILEDEF FMLDAT2 DISK FMLDAT2.TXT
FILEDEF FMLDAT3 DISK FMLDAT3.TXT
-RUN

so that the POST files are written to the agent directorys in EDATEMP.

T

Tony,
Here's the code with your changes. I'm still getting 0 records for my pickup report.

APP HOLD LUIZ_NEW
FILEDEF FMLDAT1 DISK FMLDAT1.TXT
FILEDEF FMLDAT2 DISK FMLDAT2.TXT
FILEDEF FMLDAT3 DISK FMLDAT3.TXT
-RUN

-INCLUDE 002_qrt_main_crosschannel

TABLE FILE HOLDCC2
SUM
NEW_AMT
ACROSS YEAR
ACROSS QVAR
FOR
MARGIN_TYPE
'1CHANNEL EXP' AS 'BMG-Step A' LABEL R1 POST TO FMLDAT1
WHERE COST_POOL_OWNER EQ 'GRPWHL';
WHERE BU_EVP EQ 'GRPBMG';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE HOLD
-*ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
END
-RUN

TABLE FILE HOLDCC2
SUM
NEW_AMT
ACROSS YEAR
ACROSS QVAR
FOR
MARGIN_TYPE
'1CHANNEL EXP' AS 'BMG-Step B' LABEL R1 POST TO FMLDAT2
WHERE COST_POOL_OWNER EQ 'GRP_CONSUMER';
WHERE BU_EVP EQ 'GRPBMG';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE HOLD
-*ON TABLE PCHOLD FORMAT EXL2K NOBREAK
END
-RUN

TABLE FILE HOLDCC2
SUM
NEW_AMT
ACROSS YEAR
ACROSS QVAR
FOR
MARGIN_TYPE
'1CHANNEL EXP' AS 'BMG-Step B' LABEL R1 POST TO FMLDAT3
WHERE COST_POOL_OWNER EQ 'GRPBMG';
WHERE BU_EVP EQ 'GRPWHL' OR BU_EVP EQ 'GRP_CONSUMER';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE HOLD
-*ON TABLE PCHOLD FORMAT EXL2K CLOSE
END
-RUN

-* PICKUP FROM POSTED DATA

TABLE FILE HOLDCC2
SUM
NEW_AMT
ACROSS YEAR
ACROSS QVAR
FOR
MARGIN_TYPE
DATA PICKUP FROM FMLDAT1 R1
AS 'BMG-A' LABEL R5 OVER
DATA PICKUP FROM FMLDAT2 R1
AS 'BMG-B' LABEL R6 OVER
DATA PICKUP FROM FMLDAT3 R1
AS 'BMG-C' LABEL R7 OVER
RECAP R8/D12.2BM=( (R5 + R6) - R7 );
AS 'BMG CROSS-CHANNEL'
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE PCHOLD FORMAT EXL2K
END


0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

I'm wondering if there's another way to do the calculations without using post and pickup. Thanks

Luiz,

your problem is the AS name in the POST TO syntax. You are not using the same ones as you want to pick up -

'1CHANNEL EXP' AS 'BMG-Step A' LABEL R1 POST TO FMLDAT1

should be -

'1CHANNEL EXP' AS 'BMG-A' LABEL R1 POST TO FMLDAT1

Also, when posting data in FML, steer clear of using spaces.

T

Tony,
I've made the changes to the report, but I'm still getting 0 records on the pickup side.

TABLE FILE HOLDCC2
SUM
NEW_AMT
ACROSS YEAR
ACROSS QVAR
FOR
MARGIN_TYPE
'1CHANNEL EXP' AS 'BMG-A' LABEL R1 POST TO FMLDAT1
WHERE COST_POOL_OWNER EQ 'GRPWHL';
WHERE BU_EVP EQ 'GRPBMG';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE HOLD
-*ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
END
-RUN

Luiz,

I take it that you have a value for MARGIN_TYPE of '1CHANNEL EXP'?

Might be worth double checking that you can do a simple
TABLE FILE HOLDCC
BY MARGIN_TYPE
END
to ensure that your data does actuallly have the values you are expecting.

T

Tony,
Below is the query I've run, and the results:

TABLE FILE HOLDCC2
SUM NEW_AMT
BY MARGIN_TYPE
BY COST_POOL_OWNER
WHERE COST_POOL_OWNER EQ 'GRPBMG' OR COST_POOL_OWNER EQ 'GRP_CONSUMER' OR COST_POOL_OWNER EQ 'GRPWHL';
END

Margin Type Cost Pool Owner NEW_AMT
1CHANNEL EXP GRPBMG $2,792,726.04
GRPWHL $604,884.05
GRP_CONSUMER $3,467,684.49
2OPERATING EXP GRPBMG $0.00
GRPWHL $0.00
GRP_CONSUMER $0.00
3CORP OVERHEAD EXP GRPBMG $0.00
GRPWHL $0.00
GRP_CONSUMER $0.00
REVENUE GRPBMG $0.00
GRPWHL $0.00
GRP_CONSUMER $0.00

Luiz,

If the first character of the field is unique to the rest of the MARGIN_TYPE, then I'd be inclined to split it off to one alpha char and then use that in the FML.

DEFINE FILE ......
FML_CHAR/A1 = EDIT(MARGIN_TYPE,'9$');
...
END
TABLE FILE ......
SUM ....
FOR FML_TYPE
'1' LABEL .....

etc.

T

Tony,
I was able to get the pickup to work some by "changing" the name of the main hold file from HOLDCC2 to STDCC2. I remember having issues before using FOCUS reserved words to name my files. However, there's still an issue with the RECAP field not showing on the report. Thanks
Luiz