Hi,

I have a Table with say 1000 records. Based on some filter (which is dynamic) i retrieve some records. My requirement is that if i get more than 500 records I display an HTML page asking user to further limit his filter, else i should display the report in required format.

Thx
CD

Try the use of &RECORDS

-IF &RECORDS GT 500 GOTO WARNING ELSE GOTO MAKEREPORT;
-MAKEREPORT
......

-GOTO END
-WARNING
.....
-END

The disadvantage is that you may go twice through the database to get the data, but you can put the found records in a hold file and use that for the reporting.

Hi Code Digger,

FrankDutch is wright .
You need to hold the data before the test on the number of records (IF &RECORDS GT 500 GOTO WARNING ELSE GOTO MAKEREPORT. If not, you will need to go through the data a second time. This is not needed since you can hold the data the first time you go through. Believe me, if your DB is pretty big, holding the data and then report from that hold file will go faster then going a second time through the data.

Actually the COUNT needs to be calculated within the TABLE REQUEST. Since getting the count for all the expected records by taking a hold file or a dummy table request requires reading all the records and hence adds to overhead.

TABLE FILE TABL
PRINT
--
--
-IF CURR_ROW_COUNT GT 500 GOTO ERRPG;
continue with report


END

-ERRPG
-HTMLFORM ERRPG (asking user to further limit the WHERE clauses like DONT select "ALL")
-EXIT

Code digger,

You need to understand how WebFOCUS reports are run.

Up until the time the parser locates the end of file or a -RUN, dialogue manager lines of code are executed first and then other code (as near as damn it) - not sequentially as you would think. So your thought of including dialogue manager (lines beginning with a hypen "-") will not do what you want it to do.

Without pulling the data from the server you will not be able to count the records. Even if you could you are still reading each record and therefore handling them so the overhead will exist what ever you do.

One way to try and limit the number of records in the report is to have smaller summary tables with counts of rows for a particular selection. Pulling the counts from these smaller tables might enable you to reduce your runtime overheads. However, you will have only shifted them to the point at which you create the smaller tables.

T

A sample code to demonstrate to what I am eluding -

-SET &ECHO = OFF;
-* This section would be performed in overnight batch
-* post main data loads
TABLE FILE GGSALES
WRITE CNT.DOLLARS AS 'DETAIL'
BY REGION
BY ST
BY CITY
BY STCD
BY CATEGORY
BY PRODUCT
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS GG_TEMP
END
-RUN

Then in your report use something like this -

-* This would comprise your main test selection to decide how many
-* records you would retrieve according to the users selections
-DEFAULT &Region = 'Midwest'
-DEFAULT &State  = 'FOC_NONE'
-DEFAULT &City   = 'FOC_NONE'
-DEFAULT &Store  = 'FOC_NONE'
-DEFAULT &Cat    = 'Coffee'
-DEFAULT &Prod   = 'FOC_NONE'

TABLE FILE GG_TEMP
SUM DETAIL
WHERE REGION   EQ '&Region'
WHERE ST       EQ '&State'
WHERE CITY     EQ '&City'
WHERE STCD     EQ '&Store'
WHERE CATEGORY EQ '&Cat'
WHERE PRODUCT  EQ '&Prod'
ON TABLE SAVE
END
-RUN

-READ SAVE, &Num_of_Recs

-TYPE There would be &Num_of_Recs retrieved for this selection

The first piece of code, run after batch loads etc, would populate a smaller table with the count of detail records at each level of heirarchy.

The second piece, run during your evaluation of retrieval limits, will pass the selection from the user and retrieve an estimate of the records that will be retrieved if the full report was run. Instead of TYPE you would have your validation code at this point and redirect as necessary.

Now GGSALES is a small table with only 4317 records within it. The summary table with the detail record counts has only 117 records within it, so reduction in record handling to begin with. When the report is run with the selection criteria, only 6 records are retrieved giving an estimate of 360 records in the final report. More efficient method of data retrieval for what you need I think.

T

edited to remove possible confusion from &RECORDS and &Records - NOT the same thing.
Also to split the two sections out to emphasise that they should be achieved in two seperate timeframes. The first during batch processing, the second during your report run so as to negate the need to double handle data at report creation time.

This message has been edited. Last edited by: Tony A,

I would suggest the use of &lines and not &records as &records is usually the number of records parsed before any screening and &lines tends to be the number of records that appear on report.

Code Digger,

If what you are trying to do is stop a request from hitting the database that will retrieve more than 500 records, there is no way in the code to do that. Resource Governer is an addon product that IBI sells that is designed to help with this sort of thing.

If you are trying to avoid sending back a large anser set to the browser then checking &LINES or &RECORDS after the initial extract is what you want to do. The only thing I would add to the other comments is that I would not do a second pass of the database. I would do an ON TABLE HOLD FROMAT HTML in the first request and then check the &LINES or &RECORDS. If they are below 500 then use -HTMLFORM BEGIN and END to return the already created HTML output.