public void CreationAndExecution()
{
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.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.Status != ReportStatus.Executed) System.Threading.Thread.Sleep(100);
string result = execution.GenerateHTMLResult();
Process.Start(result);
}
public void ExecutionWithExternalDataTables()
{
//Get Data Table from another source
string sql = @"
SELECT DISTINCT
DateSerial(DatePart('yyyy',[Orders.OrderDate]), 1, 1) AS C0,
Products.CategoryID AS C1,
Customers.Country AS C2,
999999 AS C3
FROM
(Products INNER JOIN
([Order Details] INNER JOIN
(Orders INNER JOIN Customers
ON Customers.CustomerID = Orders.CustomerID)
ON Orders.OrderID = [Order Details].OrderID)
ON Products.ProductID = [Order Details].ProductID)
";
var connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ProgramData\Seal Report Repository\Databases\Northwind.mdb;Persist Security Info=False");
var command = connection.CreateCommand();
command.CommandText = sql;
var adapter = new OleDbDataAdapter(command);
var newTable = new DataTable();
adapter.Fill(newTable);
//Data table must have the same column definition as the one defined in the report
var repository = Repository.Create();
var report = Report.LoadFromFile(@"C:\ProgramData\Seal Report Repository\Reports\Samples\03-Cross tab - Simple chart (Orders).srex", repository);
//Set data table to the report model (here there is only 1 model)
report.Models[0].ResultTable = newTable;
//Set report in Render only mode so the Result table is not loaded during the execution
report.RenderOnly = true;
//Note that if model.ResultTable is null, the table will be loaded anyway
var execution = new ReportExecution() { Report = report };
execution.Execute();
while (report.Status != ReportStatus.Executed) System.Threading.Thread.Sleep(100);
string result = execution.GenerateHTMLResult();
Process.Start(result);
}
public void SimpleExecution()
{
//Simple load and report execution and generation in a HTML Result file
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.Status != ReportStatus.Executed) System.Threading.Thread.Sleep(100);
string result = execution.GenerateHTMLResult();
Process.Start(result);
}
public ActionResult SWIExecuteReport(string path, string viewGUID, string outputGUID, string format)
{
try
{
if (WebUser == null || !WebUser.IsAuthenticated) throw new Exception("Error: user is not authenticated");
if (string.IsNullOrEmpty(path)) throw new Exception("Error: path must be supplied");
string filePath = Repository.ReportsFolder + path;
if (System.IO.File.Exists(filePath))
{
SecurityFolder securityFolder = WebUser.FindSecurityFolder(Path.GetDirectoryName(filePath));
if (securityFolder == null) throw new Exception("Error: this folder is not published");
if (!string.IsNullOrEmpty(outputGUID))
{
if (securityFolder.PublicationType != PublicationType.ExecuteOutput) throw new Exception("Error: outputs cannot be executed");
}
Repository repository = Repository.CreateFast();
Report report = Report.LoadFromFile(filePath, repository);
report.ExecutionContext = ReportExecutionContext.WebReport;
report.SecurityContext = WebUser;
report.CurrentViewGUID = report.ViewGUID;
//Init Pre Input restrictions
report.PreInputRestrictions.Clear();
foreach (string key in Request.Form.Keys) report.PreInputRestrictions.Add(key, Request.Form[key]);
foreach (string key in Request.QueryString.Keys) report.PreInputRestrictions.Add(key, Request.QueryString[key]);
//execute to output
if (!string.IsNullOrEmpty(outputGUID))
{
report.OutputToExecute = report.Outputs.FirstOrDefault(i => i.GUID == outputGUID);
report.ExecutionContext = ReportExecutionContext.WebOutput;
if (report.OutputToExecute != null) report.CurrentViewGUID = report.OutputToExecute.ViewGUID;
}
//execute with custom view
if (!string.IsNullOrEmpty(viewGUID)) report.CurrentViewGUID = viewGUID;
ReportExecution execution = new ReportExecution() { Report = report };
Session[report.ExecutionGUID] = execution;
int index = Request.Url.OriginalString.ToLower().IndexOf("swiexecutereport");
if (index == -1) throw new Exception("Invalid URL");
report.WebUrl = Request.Url.OriginalString.Substring(0, index);
repository.WebPublishFolder = Path.Combine(Request.PhysicalApplicationPath, "temp");
repository.WebApplicationPath = Path.Combine(Request.PhysicalApplicationPath, "bin");
if (!Directory.Exists(repository.WebPublishFolder)) Directory.CreateDirectory(repository.WebPublishFolder);
FileHelper.PurgeTempDirectory(repository.WebPublishFolder);
report.InitForExecution();
initInputRestrictions(report);
execution.Execute();
while (report.Status != ReportStatus.Executed) System.Threading.Thread.Sleep(100);
string result = "";
if (!string.IsNullOrEmpty(outputGUID))
{
//Copy the result output to temp
result = publishReportResult(report);
}
else {
string fileResult = "";
if (string.IsNullOrEmpty(format)) format = "html";
if (format.ToLower() == "print") fileResult = execution.GeneratePrintResult();
else if (format.ToLower() == "pdf") fileResult = execution.GeneratePDFResult();
else if (format.ToLower() == "excel") fileResult = execution.GenerateExcelResult();
else fileResult = execution.GenerateHTMLResult();
result = execution.Report.WebTempUrl + Path.GetFileName(fileResult);
}
return Json(new { url = result });
}
}
catch (Exception ex)
{
return Json(new { error = ex.Message });
}
return Content("Error: Report file not found.\r\n");
}