Hi,

We use FOCUS to produce reports on demand, i.e., whenever the report is required, it can be run online. Most of the time the reports contain more than 65K records, which when taken in a excel file gives error (as max record limit of excel will be crossed). Is there an in-built procedure in FOCUS to automatically break the report in various parts based on the number of record or what can be the best way to handle such situations.

This can be one of the solutions.

You can use the command
on table pchold format exl2k bytoc

This command would give results in different tabs based on your first sort field.

Hope this helps

Or if the company can afford it upgrade to office 2007, excel in it holds a lot more data. Of course that's not what anyone wants to hear. Is excel the required output method, bytoc sounds good, never used it myself.

Then again ... I am always going to question why anyone would want a report with more than 65,000 records.

The job of a developer is to meet the user's requirements... their business requirements. The user often does not know the different ways data can be presented to them so they express their needs in terms of what they are comfortable with and it often doesn't make sense. We need to counsel them to find the most effective and efficient way of presenting the information to them.

What are they going to do with that 65000+ rows of data once they get it? Is there anyway we can further automate whatever processes they are doing and present them with concise pieces of information?

I support Piipster's answer!

Hi Shrikant,

I also have this problem with my users. I have tried to provide summary reports wherever possible and nearly all our reports offer the option to run a detail or summary.

However the need to have such a large data extract is usually to meet a client request. They quite often want an extract of all orders for the last x months.

To prevent the report from producing an error message on load into Excel, I try to check the size of the data as it moves through the program. If the data is too large then I stop the processing and issue a message to the user letting them know that they will have to be more specific with their selections:

quote:

TABLE FILE ORDERS
PRINT
***
***
ON TABLE HOLD AS HOLD_1
END
-RUN

-* --------------------------------------------------------------------------
-* --- CHECK THAT RESULTS WILL NOT BE TOO LARGE FOR OUTPUT (IF DETAIL REPORT)
-* --------------------------------------------------------------------------

-IF &LINES GT 65000 AND &FUNC EQ 'PRINT' AND &WFFMT NE 'COMT' THEN GOTO ERR_VOLUME;

-* --------------------------------------------------------------------------
-* --- ERROR MESSAGES
-* --------------------------------------------------------------------------

-ERR_VOLUME
-HTMLFORM BEGIN
Number of lines being processed has exceeded the limit of 65,000.

Please be more specific with selections.

ALso before we enter the program, we try to validate the selections made as much as possible. For example, check that a date range supplied is not greater than 3 months etc.

I tend to agree with Karen's questioning of the need of these large sheets.
But, on the other hand, I think there must be a serious demand for large(r) excel sheets, otherwise Microsoft would not have increased the capacity of Excel 2007 to maximally 1.000.000 rows.
And IF the client needs these large number of rows, then they will have to go to 2007, or maybe implement Mark's solution, possible amending it so that not an error message is given, but the result in html (not advisable) or PDF, or with the ODP enabled.

The main reason for Microsoft to increase the capacity of Excel is that they tend to stop development of Access and they want Excel to be the front end database instead.

The BYTOC works well, it will break the report into separate sheets on your first BY field.

I had a client in 2005 who had an "excel database" which consisted of many spreadsheets. There's something about a spreadsheet that sarbenes-oxley doesn't like and gives database developers a little work to do.

If you're putting out 65,000 records, it's probably to be used as raw data for something else rather than an actual report. I would go three steps back and figure out exactly why they need all of this data...they may be developing their own graphs off of it in excel, or using a bunch of vlookup's and subtotaling to aggregate the data.

Just because I have yet to see a report of 65,000 rows of straight data doesn't mean it doesn't have a valid business solution, but the fact that they want it all in excel means that they are probably using some of the excel functionality to analyze your data, rather than simply learning webfocus or asking you to do it.

Here's another reason for this many rows. In our application we have an audit trail. It keep track of who read/inserted/updated/deleted data and when. Normally when this report is run, it is for a very specific date range, usually just a few days. I now have to run this report without time constraints which may very well cause a return of more than 65K rows in response to a law suit and a request for evidence.

You can use this method also.

TABLE FILE CAR
PRINT CAR COUNTRY MODEL
COMPUTE COUNTER/I7=COUNTER + 1; NOPRINT
COMPUTE WORKSHEET/I3=COUNTER/65000; NOPRINT
BY TOTAL WORKSHEET NOPRINT
BY COUNTRY NOPRINT
ON TABLE PCHOLD FORMAT EXL2K BYTOC
END

Note: This solution I got it from this forum long back and it works good.

Hope this helps,

Kamesh, this is a pretty good technique.

It's a widely known fact that there is a huge dependency on Excel spreadsheets. Anyone who has been developing over the past decade (and before) knows this. A lot of this is because of the ease of use of spreadsheets. But, it has certainly caused it's share of headaches too.

The problem I have with this is that just because something was done a certain way all along, doesn't mean it was the best way. It's simply...the way it's always been done. I for one have to question why someone needs to have a report this large.

About 2 years ago, I had a discussion with a colleague of mine and he recalled a situation where he would receive a report once a month (paper version at that), that contained about 2000+ pages. Why? Because that was the solution that was implemented many many moons before. It doesn't make it right.

Information Management is more about the efficient presentation of information as needed, not to satisfy the warm and fuzzies of end users.

Obviously, there will be some who say there are exceptions. I always question anyone who says they need a specific amount of information at any given time. In fact, one of the first questions I always ask is, what business process is kickstarted by you seeing this piece of information? If they can't answer, they don't need it.

Some might view it as negative, but we need to be in the business of being the data gatherers and presenting the information that is required. Let the end users do what they're paid to do - to analyze the data and make business decisions based on what they see.

Requests for evidence might be a bit of a different story, an exception so to speak. But, generally, end users want to stick with what they feel comfortable with.

Just my two cents.

Many times I see askers of this form of bulk data, that they feel uncertain when I suggest to build some reports to reduce this to a more handy report with drill downs and graphs.
It also comes sometimes into there their work field. Me and our small department make many others less important.
I have build several processes that gives the management the same info every day without the manual "hanky panky" that takes 2 or 3 hours every day.

Just a thought everyone will recognize.

K.Lane. I totally agree with you. I fight the same battle daily. However, in this case, we had not choice unless we but to make another version of the program and produce .pdf instead of excel. My mgmt wanted excel.

Also, thanks to Kamesh, you technique worked great.