Recipes

This page is dedicated to list common questions, tricks and solutions (mainly got from the Seal Report Forum or from our experience on site).

We assume that you are familiar with the Seal Report architecture and components (Advanced users are welcome here !).

Add your Recipe

Do you have a new recipe to share ?
As the site for the documentation is part of the solution, you are welcome to Pull a Request at GitHub.

Adding translation files

Languages files are located in the /Settings Repository sub-folder.
The files have the CSV format and are stored in UTF8 (not ANSI). The column containing the translations has the header with the Two Letter ISO Language Name (e.g. it for Italy).

Tanslations.csv contains the public translations for the product (Web Report Server and Report Result).
To add a new language, create a new translations file in the directory and name it with the Two Letter ISO Language Name before the extension (e.g. Translations.it.csv for Italy).

In addition, you can translate labels specific to your repository (date sources, connections, column names, report names, folder names, etc.) in the RepositoryTranslations.csv file.
You can generate working files for your repository translations using the Server Manager application: Use the 2 menus Tools->Export ... in CSV to generate the files, then copy and paste the records in the RepositoryTranslations.csv file according to your needs.

Once the new files saved, you can then specify the default language using the Server Manager application, click on the menu Configuration->Server Configuration... and specify the default culture of the product.

In reports, the culture can also be overridden per View (Culture Name property in the root View parameter), thus you can generate the same report in different languages.

Make sure that the new file follows the original CSV format and is saved in UTF8.
Test your new translations with the Web Report Server, just change your culture in your profile.

Formulas and custom SQL

There is no formula engine in Seal Report.
This is because you should rely mainly on your database engine to perform specific calculations.
However, it is easy to change the SQL used to query your column and thus add SQL formula in your model:

Create a Metadata model, based on Northwind.
Drag and drop the Suppliers/Supplier column in the Row Elements of the model.
Use the property Advanced->Custom SQL, you can here customize the SQL with what you want (call of a function, case statements, etc.)
For this sample, just type 'SUPPLIER: ' & Suppliers.CompanyName to add a prefix to your column value.

You can also use this property for the Data elements.
Drag and drop the Order Details/Quantity column in the Data Elements.
Rename it to Total Price and set the Advanced->Custom SQL property to:
Sum([Order Details].Quantity*[Order Details].UnitPrice)
this will give the calculation of the price per customer.

Finally, using the aliases of the table involved in the query, you can have a column based on a calculation of columns coming from different tables:
Drag and drop the Order Details/Quantity column in the Data Elements and set the Advanced->Custom SQL property to:
Sum([Order Details].Quantity + Products.UnitsInStock)

Do not hesitate to use the CASE statements in SQL Server or Oracle, they can be very useful.

You can also override the default SQL generated with the following Model properties : Select Clause,From Clause,Order By Clause,Group By Clause,Common Table Expression Clause

Custom expression for LINQ Models

Like for SQL Models, you can change the default C# expression of an element part of a LINQ Model with the Advanced->Custom expression property.
e.g. 'Helper.ToString(Northwind["Employees.LastName"])' can be overriden to 'Helper.ToString(Northwind["Employees.LastName"]).ToUpper()'.

Filtering records based on security

It is sometimes useful to change the result set returned by a report per user logged (e.g. The salesman can see only his orders).
This may be implemented by the use of a Razor Script in the Additional WHERE Clause property of the relevant table in your Data Source.

Example for Northwind:

In the Northwind Data Source, select the Orders table, then use the following Additional WHERE Clause:
@{
    MetaTable table = Model;
    string restriction = Environment.UserName; //This gives the windows user of the process running the engine
    if (table.Source.Report != null && table.Source.Report.SecurityContext != null)
    {
        var securityContext = table.Source.Report.SecurityContext; //User is logged through a Web Report Servert and has a context
        restriction = securityContext.Name; //Name of the user set during the login
        restriction = securityContext.WebUserName; //Name got from the login window
        //securityContext.SecurityGroups; //List of security groups set for the user
        if (securityContext.BelongsToGroup("Default Group")) { //Test if the user belongs to a group
            //Special restriction here
        }
    }
    string result = string.Format("Orders.EmployeeID in (SELECT EmployeeID FROM Employees WHERE LastName={0})", Helper.QuoteSingle(restriction));
    }
@Raw(result)
                                
In line 16, a dynamic restriction is set using the name of the user logged.
Thus, when the Orders table is involved in a model, the table will be automatically filtered with this restriction.

Defining security during the login process:

Run the Server Manager and edit the Web Security (CTRL + W), then add the following code to the Custom Security Script:
		var source = user.Security.Repository.Sources.FirstOrDefault(i => i.Name.StartsWith("Northwind"));
		if (source != null)
		{
            foreach (var metaTable in source.MetaData.Tables.Where(i => i.Name.ToLower() == "orders")) 
            {
                if (user.BelongsToGroup("CustomerGroupName")) {            
                    metaTable.WhereSQL = string.Format("{Orders.CustomerID in (SELECT CustomerID FROM Customers WHERE ContactName={0})", Helper.QuoteSingle(user.Name));
                }
            }        
		}
                                
If the logged user belongs to the group named 'CustomerGroupName', a dynamic restriction will be added to the Order table.
Thus, when the user executes a report, he can only see the orders having his ContactName
You may adapt this example to fit your requirements. There might be also other ways to implement dynamic security...

SQL Server Stored Procedures

Using Stored Procedures to generate a report is often requested. However as a SP is not a table, it cannot be directly defined as a table in your Data Source.
The call of a SP depends also on your database engine.
The following sample shows 3 different methods to call a SP in SQL Server, it must be adapted for other database engines.

SQL Server Stored Procedures

Check the report 401-SQL Server Stored Procedure with parameters in the /Reports/Samples/04-MS SQLServer Repository sub-folder.

Integrating Seal Report

Here are some recipes if you consider to use Seal Report in your own .Net or .NET Core application (Fat Client, ASP .Net, Console, etc.), there is no simple answer and the integration depends on your requirements and target architecture.

SealLibrary Assembly

If you have a project referencing the SealLibrary.dll assembly (you may use the SealReportLibrary.Net and SealReportLibrary.NETCore NuGet Packages), you can execute a report with the following code (got from the TestAndSamples project of the solution).

            Repository repository = Repository.Create();
            Report report = Report.LoadFromFile(@"C:\ProgramData\Seal Report Repository\Reports\Search - Orders.srex", repository);
            ReportExecution execution = new ReportExecution() { Report = report };
            execution.Execute();
            while (report.IsExecuting) {
                System.Threading.Thread.Sleep(100);
            }
            string resultPath = execution.GenerateHTMLResult();
            Process.Start(resultPath); //result contains the file path of the HTML report result
                                

The following code shows how to create a report having a restriction (also got from the TestAndSamples project of the solution).

           var repository = Repository.Create();
            Report report = Report.Create(repository);
            report.DisplayName = "Sample Report";
            var source = report.Sources.FirstOrDefault(i => i.Name.StartsWith("Northwind"));
            source.MetaData.Tables.Clear();
            //Update the data source with a new table
            var table = source.AddTable(true);
            table.DynamicColumns = true;
            table.Name = "products";
            //Instead of the name, could be a direct SQL statement:
            //table.Sql = "select * from products";
            table.Refresh();

            //Set the source of the default model
            report.Models[0].SourceGUID = source.GUID;
            //Add elements to the reports model
            foreach (var column in table.Columns)
            {
                var element = ReportElement.Create();
                element.MetaColumnGUID = column.GUID;
                element.Name = column.Name;
                element.PivotPosition = PivotPosition.Row;
                element.Source = source;
                report.Models[0].Elements.Add(element);
            }

            //Add a restriction to the model
            var restriction = ReportRestriction.CreateReportRestriction();
            restriction.Source = report.Models[0].Source;
            restriction.Report = report;
            restriction.Model = report.Models[0];
            restriction.MetaColumnGUID = table.Columns.FirstOrDefault(i => i.Name == "products.ProductName").GUID;
            restriction.SetDefaults();
            restriction.Operator = Operator.Contains;
            restriction.Value1 = "er";
            report.Models[0].Restrictions.Add(restriction);
            //Set the restriction text
            if (!string.IsNullOrEmpty(report.Models[0].Restriction)) report.Models[0].Restriction = string.Format("({0}) AND ", report.Models[0].Restriction);
            report.Models[0].Restriction += ReportRestriction.kStartRestrictionChar + restriction.GUID + ReportRestriction.kStopRestrictionChar;

            //Then execute it
            ReportExecution execution = new ReportExecution() { Report = report };
            execution.Execute();
            while (report.IsExecuting) System.Threading.Thread.Sleep(100);
            string result = execution.GenerateHTMLResult();
            Process.Start(result);
                            

Generating report results on the fly...

Check the interesting report 102-Task Generate several Report Results in the /Reports/Samples/01-Tasks Repository sub-folder.
The reports has a task that generates a report result by country using the Search - Orders report.
@using System.IO
@using System.Data
@{
    ReportTask task = Model;
	TaskHelper helper = new TaskHelper(task);
	Repository repository = task.Report.Repository;
    string result = "1"; //Set result to 0 to cancel the report.

	//Load report to execute
	Report report = Report.LoadFromFile(Path.Combine(repository.ReportsFolder, "Search - Orders.srex"), repository);
	task.Report.LogMessage("Load done");
	//Create an output for a folder
	var folderDevice = repository.Devices.First(i => i is OutputFolderDevice);
	var output = report.AddOutput(folderDevice);
	output.FolderPath = repository.ReportsFolder;
	
	//Load list of countries
	string sql = @"select distinct Country from Customers";
	var toProcess = helper.LoadDataTable(task.Connection, sql);
	foreach (DataRow row in toProcess.Rows)
	{
		string country = (string)row[0];
		//Set the restriction
		var restriction = report.Models[0].GetRestrictionByName("Customer Country");
		restriction.Prompt = PromptType.None;
		restriction.Operator = Operator.Equal;
		restriction.EnumValues.Clear();
		restriction.EnumValues.Add(country);
		
	    ReportExecution execution = new ReportExecution() { Report = report };
        report.OutputToExecute = output;
        report.CurrentViewGUID = output.ViewGUID;
		
		//Set result file name and culture
		output.FileName = "Search - Orders for " + country;		
		report.ExecutionView.CultureName = "English";
		
		task.Report.LogMessage("Executing report for '{0}'", country);
		execution.Execute();	
		while (report.Status != ReportStatus.Executed) {
			System.Threading.Thread.Sleep(1000);
		}
		task.Report.LogMessage("File generated in '{0}'", output.FileName);
	}

}
@Raw(result)
                                

Seal Web Interface API

You can also install a Seal Web Server and use the Seal Web Interface API to execute reports from JavaScript.
Here is a sample to execute a report in another window from a browser (requires JQuery):

function executeReport(path) {
    var server = "https://sealreport.org/demo/"
    $.post(server +"SWILogin", {
        user: "", // The user name
        password: "" // The password
    })
        .done(function (data) {
            var f = $('<form method="post" target="_blank" />').appendTo('body');
            f.attr('action', server + "SWExecuteReport");
            f.append($('').attr('name', 'path').attr('value', path));  //the report path,  e.g. "/Samples/01-Simple list (Products).srex"
            f.children('input').attr('type', 'hidden');
            f.submit();            
        });
    return false;
}

executeReport("/Overview - Sales.srex");
                                

Copying input value with a task

This recipe shows how to copy input values prompted to the user to several model restrictions.
Using the Report Designer, create a report having one to several models with a date restriction (e.g. Order Year)

Create an input value:
Select the root General node in the main Tree View and edit the Report Input Values property.
Add a new input value with the following properties:
Name='My Date', Prompt restriction='Prompt only two values' and Data type='Date & Time'

Add a task to copy the value during execution:
Create a report task with the following code:

@using System.Data
@{
     ReportTask task = Model;
    Report report = task.Report;
    var inputValue = report.InputValues.FirstOrDefault(i => i.DisplayName == "My Date");
    if (inputValue != null) {
        foreach (var model in report.Models) {
            report.LogMessage("Checking model '{0}'", model.Name);
            foreach (var restriction in model.Restrictions.Where(i => i.Name == "Orders.OrderDate")) {
                report.LogMessage("Copying to '{0}'", restriction.DisplayNameEl);
                restriction.Date1 = inputValue.Date1;
                restriction.Date2 = inputValue.Date2;

                //Use restriction.Value1 = inputValue.Value1; if the restriction is a Text or a Numeric
                //Use restriction.EnumValues = inputValue.EnumValues; if the restriction is an Enumerated list
           }
        }
    }
}
                                

During the report execution, the task will copy the 2 values prompted to all Orders.OrderDate restrictions.

Task to modify result tables

Note that you can also create a task with the Execution Step set to 'Models generated, before rendering' to modify the result tables generated in the models (e.g. modify cell values or styles).

Dynamic report and chart title

This recipe explains how to modify a report or a chart title to display the current restriction or the current page value.
Using the Report Designer, create a report having several pages and a Chart JS.
Add a restriction in the model (e.g. prompt a value for Order Year)

To modify the report title:
Select the root General node in the main Tree View and edit the Display Name property.

@using System.IO
@{
    Report report = Model;
    string result = Path.GetFileNameWithoutExtension(report.FilePath) + " " + report.Models[0].GetRestrictionByName("Order Year").DisplayValue1 + " " + DateTime.Now.ToShortDateString();
}
@Raw(result)
                                

The report title will contain the restriction chosen by the user and the current date.

Note that you can also create a task to do the same with the following script:
@using System.IO
@{
    ReportTask task = Model;
    Report report = task.Report;
    report.DisplayName = Path.GetFileNameWithoutExtension(report.FilePath) + " " + report.Models[0].GetRestrictionByName("Order Year").DisplayValue1 + " " + DateTime.Now.ToShortDateString();
}
                                


To modify the chart title:
Select the Chart JS View, then edit the Custom template property to change the code that defines the chart title (around the line number 70):

                title: {
                    display: true,
                    text: '@Raw(Helper.ToJS("Sales for " + page.PageTable[1,0].HTMLValue))', //Value 1 of Page Table
//HTML for first restriction    Raw(Helper.ToJS("Sales for " + reportModel.GetRestrictionByName("Order Year").DisplayValue1))',               
//for Enum                      Raw(Helper.ToJS("Sales for " + reportModel.GetRestrictionByName("Category").EnumDisplayValue))', 
                    position: '@view.GetValue("chartjs_title_position")'
                },
                                

page.PageTable[1,0].HTMLValue returns the first value of your current page table, but you can use any relevant value from your model or report (e.g. reportModel.GetRestrictionByName("Order Year").DisplayValue1 for a restriction value).

This may be easily adapted for the other chart type.

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 file 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.