Hi WebFocus folks,

Hope you are doing well!

I would like to achieve below layout:

If there are too many columns so that some columns have to fly over to the second or third row. In this case, I was wondering if we can keep the first row.

This kind of layout can be achieved by Hyperion(SQR), we need to achive the same layout.

  
TABLE FILE CAR
PRINT 
     CAR.CARREC.MODEL
     CAR.BODY.DEALER_COST
     CAR.BODY.RETAIL_COST
     CAR.BODY.SALES
     CAR.SPECS.LENGTH
     CAR.SPECS.WIDTH
     CAR.SPECS.HEIGHT
     CAR.SPECS.WEIGHT
     CAR.SPECS.FUEL_CAP
BY  LOWEST CAR.ORIGIN.COUNTRY
WHERE COUNTRY EQ 'ENGLAND'
WHERE MODEL EQ 'TR7';
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END

Default layout:
COUNTRY MODEL DEALER_COST RETAIL_COST SALES LENGTH WIDTH HEIGHT WEIGHT FUEL_CAP
ENGLAND V12XKE AUTO 7,427 8,878 0 190 66 48 3,435 18.0

We would like to achive(country column will be displayed for each row):
COUNTRY MODEL DEALER_COST RETAIL_COST SALES
ENGLAND V12XKE AUTO 7,427 8,878 0
COUNTRY LENGTH WIDTH HEIGHT WEIGHT
ENGLAND 190 66 48 3,435
COUNTRY FUEL_CAP
ENGLAND 18.0


Appricate if you can give me any advice.

This message has been edited. Last edited by: FP Mod Chuck,

Look up the syntax for OVER and/or SUBHEAD in the language. I think something like this might work:

TABLE FILE CAR

BY  COUNTRY NOPRINT
ON COUNTRY SUBHEAD
"COUNTRY MODEL DEALER RETAIL"
"<COUNTRY <MODEL <DEALER <RETAIL"
WHERE COUNTRY EQ 'ENGLAND'
WHERE MODEL EQ 'TR7';
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END

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

Junjie,
Do you want the titles also on separate lines?

  
COUNTRY MODEL         DEALER_COST RETAIL_COST SALES 
ENGLAND V12XKE AUTO         7,427       8,878     0 
COUNTRY LENGTH WIDTH HEIGHT WEIGHT 
ENGLAND    190    66     48 3,435 
COUNTRY FUEL_CAP 
ENGLAND     18.0

If so, go with SUBHEAD.

Hi Babak,Danny,
Sorry about we didn't describe my problem correctly.

I found subhead is for group head. It's only for group.

My problem is that one student takes English, Art, Music etc subjects(subjects are dynamical), there are 9 terms in one year. I need to print score for each subject of each term.

SUBJECT TERM1 TERM2 TERM3 TERM4
ENGLISH 50 90 98 89
ART 20 89 88 88
Music 88 89 87 78
SUBJECT TERM5 TERM6 TERM7 TERM8
ENGLISH 50 90 98 89
ART 20 89 88 88
Music 88 89 87 78
SUBJECT TERM9
ENGLISH 50
ART 20
Music 88


We also can only choose any number of terms to display, for example, if we only choose term 1 -term 6, the layout will be:
SUBJECT TERM1 TERM2 TERM3 TERM4
ENGLISH 50 90 98 89
ART 20 89 88 88
Music 88 89 87 78
SUBJECT TERM5 TERM6
ENGLISH 50 90
ART 20 89
Music 88 89

I need the titles on separate lines as well.

Thanks for your advice.

Can you share your data structure with us? Are TERM1 ... TERM6 individual columns in your table or values in a single column? In your example are you showing SUBJECT TERMx TERMy TERMz for each student? Perhaps if you shared your table's structure we could get a better understanding of what you're trying to do.

Junjie,
Your example made it clearer.
Notice that your data repeats itself, up to 3 times depending on the number of fields you want.
This points me in the direction of MacGuyver, a technique to multiply the number of times you want to use each record.
Also because of the necessity to have your titles within the report body, you will have to use SUBHEAD.
Add to that the fact that the SUBHEAD is different for each group of fields, you will need the WHEN clause to differentiate between each occurence of the SUBHEAD.
Putting all this together you will need some Dialog Manager to build each SUBHEAD occurrence dynamically.
Now that I have probably confused you, here is a solution using the CAR file:

  
-SET &ECHO=ALL;
-* File junjie.fex
-* &FNUM is the number of fields in the final report, maximum 12
-DEFAULTH &FNUM=10;
-SET &NOP=' ';
-* &fields is the list of all possible fields in order
-SET &FIELDS='MODEL DEALER_COST RETAIL_COST SALES LENGTH WIDTH HEIGHT WEIGHT MPG RPM FUEL_CAP';
-SET &FLEN=&FIELDS.LENGTH;
-* Since there should be a maximum of 4 fields per line, &G hold the number of copies of each record to be retrieved
-SET &G=IF &FNUM GT 8 THEN 3 ELSE IF &FNUM GT 4 THEN 2 ELSE 1;
-* Extract the fields from the field list
-REPEAT #GETFIELDS FOR &I FROM 1 TO &FNUM;
-SET &F.&I=GETTOK(&FIELDS, &FLEN, &I, ' ', 12, 'A12');
-#GETFIELDS
-*
-* McGuyver technique: using a flat file
-* Create the MacGuyver Master and data file on the fly
-*
EX -LINES 7 EDAPUT MASTER,FSEQ,C,MEM
FILENAME=FSEQ,SUFFIX=FIX
SEGNAME=CHAR1,SEGTYPE=S0
FIELDNAME=BLANK,BLANK,A1,A1,$
SEGNAME=CHARS,SEGTYPE=S0,PARENT=CHAR1,OCCURS=VARIABLE
FIELDNAME=CHAR,CHARS,A1,A1,$
FIELDNAME=COUNTER,ORDER,I2,I4,$
-RUN
FILEDEF FSEQ DISK FSEQ.FTM
-RUN
-WRITE FSEQ  FILEFORMACGUYVERFILEFORMACGUYVERFILEFORMACGUYVER
-RUN
-*
-* define a blank logical field attache to the CAR file to JOIN to the MacGuyver file
JOIN BLANK WITH BODYTYPE IN CAR TO BLANK IN FSEQ AS B_
DEFINE FILE CAR
BLANK/A1 WITH BODYTYPE=' ';
END
-*
-* HOLD the multiple records. the field DLINE number the records per MODEL within COUNTRY
TABLE FILE CAR
WHERE COUNTER LE &G
PRINT
-REPEAT #PUTFIELDS FOR &I FROM 1 TO &FNUM;
&F.&I &NOP
-#PUTFIELDS
 BY COUNTRY &NOP
 BY COUNTER &NOP
RANKED AS DLINE BY MODEL &NOP
ON TABLE SET ASNAMES ON
ON TABLE HOLD
END
-RUN
-*
-* Output!
TABLE FILE HOLD
BY COUNTRY NOPRINT
BY COUNTER NOPRINT
BY DLINE NOPRINT
-* build the SUBHEAD titles and data for the first 4 fields
ON COUNTER SUBHEAD
-SET &L1=MIN(4,&FNUM);
-SET &L1T='"COUNTRY';
-SET &L1F='"<COUNTRY';
-REPEAT #L1FIELDS FOR &I FROM 1 TO &L1;
-SET &L1T=&L1T || '<+0>' || &F.&I ;
-SET &L1F=&L1F || '<' || &F.&I;
-#L1FIELDS
-SET &L1T=&L1T || '"';
-SET &L1F=&L1F || '"'
&L1T
WHEN COUNTER EQ 1;
ON DLINE SUBHEAD
&L1F
WHEN COUNTER EQ 1
-IF &FNUM LE 4 GOTO #ENDHEAD;
-* build the SUBHEAD titles and data for the next 4 fields. skip if less than 5
ON COUNTER SUBHEAD
-SET &L2=MIN(8,&FNUM);
-SET &L2T='"COUNTRY';
-SET &L2F='"<COUNTRY';
-REPEAT #L2FIELDS FOR &I FROM 5 TO &L2;
-SET &L2T=&L2T || '<+0>' || &F.&I;
-SET &L2F=&L2F || '<' || &F.&I ;
-#L2FIELDS
-SET &L2T=&L2T || '"';
-SET &L2F=&L2F || '"'
&L2T
WHEN COUNTER EQ 2;
ON DLINE SUBHEAD
&L2F
WHEN COUNTER EQ 2
-IF &FNUM LE 8 GOTO #ENDHEAD;
-* build the SUBHEAD titles and data for the last 4 fields. skip if less than 9
ON COUNTER SUBHEAD
-SET &L3=MIN(12,&FNUM);
-SET &L3T='"COUNTRY';
-SET &L3F='"<COUNTRY';
-REPEAT #L3FIELDS FOR &I FROM 9 TO &L3;
-SET &L3T=&L3T || '<+0>' || &F.&I;
-SET &L3F=&L3F || '<' || &F.&I;
-#L3FIELDS
-SET &L3T=&L3T || '"';
-SET &L3F=&L3F || '"'
&L3T
WHEN COUNTER EQ 3;
ON DLINE SUBHEAD
&L3F
WHEN COUNTER EQ 3
-#ENDHEAD
ON TABLE SET PAGE OFF
-* align the output
ON TABLE SET STYLE *
TYPE=SUBHEAD, HEADALIGN=BODY, $
ENDSTYLE
END

Good luck.

quote:

Originally posted by BabakNYC:
Can you share your data structure with us? Are TERM1 ... TERM6 individual columns in your table or values in a single column? In your example are you showing SUBJECT TERMx TERMy TERMz for each student? Perhaps if you shared your table's structure we could get a better understanding of what you're trying to do.

Hi Babak,

Yes, you are correct. TERM1 ... TERM6 are individual columns. Each subject has its individual row which identifies by student id.

For example, the student with ID 123456789 took 3 subjects(English, Art, Music), the value of TERMx is the score for each subject.

STUDENT_ID SUBJECT TERM1 TERM2 TERM3 TERM4 TERM5 TERM6 TERM7 TERM8 TERM9
123456789 ENGLISH score score score score score score score score score
123456789 ART score score score score score score score score score
123456789 MUSIC score score score score score score score score score

The parameter page allows the user to select any number of terms to display.

Hi Daniel,

The outcome is what we would like to achieve. Appreciate your effort to bring us this idea with the CAR example which makes us easier to understand MacGyver Technical.
To be honest with you, I learned a lot of new staff from your codes, especially for MacGyver Technical.

I have replaced below codes to make it runnable on my laptop, I still would like to figure out why I cannot run your codes directly.
I have taken some time to debug the codes, I found that there is no data after joining CAR with FSEQ. If I print the CAR and FSWQ respectively, there is data in both files.

(Below codes are part of your Car sample codes.)

 
EX -LINES 7 EDAPUT MASTER,FSEQ,C,MEM
FILENAME=FSEQ,SUFFIX=FIX
SEGNAME=CHAR1,SEGTYPE=S0
FIELDNAME=BLANK,BLANK,A1,A1,$
SEGNAME=CHARS,SEGTYPE=S0,PARENT=CHAR1,OCCURS=VARIABLE
FIELDNAME=CHAR,CHARS,A1,A1,$
FIELDNAME=COUNTER,ORDER,I2,I4,$
-RUN
FILEDEF FSEQ DISK FSEQ.FTM
-RUN
-WRITE FSEQ  FILEFORMACGUYVERFILEFORMACGUYVERFILEFORMACGUYVER
-RUN
JOIN BLANK WITH BODYTYPE IN CAR TO BLANK IN FSEQ AS B_
DEFINE FILE CAR
BLANK/A1 WITH BODYTYPE=' ';
END

TABLE FILE CAR
PRINT MODEL COUNTER
END 
 

The result is 0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

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

Jun Jie,
Very strange! I ran your code as is and got output.
After running your code, I added a condition in order to limit the sze of the output:

  
TABLE FILE CAR
PRINT MODEL COUNTER
WHERE COUNTER LE 2;
ON TABLE PCHOLD FORMAT PDF
END 

See: Output

If you wish we can have a conversation.

Hi Daniel,

Thanks for your kindness. I think it's due to Environment setup. I will get our infra team to take a look.

We are migrating Hyperion(SQR) report to WebFOCUS. We encountered a lot of layout issues.

You let me feel WebFocus is powerful through your codes. Unfortunately, there is too less this kind of codes on the internet.

They are just a few pieces of code. I was wondering if there is somewhere we can download the sample source code to learn WebFocus.

For developers, we learn from codes much faster than reading the manual.

"We are migrating Hyperion(SQR) report to WebFOCUS." It's always good to hear about migrating anything to WebFOCUS.

While "For developers, we learn from codes much faster than reading the manual." may be faster, it is not always the best practice as you may be just duplicating code which may not be efficient. With all due respect, we're all familiar with GIGO... I do hope that is not the case here.

Consider the following http://www.informationbuilders.com/resources]IBI Resources:

1) IBI Resource Center

2) Tech Support

3) Online WebCasts

Teaching them to fish instead of giving them fish...