Data to a report can be connected in various ways. The easiest way is to store connection settings in the report template. You can also connect the data from the code. You can do this when the report is loaded in the GetReport action.
HomeController.cs
|
...
public ActionResult GetReport()
{
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("~/Content/Data/Demo.xml"));
StiReport report = new StiReport();
report.Load(Server.MapPath("~/Content/TwoSimpleLists.mrt"));
report.Dictionary.Databases.Clear();
report.RegData("Demo", ds);
return StiMvcViewer.GetReportResult(report);
}
...
|
Data for the report can be connected not only when the report is loaded. For example, you can connect new data at the moment of interactive actions in the viewer (applying report parameters, sorting, drill-down, collapsing). To do this, you should set the Interaction action for the HTML5 Viewer component and, in the action handler, connect the data for the current report. In the same way, you can connect data in other actions of the viewer.
Index.cshtml
|
...
@Html.Stimulsoft().StiMvcViewer("MvcViewer1",
new StiMvcViewerOptions() {
Actions =
{
GetReport = "GetReport",
ViewerEvent = "ViewerEvent",
Interaction = "ViewerInteraction"
}
})
...
|
HomeController.cs
|
...
public ActionResult ViewerInteraction()
{
DataSet data = new DataSet();
data.ReadXml(Server.MapPath("~/Content/Data/Demo.xml"));
StiReport report = StiMvcViewer.GetReportObject();
report.RegData("Demo", data);
return StiMvcViewer.InteractionResult(report);
}
...
|
If you want to connect new data only for a certain interactive action of the viewer, for example, only when you apply report parameters you can use the parameters of the viewer. The viewer parameters are represented as an object of the StiRequestParams class. They are passed to any server-side on any request and contain all necessary information and states of the client part of the viewer. To determine the type of action of the viewer, it is enough to check the Action property of the viewer parameters.
HomeController.cs
|
...
public ActionResult ViewerInteraction()
{
StiRequestParams requestParams = StiMvcViewer.GetRequestParams();
if (requestParams.Action == StiAction.Variables)
{
DataSet data = new DataSet();
data.ReadXml(Server.MapPath("~/Content/Data/Demo.xml"));
StiReport report = StiMvcViewer.GetReportObject();
report.RegData("Demo", data);
return StiMvcViewer.InteractionResult(report);
}
return StiMvcViewer.InteractionResult();
}
...
|
SQL data sources
The connection parameters to the SQL data source and any other ones can be stored in the report template. Suppose you want to set the connection parameters from the code before rendering the report (for example, for security reasons or depending on the authorized user). In that case, you can use the example below.
HomeController.cs
|
...
public ActionResult GetReport()
{
OracleConnection connection = new OracleConnection("Data Source=Oracle8i;Integrated Security=yes");
connection.Open();
OracleDataAdapter adapter = new OracleDataAdapter();
adapter.SelectCommand = new OracleCommand("SELECT * FROM Products", connection);
DataSet dataSet = new DataSet("productsDataSet");
adapter.Fill(dataSet, "Products");
StiReport report = new StiReport();
report.Load(Server.MapPath("~/Content/SqlSampleReport.mrt"));
report.RegData("Products", dataSet);
return StiMvcViewer.GetReportResult(report);
}
...
|
Also, for SQL data sources used in the report, you can specify the Query Timeout in seconds. The value of this property is stored in the report template for each SQL connection separately.
Below is an example of code that you may use to change the connection string for MS SQL, adjust the query, set the query timeout for the already created connection and data sources in the report.
HomeController.cs
|
...
public ActionResult GetReport()
{
StiReport report = new StiReport();
report.Load(Server.MapPath("Report.mrt"));
((StiSqlDatabase)report.Dictionary.Databases["Connection"]).ConnectionString = @"Data Source=server;Integrated Security=True;Initial Catalog=DataBase";
((StiSqlSource)report.Dictionary.DataSources["DataSourceName"]).SqlCommand = "select * from Table where Column = 100";
((StiSqlSource)report.Dictionary.DataSources["DataSourceName"]).CommandTimeout = 1000;
return StiMvcViewer.GetReportResult(report);
}
...
|
You can also use data for designing reports and dashboards obtained from OData storage. In this case, you can do the authorization using a username, user password, or token. Authorization parameters are specified in the connection string to the OData storage using the ";" separator.
HomeController.cs
|
...
public ActionResult GetReport()
{
var report = new StiReport();
//Authorization using a user account
var oDataDatabase = new StiODataDatabase("OData", "OData", @"https://services.odata.org/V4/Northwind/Northwind.svc;AddressBearer=adress;UserName=UserName;Password=Password;Client_Id=Your Client ID", false, null);
//Authorization using a user token
var oDataDatabase = new StiODataDatabase("OData", "OData", @"https://services.odata.org/V4/Northwind/Northwind.svc;Token=Enter your token", false, null);
report.Dictionary.Databases.Add(oDataDatabase);
oDataDatabase.Synchronize(report);
//Query with data filter
((StiSqlSource)report.Dictionary.DataSources["Products"]).SqlCommand = "Products?$filter=ProductID eq 2";
return StiMvcViewer.GetReportResult(report);
}
...
|
The table below shows the connection string templates for different types of data sources.
Data Source
|
Connection String Template
|
MS SQL
|
Integrated Security=False; Data Source=myServerAddress;Initial Catalog=myDataBase; User ID=myUsername; Password=myPassword;
|
MySQL
|
Server=myServerAddress; Database=myDataBase;UserId=myUsername; Pwd=myPassword;
|
ODBC
|
Driver={SQL Server}; Server=myServerAddress;Database=myDataBase; Uid=myUsername; Pwd=myPassword;
|
OLE DB
|
Provider=SQLOLEDB.1; Integrated Security=SSPI;Persist Security Info=False; Initial Catalog=myDataBase;Data Source=myServerAddress
|
Oracle
|
Data Source=TORCL;User Id=myUsername;Password=myPassword;
|
MS Access
|
Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Password=pass;Data Source=C:\\myAccessFile.accdb;
|
PostgreSQL
|
Server=myServerAddress; Port=5432; Database=myDataBase;User Id=myUsername; Password=myPassword;
|
Firebird
|
User=SYSDBA; Password=masterkey; Database=SampleDatabase.fdb;DataSource=myServerAddress; Port=3050; Dialect=3; Charset=NONE;Role=; Connection lifetime=15; Pooling=true; MinPoolSize=0;MaxPoolSize=50; Packet Size=8192; ServerType=0;
|
SQL CE
|
Data Source=c:\MyData.sdf; Persist Security Info=False;
|
SQLite
|
Data Source=c:\mydb.db; Version=3;
|
DB2
|
Server=myAddress:myPortNumber;Database=myDataBase;UID=myUsername;PWD=myPassword;Max Pool Size=100;Min Pool Size=10;
|
Infomix
|
Database=myDataBase;Host=192.168.10.10;Server=db_engine_tcp;Service=1492;Protocol=onsoctcp;UID=myUsername;Password=myPassword;
|
Sybase
|
Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
|
Teradata
|
Data Source=myServerAddress;User ID=myUsername;Password=myPassword;
|
VistaDB
|
Data Source=D:\folder\myVistaDatabaseFile.vdb4;Open Mode=ExclusiveReadWrite;
|
Universal(dotConnect)
|
Provider=Oracle;direct=true;data source=192.168.0.1;port=1521;sid=sid;user=user;password=pass
|
MongoDB
|
mongodb://<user>:<password>@localhost/test
|
OData
|
http://services.odata.org/v3/odata/OData.svc/
|
Other...
|
The table shows the most commonly used templates for the connection string. You can view various connection string options at the special website.
|
|
Data from XML, JSON, Excel files
Connecting to XML and JSON data sources can be stored in the report template. If you want to specify data files from the code, you can use the example below.
HomeController.cs
|
...
public ActionResult GetReport()
{
DataSet data = new DataSet();
data.ReadXml(Server.MapPath("~/Content/Data/Demo.xml"));
StiReport report = new StiReport();
report.Load(Server.MapPath("~/Content/SimpleList.mrt"));
report.RegData(data);
return StiMvcViewer.GetReportResult(report);
}
...
|
HomeController.cs
|
...
public ActionResult GetReport()
{
DataSet data = StiJsonToDataSetConverterV2.GetDataSetFromFile(Server.MapPath("~/Content/Data/Demo.json"));
StiReport report = new StiReport();
report.Load(Server.MapPath("~/Content/SimpleList.mrt"));
report.RegData(data);
return StiMvcViewer.GetReportResult(report);
}
...
|
|