Report

Reports built once, produced as PDF, Word, Excel or CSV, on demand or at a set time.

Report installs on your device (Windows) and opens with your Prophetix account, the same one as for the web applications.

Report connects to your databases (SQL Server, PostgreSQL, MySQL, ODBC) and to your flat files, delimited or fixed-width. From them you build datasets that several reports can share, transform them (calculated columns, sorting, filtering, grouping, joins, pivots), then lay out the page by drag and drop.

Each report comes out as a PDF faithful to what the designer shows, as an editable Word document, as an Excel workbook with typed values, or as CSV. A schedule, with cron-style due times, produces them on its own and drops them in a folder or sends them by email; the history keeps a record of every run.

A Windows service runs the reports and keeps the catalog, whose connection strings are encrypted for the machine. Both applications, the Studio, which configures, and the Scheduler, go through it.

Report is in preparation. It will join the Prophetix subscription catalogue when it is released.

Every feature of Report

76 features in 11 areas

Data sources

Connects to SQL Server, PostgreSQL, MySQL and, through ODBC, to any database whose driver is installed on the service machine.
Flat files as sources: delimited files (CSV, text) and fixed-width files, read from a root folder.
File options: separator, encoding, quote character, leading lines to skip, culture for numbers and dates, first line as header.
Fixed-width files: the width of each column and the column names must be declared.
Detects the type of each column from the values read.
Tests the connection on what is being entered, without saving it, and browses the objects of the source (tables, views).
Maximum query execution time per source.
Sources organized in folders in a tree, with search, duplication and copying of the name.

Shared datasets

A dataset = a source + a query, shared by several reports that each rework it in their own way.
Query written in the language of the database, with parameters written @name whatever the database.
Parameters declared with type, default value and required flag, added automatically from the query.
Data preview from the keyboard (Ctrl+Enter) with parameter values entered on the fly.
Column schema detected and remembered: the designer offers the columns without reopening a connection.
Cap on the number of rows returned and caching of the result for a chosen number of seconds.
Organization in folders, search, duplication.

Data transformations

Fourteen stackable steps in a pipeline specific to each report, which you can move up, move down, disable without deleting, and comment.
Columns: choose or remove, rename with a header label, add a calculated column, merge several columns with a separator.
Types and values: convert the type of a column with a format, replace values (regular expression, case, null values only).
Rows: filter by a condition, sort on several keys, remove duplicates, limit to a slice.
Grouping: group and aggregate (sum, average, minimum, maximum, count, distinct values, first, last, concatenation).
Sets: join another dataset (inner, left, right, full, cross), stack another dataset, pivot (crosstab).
Each step shows a preview of the result: the columns offered are always those of the draft in progress.

Expressions

Excel-like expressions, no code: multiply two columns, test whether a balance is negative, assemble a last name and a first name, compare a year to the current year.
Columns, report parameters and system variables (today's date, page number, row count, user, report name) are each designated by a prefix, with no ambiguity in case of identical names.
Text functions: Concat, Left, Right, Mid, Len, Upper, Lower, Trim, Replace, PadLeft, PadRight, Contains, StartsWith, EndsWith.
Number and date functions: Round, Floor, Ceiling, Abs, Sqrt, Pow, Today, Now, Year, Month, Day, AddDays, AddMonths, DateDiff, FormatDate.
Conversion and test functions: If, IfNull, Coalesce, IsNull, IsEmpty, ToNumber, ToDate, Format.
Expression editor with lists of columns, functions, parameters and variables, to insert with a double-click.

Report designer

Band-based layout: report header and footer, page header and footer, group headers and footers (several levels), detail.
Elements to drag onto a band: label, field, expression, image, line, rectangle, table, page number.
Summary fields (sum, average, count, etc.) over the whole report, the group or the page.
Table with expression columns, totals row, alternating rows, header repeated on every page, stretch to full width.
Formatting: font, size, bold, italic, underline, color, background, borders, inner margins, line spacing, alignment, text wrapping.
Visibility of an element or a band according to a condition, page break before or after a band, keep-together band, growing or shrinking band.
Precise positioning with the mouse: rulers, grid, snapping, moving and resizing, selection by rectangle, alignment.
Undo and redo, duplication and deletion of elements.
Page setup: paper size, orientation, margins, with units in millimeters or points.
Faithful paginated preview, computed on the draft without saving anything, with Page x / y numbering.

Output formats

PDF faithful, to the point, to what the designer shows: it is the reference format.
Word: truly editable flow document, with real headers and footers and a table with a repeated header row.
Excel: typed values (a number arrives as a number), autofilter and adjusted column widths.
CSV: the transformed dataset, semicolon separator, UTF-8 with byte order mark.
Default formats chosen in each report, changeable at each run or schedule.
Report parameters with label, type, default value, required flag and list of allowed values.

Scheduling and delivery

Schedules by cron expression, by interval, or for a single run, with shortcuts for every day, certain days of the week or a day of the month.
Validity period (start, end) and time zone of the due times.
Preview of the upcoming due times and the date of the next run.
Parameter values with relative dates resolved at each run: yesterday, today, previous month, N days ago, etc.
Output formats specific to each schedule and a file name template with date, time, and the name of the report, the schedule or a parameter.
Delivery to a folder: overwriting or numbering of duplicates, number of generations kept.
Delivery by email: recipients, copy and blind copy, subject, body as text or HTML, attached files.
Automatic retry on failure, with the number of attempts and the delay between them.
Immediate run of a schedule in one click, activation, deactivation and duplication.

History

History of each run: schedule, report, trigger, start, duration, row count, status.
Filters by period, report, schedule and status, with automatic refresh every thirty seconds.
Detailed log of the run, parameters used and files produced.
Saving of a produced file wherever you choose.

Access rights and security

Integrated Windows authentication: the service knows who is calling, with no additional password.
Rights per source, dataset and report: read, run, modify, ownership, granted to an account or a Windows group.
An object is restricted only if you decide so: as long as the restriction is not ticked, it remains open to everyone.
Safeguards: you cannot remove your own ownership of an object; an administrator of the machine always keeps control.
The Studio disables in advance what the service will refuse and shows your effective level.
Connection strings encrypted in the catalog, tied to the machine; the service listens only on the local machine.
Schedules run under the service account, even on a report whose access is restricted.

On your devices

Windows 10 22H2 or Windows 11: three components, a Windows service, the configuration Studio and the Scheduler.
The service keeps the catalog (SQLite database; only its connection strings are encrypted), runs the reports and holds the scheduler; it restarts on its own after an incident.
Studio and Scheduler are two desktop applications that go through the local interface of the service, never through the database.
Publishing and installation scripts for the service, with a choice of service account.

Good to know

No web version, nor Linux, nor Android: PxReport is reserved for Windows.
A domain account is required for integrated Windows authentication to a remote database or to a repository on a network share.
ODBC drivers must be installed on the service machine, in the same architecture as the service.
The chain has been tried end to end with a CSV source, up to delivery to a folder; database connections and email sending are not announced as proven on your servers.
Email sending goes through an SMTP server configured on the service.
The Word output is a flow document, without the absolute positioning of the PDF; Excel has no pages.
The service listens only on the local machine: Studio and Scheduler are used on the same machine as the service.
The installation packages and the publication in the Microsoft Store are not ready yet.