Report Workshop 5.1 - report template generation

Post by **Sergey Tkachenko** » Sat Feb 18, 2023 3:53 pm

ReportWorkshop 5.1.

ReportWorkshop v5.1 corresponds to TRichView v21.0

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/v ... istory.htm

This topic 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

Previous version:
viewtopic.php?t=10978

Post by **Sergey Tkachenko** » Sat Feb 18, 2023 3:54 pm

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:

Code: Select all

My document: {DATAFIELDNAME html}

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

Code: Select all

Source code: {DATAFIELDNAME text}

Empty (NULL) field values

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.

[+] It may be a problem if the field is used in a SQL statement

Let we have the following SQL statement:

Code: Select all

select * from MyTable where MyField={ValueThatCanBeNULL}

For NULL fields, it now produces incorrect statement

Code: Select all

select * from MyTable where MyField=

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

Code: Select all

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

This code for NULL field value produces:

Code: Select all

select * from MyTable where 
  MyField=0

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

Code: Select all

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

This code for NULL field value produces:

Code: Select all

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/e ... s.htm#null

Essential table row generation rules

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

: Essential rule.png (24.39 KiB) Viewed 47375 times

$IFDEF and $IFnDEF

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

Code: Select all

{$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}.

: Cell Name.png (15.24 KiB) Viewed 47375 times

Post by **Sergey Tkachenko** » Sat Feb 18, 2023 3:58 pm

Report table insertion

Introduction

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:

optional overall header row
optional header row containing field names
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

Dialog for report table insertion:

: Insert-Report-Table-Dialog.png (11.68 KiB) Viewed 47682 times

Inserted table:

: Table-in-Report-Template.png (51.93 KiB) Viewed 47682 times

Report result:

: Table-in-Report-Result.png (63.1 KiB) Viewed 47682 times

Post by **Sergey Tkachenko** » Sat Feb 18, 2023 4:02 pm

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:

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.

[+] About spaces in table and field names

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:

source of data (a dataset) and how it is linked to the previous levels (correspondence between fields)*
how this data is displayed in the document
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)

: Top-Level-Report-Types.png (11.62 KiB) Viewed 47682 times

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 the 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)

: How-To-Place-Details-in-Table.png (13.9 KiB) Viewed 47682 times

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.

Post by **Sergey Tkachenko** » Sat Feb 18, 2023 4:04 pm

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.

Post by **Sergey Tkachenko** » Sat Feb 18, 2023 4:04 pm

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.

Post by **Sergey Tkachenko** » Sat Feb 18, 2023 4:05 pm

Video: Report Wizard demo Part I

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

Video: Report Wizard demo Part 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: Report Wizard demo Part 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).

Post by **Sergey Tkachenko** » Fri Mar 10, 2023 12:48 pm

Report Workshop 5.2

We've updated ReportWorkshop to version 5.2.

New feature: an option that allows using the same dataset on different master/detail levels in the report wizard: TrvrActionReportWizard.AllowSelfReferences.

TRichView.com

Report Workshop 5.1 - report template generation

Report Workshop 5.1 - report template generation

HTML, NULL fields, essential rules, $IFDEF, cell names

Report table insertion

Report wizard

ReportWorkshop demo projects

ReportWorkshop localization

Video demos

Report Workshop 5.2