Hi,

I've similar kind of requirement

Please find the image



In Column-A, values (12345,56789,45673) coming from one column and values(543210876,987654213,321456789) coming from another column.
I can give a try by using FILEDEF for each row, that mightn't be an efficient method i feel. I tried using OVER command but couldn't make it.

Thanks,
Rifaz

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

Some questions:
1. do the other values come from the same columns or also from different columns?
2. What is the structure of your data? (Maybe show the master file?)
3. What attempts have you already done to get this report to show?
4. Are all debit amounts always with the same account number? Is that also valid for the credit amounts?

1.Other values are different columns

2.Can't show my Master now, but...
12345,56789,45673(Datas in gray BG) - Payer code(Alphanumeric)
543210876,987654213,321456789(Datas in blue BG) - Payee code(Alphanumeric)
Debit/Credit are from one column, I need to create a two field as Debit & Credit
Currency-One column
Remarks-One column

3.I din't try, but have an idea of using FILEDEF for every row( I mean first three rows, including calculating totals) and appending one over other.

4.No, it varies..

Thanks GamP, let me know if you any other details

How about using MacGyver to "split" each of your records in 2?

You can then define your account, debit and credit amounts depending on which "version" of each record is being used.

For instance, using the CAR table you obtain 6 records for cars in 'ENGLAND' and 'JAPAN':

TABLE FILE CAR
PRINT COUNTRY AND CAR AND MODEL AND DEALER_COST AND SEATS
WHERE COUNTRY EQ 'ENGLAND' OR 'JAPAN'
END

Using the MacGyver technique, you can duplicate each of those records allowing you to display 12 rows in your report in addition to calculating subtotals as needed.

FILEDEF FSEQ DISK fseq.mas
-RUN
-WRITE FSEQ NOCLOSE      $
-WRITE FSEQ NOCLOSE FILE=FSEQ, SUFFIX=FIX
-WRITE FSEQ NOCLOSE SEGNAME=SEG1, SEGTYPE=S0
-WRITE FSEQ NOCLOSE FIELD=CONTROL, BLANK , A1, A1, $
-WRITE FSEQ NOCLOSE SEGNAME=SEG2, SEGTYPE=S0, PARENT=SEG1, OCCURS=VARIABLE
-WRITE FSEQ NOCLOSE FIELD=1CHAR, 1CHAR, A1, A1, $
-WRITE FSEQ NOCLOSE FIELD=COUNTER, ORDER, I4, I4, $
-CLOSE FSEQ
-RUN

SET ALL=OFF
JOIN CLEAR *
JOIN BLANK WITH SEATS IN CAR TO CONTROL IN FSEQ AS J0

DEFINE FILE CAR
BLANK/A1 = ' ';
KEY/A50 = CAR || '_' || MODEL;
ACCOUNT/A30 = IF COUNTER EQ 1 THEN CAR ELSE MODEL;
DEBIT/D20.2  MISSING ON = IF COUNTER EQ 1 THEN DEALER_COST ELSE MISSING;
CREDIT/D20.2 MISSING ON = IF COUNTER EQ 2 THEN DEALER_COST ELSE MISSING;
END

TABLE FILE CAR
PRINT
   ACCOUNT
   DEBIT
   CREDIT
   SEATS
   COUNTRY
BY KEY NOPRINT
BY COUNTER NOPRINT
WHERE COUNTRY EQ 'ENGLAND' OR 'JAPAN'
WHERE COUNTER LE 2
HEADING
"Results after using MacGyver"
ON KEY SUBFOOT
"Totals <ST.DEBIT<ST.CREDIT <+0> "
ON TABLE SET PAGE NOPAGE
ON TABLE HOLD AS HFINAL FORMAT HTMTABLE
ON TABLE SET STYLE *
TYPE=REPORT, FONT='ARIAL', SIZE='8', $
TYPE=SUBFOOT, BY=1, HEADALIGN=BODY, BACKCOLOR='SILVER', $
ENDSTYLE
END

Hope this helps.

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

Thanks njsden for your idea,

Today, i tried with real time datas. Counter value remains same as 1 for each KEY field. Where I'm going wrong?
What is the use of field 1CHAR over here?

BTW, my "AccountCode" fields format is alphanumeric(A20V).

When i remove NOPRINT from Key field in your sample code, it truncates the first 4 characters from datas.

Hi Rifaz,

I may not be able to offer a "decent" explanation of the FSEQ's masterfile structure so please search the forum for details on the MacGyver technique for a complete description of what it can help you achieve and a complete definition of the file's structure.

Using an A20V fields should have no impact on using the technique but without looking at your code it's rather hard to help out.

My suggestion is:

1) Retrieve your actual data from the database and place it in a HOLD file.

2) Following the MacGyver technique, you will then use a defined-based JOIN from your HOLD file to the FSEQ structure (the one that will help you duplicate your records). The idea is to just have a value you can safely use to join, that's the purpose of the BLANK field in the DEFINE FILE portion in my example.

It would look something like this:

-* 1) Retrieve data and HOLD it
TABLE FILE YOUR_DATA
PRINT .....
ON TABLE HOLD AS HDATA
END
-RUN

-* 2) Dynamically create FSEQ master to implement MacGyver
-*    Here we use a space character to implement the join value

FILEDEF FSEQ DISK fseq.mas
-RUN
-WRITE FSEQ NOCLOSE    $    <-- We'll need 2 instances of each record in your data, so we'll place at least 2 spaces before the $ sign
-WRITE FSEQ NOCLOSE FILE=FSEQ, SUFFIX=FIX
-WRITE FSEQ NOCLOSE SEGNAME=SEG1, SEGTYPE=S0
-WRITE FSEQ NOCLOSE FIELD=CONTROL, BLANK , A1, A1, $
-WRITE FSEQ NOCLOSE SEGNAME=SEG2, SEGTYPE=S0, PARENT=SEG1, OCCURS=VARIABLE
-WRITE FSEQ NOCLOSE FIELD=1CHAR, 1CHAR, A1, A1, $
-WRITE FSEQ NOCLOSE FIELD=COUNTER, ORDER, I4, I4, $
-CLOSE FSEQ
-RUN

-* 3) Use a define-based JOIN from your data to the FSEQ file.
-* As the "data" in FSEQ consists of " " values, we'll join later based on that 
SET ALL=OFF
JOIN CLEAR *
JOIN BLANK WITH ANY_FIELD_IN_YOU_HOLD_FILE IN HDATA TO CONTROL IN FSEQ AS J0

DEFINE FILE HDATA
BLANK/A1 = ' ';   <-- We use a blank value to join to FSEQ
....
END

-* 4) Run the final request seeing how we'll now get 2 instances of each record in HDATA
TABLE FILE HDATA
PRINT/SUM 
......
BY COUNTER NOPRINT
WHERE ......
WHERE COUNTER LE 2  <-- This makes sure you'll get no more than 2 "versions" of each row
END

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

Due to the different ways used in the Forum and even some of the Tech Support responses to refer to this technique, it was rather tricky to find the documentation I needed. After trying McGyver, McGuyver and MacGyver, the latter finally took me to the FOCUS Techniques/Code Example page I wanted.

So, just login to IBI's tech support site and in the "Advanced Search" text box just type "MacGyver Techniques", hit search and you should find it there.

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

Hi Neftali,

Thanks again for your "MacGyver Techniques"

Problem facing in "MacGyver Techniques" is that i can't restrict my counter to 2, it is again dynamic & shouldn't be repeated as well.
For instance, JAGUAR can have multiple MODELs like V12XKE AUTO, XJ12L AUTO etc and it must display as
JAGUAR
V12XKE AUTO
XJ12L AUTO
So, I tried with my favourite FILEDEF and it works.

Here is my code:

TABLE FILE CAR
PRINT COUNTRY CAR MODEL BODYTYPE SEATS DEALER_COST RETAIL_COST SALES 
ON TABLE HOLD AS MAIN
END
-RUN


-***************First - CAR
DEFINE FILE MAIN
CAR1/A24=CAR;
APPENDCAR/A25='1'||CAR;
SORTINGCAR/A24=CAR;
END

FILEDEF CARTEST DISK CARTEST
-RUN
TABLE FILE MAIN
PRINT
CAR1
APPENDCAR
DEALER_COST
SEATS
COUNTRY 
MODEL
BY SORTINGCAR
ON TABLE HOLD AS CARTEST
END
-RUN
-**************Second - MODEL
DEFINE FILE MAIN
CAR1/A24=MODEL;
APPENDCAR/A25='2'||MODEL;
SORTINGCAR/A24=CAR;
END

FILEDEF CARTEST DISK CARTEST (APPEND
-RUN
TABLE FILE MAIN
PRINT
CAR1
APPENDCAR
DEALER_COST
SEATS
COUNTRY 
MODEL
BY SORTINGCAR
ON TABLE HOLD AS CARTEST
END
-RUN
-*****************
DEFINE FILE CARTEST
CAR2/A50=CAR1;
DEBIT/D20.2  MISSING ON = IF APPENDCAR LIKE '1%' THEN DEALER_COST ELSE MISSING;
CREDIT/D20.2 MISSING ON = IF APPENDCAR LIKE '2%' THEN DEALER_COST ELSE MISSING;
END

TABLE FILE CARTEST
SUM 
DEBIT 
CREDIT 
SEATS
COUNTRY
BY SORTINGCAR NOPRINT
BY APPENDCAR NOPRINT
BY CAR2 AS 'Account Code'
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET PAGE NOPAGE
ON SORTINGCAR SUBFOOT
"<+0>Totals<+0><ST.DEBIT<ST.CREDIT <+0>"
ON TABLE SET STYLE *
TYPE=REPORT,FONT='ARIAL',SIZE=10,COLOR='BLACK',BACKCOLOR='NONE',STYLE=NORMAL,$
TYPE=TITLE,FONT='ARIAL',STYLE=-UNDERLINE+BOLD,JUSTIFY=CENTER,$
TYPE=SUBFOOT,HEADALIGN=BODY,STYLE=BOLD,$
ENDSTYLE
END
-EXIT