Page 1 of 1

Report Workshop 5.1 - report template generation

Posted: Sat Feb 18, 2023 3:53 pm
by Sergey Tkachenko
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

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

Posted: Sat Feb 18, 2023 3:54 pm
by Sergey Tkachenko
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
Essential rule.png (24.39 KiB) Viewed 48863 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
Cell Name.png (15.24 KiB) Viewed 48863 times

Report table insertion

Posted: Sat Feb 18, 2023 3:58 pm
by Sergey Tkachenko
Report table insertion

Introduction
Image
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

Dialog for report table insertion:

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

Inserted table:

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

Report result:

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

Report wizard

Posted: Sat Feb 18, 2023 4:02 pm
by Sergey Tkachenko
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:
Image
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:
  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)
Top-Level-Report-Types.png
Top-Level-Report-Types.png (11.62 KiB) Viewed 49170 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
How-To-Place-Details-in-Table.png (13.9 KiB) Viewed 49170 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.

ReportWorkshop demo projects

Posted: Sat Feb 18, 2023 4:04 pm
by Sergey Tkachenko
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.

ReportWorkshop localization

Posted: Sat Feb 18, 2023 4:04 pm
by Sergey Tkachenko
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.

Video demos

Posted: Sat Feb 18, 2023 4:05 pm
by Sergey Tkachenko
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).

Report Workshop 5.2

Posted: Fri Mar 10, 2023 12:48 pm
by Sergey Tkachenko
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.