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).
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 either the Excel (.xlsx) or the CSV format (stored in UTF8). The column containing the translations has the header with the Two Letter ISO Language Name (
Tanslations.xlsx or Tanslations.csv contain 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 (
In addition, you can translate labels specific to your repository (date sources, connections, column names, report names, folder names, etc.) in the RepositoryTranslations.xlsx or RepositoryTranslations.csv file.
You can generate working files for your repository translations using the Server Manager application: Use the 2 menus
Once the new files saved, you can then specify the default language using the Server Manager application, click on the menu
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.
Changing Logo and Product Name
From 6.5, the logo file locations are the followings:
- C:\ProgramData\Seal Report Repository\Views\Images (change the logo seen in Seal Report Designer)
- C:\inetpub\wwwroot\Seal\wwwroot\Images (change the logo seen in the web browser)
The name of the image file can also be changed in the Server Manager using the menu
Modify the logo file name using the Logo file name
property.
The product name displayed in the Web Report Server can also be modified using the Web Product Name
property.
The recommended format is a 125*50 images with white/transparent background color.
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)
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 theAdvanced->Custom expression
property.Filtering records based on security
It is sometimes useful to change the result set returned by a report per user logged (
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 followingAdditional 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 Server 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 (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
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 Tests project of the solution).
Note that you must have the Repository Folders and Files installed on the machine running the program.
//Repository.RepositoryConfigurationPath = @"C:\ProgramData\Seal Report Repository"; //to specify an alternate Repository Path 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(); var p = new Process(); p.StartInfo = new ProcessStartInfo(resultPath) { UseShellExecute = true }; //resultPath contains the file path of the HTML report result p.Start();
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 var restrictionText = report.Models[0].Restriction; if (!string.IsNullOrEmpty(restrictionText)) restrictionText += string.Format("({0}) AND ", restrictionText); restrictionText += restriction.Pattern; report.Models[0].Restriction = restrictionText; //Then execute it ReportExecution execution = new ReportExecution() { Report = report }; execution.Execute(); while (report.IsExecuting) System.Threading.Thread.Sleep(100); string result = execution.GenerateHTMLResult(); var p = new Process(); p.StartInfo = new ProcessStartInfo(result) { UseShellExecute = true }; p.Start();
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 && !report.HasErrors && !report.Cancel) { 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 got from the project TestWebApplication to execute a report in another window from a browser (requires JQuery):
$("#login_execute_button") .click(function () { $("#login_label").html("Connecting...
"); $.post(sealServer + "SWILogin", { user: 'ben', password: 'ben' }) .done(function (data) { if (!data.error) { //SWILogin done, Executing report... var mySessionId = data.sessionId; var form = $('<form method="post" target="_blank" />').appendTo('body'); form.attr('action', sealServer + "SWExecuteReport"); form.append($('').attr('name', 'path').attr('value', "\\Search - Orders.srex")); form.append($('').attr('name', 'r0_name').attr('value', "Quantity")); form.append($('').attr('name', 'r0_operator').attr('value', "Between")); form.append($('').attr('name', 'r0_value_1').attr('value', "34")); form.append($('').attr('name', 'r0_value_2').attr('value', "123")); form.append($('').attr('name', 'use_default_restrictions').attr('value', "true")); form.append($('').attr('type', 'hidden').attr('name', 'sessionId').attr('value', mySessionId)); form.children('input').attr('type', 'hidden'); form.submit(); //Logout $.post(sealServer + "SWILogout", { sessionId: mySessionId }) .done(function (data) { if (!data.error) { //SWILogin done, SWExecute report done, SWILogout done } else { $("#login_label").text("Error: " + data.error); } }); } else { $("#login_label").text("Error: " + data.error); } }) });
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 (
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 theExecution 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 (
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.
@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].DisplayValue))', //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 (
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 ?As the translations are part of the project, feel free to Pull a Request at GitHub.
Sponsor the project
If you are using Seal Report in a business application, please consider to sponsor the product to ensure its maintenance, quality and independence.Please check Support and Sponsor.
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.