How to create a report with stored procedures and dynamic SQL queries

[Codenest]

Hi,

I need to create a report in my MVC application that using SQL stored procedure with dynamic SQL query string and EXEC command. 

How can I do it and is there any example of that?​ 

 

Best Regards

[Stef]

Hello Codenest,

There is no auto-generating feature, thus you will have to adjust the SQL query to return data always with the same schema (number of columns and names of columns) which allows you to use properly designed report, or build the report at run-time based on the retrieved data.

The recommended approach to create reports programmatically is to first use the integrated in Visual Studio Report Designer and design a simple report e.g., only the item you need in a simplified way and standard data binding. Thus you can get familiar with the report's structure. Then you can modify and re-use the code generated in the report's designer.(cs)vb file.


The HTML5 Viewer is a client-side widget which just requests a report by sending a string description. The string is resolved to a report on the server by the reporting REST service, which also is responsible for processing and delivering the rendered HTML to the viewer.
If you need to build reports programmatically, you will have to use your custom resolver in the service's CreateReportResolver method.

More details how the HTML5 viewer and the Reporting REST service work are available here and in the linked overview articles.


I hope this helps you.

Regards,
Stef
Telerik

 

 

[Codenest]

I have tried recommended approach but it is not working. I have Stored procedures where output is always different, I can not assume column names or column number.

I downloaded sample code from this forum but it is not working. Report displays table with headers only, without data. 

I am attaching picture and code for your reference.

I really need to implement this ASAP and your help would be appreciated. 

 

 string connectionString = "Data Source=CODENEST;Initial Catalog=AdventureWorks;Integrated Security=True";

//get the processing table object since we're in the context of event
Telerik.Reporting.Processing.Table processingTable = (sender as Telerik.Reporting.Processing.Table);
//construct the select statement based on the selected report parameters
string selectString = "SELECT * FROM Person.Address";
SqlDataAdapter sqlAdapter = new SqlDataAdapter(selectString, connectionString);
//create a dataset, fill it and set it as datasource to the processing table object
DataSet ds = new DataSet();
sqlAdapter.Fill(ds);
processingTable.DataSource = ds.Tables[0];

//create two HtmlTextBox items (one for header and one for data) which would be added to the items collection of the table
Telerik.Reporting.HtmlTextBox textboxGroup;
Telerik.Reporting.HtmlTextBox textBoxTable;

//we do not clear the Rows collection, since we have a details row group and need to create columns only
this.table1.ColumnGroups.Clear();
this.table1.Body.Columns.Clear();
this.table1.Body.Rows.Clear();
int i = 0;
this.table1.ColumnHeadersPrintOnEveryPage = true;

foreach (System.Data.DataColumn dc in ds.Tables[0].Columns)
{
Telerik.Reporting.TableGroup tableGroupColumn = new Telerik.Reporting.TableGroup();
this.table1.ColumnGroups.Add(tableGroupColumn);
this.table1.Body.Columns.Add(new Telerik.Reporting.TableBodyColumn(Unit.Inch(1)));

textboxGroup = new Telerik.Reporting.HtmlTextBox();
textboxGroup.Style.BorderColor.Default = Color.Black;
textboxGroup.Style.BorderStyle.Default = BorderType.Solid;
textboxGroup.Value = dc.ColumnName.ToString();
textboxGroup.Size = new SizeU(Unit.Inch(1.1), Unit.Inch(0.3));
tableGroupColumn.ReportItem = textboxGroup;

textBoxTable = new Telerik.Reporting.HtmlTextBox();
textBoxTable.Style.BorderColor.Default = Color.Black;
textBoxTable.Style.BorderStyle.Default = BorderType.Solid;
textBoxTable.Value = "=Fields." + dc.ColumnName;
textBoxTable.Size = new SizeU(Unit.Inch(1.1), Unit.Inch(0.3));
this.table1.Body.SetCellContent(0, i++, textBoxTable);
this.table1.Items.AddRange(new ReportItemBase[] { textBoxTable, textboxGroup });
}

 

[Stef]

Hello,

Please check the attached demo project which illustrates how to generate Table item at run-time. To create the project we used a simple report created with the Report Designer in Visual Studio. Then we extracted the code generated in the report's designer.cs(vb) file and modified it to work with unknown data schema.

The approach is applicable for creating reports and any items programmatically.


With the HTML5 Viewer and the Reporting REST service, the generation of the report should happen in a custom resolver's Resolve method, where the modified report at the end is wrapped by an InstanceReportSource.

Regards,
Stef
Telerik