Hi everyone, i am having trouble to find a way to limit the record return. I have a program that generates report in excel format. Sometimes there are more than 65K records return which exceeds the excel limit. I tried to use "recordlimit", but it doesn't work the way i expect. My program is shown below.(The CAR file has more than 65k records)



TABLE FILE CAR
SUM
SALES
RANKED AS 'RANK' BY TOTAL HIGHEST SALES NOPRINT
BY MODEL
BY TOTAL SALES
ACROSS COUNTRY
........
ON TABLE PCHOLD FORMAT EXL2K
end


IF I put the record limit in the above code, what the system does is to read only the first 65k record, then it does the "summing" and "grouping". I was hoping that it will actually do the "summing" and "grouping" first and if it still has more than 65k records return, send me the first 65k and dump the rest. Is there a way i can make the system processe the data first and then do "simming" and "grouping" afterward? I have done some search in this forum and it does not seem to have solution to my problem. I also tried EXL2K BYTOC, but it crashes the server. Iam running out of idea. Could someone help?

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

I don't know if you can do by HIGHEST 65,000 .

But you could try READLIMIT insead of recordlimit

Do the summing and grouping against the whole file first, changing ACROSS COUNTRY to BY COUNTRY. Put the results in a hold file.

Then table the hold file as you have it in your post for the car file and put the readlimit in that request.

So it would look something like this:

TABLE FILE CAR
SUM
SALES
RANKED AS 'RANK' BY TOTAL HIGHEST SALES 
BY MODEL
BY TOTAL SALES
BY COUNTRY
ON TABLE HOLD FORMAT ALPHA
END
TABLE FILE HOLD
SUM
SALES
RANKED AS 'RANK' BY TOTAL HIGHEST SALES NOPRINT
BY MODEL
BY TOTAL SALES
ACROSS COUNTRY
WHERE READLIMIT LE 65000
ON TABLE PCHOLD FORMAT EXL2K
END

or something like that.

Hi Ginny,

Thanks for the suggestion. Yes, I actually have already done the first part your posted. Below is the complete code.

TABLE FILE OTHERFILES
SUM
SALES
BY TOTAL HIGHEST TEU NOPRINT
BY MODEL
BY COUNTRY
ON TABLE HOLD AS CAR FORMAT ALPHA
END

TABLE FILE CAR
SUM
SALES
RANKED AS 'RANK' BY TOTAL HIGHEST SALES NOPRINT
BY MODEL
BY TOTAL SALES
ACROSS COUNTRY
........
WHERE RECORDLIMIT EQ 65000
........
ON TABLE PCHOLD FORMAT EXL2K
END


If I put "RECORDLIMIT" as shown above, it reads the first 65K records and then do the "summing" and "grouping" and "acrossing" based on the first 65K records. But what i need is to do the "summing" and "grouping" and "acrossing" first and then send the first 65K records and dump the rest. I hope I am confusing anyone.

READLIMIT : Number of Records actually Read (Perfectly clear with Fix Files). Independant of Filtering
Did not Apply to Focus Data Bases (Multisegments) because Focus stop Reading Children when a Filter rejects a parent, so there is no actual Counting of the Lower Level Segment accessed by the Focexec
RECORDLIMIT : Number of "records" that get through Filtering and are placed in the internal Matrix
(This is 1980's level. Version Nobody remembers ...)
This being Said : Across + By Highest make things difficult (I Use DM to avoid Across, but then, we turn to Focus ++)

Learner,

You could try to assign a value to each record after doing sum, by and across. And then screen on that column. Consider the following code:

 TABLE FILE CAR
SUM SALES
BY COUNTRY
BY CAR
ACROSS SEATS
COMPUTE RECNO/I4=RECNO+1;
ON TABLE HOLD
ON TABLE SET ASNAMES ON
END

TABLE FILE HOLD PRINT *
WHERE RECNO LE 6;
END 

This will give you more or less exactly what you want to have, albeit that the columnnames of the across fields are not quite what they should be - but that is a minor issue.

Hope this helps you on your way to a final solution.

COMPOUND BYTOC would be the best way to handle the information, have you investigated why it crashes the server?

The talk I gave at FUN (Focus Users of New England) contained the code for several ways of limiting data to get a sample.

Some of the methods might help you restrict your data. My slides are posted at http://fun.ibi.com/ follow the presentations link. It's in the Fall 2008 section.

First of all, thanks for all the responses. Very much appreciated. I have tried all the methods but with no luck. 8-(

First, I used bytoc but got the below error message

==========error message===========

Unknown error occurred.
Agent on reporting server EDASERVE may have crashed.
Please investigate reporting server log.

ERROR:

==================================



Then i put a counter in the report with the hope that i would be able to use it to screen out the record, (AFTER the summing, grouping, etc... completed), but it crashes the server too.



DEFINE FILE CAR
TSTCNT/D1=1;
END

TABLE FILE CAR
SUM SALES COMPUTE COUNTINDEX/I11=(LAST COUNTINDEX)+TSTCNT; NOPRINT
BY COUNTRY
BY CAR
ACROSS SEATS
WHERE TOTAL COUNTINDEX LE 65000
ON TABLE HOLD
ON TABLE SET ASNAMES ON
END

TABLE FILE HOLD PRINT *
WHERE RECNO LE 6;
END


Waz mentioned that i should look at the log files and see what happen. Could someone show me where do they reside.

I still hope that there is a way to solve this problem.

The server logs would be in ...\ibi\srv71\wfs on the machine were the reporting server is. However, I seriously doubt whether looking at them is going to help you.

Are you crashing an agent or are you crashing the whole server process?

What I think I would do if I were you is start with a much smaller file, 100 rows or so. Then try to do what you are doing with a much smaller such that you might get error messages instead of crashes. That way you might be able to figure out what you are doing wrong.

Hi GinnyJakes,

Thanks for the tips. Yes, i just tried it with a much small file, it still crashes. I think i must have used the wrong term.. From the attached error message in the previous post, it seems that the agent has been crashed, not the whole process. But I really don't know why! I thought at first the file was too large for webfocus to handle, but it doesn't seem to be the case because I still get the same error message even the file is much smaller. Do you know what causes the error?

Learner, can you post your code so we can have a look?

Can you replicate this with the CAR file or one of the other sample files?

This is just an observation, but I don't see your field 'RECNO' defined anywhere, which you use in your where statement.

quote:

TABLE FILE OTHERFILES
SUM
SALES
BY TOTAL HIGHEST TEU NOPRINT
BY MODEL
BY COUNTRY
ON TABLE HOLD AS CAR FORMAT ALPHA
END

Going back thru the stream, I found this piece of code, I think CAR is a protected file. Also, I think it should be BY HIGHEST TOTAL TEU and as LINUS pointed out RECNO isn't defined. I would not recommend doing a BY HIGHEST TOTAL on a file with more than 65,000 records.

Glenda recently posted code for breaking an EXL2K report on 65,000 records. Search on her posts.

Very much appreciate for all the suggestions and responses.

Waz, after reading your email, i retested my code and found that the counter i put in the my code doesn't do the job that i would like to, maybe that was the reason that the agent crashed. Thanks for your notes.

Let me explain in more details what i would like to acheive. Let's assume that the excel can only handle 4 rows of records and the user's request returns the six rows of records shown below. How to tell WebFocus if there is more than 4 rows of recrods, only give me the first four records. In this case, only up to US and disregard Korea and Italy.

		Highest Sales	Q1	Q2	Q3	Q4
1.	England	4000	1000	1000	1000	1000
2.	China	2000	500	500	500	500
3.	Japan	1000	250	250	250	250
	US	1000	250	250	300	200
4.	Korea	500	100	300	20	80
5.	Italy	200	50	50	50	50

As I mentioned in the previous post, if I used "recordlimit" ONLY, there may be a chance that China or other countries before the 4th row may not have the the same sales number as it shows here. The reason is the "recordlimit" only reads up to the fourth record before it sums and groups and puts the report together into the across form.

As you probably notice that I also need the ranking in the front as well, which makes it even more complicated.

Can anyone find a solution? Thanks in advance for all the suggestions and resposnes.

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

Learner,

I think that your best method would be to use a combination of the CNT field and BY HIGHEST. If you want a Rank prior to the first column then you may have to perform an additional pass.

EX -LINES 5 EDAPUT MASTER,LEARNER,CF,MEM,FILENAME=LEARNER, SUFFIX=FOC,$
SEGNAME=ONE, SEGTYPE=S1, $
  FIELD=COUNTRY,     ,A10  ,A10  ,$
  FIELD=QUARTER,     ,A3   ,A3   ,$
  FIELD=SALES,       ,D12.2,D12.2,$
-RUN
MODIFY FILE LEARNER
FREEFORM COUNTRY QUARTER SALES
DATA
England,Q1,1000,$
England,Q2,1000,$
England,Q3,1000,$
England,Q4,1000,$
China,Q1,500,$
China,Q2,500,$
China,Q3,500,$
China,Q4,500,$
Japan,Q1,250,$
Japan,Q2,250,$
Japan,Q3,250,$
Japan,Q4,250,$
US,Q1,250,$
US,Q2,250,$
US,Q3,250,$
US,Q4,250,$
Korea,Q1,100,$
Korea,Q2,300,$
Korea,Q3,20,$
Korea,Q4,80,$
Italy,Q1,50,$
Italy,Q2,50,$
Italy,Q3,50,$
Italy,Q4,50,$
END
-RUN
TABLE FILE LEARNER
SUM SALES AS COUNTRY_SALES
 BY COUNTRY
SUM SALES
 BY COUNTRY
 BY QUARTER
ON TABLE HOLD
END
-RUN
TABLE FILE HOLD
SUM MAX.COUNTRY_SALES AS 'Highest Sales'
    COMPUTE CNT/I3 = IF COUNTRY NE LAST COUNTRY THEN CNT + 1; NOPRINT
 BY HIGHEST 4 COUNTRY_SALES NOPRINT
 BY COUNTRY           AS ''
SUM SALES             AS ''
 BY COUNTRY_SALES
 BY COUNTRY
ACROSS QUARTER        AS ''
WHERE TOTAL CNT LE 4
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF, SIZE=9, $
ENDSTYLE
END

Will give you -

                        Q1       Q2       Q3       Q4 
          Highest Sales     
-----------------------------------------------------------
England        4,000.00 1,000.00 1,000.00 1,000.00 1,000.00 
China          2,000.00   500.00   500.00   500.00   500.00 
Japan          1,000.00   250.00   250.00   250.00   250.00 
US             1,000.00   250.00   250.00   250.00   250.00 

T

Tony, nice to see you using EDAPUT.

I've butchered Tony's example to return an XLS with multiple tabs.

Check it out an see if it works.

EX -LINES 5 EDAPUT MASTER,LEARNER,CF,MEM,FILENAME=LEARNER, SUFFIX=FOC,$
SEGNAME=ONE, SEGTYPE=S1, $
  FIELD=COUNTRY,     ,A10  ,A10  ,$
  FIELD=QUARTER,     ,A3   ,A3   ,$
  FIELD=SALES,       ,D12.2,D12.2,$
-RUN
MODIFY FILE LEARNER
FREEFORM COUNTRY QUARTER SALES
DATA
England,Q1,1000,$
England,Q2,1000,$
England,Q3,1000,$
England,Q4,1000,$
China,Q1,500,$
China,Q2,500,$
China,Q3,500,$
China,Q4,500,$
Japan,Q1,250,$
Japan,Q2,250,$
Japan,Q3,250,$
Japan,Q4,250,$
US,Q1,250,$
US,Q2,250,$
US,Q3,250,$
US,Q4,250,$
Korea,Q1,100,$
Korea,Q2,300,$
Korea,Q3,20,$
Korea,Q4,80,$
Italy,Q1,50,$
Italy,Q2,50,$
Italy,Q3,50,$
Italy,Q4,50,$
END
-RUN
TABLE FILE LEARNER
SUM COMPUTE COUNTRY_SALES/D12.2 = SALES ;
 BY TOTAL HIGHEST COUNTRY_SALES
 BY COUNTRY
SUM SALES
 BY TOTAL HIGHEST COUNTRY_SALES
 BY COUNTRY
 BY QUARTER
ON TABLE HOLD
ON TABLE SET ASNAMES ON
END
-RUN
DEFINE FILE HOLD
 CNT/I3 = IF COUNTRY NE LAST COUNTRY THEN CNT + 1;
 TAB/I3 = (CNT-1) / 4 + 1 ;
 Tab/A10= 'Group ' || LJUST(3,FTOA(TAB,'(F3)','A3'),'A3') ; 
END
TABLE FILE HOLD
SUM MAX.COUNTRY_SALES AS 'Highest Sales'
 BY Tab NOPRINT
 BY HIGHEST COUNTRY_SALES NOPRINT
 BY COUNTRY           AS ''
SUM SALES             AS ''
 BY Tab NOPRINT
 BY COUNTRY_SALES
 BY COUNTRY
ACROSS QUARTER        AS ''
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET COMPOUND 'BYTOC 1'
ON TABLE SET STYLE *
GRID=OFF, SIZE=9, $
ENDSTYLE
END

Thanks Tony and Waz for the sugguestions. I do very much appreciate your efforts.

Tony, I tried your method but i got the error message as shown below

0 NUMBER OF RECORDS IN TABLE= 36710 LINES= 19806
0 ERROR AT OR NEAR LINE 95 IN PROCEDURE power102FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: MAX.COUNTRY_SALES
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

It seems that it doesn't recognize COUNTRY_SALES. I have checked my code carefully but it seems that it matches exactly the same as the one you posted. Not sure why it does that to me?


Waz, I also tried yours. First I tried it with - ON TABLE SET COMPOUND 'BYTOC 1'. It ran and I got the report. But the data are all spread out in different worksheets which is not the way the users want. Also, the agent crashes again when I change
"TAB/I3 = (CNT-1) / 4 + 1" to "TAB/I3 = (CNT-1) / 100 + 1". Then, I took it out and ran again, this time all the data is in one worksheet, but they are not sorted by highest sales. How to make it sort by highest sales?

*By the way, not sure whether it is a factor. The by and across field are dynamic. They need to be flexible. The sample report I posted is only one of the combinations.

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

Learner,

It should work fine as a straight copy'n'paste. The line SUM SALES AS COUNTRY_SALES will create the field COUNTRY_SALES in the interim HOLD file and so you should not receive that error.

Place CMD TYPE HOLD.MAS as a new line immediately before TABLE FILE HOLD and let me know what the master looks like from the HOLD file.

T

Tony, maybe i am just not smart enough. First of all, as I mentioned that the by field and across field are dynamic. So, the data is also dynamic. I was assuming that your code

from line

EX -LINES 5 EDAPUT MASTER,LEARNER,CF,MEM,FILENAME=LEARNER, SUFFIX=FOC,$

to ...

Italy,Q4,50,$
END


is just for you to have some data to run against. So, I did not put that in my code when i ran a test. What I did was to translate all the field names in your code into the field names in my code. So, in term of the field names, I am not the same as yours. But the logic should match exactly the same. Not sure that would be the cause of the error message.

Then, I copy and paste everything from

EX -LINES 5 EDAPUT MASTER,LEARNER,CF,MEM,FILENAME=LEARNER, SUFFIX=FOC,$

to ...

Italy,Q4,50,$
END

and run it. I get this error message

Error occurred.
ERROR: ERROR_MR_FEX_NOT_FOUND Can't create item object based on provided item key -LINES.fex.

I may have done something stupid that i may not even realize. I am now put the code that i have and hope you can help me to find what i did wrong.


-*************Code starts here**************-


SET EMPTYREPORT = ON
SET XRETRIEVAL = ON
SET SQUEEZE = ON

-DEFAULTS &across = 'FOC_NONE';
-DEFAULTS &sortField = 'FOC_NONE';

TABLE FILE ATLI
SUM TEU
BY &sortField
BY &across
ON TABLE HOLD
END
-RUN
TABLE FILE HOLD
SUM TEU AS BYFIELDTEU
BY &sortField
SUM TEU
BY &sortField
BY &across
ON TABLE HOLD
END
-RUN
CMD TYPE HOLD.MAS
TABLE FILE HOLD
SUM MAX.BYFIELDTEU AS 'Highest Teu'
COMPUTE CNT/I3 = IF &sortField NE LAST &sortField THEN CNT + 1; NOPRINT
BY HIGHEST 100 BYFIELDTEU NOPRINT
BY &sortField
SUM TEU
BY BYFIELDTEU
BY &sortField
ACROSS &across
WHERE TOTAL CNT LE 100
ON TABLE PCHOLD FORMAT EXL2K
END

-***************Code ends here*************-


The reason I did not post my code was because i wanted to create something simpler so that it is easy for people to understand. Is there anything i did wrong?

Tony, the reason Learner got a FOC003 was the hold file had the column SALES and not COUNTRY_SALES.

I would assume you have SET ASNAMES = ON in you profile.

I just added SET ASNAMES = ON and it fixes the problem about not recognizing the field name. So, we are at least one step closer now. Thanks Waz for the tips. Although we are one step closer, but i got another error messsage 8-( which is saying that the Agent my have creashed.

Unknown error occurred.
Agent on reporting server EDASERVE may have crashed.
Please investigate reporting server log.
ERROR:

I did some more testings and found that the line "WHERE TOTAL CNT LE 100" caused the agent crashed. Why is that? This line is very important because it controls the actual number of records return. What should i do in order to make it work?

If the WHERE TOTAL is causing the agent to crash, then shift the WHERE TOTAL to another TABLE FILE

Although I am concerned that a simple piece of code like this could crash the server.

I have noticed that V7.6.x is very sensitive to errors and then reproting a crash.

SET EMPTYREPORT = ON
SET XRETRIEVAL = ON
SET SQUEEZE = ON

-DEFAULTS &across = 'FOC_NONE';
-DEFAULTS &sortField = 'FOC_NONE';

TABLE FILE ATLI
SUM TEU 
BY &sortField
BY &across
ON TABLE HOLD
END
-RUN
TABLE FILE HOLD
SUM TEU AS BYFIELDTEU
BY &sortField
SUM TEU
BY &sortField
BY &across
ON TABLE HOLD
END
-RUN
CMD TYPE HOLD.MAS 
TABLE FILE HOLD
SUM MAX.BYFIELDTEU
COMPUTE CNT/I3 = IF &sortField NE LAST &sortField THEN CNT + 1;
BY HIGHEST 100 BYFIELDTEU
BY &sortField
SUM TEU
BY BYFIELDTEU
BY &sortField
BY &across
ON TABLE HOLD AS HOLD_2
END
TABLE FILE HOLD_2
SUM MAX.BYFIELDTEU AS 'Highest Teu'
BY HIGHEST 100 BYFIELDTEU NOPRINT
BY &sortField
SUM TEU
BY BYFIELDTEU
BY &sortField
ACROSS &across
WHERE CNT LE 100
ON TABLE PCHOLD FORMAT EXL2K
END

Hi Waz, it seems that no matter how I arrange it, the ACROSS AND WHERE CNT LE 100 don't seem to like each other that much. It works only if I remove either one of them. Not sure whether it is a program or just my machine. I have a small excel file that has all the data. Is there a way to attach the data i have and attach it with this post so that people can look at the data and run a test on it?

As far as I know, the average person cannot attach anything but the text in the post.

You have a couple of options.

1. Put your data and fex in a location exposed to the internet, and put a link to it here.

2. If your data is small emough, post the fex, master, acx and export the excel file to csv and post it all here.

quote:

TABLE FILE CAR
SUM
SALES
RANKED AS 'RANK' BY TOTAL HIGHEST SALES NOPRINT
BY MODEL
BY TOTAL SALES
ACROSS COUNTRY
........
ON TABLE PCHOLD FORMAT EXL2K
end

Have you tried
[quote]
TABLE FILE CAR
SUM
SALES
RANKED AS 'RANK' BY TOTAL HIGHEST 6 SALES NOPRINT
BY MODEL
BY TOTAL SALES
ACROSS COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
[end quote]
?

Hi Waz, haven't had a chance to follow up this issue for the past few days. I just went over the code once again and found out that there is one thing in the code that is different between mine and yours, and it is the where statement. Somehow i put "WHERE TOTAL CNT LE 100" instead of "WHERE CNT LE 100". The "TOTAL" messes up the program and causes the agent crashed. As soon as i take away the TOTAL, it works. I really wanted to say thank you for all of your help as well as other people in this forum who have helped out.
RSquared, thank you for your suggestion too. I indeed have tried your suggestion. Thanks again everyone!