Reports

A Report is edited with the Report Designer and stored in a file (*.srex) either in the /Reports Repository sub-folder or on the file system.
A report references repository Data Sources but it may have its own Data Sources definitions.
A report contains Models to define how to generate the Result Set (Data Table) and Series from the database.
Once the result got from the database, report Views are used to generate a HTML document using Razor parsing and rendering.
In addition, Outputs and Schedules can be defined to automate report generation (e.g. Sending an Email every morning).
Report allows also to perform Tasks to execute SQL or Razor Scripts.

Report Objects
Check also Seal Report Library (Seal.Model namespace) for more information.

Models

A Model defines how to build the SQL Select Statement to get the result set from the database.
A report can contain several models referencing different data sources (one data source per model).
Once got from the database, the result set is processed in a Summary Table and several Pages containing a Page Table, a Data Table and Series if a chart is defined.
The Data Source chosen defines which elements and restrictions are available to define the model.

The SQL generated for a model can be viewed and executed in a normal SQL Editor (e.g. SQL Management Studio for MS SQLServer). Just cut and paste your SQL to test it.
Report Model Objects
Check also Seal Report Library (Seal.Model namespace) for more information.

Model Definition

Once the Source defined, the Connection can also be chosen for the model.
Several scripts allow to modify the model before or after the table is loaded and processed: Pre Load Script, Post Load Script, Final Script.
Check Scripts for more information and samples.
You can also override the default SQL generated with the following properties : Select Clause,From Clause,Order By Clause,Group By Clause,Common Table Expression Clause

Samples

In MS MSQLServer, set the Select Clause to SELECT TOP 100 to limit the result to 100 records.
Add a custom restriction in the From Clause to enhance your SQL performances.
Add a column in the Order By Clause to sort the result on a column not shown in the report.

Like tables in Data Source, Pre/Post SQL Statements can be executed just before and after the SELECT statement is executed.
Finally ,the model definition allows to configure build timeout and join preferences, and to edit all the Common Restrictions found in the SQL generated for the model (use the property Common Restrictions).

Elements

The Data Source chosen for the model defines the elements available
You can drag and drop the elements you want to see in your report in four different panels corresponding to a cross table definition:
Row/Column for the dimensions of the result table, Data for the measures aggregated and Page to force a new page per page value.

Simply, drag and drop your elements to the Row panel to display a simple table in your report. If your element is a measure (like an amount), drag it to the Data panel to have more options.
If you are not familiar with cross tables, please check "Cross Table" or "Excel Pivot Table" on the internet to learn what is a cross table, a dimension and a measure.

Once chosen, the element can have a custom Name, and Sort Order.
The sort priority is the following: First Page elements, then Row/Column elements and finally Data elements.
In addition, the element Data Type and Format can be modified to change the display result.

Properties for Data element

  • Aggregate to choose which aggregation will be done: Sum, Average, Count, Min, Max.
  • Calculation Options to change the display in % of the row, column or total.
  • Totals to add extra line or column containing the total of the other cells in the result table.

Advanced Properties

  • Custom SQL to change the default SQL used for the element (which comes from the column name).
    By changing the SQL, you can use all SQL functions/operators/features supported by the database engine or other column of the table. Thus, formula can be defined at this level. Once modified, be sure that the new SQL generated is still correct (F8).
  • Cell Script to execute a razor script when the table cell is generated.
    The script can modify cell presentation and value (e.g. Setting the font color in red if the value is negative, calculating a progression or a running total, etc.).
    Using cell scripts can be time consuming as the script is executed for each cell of the tables generated.
  • Custom Enumerated List to force an existing enumerated list to use its display and sort.

Custom SQL Samples

Replace Orders.Amount by 2*Orders.Amount to multiply your measure by 2.
Replace Orders.Amount by Orders.Amount/Orders.Quantity to get a unit price.
Change Country by a CASE WHEN Country='France' THEN 'Other' ELSE Country END Clause to categorize your countries.

Cell Script

Live Sample: 50-Cell Script - Progression and running totals
Two columns have been added in the report model with custom Cell Script: One to calculate the Progression and the other one to calculate the Running Total.

Restrictions

Restrictions are used to filter data displayed in the report result
You can drag and drop the elements into the Restrictions text or into the Aggregate Restrictions text to add the restriction to the model.
The Restrictions text is then used in the SQL WHERE Clause and the Aggregate Restrictions text in the SQL HAVING Clause.

In this sample the Restrictions text contains the restriction on Country and the Aggregate Restrictions text on Amount with the Sum aggregate.
                                SELECT Country,SUM(Amount) FROM Orders 
                                WHERE Country='France' 
                                GROUP BY Country 
                                HAVING SUM(Amount)>10)
                            

A restriction inherits from an element, thus several properties are common: Name, Data Type, Custom SQL, Custom Enumerated List.
The operators available for the Operator property depend on the element type (Text, Numeric or Date & Time).

Date & Time keywords
If the restriction is a Date & Time, keywords can be used to have value relative to the execution date time: Now, Today for the current day, ThisMonth for the first day of the current month, etc.
In addition to the keyword, the value can be changed by +/- operations with a given unit (s for seconds, m for minutes, h for hours, D for Days, W for Weeks, M for Months, Q for Quarters, S for Semesters, Y for Years)

Keywords Samples

'Today - 1D' is yesterday
'ThisMonth +1M - 1D' is the end of the current month
'ThisQuarter +1Q - 1D' is the end of the current quarter
'Now+1.5s -2m +3h' gives the current time plus 1.5 seconds, minus 2 minutes and plus 3 hours...

Prompting the value at execution
When the report is executed, the restriction value can be prompted to end-user by modifying the Prompt Restriction property.
In this case, the flags Is Required and Can Change Operator can be set.

Flags 'Value only'
If the Operator is 'Value Only', the SQL generated for the restriction will not include the column name and operator and set only the value.

In the SQL, the restriction [Country Egals 'France'] will be replaced by Customer.Country = 'France', but if the Operator is set to 'Value only', the name of the operator is taken from Operator Label and the SQL generated is 'France'

Charts

Charts axis and series are also defined in the report model.
Three JavaScript Chart libraries are supported by Seal Report: Chart JS, NVD3 Chart and Plotly Chart.
According to the library chosen, the look and functionalities are different.

To define a simple chart, select one Row/Column element and set the Serie Definition property to Axis, then select a Data element and define the serie with Chart JS Serie or NVD3 Serie or Plotly Serie.

Instead of being an axis, a dimension can be a Splitter to create several series when the value changes (e.g. to create one serie per country).

For Date & Time or Numeric axis element, if the flag Use value for axis is true, the values of the dimension element is used to build the axis values, otherwise the X values are linear.

Other properties allow to control Sort Type and Sort Order of the chart (e.g. to show the biggest Bars at the start or at the end) and to define a secondary Y Axis Type (e.g. to have a serie for amount for Primary Y, and another for quantities for Secondary Y shown on the same chart).

Charts Samples

Live Sample: 04-Charts Gallery - Basics Live Sample: 05-Charts Gallery - Advanced
These two reports show different chart definitions with their renderings with the three supported libraries.

SQL Model

A SQL Model is a report model defined with a simple SQL Select Statement edited by the user designing the report.
It is equivalent to creating a table with dynamic columns in the Data Source with a simplified edition.
When the SQL Select Statement is modified, the elements available are updated.

The option Use raw SQL indicates to use directly the SQL Select Statement to generate the result table instead of using a 'select * from (SQL Select Statement) a' statement.
In this case, aggregations, restrictions and custom SQL cannot be used.

Note that the SQL Select Statement may contain Common Restrictions.

Converting a model

A SQL Model cannot not have join to other tables defined in the Data Source, however you can convert a SQL Model to a normal MetaData Model.
In this case, a new table is create in the Data Source and the model elements refer to this new table.

You can also convert a MetaData Model to a SQL Model, the SQL generated for the model will be then used for the SQL Select Statement. This may be useful to customize the SELECT generated for a report.

Just right click on your model in the TreeView to show the conversion popup menu.

SQL Model Sample

Live Sample: 08-SQL Model - Common Restrictions
This report shows a simple SQL Model based on the query of the Northwind database sample.
A Common Restriction and a Common Value are defined in the SQL and prompted at execution.

Views

Views are used to generate the report result from the models results.
A view is implemented as a Razor script that will be compiled and executed when rendering the report.
A view references a View Template. The template defines the default razor script and the view parameters.
The script and the parameters can be customized per view.

To be executed, a report has at least a root view referencing the Report Template.
A view can have children views, the rendering starts from the root view and ends at the last child views (from top to bottom).
After the execution of the view, the HTML or the text generated is appended to the report result.
A view has several parameters to change the rendering (e.g. changing the legend position in a chart, add CSS styles, etc.).
The parameters are defined in the view template configuration file. They may have different purposes and depends on the components/items handled by the view.

Model Views
Certain views reference a model, in the case the rendering is done using the model results.
When executed, a report model has a report model result: one summary table and several result pages. Each page has a Page Table, a Data Table and Chart Series.
Thus , there are dedicated views to render these results.

Customize your rendering

If necessary, the view scripts can be customized to get the rendering you want.
Set the Use custom template flag to true and edit the Custom template property.
If the view has Partial Templates, you can modify them using the Custom Partial Templates collection.

Templates

All View Templates are defined in files located in the /Views Repository sub-folder.
Here is the list of the current view templates supported:

  • Report: Root view containing all the code necessary for the report execution.
  • Model: Main view to display a model. Its children are dedicated to view each part of the model result: Page tables, Data tables, Charts, KPI.
  • Model Detail: Show a model as a detailed record. This view works for a model result having a single record.
  • Container: A grid container (refer to Bootstrap 3 grid at https://getbootstrap.com/docs/3.4/css/#grid).
  • Tab Control: A Tab Control container. Only Tab Page children can be defined from this view.
  • Tab Page: Container for a given page of the parent Tab Control. The name of the view is used for the Tab.
  • Free: Empty view that can be used freely to add HTML or text during the rendering.

From a Model view, the following children are supported and are applied per page result:

  • Page Table: Render the Page table of the page result.
  • Data Table: Render the Data table of the page result
  • Chart JS: Render the Chart JS Series of the page result
  • NVD3 Chart: Render the NVD3 Series of the page result
  • Plotly Chart: Render the Plotly Series of the page result
  • KPI: Display a Key Performance Indicator. The KPI is built from a data table result with at least 2 columns: 1 for the dimension, 1 for the measure (with optionals columns to define the goal if the column name has the _GOAL suffix).
  • Model Container: A grid container for the page result (refer to Bootstrap 3 grid at https://getbootstrap.com/docs/3.4/css/#grid).
Try some useful parameters of a Report View:
  • Report Format to choose the result format: HTML, CSV, HTML Print, Excel, PDF
  • Display: Execution Messages to control the messages panel of the report result. Use Messages enabled and shown for a report having only tasks.
  • Display: Restrictions per row to configure how many prompted restrictions are displayed per row.
  • Options: Force Execution to execute the report even if a restriction is prompted.
  • Options: Auto-Refresh to re-execute automatically the reports every xx seconds.
Other useful parameters of a Data Table View:
  • Options: Invert data table to swap the columns and rows of the data table from their original presentation.

Create your own View Template

It is easy to create your own view templates.
Just add two *.cshtml files in the /Views Repository sub-folder: one for the main script and one for the configuration.

Widget Publication for Dashboards

To be able to display a view in a Dashboard, it has to be published as a Widget.
Just expand the Widget Definition property and set a Name, the view is then published and will be available to be published manually or through the Dashboard Manager.
Several properties allow to configure the Widget default look and behavior (e.g. Default height and width, auto-refresh rate, etc.).

Edit your dashboards manually

To add the Widget to one of your Dashboard, just edit the dashboard definition file (*.sdax) located in the /Dashboards Repository sub-folder with a text editor.
Then cut and paste the Dashboard XML text into the <Items> XML tag.
Here is a basic dashboard definition file having one widget item:
                                    <?xml version="1.0" encoding="utf-8" ?>
                                    <Dashboard xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
                                    <GUID>97adfacd-de4e-437e-a6bd-709797a89345</GUID>
                                    <Name>A Dashboard Name</Name>
                                    <Items>
                                        <DashboardItem>
                                            <WidgetGUID>f1178eee-fe6c-4263-bf64-1499a69c8a53</WidgetGUID>
                                        </DashboardItem>
                                    </Items>
                                    </Dashboard>                                    

Widgets Navigation

From the Report Designer, use the Tools->Go to next widget in the report menu to navigate between the different Widgets published contained in the report.

Outputs and Schedules

A Report Output defines how to generate the report result of a root View to an Output Device (Folder or Email).
A report can have several outputs on different devices.

The execution of an output can differs from the normal report execution in several ways:
Custom restrictions to specialize the restriction values of the report.
Custom view parameters to change the parameter values of the root view (e.g. Changing the format to CSV for the output).
Culture to change the culture (e.g. Changing from English to French).

Conditional generation
If Cancel generation if no records is set to true, the output generation is cancelled if the result set has no records.
Pre/Post-generation scripts can be defined and executed before and after the generation.
The Pre-generation script allows also to cancel the output generation when more complex conditions are met.

Security
As an output can be scheduled and executed by the Task Scheduler, you can define particular security context using the User name and User Groups properties.
In this case, the user name and groups are used to execute the output. This may be useful if you have defined dynamic security based on user name or groups.

Devices

A Device is used to define a Report Output.
Two types of devices are supported:

  • Folder: To generate the report result into a file on the file system.
    The Folder device is always available.
  • Email: To send the report result by email through a SMTP server.
    Email devices are edited and configured using the Server Manager and are stored in a file (*.scfx) in the /Devices/EMail Repository sub-folder.
    The configuration contains all parameters required to send an email through a SMTP server.
Make sure that you have created one Email Device with the flag Used for notification to true to allow schedule notifications.

Schedules

A Schedule is a regular execution of a report output.
The execution is done through the standard Windows Task Scheduler via the batch executable Task Scheduler.
All the schedules can be managed by the MMC (Microsoft Management Console) Task Scheduler under the dedicated folder Seal Report.
A report output can have several schedules.
Several properties allow the configuration of the notifications and fail-over procedure (e.g. an email sent in case of success or failure, the execution can be retried several times, etc.).

Check users and synchronization

A Schedule is created by a Windows Users (the one running the Report Designer or the Web Report Server) but can be executed by another Windows User.
Be sure that the Windows User has the rights to execute the report and process the output.

You can synchronize schedules from the Server Manager if reports have been moved/copied with the Windows Explorer.
Just run the Server Manager in Administrator mode and select the menu Tools->Synchronize Report Schedules....

Define alerts

Alert and regular checks can be easily implemented using a report and a schedule.
Create a SQL Select statement that detects an anomaly in your database.
Just create a report output on an email address with the option Cancel generation if no records and schedule it regularly (e.g. every 15 minutes).
Every 15 minutes, the reports is executed, if a record is returned, an email will be sent.
To change the default folder name containing the schedules, run the Server Manager and select the menu Configuration->Configure Server..., then modify the Task Folder Name property.

Tasks

A Report Task is a SQL or a Razor Script executed before the report models are loaded and the report result is generated.
A task references one Data Source and on Connection.
A report can have several tasks executed sequentially from the top to the bottom.
If a task is a Razor Script, it can stop the report execution by returning the "0" value or by setting the CancelReport flag to true.
Thus, simple workflows can be implemented with tasks (e.g if a given condition fails, the report is cancelled).
Using the Execute for each connection property, a task can be executed for all connections defined in the Data Source.

As Tasks executes a Razor Script, they can reference, instantiate and use objects from any .Net Assemblies located in the /Assemblies Repository sub-folder.

Schedule your tasks

As tasks are part of the report, they can be scheduled as a normal report, even if the model has no element.
In the tree view, right click on the Schedules node and select the menu Add Schedule for Report Tasks.

Tasks Samples

Check the Repository Samples from the /Reports/Samples Repository sub-folder:
  • 30-Task Refresh Enumerated Lists
  • 31-Task Backup SQLServer Database
  • 32-Task Load Excel Table in SQLServer Database
  • 33-Task Load Table in SQLServer Database
  • 34-Task Generate several Report Results
  • 35-Task Search Reports

Input Values

To prompt a value to the user before executing the tasks or before executing a report, you can define Report Input Values (e.g. prompt a value before building a temporary table that will be used in the models).
This collection property is available in the root Views folder node of the main Tree View.
Like Common Value, the restriction will be prompted when the report is executed and the value can be used in the tasks or in other scripts used to generate the report result..
The value prompted can be either a text, a numeric, a date time or values of an enumerated list.

Task script to display the report input values:
@using Seal.Model
@{
	ReportTask task = Model;
    Report report = task.Report;
    foreach (ReportRestriction restr in report.InputValues) {
        report.LogMessage("[{0}]={1} Value={2}", restr.DisplayNameEl, restr.DisplayText, restr.FirstValue); //You can use restr.Value1, restr.FinalDate1, restr.EnumValues[0], restr.EnumDisplayValue, restr.FirstStringValue, restr.FirstNumericValue, restr.FirstDateValue
    }
}
                                

Input Values Samples

Live Sample: 51-Input Values - Connection and threshold
This reports shows the use of 2 input values.

PDF and Excel Converter

The Excel and PDF Converter is a commercial component available at https://ariacom.com that allow the generation of the report result in native PDF and Excel files.

Download the Evaluation Version of the Converter and start converting...

PDF Converter

The PDF Converter render the Print View of an HTML Result into a single PDF document (*.pdf).
Thus the HTML Print layout will be the PDF layout.
In the root View, several properties are available to configure the document generation (e.g. margin, password, document information, etc.)

The generation can also be customized using two dedicated scripts in the root View:
The PDF Converter Script is used to configure the PDF Converter before the document conversion (e.g. configuring the header or the footer, etc.).
The PDF Document Script is used to modify the PDF Document after its generation (e.g. writing additional text, images, etc.).

Excel Converter

The Excel Converter allows to generate the report result in a native Excel file (*.xlsx).
The document generation is done by the execution of several scripts defined in the views.
Their executions follows the rendering order: From the top view to the bottom.

Execution steps for the root view

From the root Report View, the Root Script: Main executes the following steps:
  • Execution of the Root Script: Configuration script to configure the converter
  • Execution of the Root Script: Header script to create the document header.
  • Creation of the document.
  • Parsing of all children views and execution of theirs View Script: Main script.
  • Cleanup and final options.

Execution steps for a child view

From a child View, the View Script: Main executes the following steps:
  • Execution of the View Script: Configuration script to change configurations from this view.
  • Execution of the View Script: Header script to create a header for the view.
  • If the view has a model, execution of the View Script: Summary table script to generate the summary table of the model.
  • If the view has a model, for each page of the model result, execution of the View Script: Page table, View Script: Chart and View Script: Data table scripts to process the different components of the page result.
  • Parsing of all children views and execution of theirs View Script: Main script.

Note that all the view scripts can be modified globally in the root Report View using the Reference Script: properties.

Default Converter Values

All default values of the converter component can be modified with the Server Manager in the Server configuration.
Check Server Configuration for more information.

Smart Copy

When using the Report Designer, the Smart Copy feature can be used to ease the report edition.
It allows the copy of property values from a source object to several destinations objects (e.g. applying the same restrictions values to all models, setting the same parameter value to several views, etc.).
In addition, the Smart Copy can copy a source object to several destinations objects (e.g. copying a model in another report, copying an element to another model, etc.).

The Smart Copy is available on most of the objects edited in a report: Task, Model, View, Output. Just right click on the object in the Tree View to show the popup menu.

You can also use the Smart Copy when editing a report model: Just right click on a report element or restriction to show the popup menu.

Find and replace using Notepad

As all Seal Report files are XML, you can easily edit them with your favorite NotePad and use its find/replace features.
Just save the file and reload the Report (or the Data Source) to check your modifications.
This may be also useful to copy MetData, change Data Source GUID, etc.

Execution Steps

When a report is executed, the following process is done.

Step 1: Execute Report Tasks

Tasks are executed sequentially (SQL or C# Script) from the top to the bottom.
If a task has a razor script returning "0", the report is cancelled.

Step 2: Build Models

Models are executed in parallel (one model per thread).
For each model:

  • Fill the model result table (DataTable)
    If SQL Source: Run the 'Pre Load Script' if any, build SQL, query the database, run the 'Post Load Script' if any.
    If No SQL Source: Build table definition using table source 'Definition Script' and load it using either the table source 'Default Load Script' or the model 'Load Script'.
  • Build the model pages: Create the pages (ResultPage) containing the page table (ResultTable) and data table (ResultTable)
  • Create the model Summary Table (ResultTable)
  • Process the totals in the result tables
  • Handle the 'Cell Script' for each cell (ResultCell) in the Data tables and then in the Summary table.
  • Calculate the series (this involves calls to 'Cell Script' if any)
  • Perform final sort.
  • Execute any 'Final Script' defined in the models.

Step 3: Render Views

Sequentially render report views from the root Report View to generate the result (from top to bottom).

Step 4: Process Output

If any, process the output defined for the execution.

  • Check if the 'Pre Execution Script' returns '0' or option to cancel if no records
  • Execute the output on the selected Device
  • Execute the 'Post Execution Script'

Contribute to Seal Report

Enhance the documentation and samples

Want to share your experience or to add a new topic ? Something is not clear in the documentation ? Do you have an interesting recipe ?
As the site for the documentation is part of the solution, you are welcome to Pull a Request at GitHub.

Add translation files

Do you have a translation files not yet supported by Seal Report ?
Check first the recipe Adding translation files.
As the translations are part of the project, feel free to Pull a Request at GitHub.

And more...

You are also welcome to help us in several different areas:
  • Beta testing.
  • Libraries of Data Sources and Reports on standard databases.
  • Additional View templates.
  • New features development.
Please feel free to contact us at Ariacom or use GitHub if you are interested in contributing to Seal Report.