NSQLRB doc

Program was created in the programming language of Delphi as an extension of very popular QuickReport 3.x components. Its I am was creating the programme that makes possible production of printing reports for end user, too.

easy creation of printing report and setting of component properties

full support of end users for formation own printing report

full support SQL (database MS SQL, Interbase, Oracle, Sybase and ODBC driver)

support ASCII, DBF and Paradox tables

full support SQL questions, possibility of using more SQL questions (support master-slave constructions)

simple event programing that makes possible parametrization SQL query, modification SQL query, doing various calclulations, doing master-slave constructions, etc.

possibility to save, save report with data and compressed

this programmme enables to combine the dates of varions types of databases

(MS SQL, Interbase, Oracle, Sybase, ODBC, DBF, Paradox), at the same time

barcode printing (UPC, EAN, Code, …)

graph and image printing

accept parameters from file, clipboard or command line

support marcos

export report to formats HTML, CSV, TXT, XLS, PDF ...

end user rights setting (read only, design modify, modify all)

save report with data to compressed format

interactive input parameters by end user

First steps

Open and preview report files:

Country.rpd, CountrySuma.rpd, EAN13.rpd, CustomerRTF.rpd, Bilolife.rp

Theme and examples .

DBGraph _Country.rpd

MasterDetail report _CountrySuma.rpd

Barcode _EAN13.rpd

RTF file with data from dataset _CustomerRTF.rpd

Macros and events programming _Country.rpd –> Macro_Select

DBImages Biolife.rp (BDE shared data)

Horizontal and vertical resize is allowable on the right and bottom margin of the components

Open a Query

Choose SQL>Query>Q1 on the program menu.

Choose Alias e.g.: NSQLRB or another (IBLOCAL,DBDEMOS) on the query property editor.

Notice: You want create new alias with the programm BDE ADMIN on the control panel

Write SQL query e.g.: select * from COUNTRY.

Click the Open button.

Add a new band

Click on the QRBand icon from tool bar menu and click on the report form

Set properties of new band.

PageHeader	The first band printed on all pages. On the first page printing is depended on the FirstPageHeader property. The default is to not print the first page header.
Title	A title band is the first band printed to a report (after the very first page header). It’s useful for printing the report title, data selection criteria, creation date and time and so on.
ColumnHeader	The column header band is printed on top of each report column. In a regular single column report the band is printed once per page, following the page header (and the title band for the first page). For a multi column report it’s printed once for each column. It’s most useful for printing field names.
Detail	One detail band is printed for each record (row) of data in your dataset. This is perhaps the most important band in your report and is usually what takes most of the space on the final output. You would typically put data aware printable controls (RLabel and others) on this band.
Summary	After all detail bands has been printed you can print a summary band. This is often used for printing totals of numeric fields.
PageFooter	The last band printed on all pages. On the last page printing is depended on the LastPageFooter property. The default is not to print the last page footer.

Preview and print report

Choose Run > Preview on the program menu.

Program language, reserved words

(similar Delphi pascal language)

TInteger, TFloat, TBoolean, TString, Procedure,

Begin End, If then else, For to do, While do,

Proc, Beep, Break, Continue, Exit, Quit

TInteger

Use: integer global variable declare

Type Range Format

Integer –2147483648..2147483647 signed 32-bit

For example:

MyValue1, MyValue2 TInteger;

TFloat

Use: global variable declare

Type Range Significant digits Size in bytes

Float 5.0 x 10^–324 .. 1.7 x 10^308 15–16 8

For example:

MyValue1, MyValue2 TFloat;

TBoolean

Use: global variable declare

Logical type with values true and false

TString

Use: global variable declare

A string represents a sequence of characters. (predefined string types).

Type Maximum length Memory required Used for

String ~2^31 characters 4 bytes to 2GB 8-bit (ANSI) characters

Procedure

A procedure declared

For example:

Procedure SetValues;

Begin

a:=true; b:=’My string’;

c:=1.23; // float value

End;

Begin End

The block contains a simple or structured statement that is executed when the program runs. In most programs, the block consists of a compound statement—bracketed between the reserved words begin and end

The compound statement is bracketed by the reserved words begin and end, and its constituent statements are separated by semicolons. For example:

begin

Z := X;

X := Y;

Y := Z;

end;

if then else

There are two forms of if statement: ifthen and the ifthenelse. The syntax of an ifthen statement is

if expression then statement where expression returns a Boolean value. If expression is True, then statement is executed; otherwise it is not. For example,

if J > 0 then A := I/J;

The syntax of an ifthenelse statement is if expression then statement1 else statement2 where expression returns a Boolean value. If expression is True, then statement1 is executed; otherwise statement2 is executed. For example,

if J = 0 then A:=0;

else A:=I/J;

The then and else clauses contain one statement each, but it can be a structured statement. For example,

if J = 0 then begin

end else begin

end;

For to do

A for statement, unlike a repeat or while statement, requires you to specify explicitly the number of iterations you want the loop to go through. The syntax of a for statement is

for counter := initialValue to finalValue do statement

for counter := initialValue downto finalValue do statement

where

counter is a local variable (declared in the block containing the for statement) of ordinal type, without any qualifiers.

initialValue and finalValue are expressions that are assignment-compatible with counter.

statement is a simple or structured statement that does not change the value of counter.

For example:.

For x:=1 to 10 do begin ... end;

While do

A while statement is similar to a repeat statement, except that the control condition is evaluated before the first execution of the statement sequence. Hence, if the condition is false, the statement sequence is never executed.

The syntax of a while statement is

while expression do statement

example:

While i<10 do begin

i:=i+1;

...

end;

Proc

A procedure call of the name of a procedure,

Example:

Proc Main;

Beep

Generates a standard beep using the computer speaker.

Break

Causes the flow of control to exit a for, while, or repeat statement.

For example:

For i:=0 to 100 do begin

if i+a>500 then Break;

end;

Continue

Allows the flow of control to proceed to the next iteration of for or while, statements. The Continue procedure causes the flow of control to proceed to the next iteration of the enclosing for or while statement.

Exit

Exits from the current procedure.

Quit

Quit from the current program.

Binary arithmetic operators

Operator Operation Operand

^ exponent

+ addition

- subtraction

* multiplication

/ division

div integer division

mod remainder

The + operator is also used as a string operator: 'Alfa' + 'Beta' = 'AlfaBeta'

Boolean logical operators

Operator Operation Operand types

and logical and

or logical or

xor logical xor

Relational operators

Operator Operation Operand types Result type

= equal

<> not equal

< less than

> greater than

<= less than or equal to

>= eater than or equal to

Type convert

Type to String to Float to Integer to Boolean

String - Float('2.3') Integer('34')=34 Boolean('false')

Float String(2.3)='2.3' - Integer(2.0)=2 Boolean(0.0)

Integer String(45)='45' Float(45) - Boolean(0)=false

Boolean String(true)='TRUE' Float(true)=1 Integer(false)=0 -

Date time convert look at the date time functionsdate time functions

Mathematical functions

Trunc, Round, Abs, ArcTan, Cos, Exp, Frac, Int, Ln, PI, Sin, Sqr, Sqrt, Power, Min, Max, Sign, Random

Trunc

Function Trunc( value: float) result integer

Truncates a float number to an integer.

Round

Function Round( X: float) result integer

Returns the value of X rounded to the nearest whole number.

Int

Function Int( X: float) result float

Returns the integer part of a real number.

Abs

Function Abs( X: integer or float) result integer or float

Returns an absolute value

ArcTan, Cos, Sin

Trigonometric functions

Ln( value:float) result float

Returns the natural log of a float expression

Exp( value:float) result float

Returns the exponential of value.

Use Pi in mathematical calculations that require pi, the ratio of a circle's circumference to its diameter. Pi is approximated as 3.1415926535897932385

Frac

Function Frac ( X:float) result float

Returns the fractional part of a float number.

Example: frac(21.5)=0.5

Random

Function Random [ ( Range: Integer) ] result float or integer

Description: Random returns a random number within the range 0 <= X < Range.If Range is not specified, the result is a real-type random number within the range 0 <= X < 1.

RoundTo

Function RoundTo(Value:Float, [RoundMode:1/0/-1, [Digits:integer]])

Description: Round value to specified digits by round mode.

RoundMode: (default 0)

1 = up (round to up, example: RoundTo(22.34, 1, 0) = 23.0

0 = natural round (default), example: RoundTo(27.45, 0, 0) = 27.0

-1 = round to down, example: RoundTo(27.75,-1, 0) = 27.0

Digits: (defult 2)

0 = indicated integer value

1 = indicated round to decimal, example: RoundTo(27.45, 0, 1) = 27.5

-2 = indicated round to hunder, example: RoundTo(22.34, 1,-2) = 100.0

example by default parameters : RoundTo(2.555) = 2.56

Min

Function Min (Value1:float, Value2:float) result float

Max

Function Max (Value1:float, Value2:float) result float

String functions

LF, Upper, Lower, Copy, ABCopy, Delete, Insert, Pos, LastPos, NextPos, Length, CHAR, ASCII, Num, DelSpaces, Trim, TrimLeft, TrimRight, Replace, ReplaceSensitive, Reverse, ToLeft, ToRight, ToCenter, Eval, TextEval

LF

function LF result CHAR(13)+CHAR(10) line feed characters

Upper

function Upper(Source: string) result string;

Description: Converts a string to upper case. Upper returns a string that is a copy of S, converted to upper case. The conversion uses the current Windows locale.

Lower

function Lower(Source: string) result string;

Description: Converts a string to lowerr case. Lowerr returns a string that is a copy of S, converted to lower case. The conversion uses the current Windows locale.

Example: Lower(‘ŠČ‘)=‘šč’

Copy

Function Copy(Source:string, FormIndex:integer, CharCount:integer) result string

Returns a substring of a string

Example: Copy(‘ALFA’,2,3)=’LFA’

ABCopy

Function ABCopy(Source:string, FormIndex:integer, ToIndex:integer) result string

Returns a substring of a string.

Example: ABCopy(‘ALFA’,2,3)=’L’

Delete

Function Delete (Source:string, FormIndex:integer, CharCount:integer) result string

Returns a substring of a string

Example: Delete(‘ALFA’,2,2)=’AA’

Insert

Function Insert (Source:string, Destinatin:string, FormIndex:integer) result string

Returns a substring of a string

Example: Insert(‘ZZZ’, ‘XXALFAXX’, 2)= ‘XZZZXALFAXX’

Pos

function Pos(Substr: string, S: string) result Integer;

Returns the index value of the first character in a specified substring that occurs in a given string.

Example: Pos(‘ALFA’, ‘XXALFAXX’)=3

LastPos

function Pos(Substr: string, S: string) result Integer;

Returns the index value of the last character in a specified substring that occurs in a given string.

Example: LastPos(‘AX’, ‘XAXLFAXX’)=6

NextPos

function NextPos(Substr: string, S: string; FromIndex:integer) result Integer;

Returns the index value of the first character in a specified substring that occurs in a given string from a specified index.

Example: NextPos(‘AX’, ‘XAXLFAXX’, 3)=6

Length

function Length(str: string) result Integer;

Example: Length(‘AX’)=2

CHAR

function Char(i: integer <0,255>) result character;

Example: CHAR(64)=’A’

ASCII

function ASCII (str:string) result integer <0,255> by first character of string;

Example: ASCII(‘Beta’)=65

Num

function Num(strnum: string, digits:integer) result string (number format)

Example:

Num('1234567.98',1)= '1 234 567,9' Num('1234567.8', 3)= '1 234 567,800'

DelSpaces

function DelSpaces(str: string) result string without space

Example: DelSpaces(' A BC D ')= 'ABCD'

Trim, (TrimLeft, TrimRight)

function Trim(str: string) result string

Trims leading and trailing spaces and control characters from a string.

Example: Trim(' A BC D ')= 'A BC D'

Replace (ReplaceSensitive)

function Replace(DestStr, OldPattern, NewPattern: string) result string;

Returns a string with occurrences of one substring replaced by another substring, (replace all old pattern, insensitive)

Example: Replace(‘Alfa’, ‘A’ ‘33’)= ‘33lf33’

Reverse

function Reverse(str: string) result string

Example: Reverse('ABC')= 'CBA'

ToLeft (ToRight, ToCenter)

Function ToLeft(Source:string, str:string, Len:integer) result string

Align to left

Example: ToLeft (‘234’, ‘+‘, 6)= ‘234+++’

Eval

Function Eval(ExpressionText:string) result string

Evaluated ExpressionText parameter

Example:

Eval(‘3+4’)=’7’;

A:=10; Integer(Eval(’A+5’))= 15;

Eval(’ ’’alfa’’ + ’’Beta’’ ’)= ’alfaBeta’ or

Eval (’ `alfa` + `Beta` ’ )=’alfaBeta’

TextEval

Function TextEval(Text:string) result string

Example:

TextEval(‘Hello John. Today is {{Date}}.’)=’Hello John. Today is 2001/05/01.’ //replaced system date

TextEval(‘3+7={{3+7}}’)= ’3+7=10’

TextEval(‘Your name {{ Q1.F(Name) }}’)=’Your name Black John’ // replaced from dataset Q1

Date time functions

Year, Month, Day, DateSepar, DecimalSepar, Date, Time, StrToDate, StrToTime, StrToDateTime, DateToStr, TimeToStr, DateTimeToStr

Year (Month, Day)

function Year (strDatebyWindows: string) result integer

Example: Year('2001.01.02')= 2001

DateSepar, DecimalSepar

Result date separator, decimal separator character by Windows regional settings.

Date, Time result string

Result system date, time to string

StrToDate, StrToTime, StrToDateTime convert string to float

DateToStr, TimeToStr, DateTimeToStr convert float to string

Variable manipulated functions in run time

VarExist, VarFree, VarAdd

VarExist

function VarExist(VariableName: string) result boolean

Result true if variable exist

VarFree

function VarFree(VariableName: string) result boolean

Destroy variable

VarAdd

function VarAdd(NewVariableName: string TypeFirstChar:string I/F/B/S ) boolean

Create new variable by type ( i=integer f=float b=boolean s=string)

File and directory functions

CurrentDir, FileExists, CreateFile, OpenFile FileSize, EOF, FilePos, Seek, Write, Writeln, Readln, Read, CloseFile

CurrentDir

function CurrentDir result current directory name as string

FileExists

function FileExists(FileName:string) result boolean

CreateFile

function CreateFile(FileName:string) result boolean

Create empty file

OpenFile

function OpenFile(FileName:string) result boolean

FileSize

function FileSize result integer

Result opened file size

EOF

function EOF result end of opened file - boolean

FilePos

function FilePos result integer , cursor position in opened file

Seek

function Seek(FilePos:integer) boolean

Moves cursor the current position (FilePos) of the opened file

Write

function Write(DataStr:string) boolean

Block write DataStr to the file

Writeln

function Writeln(DataStr:string) boolean

Block write DataStr to the file with end of line characters (#13#10).

Readln

function Readln result string

Reads a line of text from the file

Read

function Read(ByteCount:integer) result string

Reads current byte from file

CloseFile

function CloseFile closed opened file - boolean

Program managing and input parameters functions

Run, SetModify, ProcessMessages, CloseAll, ToClipboard, Hourglass, Default, ShowMessage, Error, InputClear, InputBegin, InputEnd, InputOK, InputField

Run

function Run (Command: string, wait:boolean, visible:boolean) result boolean

Run command or file. If ”wait” parameter is true, program wait until finish command. If ”visible” parameter is false, than command window will bee not visible.

Example: run(‘c:\Adobe\Acrobat.exe’,false,true)

SetModify

function SetModify(Enabled:boolean) result true

Enabled or disabled datasets and program modify

Processmessages

Application process messages

CloseAll

Stop program and close application

ToClipboard

function ToClipboard(text: string) boolean;

Copy text to clipboard

Hourglass

Screen mouse cursor change on Hourglass

Default

Screen mouse cursor change on Default

ShowMessage

function ShowMessage(text: string) boolean;

Show text

Error

function Error(text: string) boolean;

Show error text

InputClear

Clear all component caption and text values on the InputForminputform, and set its not visible

InputBegin

Show InputForminputform and hide another forms.

InputEnd

Input end set to the true after if user closed InputForminputform

InputField

function InputField(ComponentIndex:integer <1..10>) boolean;

Function is true after user exit component with index ComponentIndex on then InputForminputform

Example:

Procedure Macro_SelectCountry;
begin
InputClear;InputForm.Title.visible:=true;
InputForm.Label1.visible:=true;
InputForm.ComboBox1.visible:=true;
InputForm.Title.caption:=‘Select Country‘;
InputForm.Label1.Caption:='Country';
InputForm.ComboBox1.add('South America');
InputForm.ComboBox1.add('North America');
InputBegin;
while InputEnd=false do begin
//if inputField(1) then
end;
if InputOK then begin //dont cancel
//report
Q1.addtoWhere('continent="'+InputForm.ComboBox1.text+'"');
//garph
Q2.addtoWhere('continent="'+InputForm.Combobox1.text+'"');
Q1.Open;
Q2.Open;
QuickRep1.Preview;
end;
end;

InputOK

After close InputForm, InputOK is false, if only user closed form with ”cancel” button.

Create simple report functions

Create_Simple_Report, Create_MasterDetail_Report

This function created new report by Q1 and Q2 dataset.

^K Create_Simple_Report

function Create_Simple_Report result boolean

Create new simple report for Q1 dataset

^K Create_MasterDetail_Report

function Create_MasterDetail_Report(MasteFieldName:string, DetailFieldName:string) MasterFieldName from Q1 and DetailFieldName from Q2 dataset fields

General property functions and methods for components in run time

Prop, Modify, PropExist, CompExist, ClassName, Create, Init

^K Prop

function Prop(PropertyName: string, [OwnerFormName:string] ) string;

Result property value as string

Example: Prop('QRLabel1.font.color')= ‘clWindowText’

^K Modify

function Modify (PropertyName: string, ValueStr::string) boolean;

Set property value

Example: Modify('QRLabel1.font.color', ‘clRed’)

^K PropExist

function PropExist(PropertyName: string, [OwnerFormName:string] ) boolean;

Result true if property exists

Example: PropExist('QRLabel1.font.color')= true

^K CompExist

function CompExist(ComponentName: string, [OwnerFormName:string] ) boolean;

Result true if component exists

^K ClassName

function ClassName(ObjectName: string, [OwnerFormName:string] ) string;

Result object class name

Example: ClassName('QRLabel23')= ‘TQRLabel’

^K Create

function Create(ObjectTypeName: string, [ControlObjectName:string] ) string;

Create new report component in run time.and result new component’s name.

ObjectTypeName must by from : QRLABEL, QRMEMO, QRSHAPE, QRIMAGE, QRDBIMAGE, QRCHART, QRGRAPH, QRBARCODE, QRRICHTEXT, QRBAND, QRCHILDBAND,QRDETAILBAND, QRGROUPBAND

Example: Create(QRLabel)= ‘QRLabel123’

^K INIT

function INIT result boolean

Report component’s events initialization (for run time created components).

TStringList and TStrings class

Add, Clear, Count, Strings, Delete, IndexOf, IndexOfName, LoadFromFile, SaveToFile, CommaText

Directly use example:

Q1.sql:='selet Name Area '+LF+ 'from country.db'+LF+

'order by Name';

InputForm.ComboBox:= 'Alfa'+LF+'Beta';

// LF=CHAR(13)+CHAR(10)

^K Add(LineText : string) boolean;

Append new line to list

Example:

InputForm.ComboBox.add('Alfa');

InputForm.ComboBox.add('Beta');

Q1.sql.add('order by Name');

Datbase1.param.add('UserName=aneta');

^K Clear :boolean

Clear all lines from list

Example:

Q1.sql.Clear;

^K Strings(index:integer) result string

Example:

FirstLine:=QRRichText.Strings(0);

FirstLine:=Q1.sql.strings(0);

^K Delete(index:integer) result boolean

Delete line with index from line list

^K IndexOf( Line:string) result integer

Returns the index of the first entry in the line with a specified value.

^K IndexOfName( Name:string) result integer

Returns the position of the first string with the form Name=Value with the specified name part..

Call IndexOfName to locate the first occurrence of a string with the form Name=Value where the name part is equal to the Name parameter. IndexOfName returns the 0-based index of the string. If no string in the list has the indicated name, IndexOfName returns -1.

^K LoadFromFile(FileName:string) boolean

Fills the list with the lines of text in a specified file.

^K SaveToFile (FileName:string) boolean

Saves the strings in the list to the specified file.

^K CommaText string

Lists the strings in the TStrings object in system data format (SDF). Use CommaText to get or set all the strings in the TStrings object in a single comma-delimited string.When retrieving CommaText, any string in the list that include spaces, commas or quotes will be contained in double quotes, and any double quotes in a string will be repeated. For example, if the list contains the following strings:

Stri,ng 1

Stri"ng 2

String 3

String4

CommaText will return:

"Stri,ng 1","Stri""ng 2","String 3",String4

TDataSet, TQuery class

RecordCount / RC, AsString / F, AsInteger, AsFloat, AsBoolean, AsDate, AsTime, AsDateTime, ParamInteger, ParamBoolean, ParamString, ParamDateTime, ParamDate, ParamTime, ParamFloat, Close, Open, ExecSQL, IsEmpty, EOF, BOF, Next, Prior, Prev, First, Last, MoveBy, Locate, LocateParcial, FieldExists, TableExists, DateType, DataSize, FieldCount, Field, GetSegment, ChangeSegment, AddToWhere, SelectCount

^K RecordCount or ^K RC result integer

Indicates the total number of records associated with the dataset.

^K AsString(FieldName:string) or ^K F result string

^K AsInteger (FieldName:string) result field value as integer

^K AsFloat (FieldName:string) result field value as float.

^K AsBoolena (FieldName:string) result field value as boolean.

^K AsDate (FieldName:string) result field value as date --> string

^K AsTime (FieldName:string) result field value as time --> string

^K AsDateTime (FieldName:string) result field value as date and time --> float

^K ParamInteger (Name:string, Value:integer)

ParamBoolean (Name:string, Value:boolean)

ParamString (Name:string, Value:string)

ParamFloat (Name:string, Value:float)

ParamDate (Name:string, Value:float)

ParamTime (Name:string, Value:float)

ParamDateTime (Name:string, Value:float)

parameterized SQL query

Example:

MD:=StrToDate(Date);

Q1.sql:=’select * from Peoples.db where Onday=

:PD’;

Q1.ParamDate(‘PD’, MD);

...Q1.Open;

^K Close

Closes a dataset.

^K Open

Opens the dataset.

^K ExecSQL

Executes the SQL statement for the query.

^K IsEmpty boolean

^K EOF boolean

Indicates whether or not a cursor is positioned at the last record in a dataset.

^K BOF boolean

Indicates whether or not a cursor is positioned at the first record in a dataset.

^K Last

Positions the cursor on the last record in the dataset.

^K First

Positions the cursor on the first record in the dataset.

^K Next

Positions the cursor on the next record in the dataset.

^K Prior or ^K Prev

Positions the cursor on the previous record in the dataset.

^K MoveBy (Count : integer)

Positions the cursor on a record relative to the active record in the dataset.

^K Locate (KeyFieldName:string; KeyValue:string) result boolean

^K LocateParcial (KeyFieldName:string; KeyValue:string) result boolean

Searches the dataset for a specified record and makes that record the current record. This function returns False, indicating that a matching record was not found and the cursor is not repositioned. Parcial =Key values can include only part of the matching key field value; for example, 'HAM' would match both 'HAMM' and 'HAMMER.'

^K FieldExists (FieldName:string) result boolean

^K TableExists (FieldName:string) result boolean

^K DateType (FieldName:string) result string

STRING, INTEGER, BOOLAN, FLOAT, CURRENCY, DATE, TIME, DATETIME, BYTES, VARBYTES, AUTOINC, BLOB, MEMO, GRAPHIC, FTMMEMO or X

^K DateSize (FieldName:string) result integer

^K FieldCount result integer

^K GetSegment (Segm:string) result string

example:

SelectSegment:=Q1.GetSegment('select');

FromSegment:=Q1.GetSegment('from');

WhereSegment:=Q1.GetSegment('where');

OrderSegment:=Q1.GetSegment('order');

^K ChangeSegment (Segm:string)

example:

//–-> select * from Customer.db where Area>1000

Q1.ChangeSegment('order by Area');

//–-> select * from Customer.db where Area>1000 order by Area

Q1.ChangeSegment('where');

//–-> select * from Customer.db order by Area

Q1.ChangeSegment('order by Name,Area');

//–-> select * from Customer.db order by Name,Area

^K AddToWhere (str:string)

Add to where segment this condition with ‘and’ operator

example:

//–-> select * from Customer.db where Area>1000’

Q1.AddToWhere('Area<1234');

Q1.AddToWhere(‘Name=”’+Q2.F(Name)+’”’);

//–-> select * from Customer.db where Area>1000 and Area<1234 and Name=”BLACK”

^K SelectCount result integer

Result record count on base SQL select

TQuickRep methods

PageNumber, Prepare, NewPage, Printing, Preview, Print, Export, Load, Save

^K PageNumber result integer

Active page number

^K Prepare result integer

Prepare function result ^K page count value.

^K Printing result boolean

Printing function result true, if report are printing on printer.

Example:

If QuickRep1.Printing then begin

QRLabel10.Visible:=false;

end else begin

QRLabel10.Visible:=true;

End;

^K NewPage

Use the NewPage method to force a report to move onto the next page.

^K Preview

^K Print

^K Export (FileName :string; FormatExt: string {‘htm’,’txt’,’csv’,’qrp’}); boolean

export report to specific file and format.

Example:

QuickRep1.Export('Alfa','htm');

QuickRep1.Export('Alfa','txt');

QuickRep1.Export('Alfa','csv');

QuickRep1.Export('Alfa','qrp');

^K Load (FileName :string); boolean

Stop program and Load specific report. FileName must have extension ”.rp” or ”.rpd”.

^K Save

Save current reportor

TQRRichText class methods

LoadFromFile, SaveToFile, LoadAndEval

SaveToFile(FileName:string)

LoadFromFile(FileName:string)

^K LoadAndEval(FileName:string)

Load specific file and call TextEvaltexteval for evaluate expressions ”{{ <expression> }}” in text.

TBatchMove class methods

^K Execute

example:

F_MainX.Table1.DatabaseName:=’’;

F_MainX.Table1.TableName:=’c:\data\mytable.db’;

F_MainX.BatchMove1.Source:=Q1;

F_MainX.BatchMove1.Destination:=F_MainX.Table1;

F_MainX.BatchMove1.Mode:=’batCopy’;

F_MainX.BatchMove1.Execute;

TPicture

SaveToFile(FileName:string)

LoadFromFile(FileName:string)

Example 1.

Theme Examples (report files) .

DBGraph _Country.rpd

MasterDetail report _CountrySuma.rpd

Barcode _EAN13.rpd

RTF file with data from dataset _CustomerRTF.rpd

Macros and events programming _Country.rpd –> Macro_Select

DBImages Biolife.rp (BDE shared data)

Procedure Q1_AfterScroll;

begin

Q2.SQL.Clear;

Q2.SQL.Add('select * from COUNTRY.DB');

Q2.SQL.Add('where Continent= "'+ Q1.F('continent') + '"');

Q2.Open;

end;

Example 2.

Procedure QRBand4_BeforePrint;

begin

QRBarcode1.BarText:=Q1.asString('name');

end;

Example 3.

Procedure Main;

begin

SumaArea,SumaPop TInteger;

SumaArea:=0; SumaPop:=0;

end;

Procedure Q1_AfterScroll;

begin

Q2.SQL.Clear;

Q2.SQL.Add('select * from COUNTRY.DB');

Q2.SQL.Add('where Continent= "'+ Q1.F('continent') + '"');

Q2.Open;

end;

Procedure Q2_AfterOpen;

begin

Q3.SQL.clear;

Q3.SQL.add('select SUM(Area) as SArea, SUM(Population) as SPop from COUNTRY.DB');

Q3.SQL.Add('where Continent= "'+ Q1.F('continent') + '"');

Q3.Open;

end;

Procedure qrLabel9_OnPrint;

begin

SumaArea:=SumaArea + Q2.AsInteger('Area');

SumaPop:=SumaPop + Q2.AsInteger('Population');

end;

Procedure QRBand3_BeforePrint;

begin

qrLabel16.caption:=NUM(SumaArea,0);

qrLabel17.caption:=NUM(SumaPop,0);

end;

Example 4.

procedure Main;

begin

CardNo Tstring;

end;

Procedure QRBand4_BeforePrint;

begin

CardNo:='0000000000000'; // for EAN13

if Q1.asString('Card_No')<>'' then CardNo:=Q1.asString('Card_No');

CardNo:=copy(CardNo,1,13);

while Length(CardNo)<13 do CardNo:= '0' + CardNo;

QRBarcode1.BarText:=CardNo;

QRLabel4.Caption:=copy(CardNo,1,1);

QRlabel5.Caption:=copy(CardNo,2,6);

QRlabel6.Caption:=copy(CardNo,8,6);

end;

Example 5.

Procedure Macro_Export;

begin

QuickRep1.Export('Alfa','htm');

QuickRep1.Export('Alfa','txt');

QuickRep1.Export('Alfa','csv');

QuickRep1.Export('Alfa','qrp');

end;

Procedure Macro_File;

begin

CreateFile('delta.txt');

OpenFile('delta.txt');

writeln('Hello JOHAN');

writeln('I`m ok');

writeln( TextEval('Today is {{DATE}}.') );

CloseFile;

closeAll; // quit

end;

Example 6.

Procedure Macro_SelectCountry;

begin

InputClear;

InputForm.Title.visible:=true;

InputForm.Label1.visible:=true;

InputForm.ComboBox1.visible:=true;

InputForm.Title.Caption:='Select Country';

InputForm.Label1.Caption:='Country';

InputForm.ComboBox1.add('South America');

InputForm.ComboBox1.add('North America');

InputBegin;

while InputEnd=false do begin

//if inputField(1) then

end;

if InputOK then begin //dont cancel

//report

Q1.addtoWhere('continent="'+InputForm.ComboBox1.text+'"');

//garph

Q2.addtoWhere('continent="'+InputForm.Combobox1.text+'"');

Q1.Open;

Q2.Open;

QuickRep1.Preview;

end;

InputForm default form with components

InputForm is defined form for data input by end users. Distribution of components can be seen on the picture All components are invisible

InputForm contains this componets:

Title (TLabel class)

Label1 ...Label10

ComboBox1 ... ComboBox10

CheckBox1 .... CheckBox10

DateTimePicker1 ... DateTimePicker10

Query1

OpenDialog1

SaveDialog1

ProcessBar1

Usage InputBegininputbegin

Command line parameters

The New SQL Report Builder uses parameters before preview function.

When New SQL Report Builder runs from another program, the parameters solves these problems:

- specification of the report file (*.rp or *.rpd)

- action specification : preview report, print report, edit report, run macro, run external procedure

- user rights specification for edit: enabled or disabled datasets and program modification

- set components properties

NSQLRB can get parameters from file, clipboard or command line.

Syntax: NSQLRB.exe [<filename>] [<command> | <procedure> [-d] ]

<filename> specified report file name

<procedure > after the report is open, it automatically run specific procedure or macro

<command>

-p print and exit

-v preview and exit

-n disabled modify program and datasets

[-d]

-d disable delete external procedure

Examples:

NSQLRB country.rp

open report

2.

NSQLRB country.rp -n

open report ”country.rp” and disabled modify program code and datasets

3.

NSQLRB country.rp Macro_Select

after country report is opened, it automatically run procedure ”Macro_Select:”

4.

NSQLRB country.rp -v

preview report and exit

5.

NSQLRB country.rp –p

print report and exit

6.

Copy to clipboard this text:

Procedure LetStart;

Begin

QuickRep1.Preview;

End;

And run NSQLRB: NSQLRB country.rp

after ”country.rep” is opened, it automatically run procedure with name ” LetStart;” from clipboard (casesensitive) , and clipboard will bee clear.

7.

Open NSQLRB and open report ”country.rp”

Copy to clipboard this text:

Procedure LetStart;

Begin

QuickRep1.Preview;

End;

And click on the NSQLRB form title bar, or on ReportDesigner form.

After NSQLRB get focus, it automatically run procedure with name ” LetStart;” from clipboard (casesensitive) , and clipboard will bee clear.

8.

NSQLRB ”c:\reports\country.rp” ”c:\myproc.txt”

after ”country.rep” is opened, it automatic run procedure with name ”LetStart” from file ”c:\myproc.txt” and file will bee delete.

9.

NSQLRB country.rp ”c:\myproc.txt” –d

after ”country.rep” is opened, it automatically run procedure with name ”LetStart” from file ”c:\myproc.txt” and file will bee not delete.

TNSQLRB Delphi5 component

Typical example:

NSQLRB1.ReportFileName:='Country.rp';

..//NSQLRB1.EnabledModifyDataSet:=false;

NSQLRB1.Open;

//NSQLRB1.RepLabel('QRLabel1').caption:='XYZ';

NSQLRB1.Preview;

//(or Print/Edit/NewEdit/RunMacro/...);

...

NSQLRB1.Close;

^K TNSQLRB component

properties:

property ProgramText:string

property EnabledModifyDataSet:boolean

property EnabledModifyProgram:boolean

property ReportFileName:string

events:

property OnEvent: TNSQLRB_Event

property OnDeactivate: TNotifyEvent

methods:

function RepComp(AName:String):TComponent;

function RepLabel(AName:String):TQRLabel;

function RepMemo(AName:String):TQRMemo;

function RepImage(AName:String):TQRImage;

function RepBarcode(AName:String):TQRBarcode;

function RepBand(AName:String):TQRBand;

function RepQRDBChart (AName:String):TQRDBChart;

function Rep(AName:String=’QuickRep1’):TQuickRep;

procedure Open;

procedure Edit;

procedure NewEdit;

procedure Preview;

procedure Print;

procedure RunMacro(MacroName:string);

procedure RunCipboard;

procedure RunFile(FileName:String; Del:boolean=true);

procedure Deactivate;

procedure Close;

procedure PreviewQRP(AFileName:string); //preview *.qrp file