Report that contains a table generated by"Insert Table" action

ReportWorkshop 5.1 – report template generation

/ Announcement, Report Workshop / By /

We’ve updated ReportWorkshop to version 5.1

ReportWorkshop v5.1 corresponds to TRichView v21.

Trial versions are available here: https://www.trichview.com/download/

This update is free for customers who ordered in 2021 and later. If you do not know how to renew, please contact me (by sending a private forum message, or email to richviewgmailcom).

The complete list of changes:
https://www.trichview.com/help-report/version_history.htm

This article discusses changes that were added since the last minor update.

New features:

  • HTML data field format
  • the action for inserting a report table (TrvrActionInsertTable) can generate a table from a DB table (dataset)
  • new action TrvrActionReportWizard generates a master-detail report template
  • special processing for empty (NULL) field values
  • editing “Essential” property in the row generation rule properties dialog
  • $IFDEF and $IFnDEF commands in reports support cross-tab headers (in addition to data fields and variables)
  • named report table cells

Minor new features

HTML data field

Since this update, Report Workshop supports fields containing HTML.
Normally, HTML data are auto-detected. If not (for example, if HTML data does not contain <html> and <head> tags), users can specify the field type explicitly:

My document: {DATAFIELDNAME html}

HTML field type has the same format string as for other document formats: you can override fonts to make multiple HTML documents look consistent.
To display HTML source code as text:

Source code: {DATAFIELDNAME text}

Empty (NULL) field values, Defined() function

Previous versions of Report Workshop converted NULL values of numeric fields to 0.
The new version does not do it.
A code for inserting a data field value in a report field inserts nothing for NULL fields.

Let we have the following SQL statement:

select * from MyTable where MyField={ValueThatCanBeNULL}

For NULL fields, it now produces incorrect statement:

select * from MyTable where MyField=

To fix this problem, this statement can be changed to:

select * from MyTable where 
  MyField={=If(Defined(ValueThatCanBeNULL),ValueThatCanBeNULL,0)}

This code for NULL field value produces:

select * from MyTable where 
  MyField=0

Another option, to check for NULL fields in SQL statement:

select * from MyTable where 
  {'=If(Defined(ValueThatCanBeNULL),"MyField="+ValueThatCanBeNULL,"MyField is NULL")'}

This code for NULL field value produces:

select * from MyTable where 
  MyField is NULL

In the examples above (click “+” to expand), you can see the new expression function Defined(). It returns True for non-empty values.

The rules of processing of NULL values in expressions are here: https://www.trichview.com/help-report/expressions.htm#null

Essential rules of table row generation

In the rule properties dialog, you can see a new checkbox “Delete the whole table if no results”.
It allows setting Essential property.

Dialog of rules for report table row generation: "essential" checkbox

$IFDEF and $IFnDEF commands

$IFDEF and $IFnDEF commands can be used not only for data fields and variables, but for cross-tab headers as well.
For example:

{$IFDEF #Caption}
{#Caption}
{$ELSE}
Untitled
{$ENDIF}

Note: {$IF Defined(#Caption)} works as well.

Named cells of report tables

New property of cells of report tables: Name.
It allows referring cells in data fields by names, just like it was implemented for table row generation rules: {MyCellName:MyFieldName}.

Report cell dialog: cell name

Report table insertion

Introduction

"Insert Report Table" icon
TrvrActionInsertTable

In the previous versions of Report Workshop, this action was a copy of a normal table insertion action, with exactly the same user interface.
This action inserts a report table instead of a normal table.
This action was redundant, because other Report Workshop actions can convert normal tables to report tables.

In this update, this action has got new user interface. It can generate a report table from data.
The resulting table has up to 3 rows:

  1. optional overall header row
  2. optional header row containing field names
  3. data row

To activate this feature, DataProvider property of the action must be assigned (and this data provider must return at least one table). Otherwise, the standard dialog for blank table insertion is displayed.
To disable this feature, either unassign DataProvider, or assign UseDataTables=False.

Formatting

If the target editor does not use named styles (UseStyleTemplates=False), all text in the resulting table uses the same font.
If the target editor uses named styles (UseStyleTemplates=True), heading rows are formatted according to HeadingTextStyleTemplateName and HeadingParaStyleTemplateName properties.
By default, HeadingParaStyleTemplateName is blank (so “Normal” style is used), HeadingParaStyleTemplateName = “Strong”.

Example

"Insert Report Table" dialog
Dialog for report table insertion
Report template that contains a table generated by "Insert Table" action
Inserted table
Report that contains a table generated by "Insert Table" action
Report result

Report Wizard

Introduction

Normally, report designing in ReportWorshop starts from a document: the user creates a rich text document, then links data to the whole document or its parts.
While this approach is very flexible, it may be hard to use.

In this update, we added a new action:

Report Wizard ("New Report") icon
TrvrActionReportWizard

This action can generate a new report template from data.
Currently, the following data providers support this action:

  • data providers that use SQL data queries
  • data providers with predefined master/detail relationship.

Most data providers included in Report Workshop are SQL-based, so this action can be used with them.
A predefined master/detail relationship can be defined in universal DB data provider: TRVReportDBDataProvider on datasets that have MasterSource:TDataSource property.

This action can generate a simple report, or a master/detail report. The number of levels of details is not limited, so complex reports can be generated. However, there is a limitation: one detail for each master. This limitation comes from the step-by-step design of a wizard dialog. Let me know if you are interested in removing this limitation.

The action generates SQL queries for master/detail reports automatically.

Data queries for this actions are generated by data provider components.
If datasets or field names have spaces, they must be quoted for using in SQL statement.
All data providers use double quotes to make SQL identifiers. The exceptions are data providers for MySQL: TRVReportMyDataProvider (for DevArt MySQL DAC) and TRVReportMySQLDataProvider (for MicroOLAP DAC for MySQL); they use backtick characters for identifiers.
There may be a problem with data providers that work with multiple DB engines, if the chosen engine does not support double quotes for SQL identifiers.
You can assign the following properties of data provider:

  • SQLIdentifierOpenQuote (double quote by default)
  • SQLIdentifierCloseQuote (double quote by default)
  • SQLStringQuote (single quote by default)

Note: if table and fields names do not include space characters, data providers do not use quotes.

Wizard dialog

On each level of data, the user chooses:

  1. source of data (a dataset) and how it is linked to the previous levels (correspondence between fields)*
  2. how this data is displayed in the document
  3. which fields of this data source to display in reports

*if master/detail relationship is predefined, the wizard does not ask how details are linked.

Data can be placed in reports as:

  • text (multiple paragraphs)
  • table (each field in a separate column)
  • one-column table (all fields in the same cell)
  • cards
  • paragraph (in a comma-delimited list)
Report wizard: top-level report type
Report Wizard: top-level report types

If you specify “Cards” with one card per row, the result will be the same as for one-column table. The difference will be on the next step. For cards, details are displayed inside a card. For tables, details are added outside of the previous level (at the right side or below)

If a master data is a table, details can be attached in several ways:

  • as new columns to the right
  • inside a new cell to the right (using the options listed above)
  • inside a new cell below (using the options listed above)
Report wizard: report type for details in a table
Report Wizard: report types for detail data when master data are in a table (grid)

The report wizard stops when

  • the user clicks “Finish” button, or
  • the user chooses “No details” for the next data source, or
  • the last step’s placement does not allow details (for example, a list in a single paragraph), or
  • all tables have been used

The user can define the report header, footer, and the outermost table header.

As a result, the wizard generates a new report template. It is ready-to-use, but of course you can edit it as you want.

Formatting

If the target editor does not use named styles (UseStyleTemplates=False), all text in the resulting report template uses the same font.
If the target editor uses named styles (UseStyleTemplattes=True), the resulting report template is formatted according to the StyleTemplates, see AccentedTextStyleTemplateName, ReportFooterStyleTemplateName for details.

By default, the action resets style templates to default values using the linked TrvActionNew action. If you want to generate a report template using the current style templates, assign UseCurrentStyleTemplates=True.

Changes in demo projects

All “ReportEditor” and “ScaleRichView ReportEditor” demos were changed:

  • a menu and a toobutton for “New Report” are added;
  • DataProvider is assigned in code to rvrActionInsertTable1 and rvrActionReportWizard.

“Northwind” sample database is rebuilt for the newest version of NexusDB.
“Crosstab Employees x Employees.rw” sample report for NexusDB is updated: SQL statement in the heading cross-tab cell was updated to takes NULL values of #ReportsTo into account.

Localization

User interface for new features is translated to English and Russian.
In other localizations, they are displayed in English.

I’ll appreciate if Report Workshop translators update their translations.

Demo videos

Video I

This video shows:
1. All types of single-level reports
2. 3-level master-detail report

Video II

This video shows how to generate 4-level report with the following levels:

  • Employees
  • EmployeesTerritories
  • Territories
  • Regions

The second level (EmployeesTerritories) is technical, it contains only links between employees and their territories (so, at the end, we remove data displayed for this level)

Video III

This video shows the report generated for “Sakila” (a sample film rental database for MySQL).
The report basically shows “Category – Film – Actor” report, but it has 5 levels (because “Category – Film” and “Film – Actor” are many-to-many relationships, so intermediate tables film_category and film_actor are used.

Share this article