Data Sources

A Data Source defines the database connections and description (table, catalog and joins).
Use the Server Manager to create and edit your Data Sources.
A Repository Data Source is stored in the /Sources Repository sub-folder in a XML file (*.scfgx).

You can also define and store a Data Source in a report with the same functionalities, however it is a good practice to use Repository Data Sources to ease the maintenance and share your configurations (e.g. if you change the display label of a column, all reports referencing the column will inherit from the modification).
Data Source Objects

Connections

Setting up a connection is the first thing to do after creating a Data Source.
A Connection defines how to connect to the database to be queried by using an OLEDB or ODBC Driver.
A Data Source can have one to many Connections (e.g. dev, test, production, etc.).

Make sure that your database driver (32bit or 64bit depending on your Seal Report installation) is installed on the machine you are running the Server Manager.

Use the Database Type property to define your database type. This type may be used by the engine in different ways (e.g. using Serial Date for Ms Access).
Once the connection successfully defined, you can use the Wizard to import tables, columns and joins from the database catalog.

For specific database type, use the Date Time Format property to specify how the date will be formatted in the dynamic WHERE clause generated by the engine.

Tables and Columns

Tables

A table is defined either by its Name or by a SQL Statement and Table Alias (e.g. 'Orders' or '(select * from Orders) OrdersAlias').
Table columns can be dynamically populated using the database catalog.

Once columns are created, you can set the Dynamic Columns property to false to rework your column definitions.

Pre and Post SQL Statement

A table can also have a Pre SQL Statement (Post SQL Statement) that is executed before (and after) the select statement is performed for a report model.
It occurs only if the report model involves the table.
These statements can be used for different purposes: Creating/Dropping a temporary table, updating columns, etc.

Additional WHERE Clause

An Additional WHERE Clause can also be defined to filter at run-time the records of the table.
This feature may be interesting to apply filter based on the person logged (e.g. a saleman can see only the orders he made).

Common Restrictions and Values

All SQL involved in the table definition (Name, SQL Statement, Pre SQL Statement, etc.) can contain the keywords '{CommonRestriction_<RestrictionName>}' or '{CommonValue_<RestrictionName>}' where <RestrictionName> is the name of the restriction.
Once defined, the common restriction can be defined in the models involving the table using the Common Restrictions property of the model definition.
Common restrictions are used to inject values prompted by the end-user in the form 'ColumnName=Value' or 'ValueOnly' if the restriction has the operator ValueOnly.
Check Restrictions for more information.

Use common restrictions if you have performance issue and you want to filter a huge table defined with sub-select.
Common restriction can also be used to pass parameters to stored procedures.
Note that the same Common Restriction or Value can be used several times.
e.g. the following SQL can be used to define a table or a SQL Model:
                                SELECT 'Type1' type, sum(aColumn1) amount FROM aTableName1 WHERE subType in (3,4) and CommonRestriction_Date
                                UNION
                                SELECT 'Type2' type, sum(aColumn2) FROM aTableName2 WHERE CommonRestriction_Date
                                UNION
                                SELECT 'Type3' type, sum(aColumn3) FROM aTableName3 WHERE CommonRestriction_Date
                            

Columns

A column is defined with its SQL (Name), its Data Type and how it is display in the Report Designer (Display Name and Category Name properties) and in the report result (Format property).

In certain cases, a column can reference an enumerated list with the Enumerated List property (e.g. the value 0 will be display with 'No', 1 with 'Yes').
Check Enumerated Lists for more information.

Drill and Sub-Reports Navigation

Once the column is displayed in the report result, a navigation popup menu can be activated (in the table cells or in charts).

Two types of navigation can be done:

  • Drill navigation to show the same report with another dimension (e.g. Country->Region->City, Year->Quarter->Month->Day).
    When drilling, the same report is executed with a new restriction on parent element value, and the parent element is replaced by its child in the model definition.
  • Sub-report navigation to view another report (e.g. from Order ID->Show a report detail on the Order).
    A new report is then executed with the restriction on the element value.

After a navigation has occurred, a new menu appears on the top left of the report result.

One to several drill children can defined using the Drill Children property.
One to several sub-reports can defined using the Sub-Reports property.
A sub-report file is often located in the /SubReports Repository sub-folder and must have a restriction prompted for the column.

Drill Element Samples

In the Northwind Data Source, select the Orders.Order Year column and check that the Drill Children property is set to Orders.Order Month.
Then the hierarchy continues to Orders.Order Year->Orders.Order Month->Orders.Order ID.

A drill child is also defined for the column Products.Category->Products Name.

Sub-Report Elements Samples

In the Northwind Data Source, select the Orders.Order ID column and check that the Sub Reports property is pointing to the SubReports\Order Detail.srex report.

A sub-report is also defined for the columns Customers.Customer, Employees.Employee Name, Products.Product Name.

Joins

A join defines how 2 tables can be joined through a SQL clause.
Joins are used by the Seal engine to build a select involving several tables (e.g. 'FROM Table1 INNER JOIN Table2 ON').

When joining two tables, the SQL clause is used by the Seal engine without any transformation, thus it is recommended to use the table names or alias before the column.

Enumerated lists

Enumerated lists are mainly used to allow the end-user to select a value from a combo-box when a restriction is prompted.
An enum has an Identifier and an optional display Value (e.g. 0 = False, 1 = True).
In addition, an enum can have a specific CSS Class, CSS Style to change the display of the result cell in the report result, and Restriction Value to change the display value when the enum is used for a restriction.
If the display value is different from the identifier, using an enumerated list for a column can avoid a table join (e.g. if my enum contains CategoryID and CategoryName, the join with a table Category is not necessary).
Using enumerated lists allows also to translate the display value in different languages (e.g. 0 gives 'Yes' or 'Oui' according to the user locale).
Finally, you can define custom sort that you could not do in SQL (e.g. having a sort by month with the names like 'January', 'February', etc. but the identifier is the month number).

By default, an enumerated list is static, just edit the values using the Collection Editor.

Dynamic enums

Enumerated list can be also loaded from the database using SQL Select Statement.
The select statement can have up to 5 columns to define dynamic enumerated list.

  • the first column is used for the identifier
  • the second optional column is the display value shown in the table result
  • the third optional column is the display value shown in the restriction list
  • the fourth optional column defines a custom CSS Style applied to the result cell
  • the fifth optional column defines a custom CSS Class applied to the result cell
Changing the CSS style or class can be useful to change the color depending on the value (e.g. green if OK, red if KO).

Performances

As building the enum list can be time consuming, it is a good practice to schedule a task to update enum values every morning.
Setting List is refreshed upon the database connection to true means the list is always queried when the report is executed. This can be useful if the list depends on the user connected but can be also time consuming.

Dynamic display for prompted restrictions

For huge list of values or for list having dependencies with other enums, a dynamic display is possible to allow the server to query live the database before showing the values to the user.
In this case, you must define the SQL Select Statement for prompted restrictionproperty.
This dedicated SQL can contain the keyword '{EnumFilter}' to specify the text typed by the user and/or '{EnumValues_<OtherEnumName>}' to use the values checked for another enumerated list.

Filter on the name of the product

                                    SELECT DISTINCT ProductID, ProductName FROM Products 
                                    WHERE ProductName LIKE '%{EnumFilter}%' 
                                    ORDER BY 2
                                

List of customers of selected countries

Build the list of customers only when countries are selected first.
This works only if both Customers and Countries are restrictions in the report.
                                SELECT DISTINCT City FROM Customers
                                WHERE Country in ({EnumValues_Country})
                                ORDER BY 1
                                

Dynamic Display Sample

Live Sample: 09-Enumerated List - Dynamic Display
This report shows how the 'Product Name' prompted list depends on the choice of a 'Product Category'.
The report contains also a custom enumerated list named to search the product name with characters.

No SQL Data Source

A No SQL Data Source can provide data that cannot be queried from a database engine.
Depending on the data fetched, it may require advanced C#/.Net skills.

The Data Source contains the Definition Script to define the main table and the Load Script to load the data.
The table is called SealMasterTable.
Once the table is defined, the columns can be used in report models as normal columns coming from a database. The scripts manage a .Net object DataTable. Restrictions are handled in the server by using filter capacities of the DataTable.

No SQL Source Sample

Live Sample: 41-No SQL Source RSS
This report shows how to read RSS BBC Feeds to fill the DataTable.
A simple model is defined with 3 elements and 3 restrictions.
Note that the connection strings are used to store the URL of the feeds.

Definition Script property of the SealMasterTable.
@using Seal.Model
@using System.Data
@{
    MetaTable metaTable = Model;
    DataTable table = new DataTable();
	//Define the table columns 
	table.Columns.Add(new DataColumn("Id", typeof(string)));
	table.Columns.Add(new DataColumn("Date", typeof(DateTime)));
	table.Columns.Add(new DataColumn("Title", typeof(string)));
	table.Columns.Add(new DataColumn("Summary", typeof(string)));
	table.Columns.Add(new DataColumn("Link", typeof(string)));
	table.Columns.Add(new DataColumn("Categories", typeof(string)));
	table.Columns.Add(new DataColumn("TitleWithLink", typeof(string)));
	metaTable.NoSQLTable = table;
}

Load Script property of the SealMasterTable.
@using Seal.Model
@using Seal.Helpers
@using System.Data
@using System.Text
@using System.Collections
@using System.Web
@using System.Xml
@using System.ServiceModel.Syndication
@{
    //Just feed the table from the RSS data
    MetaTable metaTable = Model;
    DataTable table = metaTable.NoSQLTable;
    ReportExecutionLog log = metaTable;

    log.LogMessage("Loading RSS feed from {0}", Model.Source.Connection.FullConnectionString);
    var reader = XmlReader.Create(Model.Source.Connection.FullConnectionString);
    var feed = SyndicationFeed.Load(reader);
    foreach (var item in feed.Items)
    {
        string link = item.Links.Count > 0 ? item.Links[0].Uri.AbsoluteUri : "";
        string categories = "";
        foreach (var category in item.Categories)
        {
            categories += category.Name + ";";
        }
        string titleWithLink = string.Format("{1}", link, HttpUtility.HtmlEncode(item.Title.Text));
        //Values must match the table definition
        table.Rows.Add(item.Id, item.PublishDate.DateTime, item.Title.Text, item.Summary.Text, link, categories, titleWithLink);
    }
    log.LogMessage("{0} record(s) loaded", table.Rows.Count);
}
                    

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.