Reports – Papyrus Database Output

A report example

Papyrus Base “Reports” allow you to extract your data in a well-organized, usable form as a text document.

A database report can be created for a variety of uses: form letters, lists, statistical reports, tables, PDFs, emails, etc. You can also export the data for use in another program.

Reports can be generated for an entire database, for a number of selected data records, or for a single data record.

If you want to select certain data records for a report, run a search to refine your selection or simply click on the records you want with your mouse.

In the entry form you can generate a report for these data records.

A report can also distinguish between areas that are filled with content and might be repeated (like one or multiple rows of a table) and areas that have fixed content (like a heading and a footer).

Papyrus will fill the “inner” data area once for every record in your data set. The size will adjust for the amount of records. The “outer” areas, above and below the “inner” data area, are only generated once for the report.

The example shows an “inner” area with a table containing data fields and an “outer” area with a table title and a footer stating “End of the Report.”

Select the data area and choose “Set Data Area from Block” in the context menu

The areas outside of the data set can also include calculations, for example, for a sum of the contents of a certain field column.

An overview of the calculations available in Papyrus can be found under “Calculation Formulas for Tables and Databases” in the “Manual” menu.

Creating Reports (Report Settings in the “Report” Dialog)

A report can be created in the Papyrus Base “File” menu by selecting “Report…” or by clicking on the / icon in the toolbar.

First, you must select a report template. The report template is mostly a normal Papyrus Word document that is used as a template for form letters and can be changed and edited according to your needs.

Creating a report

The report template works like a form letter, which means you will create a form letter field for each data field in your database. This field will then be filled with the field content from each record in the report.

There are different functions in the “Report” dialog for you to edit reports:

The “Edit” button allows you to open an existing report template as a Papyrus document and edit it.

Add a File” lets you add a report template that already exists as a Papyrus file and assign it to your database. This file will also appear in the dialog.

New Report Template” will create a completely new report with all of the fields in the current database using the format from “_REPORT.PAP” (found in folder “Modules\Templates.fix”).

Remove” will delete a report template from the list for this database (the file itself will not be deleted, it will simply not appear in the list anymore).

Hint: A report template that has been designated a “Form” allows you to take the data from your database and use it as a formula, in which you can later put in other values.

A normal, running-text report will give you the entire database in running text, one report after the other.

There are other types of reports, though, such as ones that provide end sums for certain fields and ones that are formatted in a list. For more information, see the following sub-chapter.

Include query” and “Include sort order” can be selected if you would first like Papyrus to run a query or sort the database before a report is made. The buttons […] after each checkbox can be used to add a new query / sort order that will be linked to the report template.

Selecting the “  Default Report” checkbox will mean that the current report template will be used as a default setting, which is useful if you frequently have to run the exact same report.

In the Papyrus Base Preferences under “Dialogs” you can select the option “Immediately executes the default report,” which will tell Papyrus to run the default report whenever you click on the report icon without needing to open the dialog first.

The default report is also used when you select the “Insert Quick Address” option located in the “Insert” menu. This allows you to quickly insert an address where your cursor is currently placed.

Under “Records” you can set which records you would like to be included in your report.

For a single report, when you open the dialog by clicking on the Entry Form for one record, the option “  Current record only” will be set as the default.
If you open the report dialog from the Table Window you will also have the options to create a report for “  Selected records only,” “  All in current list” (those records that currently appear in the table window), or the “  Complete table.”

Lastly, you can determine how and where the report it will be sent under the “Output” section:

For most reports that contain many records, it makes sense to use a traditional type of report template with a page layout (more about this in the next few paragraphs).

You can choose the option to “Create new report document,” which creates a new Papyrus document that can be saved or opened in a new window. There is also an option to “Print report immediately.”

If you are creating a report for only a few records, you may want to insert the report into another document. The “Copy to clipboard” option can be used if you are planning to insert the report into another non-Papyrus program. Once the report is on the system clipboard, you can simply paste it into another program. What you paste into the other program will only be ASCII text, as the text style information from the report cannot be copied to the clipboard.

The “Insert into document” option is especially useful because it will insert your records, one after another, into a document in Papyrus Word format. The option allows you to insert the report including all text style and paragraph template options that a report contains. The document, into which the report is being inserted, will determine the page layout and any additional text of your choice.

The next drop-down menu allows you to choose which document your report will be inserted into. “Top:” is the Papyrus Word text window that currently appears at the top. In this list, you can select the document, in which your report should appear. If you are ready to insert your report and suddenly realize that you have forgotten to open the correct Papyrus Word document, simply click on the “Open…” button to open it.

The “Send email(s)” option lets you send the report as an email.

The checkbox “ Create Text–Database Links on Records” means that the records will appear as hyperlinks in the report.

If one of these links is double-clicked, the record to which it is linked will automatically be opened in the entry form.

Save report in folder ‘Auto-Reports’” will automatically save the report with an easy-to-find file name in the “Auto-Reports” folder.

The functions of the following checkboxes can be used according to your needs and are especially useful for “alarm reports,” or those reports that are created automatically when an alarm activates.

With the checkbox “ Generate a new page for each record” you can choose whether you would like each record to be followed by a page break, or whether the records should be listed one after another without beginning a new page.

Close form and dialog afterwards” is useful if you simply want to create a report and close the “Report” window after.

Sometimes it also makes sense to create more than one report at a time. The following describes how to quickly and easily create invoices: enter the necessary data in the customer address document in Papyrus Base and transfer this data for the current customer via the report dialog (with an appropriate report template). Then the purchase values from the database can be entered into a Papyrus table, which will then contain the final sum of the invoice.

You can use the date placeholder “Date (always current)” in the report template to enter the current date in the invoice. This date will then be “frozen” in the document, so that the date will not change after the invoice has been written.

Before you actually create the invoice via a report, you might need to create additional reports–perhaps an address label or a payment form that includes the credit cards that can be used for payment at your company.

You can make a report template for each payment method, but before you create the report with your invoice report template and choose the option “Close form and dialog afterwards,” make sure you have created the additional reports for payment and credit card options as well as an address label.

Inserting New Data Fields in a Report Template

In the “Data Field” dialog (“Insert” Database menu) you can create a new database field for a Papyrus Base report template. You can set a new expression for the data field by using Papyrus’s calculation formulas.

The command words “CR” and “TAB” are for formatting your field. The expression “TAB” will create a new tab and “CR” will create a new line with the “carriage return.”

You can insert comments, which are useful for complicated formulas.

Comments can be inserted after your data by setting them off with “//”:

Field_3: Field_2 * 1.19 // Comment: Sales Tax

 

Comments longer than one line must be surrounded by “/*” and “*/”:

Field_3: Field_2 * 1.19

/* Multi-line Comment: Sales Tax Calculation.

This field contains information that will

be given to the IRS. */

 

The list will show the field names of the database window which is currently open and displayed.

Reports With Sum Fields and Reports With Data Sets

The normal running text report template will duplicate the entire report document for each record.

It is, however, also possible to produce reports with field sums, reports in list form, and much more.

For these types of reports, which mostly contain tables of values and which we thus call table reports, only a part of the text (or table) for each record with be duplicated.

It is possible to edit a running text report so that it becomes a table report in which only an area in the middle is repeated:

The data area (the area in each record that will be repeated) can be set by selecting it and choosing “Set Data Area from Block” in the context menu. This context menu entry will appear when the selection contains data fields or when you are editing a database report.

The data area can also be easily deselected with the context menu. If you set a new data area, the old selection will be automatically deselected.

Papyrus asks you, if you want to crate a table report or flowed text report

A table report can also be automatically generated. Once you click on the “New Report Template” button in the report dialog, a smaller dialog will pop up, asking you which type of report, a running text or table report, you would like to create. A table report will contain a cell with the column title followed by the rest of the report fields underneath.

In the part of the report text outside the data area you can use special area functions (which are very similar to those used in Papyrus tables):

  • SUM(Fieldname)–sum of all the reported records. The result will be formatted like the field itself, only with “text” or “text or number” the result will be displayed as a floating point number
  • MIN(<Fieldname>)–smallest value, empty fields will not count.
  • MAX(<Fieldname>)–largest value
  • COUNT(*)–number of reported records
  • NVALID(<Fieldname>) number of valid and not empty fields
  • NINVALID(<Fieldname>)–number of invalid fields (e.g. text instead of a number)
  • NEMPTY(<Fieldname>)–number of empty fields
  • AVG(<Fieldname>)–statistical mean. Empty fields are not included. Equals
    SUM(<Fieldname>)/NVALID(<Fieldname>)
  • DEV(<Fieldname>)–standard deviation. Empty fields are not included.
  • VAR(<Fieldname>)–statistical variance. Empty fields are not included.
  • MSQUARES (<Fieldname>)–Statistical root mean square.

Email Options for Reports

By clicking on the “Email Options” button (appears once you choose “Send Email” under “Output”) you can choose the necessary settings for Papyrus to send form letters via email with the report dialog.

In “SMTP-Server for sent mail” you can enter over which SMTP server from your internet service provider you would like to send the emails. This is the so-called “name” of the computer that sends your emails. This address can be found in the email program you use. Simply copy it from your email program and paste it into Papyrus here.

After that comes your normal data, with which you can be identified by the recipients: the sender’s name (your first and last name), the sender’s email address (your email address), the reply-to email address (most of the time, this is identical with the sender’s email address), as well as information about the company or organization.

At the bottom you can also specify whether and how invalid email addresses will be recorded in a log. This is useful if you would like to use the information to see which email addresses in your customer database are no longer valid.

A SMTP log file can provide useful information for technologically savvy users about why an email cannot be sent.

There are some email providers that need you to send an email request before you can send emails. This is to make sure that you have a valid email account with the provider and that you are not using the server without actually being a customer. If this is the case, simply run an email program (Netscape, Mozilla, Outlook Express, etc.) that checks for emails every few minutes, so that Papyrus is authorized as a provider for the time it takes to send your emails.

Sending Emails From a Database Report

Papyrus supports the most common SMTP authentication mechanisms CRAM-MD5, LOGIN and AUTH PLAIN.

Authentication will only be attempted when you enter a “User name for authentication” in the email options dialog and a password if you choose. If the authentication fails, you will see an error message.

The user name and password can also be defined in the report itself by creating a form data field with:

LET EMAIL_LOGIN_USER=’<User Name>’
LET EMAIL_LOGIN_PASSWORD=’<Password>’

You can add a list of email attachments separated by a comma by using the EMAIL_ATTACHMENTS variable, for example:

LET EMAIL_ATTACHMENTS=’Attachment.rtf, C:\Picture\Attachment2.jpeg’

Papyrus will then create a multi-part MIME email.

Files with the extension *.txt will be sent as 8-bit plain text inline; all other files will be handled as binary files and attached using base64 encoding.
For Windows, the MIME type will be ascertained from the registry, in case no other type has been assigned. On Mac, there are standard types for .txt, .pdf, .gif, .jpg, .jpeg, .png, and .bmp; everything else will be sent as an application/octet-stream.
If no path has been entered, Papyrus will look for the file to attach in the database report folder.

In the case that Papyrus is unable to find the attachment there, an error message will appear and the email will not be sent.

Send to more than one recipient: in addition to the entry fields EMAIL_RECIPIENT, EMAIL_RECIPIENT_NAME, and EMAIL_RECIPIENT_ROLE, there are also the variables EMAIL_TO, EMAIL_CC, and EMAIL_BCC, with which you can enter a recipient list separated by commas, for example:
LET EMAIL_TO=’John Doe <[email protected]>, “Jane Doe” <[email protected]>, [email protected]
(this will be sent to 3 different recipients).

These entries all have the form ‘Name <email address>’ or ‘”Name” <email address>’ (this form is necessary when the name contains a comma).

Send as plain text, HTML only, plain text and HTML, or as a PDF–when you choose “send email(s)” in the report dialog, you can choose from the following formats:

Plain Text: only paragraph ends and all characters in the selected email code page can be used; everything else will be filtered out. This format can be used by all email clients.

HTML and plain text: the email contains two versions of the report, both HTML and plain text. Email clients that can’t display HTML will use the plain text. HTML emails can also contain pictures that will be sent as a GIF (GIF picture), JPEG (JPEG picture) or PNG (all other bitmaps). Vector graphics are not supported.

HTML only: the plain text version will not be included. Email clients that can only work with plain text will show the HTML code.

PDF: A PDF file (Adobe Acrobat format) will be created from the report and sent as an attachment. The name of the PDF file can be assigned using the variable EMAIL_PDF_FILE. The extension *.pdf will appear automatically on the end of the path. In default this will contain the document name of the report.

The email format can also be set in the report itself. This setting will then be used as a default in the drop-down menu of the report dialog.

LET EMAIL_HTML=0 /* no HTML version */
LET EMAIL_HTML=1 /* HTML and plain text */
LET EMAIL_HTML=2 /* HTML only */
LET EMAIL_PDF=0 /* no PDF */
LET EMAIL_PDF=1 /* send as PDF attachment */

With EMAIL_PLAIN_TEXT you set the contents of the plain text for PDF and for “HTML and plain text;” ‘|’ can be used for paragraph breaks. This is how it looks:

LET EMAIL_PLAIN_TEXT= Please see the content of the attached PDF document.

Title–the name of the document (can be changed in “Document Properties” under the “Document” menu) will be used as the subject of the email if you have not set a subject of your own with EMAIL_SUBJECT.

Loggingin the “sent email” log, the used search query will be logged.
In the SMTP log, the name of the report used for the email will be logged.

In the email header, your version of “Papyrus BASE” will be shown.

Last updated by Leon Ramps on October 26, 2017