Technical Bulletins
The
following documents are saved in
Adobe® Acrobat® format, which can be viewed using the
Adobe Acrobat Viewer available as a download from the Adobe Web site. Use your
Browser's Find function to search this page for keywords.
ARPEGGIO Data Access FAQs
This technical bulletin provides answers to frequently asked questions about the Data Access software included with ARPEGGIO for performing queries against ODBC and DRDA compliant databases.
ARPEGGIO Viewer Distribution
Included with the ARPEGGIO For The Desktop and ARPEGGIO For The Developer products is the ARPEGGIO Viewer. Developers who create ARPEGGIO reports or report applications can distribute the ARPEGGIO Viewer to their end-users free-of-charge.
The ARPEGGIO Viewer can be installed from your ARPEGGIO CD by using a special product key. This is the easiest way to give your users the ARPEGGIO Viewer and should be used when your users are all “local,” your users have access to the network, or when you are distributing your own application on CD. You can also create your own ARPEGGIO Viewer installation program, or include such functionality in an existing installation program for your application.
Assorted Tips and Tricks
This technical bulletin includes a variety of information that may be helpful when using R&R Report Writer including working with alphabetizing titles, currency formatting, memo date arithmetic, Gannt charts, formatting and statistics. Many of these tips involve the use of User Defined Functions (UDFs.)
Also available is a sample User Defined Function library (UDF file) with the UDF examples for you to use in your applications. If you are using R&R Xbase Edition, copy RR.UDF to your R&R program directory, If you are using R&R SQL Edition of ARPEGGIO, copy RSW.UDF to your R&R or ARPEGGIO program directory.
AS/400 Remote Command Execution
The ARPEGGIO Data Access component provides the ability to execute commands on the AS/400 from your PC® without the use of a terminal emulation product. There are two methods of execution; via an SQL statement that follows the ODBC SQLExecDirect call as defined in this document or through the Remote Command dialog box in ARPEGGIO Data Access. This technical bulletin details how to execute commands remotely.
Checking Database Integrity
Here is a tip every database user will find handy for checking the integrity of a database. Bad data seems to creep into databases despite attempts to validate data during data entry. We’ll discuss the sources of bad data and suggest a reporting technique you can use to expose bad data. The resulting reports can help you find and correct database integrity problems.
Column and Line Shading
R&R Report Writer allows you to highlight selected data in a report by using attributes such as bold, italic, and underscore. In R&R for DOS, starting in Version 4, R&R Report Writer also allows users with laser printers to highlight selected areas of your report with shading. The tips in this bulletin illustrate two types of shading: column shading and line shading.
Conditional Counts
We received many calls from R&R Report Writer users asking if there is a way to count records that contain a particular value in one of the fields, and we subsequently added a feature to allow conditional totals. However, if you are using a version of R&R before this feature was added, this bulletin will teach you how to accomplish the same result. For example, you may want to know how many orders are for product X and how many are for product Y.
Conditional Field Placement
Many accounting applications store a negative number in a database field to indicate a credit and a positive number in the same field to indicate a debit (or vice versa). Reports must then print credits and debits in different columns as in this example.
Creating a Balance-Forward Report
This technical bulletin provides an example for creating a balance forward report. The example report shows a sample balance forward statement.
Creating Bar Charts
The REPLICATE function offers a handy tool for creating bar charts. You can quickly adapt the Basic Columnar report to produce the following bar chart with just one calculated field and a little rearranging.
Creating Group Fields
Let's say you need to group records by categories, but the category names are not stored in your database. This tech bulletin shows you an easy way to assign categories on the fly to give you the ultimate flexibility in reporting. Also, you will learn how to order the categories any way you like, rather than being limited to alphabetical order.
Creating a Multi-Column Report
Many users have asked our support staff whether R&R Report Writer can format a multi-column directory. An example of this format is the telephone book. Names are printed in alphabetical order in four columns per page. Unlike a report that prints names in a single column per page, the directory format lists names down the first column, and then down the second column, etc.
Creating User-Defined Functions
User-Defined Functions (UDFs) allow you to define general purpose calculations that can be used in any report. This technical bulletin provides exercises to help you become comfortable creating and using UDFs so you can begin to build your own UDF library.
Also available is a sample UDF file with the UDF examples for you to use in your applications. If you are using R&R Xbase Edition, copy RR.UDF to your R&R program directory, If you are using R&R SQL Edition of ARPEGGIO, copy RSW.UDF to your R&R or ARPEGGIO program directory.
Cross-Tabulating Totals
R&R Report Writer enables you to select a group field and create totals for each value in that field. Cross-tabulating totals extends this capability by allowing you to create totals for two fields at the same group level. Since the resulting report resembles a table with totals tabulated down columns and across rows, it is referred to as a crosstab report.
Disabling Journaling on the AS/400
With version 2, release 3 of the OS/400 operating system, IBM® made available 'No Commit' isolation. If you use this version or later as the operating system on your AS/400, you can disable journaling for specific user IDs. Disabling journaling saves space on your AS/400 and improves the response time of SQL activity on tables. In making the decision to disable journaling for a user ID, you must carefully weigh the importance of improved performance against the ability to provide recoverability.
Dot Leader Tabs
Dot leader tabs are useful when you are creating directories and indexes. Dot leader tabs are also used in a table of contents. A variable number of dots is automatically inserted between each topic and the corresponding page number.
Duplex Printer Tips
When you are printing single-sided reports, it’s easy to insert a new page line wherever you want, for example after each group of records. However, when printing duplex, or double-sided, you may want to begin a new sheet of paper – not just a new page (i.e. side) after each group. This can be accomplished using the Logical Field property for Band Lines.
Exporting to the ActiveX Viewer
The Viewer Control is an export to a Web site. This export can be viewed by your Web browser and supports images, OLE objects, lines, boxes, fonts, colors, shading, and charting. Reports created in R&R Report Designer are exported to a Web browser with the same formatting.
Extended Underlining
R&R Report Writer normally underlines a field only as far as the data extends. However, there are some applications in which you may want to underline the entire field width, even when the field is blank or partially filled. The results give your report the look and feel of a preprinted form as in Figure 1. This technical bulletin describes this process.
Focus on the IIF Function
This technical bulletin discusses the immediate if (IIF) function. The IIF function plays an important role in many report applications. First we will review the IIF syntax, and then construct some useful expressions with IIF.
Focus on the TRANSFORM Function
The TRANSFORM function provides a powerful tool for formatting character and numeric data. It works by converting a character string or numeric value into a format you specify. Common applications include phone numbers, zip codes, and social security numbers. For example, you can use TRANSFORM to convert a phone number stored as 8143403 into (425) 814-3303.
Font Information Files
This technical bulletin documents the procedure for using downloadable (soft) fonts in an R&R Report Writer for DOS report.
Group and Page Totals
In reports that contain group and page totals but no body lines, page totals may include a group value that prints on the following page. You can force page totals that are sums of group totals to take account only of groups on a given page. This technical bulletin describes how to do this.
Group on Number of Records
For some applications, you may want to insert a blank line periodically to make reading the report easier. This technical bulletin describes how to group on a certain number of records and insert a blank line after that grouping.
LaserJet Envelopes
Following the instructions given, you can use R&R Report Writer to print envelopes on any HP® LaserJet printer that will accept either an HP envelope tray or envelope feeder. If you don't have either of these printer accessories, you can feed single envelopes into the printer manually.
Line and Box Drawing
This technical bulletin provides help designing forms with lines and boxes. The main problem is getting the lines to continue all the way to the bottom of the page when there aren't enough records to fill the page.
Multi-Scans in SQL
R&R Report Writer allows you to create reports in which there are essentially multiple detail areas. While the “multi-scan” feature is not explicitly available in ARPEGGIO, the end result can be achieved using the technique explained in this document.
Personalized Form Letter Greetings
If you have ever received a personalized form letter from us, you have seen an example of a personalized greeting. You don't necessarily know you're reading a form letter because it is camouflaged with a personalized greeting such as Dear Mr. Smith.
What should be a simple insertion of the title (Mr. or Ms.) followed by the last name is complicated by the fact that some records in your database may not provide the full names. Some people only give their first initials, others give just their last names, while some don't give any name at all. In order to be as personal as possible, we developed a calculated field expression to handle all possible cases.Printing Page Number X of Y
This technical bulletin describes how you can generate Runtime reports that print each page number out of the total number of pages. For example, if you are generating a 20-page report, the report can show “Page 1 of 20,” “Page 2 of 20,” and so on, instead of simply each page number.
Printing Related Records
We have had several inquiries lately from R&R Report Writer users who wanted to print field values from multiple records in a scanned file on the same line of a report. Since these users did not always know how many records in the scanned database would match a record in the controlling database, they could not use R&R Report Writer's /Print Options/Record-Layout "Records across" option to solve their problem.
Producing Labels Using If/Then Logic
R&R Report Writer allows you to easily produce a mailing labels report. The Getting Started booklet includes an example of a typical mailing labels report. However, you might need to produce a more complex mailing labels report that makes use of the If/Then/Else logic in R&R Report Writer. For example, you might want to mail information to the highest-ranking officer at each company in your customer database. This technical bulletins describes how to do this.
Recursive Functions
This technical bulletin explains a powerful technique called recursive functions. In computer terminology, recursion occurs when a function calls itself. You may want to think of recursion as a form of looping, which occurs when a program performs the same thing repeatedly while a given condition is true.
Rounding Numbers
If you create reports that include non-integer values (that is, numbers with decimal places), you probably understand about rounding. But do you understand the difference between using the /Field Format command and the ROUND() function to specify the number of decimal places you want? Do you know when you should use the ROUND() function? Do you know how the ROUND() function works? For those of you who want your reports to be numerically accurate, we offer the following explanation of R&R Report Writer's two methods of rounding numbers: rounding values and rounding display formats.
Runtime Prompts
We’re received many calls from R&R users asking how to make the runtime executable be able to prompt the user for data to be used in a query. The most popular prompt request is the START and END dates to return a subset of data from a database.
TeleMagic Support
The TeleMagic application uses non-printable characters in indices, which causes R&R not to find all records in related tables that use such indices. This is because R&R's default behavior is not to allow non-printable characters in linking fields. However, R&R can be configured to enable linking on fields that contain non-printable characters.
Time Arithmetic
Time arithmetic is useful in applications that need to measure the duration of events. Time arithmetic is used in this example to analyze a support call history. The results help in planning and budgeting support services to maintain a 100% call service rate.
Using the PREVIOUS Function
This technical bulletin presents an example of using the PREVIOUS() function to suppress repeating data in fields.
Using Totals for Data Analysis
The SUM, COUNT, and AVERAGE commands can be used in R&R Report Writer reports for a variety of data analysis techniques. These include % of total calculations, subtotal averaging, queries on totals, and sorting and grouping on totals.
Using the TRANSFORM Function
The TRANSFORM function enables you to format character or numeric data by using one of several predefined formats or by supplying a picture or template to represent the desired format.
ARPEGGIO Data Access FAQs
This technical bulletin provides answers to frequently asked questions about the Data Access software included with ARPEGGIO for performing queries against ODBC and DRDA compliant databases.
ARPEGGIO Viewer Distribution
Included with the ARPEGGIO For The Desktop and ARPEGGIO For The Developer products is the ARPEGGIO Viewer. Developers who create ARPEGGIO reports or report applications can distribute the ARPEGGIO Viewer to their end-users free-of-charge.
The ARPEGGIO Viewer can be installed from your ARPEGGIO CD by using a special product key. This is the easiest way to give your users the ARPEGGIO Viewer and should be used when your users are all “local,” your users have access to the network, or when you are distributing your own application on CD. You can also create your own ARPEGGIO Viewer installation program, or include such functionality in an existing installation program for your application.
Assorted Tips and Tricks
This technical bulletin includes a variety of information that may be helpful when using R&R Report Writer including working with alphabetizing titles, currency formatting, memo date arithmetic, Gannt charts, formatting and statistics. Many of these tips involve the use of User Defined Functions (UDFs.)
Also available is a sample User Defined Function library (UDF file) with the UDF examples for you to use in your applications. If you are using R&R Xbase Edition, copy RR.UDF to your R&R program directory, If you are using R&R SQL Edition of ARPEGGIO, copy RSW.UDF to your R&R or ARPEGGIO program directory.
AS/400 Remote Command Execution
The ARPEGGIO Data Access component provides the ability to execute commands on the AS/400 from your PC® without the use of a terminal emulation product. There are two methods of execution; via an SQL statement that follows the ODBC SQLExecDirect call as defined in this document or through the Remote Command dialog box in ARPEGGIO Data Access. This technical bulletin details how to execute commands remotely.
Checking Database Integrity
Here is a tip every database user will find handy for checking the integrity of a database. Bad data seems to creep into databases despite attempts to validate data during data entry. We’ll discuss the sources of bad data and suggest a reporting technique you can use to expose bad data. The resulting reports can help you find and correct database integrity problems.
Column and Line Shading
R&R Report Writer allows you to highlight selected data in a report by using attributes such as bold, italic, and underscore. In R&R for DOS, starting in Version 4, R&R Report Writer also allows users with laser printers to highlight selected areas of your report with shading. The tips in this bulletin illustrate two types of shading: column shading and line shading.
Conditional Counts
We received many calls from R&R Report Writer users asking if there is a way to count records that contain a particular value in one of the fields, and we subsequently added a feature to allow conditional totals. However, if you are using a version of R&R before this feature was added, this bulletin will teach you how to accomplish the same result. For example, you may want to know how many orders are for product X and how many are for product Y.
Conditional Field Placement
Many accounting applications store a negative number in a database field to indicate a credit and a positive number in the same field to indicate a debit (or vice versa). Reports must then print credits and debits in different columns as in this example.
Creating a Balance-Forward Report
This technical bulletin provides an example for creating a balance forward report. The example report shows a sample balance forward statement.
Creating Bar Charts
The REPLICATE function offers a handy tool for creating bar charts. You can quickly adapt the Basic Columnar report to produce the following bar chart with just one calculated field and a little rearranging.
Creating Group Fields
Let's say you need to group records by categories, but the category names are not stored in your database. This tech bulletin shows you an easy way to assign categories on the fly to give you the ultimate flexibility in reporting. Also, you will learn how to order the categories any way you like, rather than being limited to alphabetical order.
Creating a Multi-Column Report
Many users have asked our support staff whether R&R Report Writer can format a multi-column directory. An example of this format is the telephone book. Names are printed in alphabetical order in four columns per page. Unlike a report that prints names in a single column per page, the directory format lists names down the first column, and then down the second column, etc.
Creating User-Defined Functions
User-Defined Functions (UDFs) allow you to define general purpose calculations that can be used in any report. This technical bulletin provides exercises to help you become comfortable creating and using UDFs so you can begin to build your own UDF library.
Also available is a sample UDF file with the UDF examples for you to use in your applications. If you are using R&R Xbase Edition, copy RR.UDF to your R&R program directory, If you are using R&R SQL Edition of ARPEGGIO, copy RSW.UDF to your R&R or ARPEGGIO program directory.
Cross-Tabulating Totals
R&R Report Writer enables you to select a group field and create totals for each value in that field. Cross-tabulating totals extends this capability by allowing you to create totals for two fields at the same group level. Since the resulting report resembles a table with totals tabulated down columns and across rows, it is referred to as a crosstab report.
Disabling Journaling on the AS/400
With version 2, release 3 of the OS/400 operating system, IBM® made available 'No Commit' isolation. If you use this version or later as the operating system on your AS/400, you can disable journaling for specific user IDs. Disabling journaling saves space on your AS/400 and improves the response time of SQL activity on tables. In making the decision to disable journaling for a user ID, you must carefully weigh the importance of improved performance against the ability to provide recoverability.
Dot Leader Tabs
Dot leader tabs are useful when you are creating directories and indexes. Dot leader tabs are also used in a table of contents. A variable number of dots is automatically inserted between each topic and the corresponding page number.
Duplex Printer Tips
When you are printing single-sided reports, it’s easy to insert a new page line wherever you want, for example after each group of records. However, when printing duplex, or double-sided, you may want to begin a new sheet of paper – not just a new page (i.e. side) after each group. This can be accomplished using the Logical Field property for Band Lines.
Exporting to the ActiveX Viewer
The Viewer Control is an export to a Web site. This export can be viewed by your Web browser and supports images, OLE objects, lines, boxes, fonts, colors, shading, and charting. Reports created in R&R Report Designer are exported to a Web browser with the same formatting.
Extended Underlining
R&R Report Writer normally underlines a field only as far as the data extends. However, there are some applications in which you may want to underline the entire field width, even when the field is blank or partially filled. The results give your report the look and feel of a preprinted form as in Figure 1. This technical bulletin describes this process.
Focus on the IIF Function
This technical bulletin discusses the immediate if (IIF) function. The IIF function plays an important role in many report applications. First we will review the IIF syntax, and then construct some useful expressions with IIF.
Focus on the TRANSFORM Function
The TRANSFORM function provides a powerful tool for formatting character and numeric data. It works by converting a character string or numeric value into a format you specify. Common applications include phone numbers, zip codes, and social security numbers. For example, you can use TRANSFORM to convert a phone number stored as 8143403 into (425) 814-3303.
Font Information Files
This technical bulletin documents the procedure for using downloadable (soft) fonts in an R&R Report Writer for DOS report.
Group and Page Totals
In reports that contain group and page totals but no body lines, page totals may include a group value that prints on the following page. You can force page totals that are sums of group totals to take account only of groups on a given page. This technical bulletin describes how to do this.
Group on Number of Records
For some applications, you may want to insert a blank line periodically to make reading the report easier. This technical bulletin describes how to group on a certain number of records and insert a blank line after that grouping.
LaserJet Envelopes
Following the instructions given, you can use R&R Report Writer to print envelopes on any HP® LaserJet printer that will accept either an HP envelope tray or envelope feeder. If you don't have either of these printer accessories, you can feed single envelopes into the printer manually.
Line and Box Drawing
This technical bulletin provides help designing forms with lines and boxes. The main problem is getting the lines to continue all the way to the bottom of the page when there aren't enough records to fill the page.
Multi-Scans in SQL
R&R Report Writer allows you to create reports in which there are essentially multiple detail areas. While the “multi-scan” feature is not explicitly available in ARPEGGIO, the end result can be achieved using the technique explained in this document.
Personalized Form Letter Greetings
If you have ever received a personalized form letter from us, you have seen an example of a personalized greeting. You don't necessarily know you're reading a form letter because it is camouflaged with a personalized greeting such as Dear Mr. Smith.
What should be a simple insertion of the title (Mr. or Ms.) followed by the last name is complicated by the fact that some records in your database may not provide the full names. Some people only give their first initials, others give just their last names, while some don't give any name at all. In order to be as personal as possible, we developed a calculated field expression to handle all possible cases.Printing Page Number X of Y
This technical bulletin describes how you can generate Runtime reports that print each page number out of the total number of pages. For example, if you are generating a 20-page report, the report can show “Page 1 of 20,” “Page 2 of 20,” and so on, instead of simply each page number.
Printing Related Records
We have had several inquiries lately from R&R Report Writer users who wanted to print field values from multiple records in a scanned file on the same line of a report. Since these users did not always know how many records in the scanned database would match a record in the controlling database, they could not use R&R Report Writer's /Print Options/Record-Layout "Records across" option to solve their problem.
Producing Labels Using If/Then Logic
R&R Report Writer allows you to easily produce a mailing labels report. The Getting Started booklet includes an example of a typical mailing labels report. However, you might need to produce a more complex mailing labels report that makes use of the If/Then/Else logic in R&R Report Writer. For example, you might want to mail information to the highest-ranking officer at each company in your customer database. This technical bulletins describes how to do this.
Recursive Functions
This technical bulletin explains a powerful technique called recursive functions. In computer terminology, recursion occurs when a function calls itself. You may want to think of recursion as a form of looping, which occurs when a program performs the same thing repeatedly while a given condition is true.
Rounding Numbers
If you create reports that include non-integer values (that is, numbers with decimal places), you probably understand about rounding. But do you understand the difference between using the /Field Format command and the ROUND() function to specify the number of decimal places you want? Do you know when you should use the ROUND() function? Do you know how the ROUND() function works? For those of you who want your reports to be numerically accurate, we offer the following explanation of R&R Report Writer's two methods of rounding numbers: rounding values and rounding display formats.
Runtime Prompts
We’re received many calls from R&R users asking how to make the runtime executable be able to prompt the user for data to be used in a query. The most popular prompt request is the START and END dates to return a subset of data from a database.
TeleMagic Support
The TeleMagic application uses non-printable characters in indices, which causes R&R not to find all records in related tables that use such indices. This is because R&R's default behavior is not to allow non-printable characters in linking fields. However, R&R can be configured to enable linking on fields that contain non-printable characters.
Time Arithmetic
Time arithmetic is useful in applications that need to measure the duration of events. Time arithmetic is used in this example to analyze a support call history. The results help in planning and budgeting support services to maintain a 100% call service rate.
Using the PREVIOUS Function
This technical bulletin presents an example of using the PREVIOUS() function to suppress repeating data in fields.
Using Totals for Data Analysis
The SUM, COUNT, and AVERAGE commands can be used in R&R Report Writer reports for a variety of data analysis techniques. These include % of total calculations, subtotal averaging, queries on totals, and sorting and grouping on totals.
Using the TRANSFORM Function
The TRANSFORM function enables you to format character or numeric data by using one of several predefined formats or by supplying a picture or template to represent the desired format.