report

Mass data import

4 Comments

There was an issue with importing all UK post codes from a csv file, about 3m post codes. Importing using rapidstart services (excel import) can cause buffer overflow messages. excel itself has row/size limitations. Increasing MaxNoOfXMLRecordsToSend in config file ClientUserSettings.config from default value 5000 to e.g. 20000 is no problem and can help. Also changing MaxUploadSize in server config file CustomSettings.config is an option (also available via nav service admin console). Better choice for mass data import are dataports (older nav versions) and xmlports.

Another option is to develope a report, which imports the file contents and loops through the lines. quite simple, no memory issues.

create a new report, add following code to trigger OnPreReport():

OnPreReport()// variables PostCode, Record, Post Code file, File fileName, Text, 250 line, Text, 1024 dlg, Dialog idx, Integer txtValue, Text, 100// code PostCode.DELETEALL(FALSE); COMMIT; dlg.OPEN('#1###### #2######'); // show progress dialog idx := 1;// downloaded post code file from https://www.doogal.co.uk/PostcodeDownloads.php// as test file, size: 500k, 2.1m lines, some of them contain obsolete post codes fileName := 'c:\temp\England postcodes.csv'; file.WRITEMODE := FALSE; file.TEXTMODE := TRUE; file.OPEN(fileName); file.READ(line); // skip header line WHILE file.READ(line) > 0 DO BEGIN   // skip obsolete post codes: 2. value = No   IF SELECTSTR(2,line) = 'Yes' THEN BEGIN  PostCode.Code := SELECTSTR(1,line); PostCode.VALIDATE(City,GetValue(SELECTSTR(15,line))); PostCode.County := GetValue(SELECTSTR(8,line)); PostCode."Country/Region Code" := 'GB'; PostCode.INSERT; dlg.UPDATE(1,idx); dlg.UPDATE(2,PostCode.Code); idx += 1;   END; END; file.CLOSE; dlg.CLOSE; MESSAGE(FORMAT(idx) + ' post codes imported.');LOCAL GetValue(txtValue : Text[100]) : Text txtValue := DELCHR(txtValue,'<','"'); // remove leading "   txtValue := DELCHR(txtValue,'>','"'); // remove trailing " IF STRLEN(txtValue) > 30 THEN // fields City,County are Text[30]   txtValue := COPYSTR(txtValue,1,30); // cut text, leading 30 chars EXIT(txtValue);

report runs with 1.5m valid lines/records about 5 min.

cheers

Options in Report Request Page

3 Comments

This posting shows how to add option fields to a report’s request page.

I developed a simple report on base of table Sales Header.

To filter by field Posting Date you can simply add that field to report data item “Sales Header”‘s property ReqFilterFields, but in this sample we check out how to handle complex filter processing.

Add 2 global variables of type Date: StartDate, EndDate.

Now edit the request page (Menu View/Request Page), add a container line, a group line and 2 field lines to the request page, set the SourceExpr in line 1 to StartDate, line 2 to EndDate.

To apply the filter fields add following code to trigger Sales Header – OnPreDataItem.

If both filter fields are filled, the data is filtered: StartDate<=Posting Date<=End Date.
if only StartDate has a value: StartDate<=Posting Date
if only EndDate has a value: Posting Date<=EndDate

When running the report, we get:

cheers