Hi,

In one of the report we have detail and summary section.

Can u get the summary in one tab and the detail in other tab?

If the number of records in Detail is greater than 65K we should move to a new tab in the same excel sheet.

From https://forums.informationbuilders.com/eve/forums/a/tpc/...831071272#5831071272 , I came to know that we can populate data’s in new tab as per our requirement (i.e. if record count is greater than 40k in a sheet move to next sheet). I also came to know that we can put data’s in a different tab as per the highest level of grouping.


Can anyone explain how to achieve do the above ….

I did searched in the forum, but I am unable to find answer for my issue. (I guess I had not overlooked any discussion topic )


Regards
Navaneeth

Off the top of my head, I would have two reports, the first, detail the next summary, or visa-versa, each report going into separate tabs.

Navaneeth,

You've done your homework!

I haven't tried this myself but, in theory at least, I would create the detail report first using ON TABLE HOLD FORMAT EXCEL BYTOC to get the multiple sheets required for large row numbers of detail data sending the output to a pre-FILEDEFd .mht file.

You would force an empty first sheet somehow to allow the summary to be targeted to SHEETNUMBER 1 within your second report.

Let us know how you get on.

T

A quick trial and I've got this so far. It forces an additional sheet in the BYTOC template output and shows that the template is created OK, but I haven't got the Summary page forced yet.

Everyone have a play (you know you want to ) - a gold star to anyone that can make this function as it's potentially very useful

APP PREPENDPATH IBINCCEN
FILEDEF TEMPFIX DISK tempfix.ftm (APPEND
FILEDEF TEMPEXL DISK tempexl.mht
-RUN
-* The next line is space padded to here                                                       V
-WRITE TEMPFIX @Summary                                                                         
TABLE FILE GGSALES
SUM DOLLARS BUDDOLLARS
 BY REGION
 BY ST
 BY CITY
 BY STCD
 BY CATEGORY
 BY PRODUCT
ON TABLE HOLD AS TEMPFIX FORMAT ALPHA
END
TABLE FILE TEMPFIX
SUM DOLLARS BUDDOLLARS
 BY REGION
 BY ST
 BY CITY
 BY STCD
 BY CATEGORY
 BY PRODUCT
ON TABLE HOLD AS TEMPEXL FORMAT EXL2K BYTOC
END
-RUN
TABLE FILE GGSALES
SUM DOLLARS BUDDOLLARS
 BY REGION
 BY ST
 BY CITY
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE TEMPEXL SHEETNUMBER 1
END
-RUN

T

You could always create your template file and place your summary in sheet 1. Then when you create your temporary field for page number to mimic your bytoc, start at number 2 and then you could output each detail page to the SHEETNUMBER relating to the temporary page number.

Either way, you are likely to have to handle the data several times.

T

And then, one can always count on McGuyver:

  
-* File Navaneeth2.fex
JOIN BLANK WITH SALES IN CAR TO BLANK IN FSEQ AS M_
DEFINE FILE CAR
BLANK/A1 WITH SALES = ' ';
NCAR/A16 = IF COUNTER EQ 1 THEN ' ' ELSE CAR;
PLACE/A27=COUNTRY | (' ' | NCAR);
LEVEL/A7 =IF COUNTER EQ 1 THEN 'SUMMARY' ELSE 'DETAIL';
END
TABLE FILE CAR
SUM SALES
BY HIGHEST LEVEL NOPRINT
BY PLACE
WHERE COUNTER LE 2
ON TABLE PCHOLD FORMAT EXL2K BYTOC
END

Maybe not exactly what you wanted but an elegant step in the direction.

Try this out.

DEFINE FILE CAR
RECCNT /I9 WITH COUNTRY = LAST RECCNT + 1;
END
-*
TABLE FILE CAR
PRINT 
      COUNTRY
      CAR
      MODEL
      BODYTYPE
      SEATS
      DEALER_COST
      RETAIL_COST 
BY RECCNT
ON TABLE HOLD AS FDATA
END
-*
-SET &LIM     = 0;
-REDO
-SET &LIM     = &LIM + 1;
-SET &HPREP   = DECODE &LIM(1 'OPEN' ELSE ' ');
-SET &HOLDER  = 'ON TABLE PCHOLD FORMAT EXL2K &HPREP.EVAL'; 
-*
TABLE FILE FDATA
PRINT *
WHERE RECCNT EQ &LIM.EVAL
ON TABLE HOLD AS LINES
END
-TYPE &LINES
-IF &LINES = 0 GOTO DOSUM;
TABLE FILE LINES
PRINT 
      COUNTRY
      CAR
      MODEL
      BODYTYPE
      SEATS
      DEALER_COST
      RETAIL_COST
&HOLDER.EVAL
END
-GOTO REDO
-DOSUM
TABLE FILE FDATA
SUM
      DEALER_COST
      RETAIL_COST 
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END
-EXIT

My first post will break on country.

This post will break on a specified line count.

-*
DEFINE FILE CAR
RFIELD/A50 = COUNTRY||CAR||MODEL;
END

TABLE FILE CAR
PRINT 
      COUNTRY
      CAR
      MODEL
      BODYTYPE
      SEATS
      DEALER_COST
      RETAIL_COST 
RANKED BY RFIELD
ON TABLE HOLD AS FDATA
END
-*
-SET &LIM     = 0;
-SET &LOOPER  = 0;
-REDO
-SET &LOOPER  = &LOOPER + 1;
-SET &HPREP   = DECODE &LOOPER(1 'OPEN' ELSE ' ');
-SET &HOLDER  = 'ON TABLE PCHOLD FORMAT EXL2K &HPREP.EVAL'; 
-*
TABLE FILE FDATA
PRINT *
WHERE RANK GT &LIM.EVAL
WHERE RECORDLIMIT EQ 5
ON TABLE HOLD AS LINES
END
-*THIS WILL SET 5 LINES PER TAB
-SET &LIM     = &LIM + 5;
-IF &LINES = 0 GOTO DOSUM;
TABLE FILE LINES
PRINT 
      COUNTRY
      CAR
      MODEL
      BODYTYPE
      SEATS
      DEALER_COST
      RETAIL_COST
&HOLDER.EVAL
END
-GOTO REDO
-DOSUM
TABLE FILE FDATA
SUM
      DEALER_COST
      RETAIL_COST 
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END
-EXIT

Hope one of these examples is what you are looking for.

Hi All,

Thanks for all your reply.

Glenda : it will be very helpful if you can explain me your last post..

I did try to figure it out, but sometimes I am completely lost

For instance I did not get the following snippet of code
RFIELD/A50 = COUNTRY||CAR||MODEL;
-REDO
-SET &HPREP = DECODE &LOOPER(1 'OPEN' ELSE ' ');
WHERE RANK GT &LIM.EVAL

Regards,
Navaneeth

I'll give it a try but it is like when someone asks how I can factor polynomials. My answer is that I don't know how but I just see it.

Pat - feel free to pop in if you can explain it better!!

 
RFIELD/A50 = COUNTRY||CAR||MODEL;

"RFIELD" is simply a unique field by which I rank the data. This gives me record count. (I'm sure there are other ways of doing this.)

 
-SET &HPREP = DECODE &LOOPER(1 'OPEN' ELSE ' ');

&HPREP is so I can set the first tab to "OPEN"

First loop through begins the COMPOUND EXCEL.

&HOLDER = 'ON TABLE PCHOLD FORMAT EXL2K OPEN'

I don't use a close in the DECODE because my summary page will close the COMPOUND EXCEL.

Three pieces of code tell where to start each loop as well as how many lines to put on each EXCEL tab.


Where to start the loop:

 
WHERE RANK GT &LIM.EVAL

How many records to pull:

 
WHERE RECORDLIMIT EQ 5

Sets &LIM for the next loop:

 
-SET &LIM = &LIM +5;

NOTE: "WHERE RECORDLIMIT" AND "-SET &LIM" must be incremented by the same number.

I hope I haven't increased your confusion.

How much does a WebFOCUS 101 class run to now-a-days?

T

How about first running the summart report and put in code similar to this
'ON TABLE PCHOLD FORMAT EXL2K OPEN
ON TABLE SET STYLE *
type=report, titletext='Summary Report', $
ENDSTYLE'

and then run the detail, with whatever breaksyou want and make sure to use the
'ON TABLE PCHOLD FORMAT EXL2K CLOSE'
with or without a titletext.

Hi,

Thanks a lot for your answers it really helps...

Navaneeth

quote:

Pat - feel free to pop in if you can explain it better!!

This is like being called on by the teacher when you haven't done your homework!

Glenda, that is an absolutely brilliant solution. The only thing I could add to your explanation is that the RECORDLIMIT is the maximum number of lines to print on one worksheet (tab). Navaneeth in your case that would be 65000. The increment on &LIM is the same as the recordlimit so that in the next iteration you are starting with the next ranked record.

Navaneeth,
One warning, you may end up with a workbook that can't be opened because of the file size. A 65,000 line worksheet takes a lot of memory to open.

Pat,

Oh but I knew you would hit the ground running.

I've always known I was not a very good mentor. I can show them how to do something, but I can't always explain how in works in such a way they will understand.

Who ya gonna call? Codebusters!

Thanks to you and all the other "Codebusters".