Importing Wordsql reports Existing Wordsql reports can be imported into Artifax Reports. Choose File



Yüklə 145,21 Kb.
tarix11.09.2018
ölçüsü145,21 Kb.
#80603

Importing WordSQL reports
Existing WordSQL reports can be imported into Artifax Reports.
Choose File|Import.
Find the WordSQL database file wordsql.mdb
You should now see a list of reports. Please select the one you wish to convert to Artifax Report. These will now be converted to Artifax Report Scripts, and their templates will be converted into RTF format.
Artifax Reports
Artifax Reports is a highly flexibly report writing package. It works by manipulating the contents of an RTF template based on the results of one or more SQL queries. The queries that generate the report can be interrelated in various different ways, and the total number of queries in a report is unlimited. The queries are hierarchical: for each row returned by a parent query, a separate instance of the child query is executed with parameters passed from the parent.
Each report may have zero or one document level query. If such a query exists, then for every row returned a separate document is created. Below this, there may be none, one or many subsections, each containing none, one or many subsections etc. There are five different types of subsection: group, table, list, code and update sections. Each uses a query to change the output document in different ways:
Every report must be associated with a database and a template in the design environment. At run time, different databases and templates can be specified. The template and resulting reports are stored in RTF (Note: make sure you use the same word processor for writing the template and reading the final output. Subtle differences in RTF format between word processors can cause problems, although Artifax Reports copes with all versions).
Reports can be given a description and a type, for use within the program that will be calling the reports. Parameters can also be passed to the report from the calling programme. These can be set using the report properties page.
Document Section
The document level report section covers the global report properties: default database and template filenames, report parameters, global report notes, etc.
The report section may also optionally include an SQL statement.
If it exists, then each row returned by the query results in a new document. The fields returned in the query are inserted into the template if bookmarks matching the field names are found.
If there is no SQL statement then only one document is produced by the report.
If there is an SQL statement, but it returns no rows, then the report fails.
See Also: Sections, Templates

Sections
The report is divided up into sections. These come in five different flavors, but all have certain properties in common:
· Sections can contain other sections:

When a section is at the top level - i.e. is not contained by any other sections - it has the whole template to play with. It will find the appropriate part (bookmark, table etc.), write data to it, and run any subsections.

When a subsection is run from within another section, the only part of the template that it gets to see is the 'appropriate part' of the calling section: a subsection of a table can only see and write to the cell that it is called from (see the columnindex property) , and a subsection of a group can only see and write to the contents of the group bookmark.
· Columnindex:

If a section is contained in a table or a list then you must specify which column of the table the sub section belongs to. This applies only the level directly below the table: if table contains group contains list, then only the group and not the list need columnindex set. Columnindex refers to the column in the SQL statement, not the table. If you are using the NOOUTPUT field formatting attribute on a column, then the table column and the field column can get out of sync. Suppose you have a four column table containing a list, and you would like the list to be inserted into column 4, in place of the value that would normally go there. If you had a table query like


SELECT one, two, three, four FROM mytable
then set the columnindex of the list to 4.

If, however, the query is like


SELECT one, two, dummy, three, four FROM mytable
and dummy is set to NOOUTPUT with field formatting, the columnindex of the list should be 5, not 4, as it is replacing the 5th field selected by the query.
· Field Formatting:

The formatting properties of the output for the report can be changed depending on the field values.

It is possible for example to turn a row of a table bold if the value in a particular cell is negative.
· Delete if empty:

If a section's SQL statement returns no rows then it is possible either to leave the empty section in the template, or to remove it entirely. You can supply an alternative text string to be inserted where the section was if it is deleted.


· Grand Totals

If there is a bookmark in the template called GRTOT_fieldname, then the bookmark will be filled with the grand total of every occurrence of fieldname selected in any SQL query. This only works for numeric data types.


· Comments

Any text in the SQL statement between will be parsed out.


· Parameters and INI file parameters

It is possible to pass parameters to the report from the outside, and values selected by any query are available as parameters to its subsections. It is also possible to access INI files within the report, and to have text from the file substituted for a control word in the SQL text.


See Also: Group Sections, Table Sections, List Sections, Code Sections, Update Sections, Document Section
Groups
The group section is the most basic and flexible of the report subsections.
A group can contain any other section.
If a bookmark matching the group's name exists then a copy of the contents of the group bookmark is taken for each row returned by the SQL statement. Each copy is filled with data and then put back into the document.
For each field in the query result set if the field name matches a bookmark inside the group bookmark the then the field value is inserted at the bookmark.
For each subsection contained in the group that subsection is run, It treats the content of the group bookmark as it's 'template' to work with, and the values of all the fields of the record set for use as || parameters.
The modified contents of the group are then added to the document.
See Also: Sections

Tables
Tables

The table section provides an easy and quick way of formatting the output of a record set and displaying it in tabular form on the document.


The table section is probably the simplest to use in its most basic form, but has the most scope for getting very complicated.
The table is different from other section types in that you do not need to add bookmarks specifying where the fi.e.ld output should be inserted. Unless specifically told not to using a formatting attribute, every field selected in the SQL query is output to the table. The report inserts the field value, moves to next cell, inserts a field value, moves to next cell, etc. When it gets to the last record in a row in the query output it does not move to the next row in the table. This means that if the query returns 3 fields, and your table has 4 columns, it will look like this:
1 2 3 1

2 3 1 2


3 1 2 3

1 2 3 Etc…


This can be useful: a table one column wide or tables where the number of columns divides or is divisible by the number of fields can be very effective. On the other hand, totals and subsections will not work correctly unless the table columns match the number of fields.
When the 'Next Cell' operation moves off the end of the last row of the table, a new row is added. The last row of the table in the template (ie before any data was added) is used as the template for the new row. This means that if you are going to use any subsections of the table which require bookmarks (see below), these bookmarks should be in the last row of the table.
Totals

Any field selected by the query that is aliased to 'TOT_fieldname' will be totalled and output below the last row of the table. This only works for numeric data types.


Gap before totals

This property specifies how far below the last row of the table the totals are output.


Totals Text

If totals are being generated then you may want explanatory text to appear in the row of the table containing the totals, either in the cell containing the total, or the first cell of the row, or whatever.

To do this:

· Add an expression to the Field Formatting section of the table properties

· Fill in the field name / index

(the drop down list of fields is there to help you, but do not rely on it too much. It is safer to refer to the field by it's index number. If you have aliased the field in the select statement, then you can refer to the field by it's alias too, but it is hard to guess when JET will parse a field name as field1 rather then mytable.field1)

· (ignore the scope)

· In the Attributes text box type

TOTALS|my text string here
No output

You may need to select a field in the query that you do not want printed in the table.


To do this:

· Add an expression to the Field Formatting section of the table properties

· Fill in the field name / index

(the drop down list of field is there to help you, but do not rely on it too much. It is safer to refer to the field by its index number. If you have aliased the field in the select statement, then you can refer to the field by it's alias too, but it is hard to guess when JET will parse a field name as field1 rather then mytable.field1)

· Set the scope to Cell

· In the Attributes text box type NOOUTPUT


Heading Height

This property refers to how many rows down the table the output will start. Setting heading height to 1 allows you fill the first row of the table with column headings. Do not set heading height to be greater then or equal to the number of rows in the table in the source template.


Transform Table

If this property is set to true, then instead of going 'insert value, next cell, insert value, next row' it goes 'insert value, next row, insert value, next row'(i.e. it swaps the rows and columns). In this case is does increment the column and return the row to 1 when you reach the last field in a record from the query. This is useful for working with cross tab queries.


Subsections of tables

Firstly, in RTF, it is impossible for tables to contain tables. So don't try it.


There are three options for adding subsections to tables:

· No extra bookmarks. This is the easy option, suitable for inserting lists into cells of tables. If cell is empty and there is a subsection to go into that cell, then Artifax Reports inserts a bookmark with the same name as the subsection into the cell for you.

· Predefined bookmarks. If you want a group as a subsection of the table, then define the group bookmark and the contents in the appropriate cell in the last row of the table in the template.

· Not working at all. If the cell contains text, then Artifax Reports assumes that you don't want it to add the default bookmark. If the text in the cell does not contain an appropriate bookmark for the subsection, then the subsection will not be run. This will be a particular problem when two table type sections both reference the same table in the template. Under normal circumstances, the data output by the second pass will be appended to whatever is in the cells after the first pass, but any subsections on the second pass will not work at all.


Any subsection of the table must have the columnindex property set. The columnindex refers to the position of the field in the query rather than the column of the table.
e.g.

if the table SQL statement is


SELECT field1, field2, field3, field4, field5 FROM mytable,
and the table in the template has 4 columns, and field3 is set to No Output, then set the columnindex of a subsection of the table that you want to appear in the 4th column of the table to 5 rather than 4, since it is the 5th field from the query that will be appearing in column 4.
The field value that would have been output to the cell occupied by the subsection is not printed anywhere. It is, however, available as a || parameter to the subsection so you can insert it from there. It is also available as a parameter referred to using ?? in the SQL string.
The subsection is passed the contents of the cell as it's 'template' to work with.
Finding your table

The following algorithm is used to identify the table in the template to direct output to:


If a bookmark matching the table's name exists then:

The table used will be the first one following the bookmark

Else if the rightmost character of the table's name is a number then:

The table used will be the Xth table in the section of the template that the table has been passed. If the Xth table cannot be found, then the section will not be executed

Else

A table will be picked by Artifax Reports, with unpredictable effects. It should be OK if there is only one table, but otherwise you should specify which table you mean more precisely.



End if
See Also: Sections

Lists
The list section makes up for one of the most frustrating omissions in SQL: there is no aggregate function to concatenate strings.
The list section concatenates the results of its SQL query, allowing you to specify a different row and field separators. It then inserts the resulting string at the bookmark with the same name as the section. Lists are particularly easy to use with tables - you don't even need to add a bookmark once you set the column index. List sections can contain list, code and update type sections. Any subsection of a list must have the columnindex property set in the same way as the table.
Row and Column Separators

These properties specify the text strings to be inserted in between the fields and the rows of the query output to be made into a list. If a field is blank (null or zero length string) then no field separator will be inserted. Similarly, if the entire row is blank then no row separator will be inserted.

e.g.

If your query returns


myField1 myField2

A 1


B 2

D

4



F 5
and your field and row separators are ', ' and '; ' respectively, you will get

A, 1; B, 2; D; 4; F, 5

rather than

A, 1; B, 2; , ; D, ; , 4; F, 5

Note that there is no trailing row separator.
Be aware that these strings will be inserted directly into the output RTF document. In order to use special characters, like paragraph marks and tabs, you will have to enter the RTF code. The codes for paragraph and tab are supplied in the drop down list along with other common separators, but if you wish to use other special characters or have a more complex separator like

bla bla bla

bla bla bla: ListItem

you will have to work it out yourself. One RTF specification can be found on the internet at http://www.sunpack.com/RTF/RTF114.htm Be careful - it's nasty in there.

Also be aware that the characters \, { and } have special significance in RTF. One { or } out of place can make your document unreadable, cause MS Word to crash, or make the document end early. To use these characters as literals use \\ for \, \{ for { and \} for }.

Prefix and Suffix

These properties specify text strings to be inserted before and after the list. If the list is empty, then the prefix and suffix will not be inserted. This is so that you can have a list header

MyList: item1, item2, item3

without being left with a blank

MyList:

if no list items are returned, which you would get if you defined the header in the template rather than here. Similar considerations about paragraph and tab characters apply as above.


SaveList

If the savelist property is true, then instead of attempting to insert the list into the document it is formatted differently and stored in memory. If savelist is true then dates are formatted as #mm/dd/yy# and strings enclosed in quotes.


The last saved list can be referenced using the @@&&LIST syntax within an SQL statement. The last saved list will be substituted for the control word in the text. The savelist feature is designed to produce a list for use with an IN clause, avoiding performance mashing sub queries.
No output

You may need to select a field in the query that you do not want printed in the list.


To do this:

· Add an expression to the Field Formatting section of the list properties

· Fill in the field name / index

(the drop down list of field is there to help you, but do not rely on it too much. It is safer to refer to the field by its index number. If you have aliased the field in the select statement, then you can refer to the field by it's alias too, but it is hard to guess when JET will parse a field name as field1 rather then mytable.field1 Refering to a field by an alias rather than an index has the advantage that if you add other fields to the query, the alias won't change but the index might)


· Set the scope to Item

· In the Attributes text box type NOOUTPUT

See Also: Sections

Updates
An update section executes an action query. This can be used, for example, to set an 'Invoice Locked' flag once an invoice report has executed successfully, or to create and delete temporary working tables.
See Also: Sections

Codes
The code section executes the SQL statement, contributes to grand totals and parameters, can contain sub sections, but does not have a bookmark and does not attempt to send any output to the document. Any subsections of a code section receive the template that was passed to the code section, intact and unaltered. It can be used to implement a crude form of IF statement: if the code section SQL returns no records, then none of it's sub sections are run. By having two code statements, one reading
SELECT DISTINCT TOP 1 * FROM mytable WHERE condition
and the other reading
SELECT DISTINCT TOP 1 * FROM mytable WHERE NOT (condition)
you can ensure only one set of subsections is run.
This technique also works for groups, and is particularly effective when Delete If Empty is true, so that the bits and bobs of the unrun subsections' bookmarks are removed from the document.
See Also: Sections
Editing Section Properties
To open the properties page of a report or a section of a report, click on the appropriate part of the tree view, and either

· press the button.

· right click on the section name and choose Properties.
Parameters
External Parameters
Almost all reports need to have parameters passed in from outside: the dates you are running your profit and loss report between, the number of the invoice you wish to generate, etc. In the properties of the document section is a tab marked Parameters. Click on this tab to set parameters to be passed to the report. The values you supply on the tab are the values that will be used when you test the report from within the report editor - when the report is run from another program the supplied values will be used.
If a parameter called Number with value 5 is added to this table of parameters, then wherever the sting @@Number appears in an SQL statement, anywhere within the report, the value 5 will be substituted in.
e.g. If an SQL statement reads
SELECT * FROM Orders WHERE Order_Number = @@Number
the actual SQL executed on the database will be:
SELECT * FROM Orders WHERE Order_Number = 5
Internal Parameters
It is also possible (necessary) to pass parameters between the separate queries that go to make up a report. Suppose a you had two queries:
Parent: SELECT CompanyName, CompanyId AS XXX FROM Companies

Child: SELECT ContactName FROM ContactPeople WHERE ...


What we are after is a list of companies, and then for each company a list of contact people. For each row returned by the parent query, we will execute the child query to get the list of people. The problem is that the child query will be different each time - the first time we want
SELECT ContactName FROM ContactPeople WHERE CompanyId = 1
the next time
SELECT ContactName FROM ContactPeople WHERE CompanyId = 2
etc.

We achieve this using the following syntax:


SELECT ContactName FROM ContactPeople WHERE CompanyId = ||XXX||
this instructs Artifax Reports to look through the fields returned by the parent query (and the parent's parent, and so on) for a field called XXX. When it finds such a field, it will substitute the field's value into the text of the SQL statement.
e.g.

Suppose the group level SQL statement read


SELECT myfield1 as foo, myfield2 FROM mytable
and returned

1 A


2 B

3 C


4 D

5 E


If the subgroup SQL statement read
SELECT data FROM myothertable WHERE key = ||foo||
then the SQL statements actually executed would be
SELECT data FROM myothertable WHERE key = 1

SELECT data FROM myothertable WHERE key = 2

SELECT data FROM myothertable WHERE key = 3

SELECT data FROM myothertable WHERE key = 4

SELECT data FROM myothertable WHERE key = 5
Additionally, when the parent section is a List or Table, and the child section is inserted using the columnindex property, any occurrence of the text ?? (note - not surrounded by ||s) in the SQL of the child section is substituted for the value of the field that is being replaced - the columnindex'th field from the parent section query.

INI File substitution
Any instance of the &&INI control word embedded in the SQL statement will be substituted for a value from an INI file.
Syntax:
&&INI(FileName, Section, Key, [Default])
Part Description

FileName Name of the INI file which contains the parameter. The full path should be used when the INI file is not in the Windows directory.

Section Section heading in the INI file. Do not include the square brackets [ ].

Key Name of the key within the section.

Default [Optional] Value to be used is the INI file, Section or Key does not exist.
e.g.

The file C:\Reports\Foo.INI contains the lines:


[Preserves]

Pickle=16

Jam=13

Jelly=0
The SQL contains the expression WHERE Value = &&INI(C:\Reports\Foo.INI,Preserves,Jam)



it will be sent to the database as WHERE Value = 13
Parameters work with text substitution, plain and simple. If you are going to pass a date in this way, remember that JET likes its dates in #mm/dd/yy# format, and if you pass a string remember to include quotes. As it is just text substitution, you can even use parameters to build the SQL statement in the first place - passing the table name or the from clause, for example, using a parameter.

Grand Totals
If you insert a bookmark whose name be.g.ins with GRTOT_ followed by the name of a standard bookmark (eg you have a bookmark TotalPrice_C, you add another called GRTOT_TotalPrice_C.) the grand total of all the values in the standard bookmark name will be inserted in the Grand Total bookmark.
You may include column totals from tables. So if a table is totalled using TOT_Price_C and you wish to have a Grand Total of all the TOT_Prices you should enter a bookmark name of GRTOT_TOT_Price_C. If this seems a little clumsy, it does at least have the benefit of being completely consistent.
GRTOT_.... bookmarks should not be placed within any subsection bookmarks.
Field Aliases
If you use an SQL statement:
SELECT * FROM Order_Lines Left Join Customers....
it might return fields like OL_Cust_Ref, OL_Order_Date, OL_Value, Name...
An SQL statement like
SELECT IIF(FirstName > "", LastName & ", " & FirstName, LastName) FROM Performers
will return a field name Expr1001.
Artifax Reports works by inserting text into the document where it can find a bookmark name that exactly matches a field name. You may, if you wish, fill your template with bookmarks like Expr1001, but there are several reasons why this may be a bad idea:

· Legibility: It goes without saying that PerformerName is easier to understand than Expr1001.

· Formatting: Although you can format fields with the SQL statement (SELECT Format(TotalCost, "Currency") AS Cost...) Artifax Reports can also do this for you. If a field name ends in _C then it will be formatted as currency when output. There are many other formatting codes that you can use.

· Totals: Any numeric field selected in a table query that begins with TOT_ will be totalled, and the sum printed below the last line of the table.

· Uniqueness: You may have many queries within your report returning fields called Expr1001, but you may only have one bookmark in a template with that name. For the report to work, you will need to give the fields different names.
To make the SQL statement return field names different to the defaults you use aliasing.
e.g.
The query
SELECT

OL_Cust_Ref AS CustomerReference,

OL_Order_Date AS OrderDate_L,

OL_Value AS TOT_Value_C,

Name AS CustomerName

FROM Order_Lines Left Join Customers....


will return field names CustomerReference, OrderDate_L, TOT_Value_C, CustomerName instead of OL_Cust_Ref, OL_Order_Date, OL_Value, Name.
The query
SELECT IIF(FirstName > "", LastName & ", " & FirstName, LastName) AS PerformerName

FROM Performers


will return a field called PerformerName instead of Expr1001.
See Also: Templates

Formatting Codes


Generally SQL will return a field value in as simple format as possible. If a value of 200 is stored in the database, then "200" is returned, equally 200.5 will return "200.5" and 200.55 returns "200.55". This seems fine at first glance, but if they are all currency sums we do not really want to see: 200

200.5

200.55




we want to see:



$200.00

$200.50

$200.55




This is achieved by formatting the output as currency. This formatting may be achieved directly in SQL - SELECT Format(200.55, "Currency") AS Total - but we have made it easier for you by allowing you to add a character to the end of the field name to ensure that the result is appropriately formatted.


So if the field name ends with "_C" (e.g. Total_C) it will be formatted as a currency. The list of options, with examples, is:


Character

Meaning

Sample

_C

Currency

$100.00

_F

Fixed

100.00

_P

PerCent

50%

_0

No decimal places

100 (even if true value is 100.1234)

_1

Exactly one decimal place

100.0

_2

Exactly two decimal places

100.00, same as fixed

_3

Exactly three decimal places

100.175

_S

Short Date

12/12/1999

_M

Medium Date

12 Dec 1999

_L

Long Date

12 December 1999

_T

Medium Time

01:25 PM

_X

Short Time

13:25

_Y

Yes/No

Yes

Alias the field names in the SQL statement to add these characters.


See also Field Formatting.
Field Formatting and Attributes
Artifax Reports contains facilities for formatting the output text strings after they have been selected in the SQL and before they have been inserted into the document.

Whilst you can easily make the text surrounding a bookmark bold or italic or whatever within the template, you may need to make the formatting dependent on the output of the field: red for negative numbers in a currency column, or bold for performances and not bold for other types of event. Other facilities include skipping the field entirely (In a table, every field is usually printed to the table. Even if you formatted the field as HIDDEN, or the field was a zero length string, the table column would be left blank. This is not always desirable), and blanking a field depending on the previous value of the field (so that the one side of a many to one relationship only gets printed out once, instead of once for each of the many values).


To add a formatting attribute go to the Field Formatting tab of the Properties page.

· Click on Add Expression.

· Click on the drop down box opposite Field. The drop down list of fields is there to help you, but do not rely on it too much. It is safer to refer to the field by it's index number. If you have aliased the field in the select statement, then you can refer to the field by it's alias too, but it is hard to guess when JET will parse a field name as field1 rather then mytable.field1

· Choose a Scope for the formatting. This varies depending on what type of section you are dealing with - for a group it can be either text or group; for a table it can be either a row, a column or the cell.

· Now fill in the Attribute box.
Syntax:

Expression|Format {, Expression|Format}
That | is a literal - not the conventional help file meaning.
Part Description

Expression Describes the criteria under which the format will be applied.

Format Indicates the format to apply to the text if expression evaluate to true
The Expression argument has the following syntax and parts:
Syntax:

{operator value} | NOOUTPUT | TOTALS


Part Description

operator optional. valid operators are

=, <, >, <>, <=, >=, LIKE and BETWEEN value1 AND value2



value Any text string or number that makes sense given the operator and field data type. Any occurrence of the string LASTVALUE will be substituted out for the last value of the field being referenced: so to stop a field from being displayed when it is the same as the previous value, use

LASTVALUE|BLANK

NOOUTPUT If Expression is 'NOOUTPUT' then the field will not be printed at all. Table and list sections normally use all of the fields selected by the query. If you need to select a field you do not want displayed, for example an intermediate stage in a calculation or the ORDER BY clause in a SELECT DISTINCT query, then use NOOUTPUT. Artifax Reports will then not leave a blank table cell or extraneous list item. You do not need to include a Format argument if the Expression is 'NOOUTPUT'.

TOTALS If Expression is 'TOTALS' then the Format argument is treated as a string, and is output in the 'totals' row of a table - the final row in which totals generated by the TOT_ Formatting Code are printed



The Format argument may take the following values: (see above for exceptions caused by special Expressions)

BOLD

ITALIC

UNDERSCORE

STRIKE

ALLCAPS


RED

GREY (or GRAY)

WHITE

GREEN


BLUE

CYAN


HIDDEN

FONTNAME {name}

FONTSIZE {size}

BLANK


FontName and FontSize must be followed by {Parameter}, which is the name of the font or the point value of the fontsize.
More than one attribute may be applied to the same value (e.g. BOLD ITALIC FONTNAME{Arial}).
More than one value may be inserted in the list (eg Important|BOLD, Unimportant|Italic FontSize{9})
Examples:

Field:= CustomerName; Scope:= Text; Attributes:= Peter|RED

Whenever the text printed at bookmark CustomerName is "Peter" it goes red.
Field:= CustomerName; Scope:= Group; Attributes:= Peter|BOLD ITALIC

Whenever the text printed at bookmark CustomerName is "Peter" the whole Group goes bold italic.


Field:= CustomerName; Scope:= Text; Attributes:= Peter|FontSize{12}

Whenever the text printed at bookmark CustomerName is "Peter" it goes into 12pt.


Field:= CustomerName; Scope:= Text; Attributes:= Peter|RED, John|BOLD, LIKE Sara|FONTNAME{Squire}, Secret|HIDDEN

Whenever the text printed at bookmark CustomerName is "Peter" it goes red, if "John" it goes bold, if "Sarah" or "Sara" it goes into Squire font and if "Secret" it is hidden. Note that both spellings of Sara and Sarah match, because of the "LIKE" feature. Also note that Secret|WHITE is an alternative way to achieve the same result as Secret|HIDDEN.


Field:= Profit; Scope:= Text; Attributes:= <0|RED

If the value of the text printed at bookmark Profit is less than zero it goes red. Note that there is no <= expression, so it may be necessary to put <.00001. An alternative would be Between -99999 and 0, which is inclusive of zero.


Field:= Profit; Scope:= Text; Attributes:= BETWEEN 0 AND 100|BOLD, >100|BOLDITALIC

If the value of the text printed at bookmark Profit is between 0 and 100 it goes bold, if more than 100 it goes bold italic. Note that the lower number must come first in a Between...And statement; Between 0 and -99 will never produce a result, because the result would have to be both greater than zero and less than -99, an impossibility.


Field:= StartDate; Scope:= Text; Attributes:= BETWEEN 1/1/97 AND 31/12/97|UNDERSCORE

Dates may be entered in any recognizable format, including 1/1/97, 01/01/1997, 1 Jan 1997, Jan 1 1997, 1 January 1997 etc. Two number years are interpreted to lie in the range 1980 to 2079. A nonexistent date, such as 29 Feb 1997, produces an error.


See Also: Formatting Codes

Templates
Each Artifax Report uses an RTF template to format the final output.
The template is linked to the report by bookmarks. In general, for a report section to produce output the template must contain a bookmark with the same name as the section. Each different type of report section produces different types and formats of output, and use the template and bookmarks in different ways.
· Groups: the section of template contained in the group bookmark is repeated once for each row returned by the group query. If there is a bookmark within the group bookmark with the same name as a field selected by the group query, then the value of that field for that row is inserted over the bookmark.

· Tables: the first table following the start of the table bookmark is filled with the data selected by the table query

· Lists: the list bookmark is overwritten by the output of the list section.

· Code and Update: neither of these sections affect the template at all, and therefore do not have any use for bookmarks.


It is possible for a section to produce output without a bookmark in the template: if the section is a subsection of a table or a list the columnindex property is used to determine which column of the table or position in the list the section output is inserted.
There are several important considerations to keep in mind when designing your template:

· If an area of the template is selected by a section (the extent of a group bookmark, or a table), then any subsections can only 'see' that area - not the whole template

for example:

suppose in the report definition MyGroup1 contains MyGroup2, but the template looks like


[Begin MyGroup1] stuff stuff stuff [End MyGroup1]

stuff stuff stuff

[Begin MyGroup2] stuff stuff stuff [End MyGroup2]
MyGroup2 would never be executed, as no bookmark for it can be found within MyGroup1
· Do not allow bookmarks to overlap:
[Begin MyGroup1] stuff stuff stuff [Begin MyGroup2]

stuff stuff stuff

[End MyGroup1] stuff stuff stuff [End MyGroup2]

See Also: Aliases, How To Add a Bookmark to a Word Template, How to enter SQL queries



How to add a bookmark to a Word Template.
Word 6.0/7.0 method:

To enter a bookmark placeholder (i.e. just a position within the document.)


Open the Template in Word

Place the insertion point (Cursor) where you want the field to appear.

Select Edit|Bookmark from the menus

Type the name of the bookmark (the field name or alias to be displayed).

Press Add
To enter a bookmark with extent (i.e. to encompass a Group, the Header or Footer)
Open the Template in Word

Place the insertion point (Cursor) at the beginning of the bookmark.

Drag the insertion point to the end of the bookmark, so that the extent of the bookmark is highlighted.

Select Edit|Bookmark from the menus

Type the name of the bookmark.

Press Add


Word 97 method:

To enter a bookmark placeholder (i.e. just a position within the document.)


Open the Template in Word

Place the insertion point (Cursor) where you want the field to appear.

Select Insert|Bookmark from the menus

Type the name of the bookmark (the field name or alias to be displayed).

Press Add
To enter a bookmark with extent (i.e. to encompass a Group, the Header or Footer)
Open the Template in Word

Place the insertion point (Cursor) at the beginning of the bookmark.

Drag the insertion point to the end of the bookmark, so that the extent of the bookmark is highlighted.

Select Insert|Bookmark from the menus

Type the name of the bookmark.

Press Add


Remember that a bookmark can contain other bookmarks, which is how Groups, the Header and Footer work.
Also, it is not necessary to include bookmarks for all fields. Some fields are included only to act as a link to other SQL statements, others may be required only to "seed" codes. That's fine...just don't put them in the Template.
You can choose whether bookmarks are displayed in the template using the Tools|Options|View menu and tab (in Word 6, 7 or 97), and checking the Bookmarks check-box.
Bookmarks can be formatted for font, size, style etc. just like text, and the formatting will be reflected in the text placed at the bookmark by Artifax Report.
How to enter SQL queries
Each query is typed into the right hand box of Artifax Report or selected from the Palette. Whichever section is highlighted in the left will show which SQL query you are entering in the right. There are no special formatting requirements, key words can be delimited by tabs, spaces, carriage returns or commas in the normal way. Extra spaces, tabs and carriage returns are ignored. Case is ignored, except where defining field name aliases, so:
SELECT * FROM Orders WHERE Order_Number = 5
is exactly the same as:
SELECT

*

FROM



Orders

WHERE


Order_Number = 5
and exactly the same as:
select * from orders where order_number = 5
To enter the SQL for a Group, Table, List or code, right click on the section immediately above it, choose New from the floating menu and select what type of subsection you require.
IMPORTANT NOTES ABOUT MEMO FIELDS:

If your SQL query returns a field of type Memo, and you wish to allow the Memo field to contain more than 255 characters, DO NOT alias the field type. This means that you must not use expressions like:

Trim(myMemo)

myMemo & Chr(13)

"Notes: " & myMemo



because they are reported as field type Text, which is limited to 255 chars and will be followed by garbage in the print out.
In any case, Memo fields are truncated to 64000 characters.

Palette

Load the Palette by pressing the button at the top of the main form.

The Palette shows you details of the database you have selected including table and field names and data.

If you double click on a table or field name it will be inserted into the SQL textbox in the right hand pane.


Glossary

aliases

Alternative names used in SQL statements so that database fields or formulas are returned with a useable name.


Microsoft Word

Requires Word 6.0c or higher


properties

Every section contains a set of properties. To see them, right-click on the section name and select Properties from the bottom of the list which appears.


RTF

Rich Text Format: a common format which can be read by most word processors.


sections

The building blocks of a report. Each Section has an SQL statement, and uses it to add data to the document in different ways.
Yüklə 145,21 Kb.

Dostları ilə paylaş:




Verilənlər bazası müəlliflik hüququ ilə müdafiə olunur ©muhaz.org 2024
rəhbərliyinə müraciət

gir | qeydiyyatdan keç
    Ana səhifə


yükləyin