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, ODBC, MS SQLServer, Mongo DB or MySQL Driver.
A Data Source can have one to many Connections (e.g. dev, test, production, etc.).

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.

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.

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 salesman can see only the orders he made).
Check Filtering records based on security for more information.

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.

                                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 defined for the column.

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').

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 and/or a Razor Script.
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

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 restriction property.
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.

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

                                SELECT DISTINCT City FROM Customers
                                WHERE Country in ({EnumValues_Country})
                                ORDER BY 1
                                

A LINQ Data Source can provide data that cannot be queried from a SQL database engine.
The Seal engine generates dynamically a LINQ query including the elements selected, the restrictions and the joins involved.
Depending on the data fetched, it may require advanced C#/.Net skills.

No SQL Table

A LINQ Data Source contains No SQL Tables based on the templates defined in the repository.
A No SQL table is then defined with a Definition Script to insert the columns in the main table and the Load script to load the data.

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 DataTable Object.
Restrictions are handled in the server by using filter capacities of the DataTable.

Joins and Table Links

Like in SQL, Joins can be defined between tables using a dedicated Join clause coded in C#.
This clause will be used by the engine to generate the LINQ query when several tables are involved.

In addition to No SQL Tables, a LINQ Data Source can have Table Links that reference tables defined in other SQL or LINQ Data Sources installed in the Repository.
Joins can then be defined using the Tables referenced by the Table Link.

In the LINQ Query, elements defined in a No SQL table are named with the Table Name and the Column Name (e.g. for the objective column of Excel Employee Objective: ExcelEmployeeObjective["objective"]), and elements defined in a Table Link are named using the Data Source Name and the original Column Name (e.g. for the LastName column of Employees: Northwind["Employees.LastName"]).

new {x1=Helper.ToString(Northwind["Employees.LastName"]), x2=((DateTime)Northwind["DATETIME2FROMPARTS(year(Orders.OrderDate),1,1,0,0,0,0,0)"]).Year}
equals 
new {x1=Helper.ToString(ExcelEmployeeObjective["employee"]), x2=Int32.Parse(ExcelEmployeeObjective["year"].ToString()) }
                    

@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;
}

@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;

	var url = metaTable.GetValue("rss_url");
    metaTable.LogMessage("Loading RSS feed from {0}", url);
	var reader = XmlReader.Create(url);
	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);
    }
    metaTable.LogMessage("{0} record(s) loaded", table.Rows.Count);
}
                    

Mongo DB Support

A dedicated Connection Type and Connection String has been implemented to handle queries on Mongo DB.

Once the connection setup in the LINQ Data Source, Mongo DB tables can be defined using the Mongo DB Table template.
A collection name and several parameters (like the name of an array to unwind) allow to define the table.

If a Mongo DB table is involved in a model, the report engine generates automatically the Mongo DB Stages Script of the table to apply elements and restrictions at the database server level.
This script will be executed before the Load Script and can be customized if the flag Generate Mongo DB Stages is set to False.

Helper.ToString(movies["_id"]) equals Helper.ToString(moviescasts["_id"])