Hi,

we have a requirement in our project where we need to develope an excel file and burst that excel file into different tabs.
I tried searching the forum with compound excel report with bursting but could not full fill my requirements.
both the requirements are feasible alone but in together when i want to display my report,it is taking the content of first hold file only and making tabs as per my by column.

in my fex file, i have four hold files which i am displaying one after other, the report bursts into different seats as per the BY column.
please find below the query which i have tried:

sql sqlora
select name , roll,class, admin number from student
on table hold as hld1

sql sqlora
select name, class, subject, admin number from student
on table hold as hld2


Table file hld1
print
name
by admin number noprint
by roll
across class

on table set by display on
on table pchold format exl2k bytoc

end
-run

table file hld2
print name
by admin number noprint
by subject
across class

on table set by display on
on table pchold format exl2k bytoc
-end
-run

After generating the report , the excel report is generated into different tabs as per the admin number,but i am getting content of first hold file only.

Kindly help. as we have to deliver this requirement in another three days and i am not sure whether this is feasible or not.


Regards,
Shankar

WF 7.6.11, Windows XP
DataBase: Oracle 11g,Output :Excel,PDF,HTML

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

You probably won't be able to use BYTOC to accomplish this. One limiting factor is that Excel does not allow duplicate tab names in a spreadsheet. Since BYTOC uses the first BY phrase for the tab names, your use of the same BY phrase in both queries causes a conflict. One solution is to manually create the report(s) for each tab. In the example below, I have placed two reports (one for dealer cost and one for retail cost) on the same tab. If you want each report on a separate tab, you can modify the compound syntax by replacing 'NOBREAK' with a blank. But you will also want to add a TITLETEXT statement to the style sheet in the second query. Just remember that none of the tabs can have the exact same name.

SET HOLDLIST = PRINTONLY
SET ASNAMES  = ON
-*
TABLE FILE CAR
BY COUNTRY
ON TABLE HOLD AS CNTRYLST
END
-RUN
-SET &COUNTRY_COUNT = &LINES ;
-*
-REPEAT :ENDREPEAT1 FOR &I FROM 1 TO &COUNTRY_COUNT
-SET &COMPOUND1 = IF (&I EQ 1) THEN 'OPEN NOBREAK' ELSE 'NOBREAK';
-SET &COMPOUND2 = IF (&I EQ &COUNTRY_COUNT) THEN 'CLOSE' ELSE '';
-READFILE CNTRYLST
-*
TABLE FILE CAR
 SUM DEALER_COST
 ACROSS CAR AS 'Dealer Cost'
 WHERE COUNTRY EQ '&COUNTRY';
 ON TABLE SUBFOOT
  " "
  " "
 ON TABLE SET STYLE *
  TYPE=REPORT, TITLETEXT='&COUNTRY', $
 ENDSTYLE
 ON TABLE PCHOLD FORMAT EXL2K &COMPOUND1
END
-*
TABLE FILE CAR
 SUM RETAIL_COST
 ACROSS CAR AS 'Retail Cost'
 WHERE COUNTRY EQ '&COUNTRY';
 ON TABLE PCHOLD FORMAT EXL2K &COMPOUND2
END
-:ENDREPEAT1

We use this compound function in combination with the bytoc, but so far only in the second or last compound sheet.

There might be a way to do this if you would create a dummy by field, by adding a different number depending on the different queries.

Hi Dan,

Thanks for your help, but the code piece is showing an error while execution.

-READFILE CNTRYLST .. is the line where it showing an error.

however i tried in my actual fex file.
in the actual report , the report need to separate into different tabs as per one column value and in each tab i need to display all the four hold file contents. when i use this query, 2 hold files generated in two different tabs. but i need those two to be generated in one tab and same two to be generated in other tab as the value changes.

Could you please help me understanding the use of titiletext so that i can get the content of four sql query in each tab and multiple tabs could be created as the value of the column changes(in the example shown above admin nbr). Thanks


Regards,
Shankar

I thought -READFILE was available in release 7.6.11, but maybe not. That section of the code can be replaced with this code:

TABLE FILE CAR
BY COUNTRY
ON TABLE SAVE AS CNTRYLST
END
-RUN
-SET &COUNTRY_COUNT = &LINES ;
-*
-REPEAT :ENDREPEAT1 FOR &I FROM 1 TO &COUNTRY_COUNT
-SET &COMPOUND1 = IF (&I EQ 1) THEN 'OPEN NOBREAK' ELSE 'NOBREAK';
-SET &COMPOUND2 = IF (&I EQ &COUNTRY_COUNT) THEN 'CLOSE' ELSE '';
-READ CNTRYLST NOCLOSE &COUNTRY.10.
.
.
.

Otherwise, my code sample should work for displaying the contents of four hold files. As long as you are planning to display data from all four hold files on the same tab, then the TITLETEXT duplication problem should not be an issue for you.

ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET COMPOUND BYTOC

This might help also

It is IMHO a bit to complex to do this with a loop. There are many examples in both the official documentation and in this forum.
We have this running on the dashboard and in reportcaster .

I am wondering whether you have not given a precise description of what you need to achieve?

Is the end result an Excel workbook with one worksheet per "Admin Number" and each worksheet containing two or more reports?

If so then you will need to resort to combining the data from each extract but still leave the ability to identify where each row of data originated. Then within the final process, split the report sections by using SUBHEAD or the like.

If we take a similar output where I want to have an Excel workbook with a seperate DIV on each worksheet but containing two reports within the worksheet. The first one is a list of names ordered BY JOBCLASS ACROSS DEPT the second is also a list of names but ordered BY TITLE ACROSS DEPT.

I can see that JOBCLASS is A8 and TITLE is A20, so I use the larger of the two to contain my "common" field in the concatenated file. I then set another field to hold my report "differentiator" and my code becomes -

SET ASNAMES   = ON
SET HOLDLIST  = PRINTONLY
SET BYDISPLAY = ON
FILEDEF TEMPDATA DISK TEMPDATA.FTM (APPEND
-RUN
DEFINE FILE EMPDATA
  NAME/A27 = LASTNAME || (', ' | FIRSTNAME);
  REPORT1/I1 = 1;
  REPORT2/I1 = 2;
  SECOND_BY1/A20 = JOBCLASS;
  SECOND_BY2/A20 = TITLE;
END
TABLE FILE EMPDATA
PRINT NAME
   BY REPORT1      AS REPORT
   BY DIV
   BY SECOND_BY1   AS SECOND_BY
   BY DEPT
ON TABLE HOLD AS TEMPDATA FORMAT ALPHA
END
-RUN
TABLE FILE EMPDATA
PRINT NAME
   BY REPORT2      AS REPORT
   BY DIV
   BY SECOND_BY2   AS SECOND_BY
   BY DEPT
ON TABLE HOLD AS TEMPDATA FORMAT ALPHA
END
-RUN
TABLE FILE TEMPDATA
PRINT NAME
   BY DIV          NOPRINT
   BY REPORT       NOPRINT
   BY SECOND_BY    AS ''
ACROSS DEPT
ON REPORT SUBFOOT
" "
ON REPORT SUBHEAD
"JOBCLASS"
WHEN REPORT EQ 1
ON REPORT SUBHEAD
"TITLE"
WHEN REPORT EQ 2
ON TABLE PCHOLD FORMAT EXL2K BYTOC
END
-RUN

T

We have a number of reports that produce multiple tabs due to different formatting and sorting needs. We use the OPEN and CLOSE commands. However, I did discover that this does not work with EXL07 format. Only the first table will be displayed. Notice the OPEN statement following the FORMAT EXL2k on the first table and the CLOSE following the FORMAT EXL2k on the last. On a side note, if one of the tables fail you will only get one tab.

TABLE FILE ELIG_RETURN
PRINT
BANNER_ID
LAST_NAME
FIRST_NAME
REG_HRS
CLASS_LEVEL_CODE
STUDENT_TYPE_CODE
FR1
SO1
JR1
SR1
UN1
REGISTRATION_CAMPUS_CODE
BY LOWEST REGISTRATION_CAMPUS_CODE
BY LOWEST PIDM NOPRINT
ON TABLE SET PAGE-NUM NOLEAD
ON REGISTRATION_CAMPUS_CODE SUBTOTAL FR1 SO1 JR1 SR1 UN1
ON TABLE COLUMN-TOTAL AS 'TOTAL' FR1 SO1 JR1 SR1 UN1
ON TABLE PCHOLD FORMAT EXL2K OPEN
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
TITLETEXT='ELIG RETURN',
$
ENDSTYLE
END
-RUN
TABLE FILE NOT_RETURN
PRINT
BANNER_ID
LAST_NAME
FIRST_NAME
REG_HRS
CLASS_LEVEL_CODE
STUDENT_TYPE_CODE
FR1
SO1
JR1
SR1
UN1
REGISTRATION_CAMPUS_CODE
BY REGISTRATION_CAMPUS_CODE
BY LOWEST PIDM NOPRINT
ON TABLE SET PAGE-NUM NOLEAD
ON REGISTRATION_CAMPUS_CODE SUBTOTAL FR1 SO1 JR1 SR1 UN1
ON TABLE COLUMN-TOTAL AS 'TOTAL' FR1 SO1 JR1 SR1 UN1
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
TITLETEXT='NOT RETURNING',
$
ENDSTYLE
END
-RUN
TABLE FILE NEXT_TERM_STU
SUM
BANNER_ID
LAST_NAME
FIRST_NAME
REG_HRS
CLASS_LEVEL_CODE
STUDENT_TYPE_CODE
REGISTRATION_CAMPUS_CODE
FR1
SO1
JR1
SR1
UN1
BY LOWEST REGISTRATION_CAMPUS_CODE
BY LOWEST PIDM NOPRINT
ON TABLE SET PAGE-NUM NOLEAD
ON REGISTRATION_CAMPUS_CODE SUBTOTAL FR1 SO1 JR1 SR1 UN1
ON TABLE COLUMN-TOTAL AS 'TOTAL' FR1 SO1 JR1 SR1 UN1
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TITLETEXT='ENROLLED STUDENTS',
$
TYPE=REPORT,
FONT='TIMES NEW ROMAN',
SIZE=10,
$
ENDSTYLE
END
-RUN
TABLE FILE NEW_STUDENTS
SUM
BANNER_ID
LAST_NAME
FIRST_NAME
REG_HRS
CLASS_LEVEL_CODE
STUDENT_TYPE_CODE
REGISTRATION_CAMPUS_CODE
FR1
SO1
JR1
SR1
UN1
BY LOWEST REGISTRATION_CAMPUS_CODE
BY LOWEST PIDM NOPRINT
ON TABLE SET PAGE-NUM NOLEAD
ON REGISTRATION_CAMPUS_CODE SUBTOTAL FR1 SO1 JR1 SR1 UN1
ON TABLE COLUMN-TOTAL AS 'TOTAL' FR1 SO1 JR1 SR1 UN1
ON TABLE PCHOLD FORMAT EXL2K CLOSE
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TITLETEXT='NEW STUDENTS',
$
TYPE=REPORT,
FONT='TIMES NEW ROMAN',
SIZE=10,
$
ENDSTYLE
END
-RUN
-EXIT

The release notes explain that BYTOC (tabs) are not yet available in EXL07 output.

quote:

The release notes explain that BYTOC (tabs) are not yet available in EXL07 output.

I lied. Sort of. The above statement is true, but the release notes aren't accurate. BYTOC does indeed work with WF7704 and EXL07.

We have WF7704. Can you give us example on how it works?

Any help is very much appreciated