Dynamic Report for Windows .NET

[Krish]

Hi,

I recently started working with Telerik Reports and looks good.

Currently I'm working on Windows .NET application and need to create a fully dynamic simple report.

The report query varies based on the user selection on the windows application (report name, database table name, db columns, db condition, db grouping columns).

Based on the above selection, the query gets built dynamically (kind of a query builder tool).

This query result should be dynamically populated in the Telerik Report.

How can I acheive the Telerik report to handle these kind of dynamic queries?

I came across multiple forums asking for similar kind of question but I'm getting lost with multiple replies.

Kindly help on what is the clear way for doing this (with db column grouping too)? with any sample attachment please.

with regards,

Krish TS

[Stef]

Hi Krish,

Reports are data-driven templates. To visualize data reports use expressions that represent data fields or other information. In order expressions to be evaluated correctly, the report's data must have the fields used in the report.

I can suggest you the following approaches depending on the data:

1. If data will have the same data schema (same fields):
   Design the report with the available Report Designers. At run-time you can get the data and assign it to the report or other nested data item. For example, consider the case illustrated in Changing the connection string dynamically according to runtime data,where at run-time data items' DataSource properties are updated (they can be entirely reassigned to custom data objects as well).
2. If data does not have the same fields, but the data-retrieval method can be adjusted to load records in a mock-up object that has always the same data schema(fields):
   The approach will be the same as in point 1.
3. If the data schema will vary always:
   In such case, the report template will have to be generated at run-time based on the fields available in the retrieved data.
   For the purpose, you can create the desired layout with test data in Visual Studio Report Designer. The generated report's designer.cs(vb) file will contain code that can be modified and adjusted for your needs.
   The attached code snippet illustrates how to create a report with Table item based on the retrieved data.

I hope this information is helpful.

Regards,
Stef
Telerik

[Krish]

Hi Stef,

That's a great input.

Let me analyze your suggestions and get back to you (for any).

Thanks for your wonderful prompt response . 

with regards,

Krish TS

[Krish]

Hi Stef,

Thanks for the input.
Need a clarification on the below.

Approach 3 (data schema will vary always) is the one I'm looking far and it meets my requirement. Thanks!
I modified the code (TableAtRunTimeCSharp-2016SP1Q1.zip) according to my need and can get the expected result.

Need to know, how to add group by fields to this dynamic report.

For example if the data fetched is like this,

-----------------------------------------------------
Employee              |       Department
-----------------------------------------------------
Alex                       |       Development
Benjamin               |       Admin
Kathy                     |       Development
Ethan                     |       Admin
Karen                     |       Admin

For me this dynamic report needs to be grouped based on the Department and the output should be as,

-----------------------------------------------------
Department              |       Employee
-----------------------------------------------------
Admin
                                         Benjamin
                                         Ethan
                                         Karen
-----------------------------------------------------
Development
                                         Alex
                                        Kathy

How can I achieve this with the same code your provided? Where should I make the change?

Kindly advice.

with regards,
Krish TS

[Stef]

Hello Krish,

Depending on the layout you want to achieve, the recommended approach is to start with the VS Report Designer and test data. the designer provides wizards easing the report designing. At the end, the report designer will serialize all changes in code in the report's designer.cs(vb) file.

The code can be modified for usage at run-time.


Details how to add groups in a Table item through the report designers are available in How to: Add groups to Table item and Crosstab item.

Regards,
Stef
Telerik

[Krish]

Hi Stef,

I followed as you said in the given link,

Telerik.Reporting.TableGroup group1 = new Telerik.Reporting.TableGroup();

group1.Name = "DepartmentID1";

group1.Groupings.Add(new Telerik.Reporting.Grouping("=Fields.DepartmentID"));

But surprisingly, I can see only one item per each group!!!

something like this for the data I have given in my above examples,

-----------------------------------------------------
Employee              |       Department
-----------------------------------------------------
Alex                       |       Development
Benjamin               |       Admin

What happened to the remaining rows? Why it is like this?

I will be great if you can provide me the actual modified code (TableAtRunTimeCSharp-2016SP1Q1.zip) for my requirement since,
it is taking too much of time to figure out on how to add the report groups dynamically.

Kindly advice.

with regards,
Krish TS

[Krish]

Hi Stef,

In the sample project provided by you,
Like to know, how to add grouping so that it groups the records based on 'col1' values.
The output format looking for is,

Col1            Col2
----------------------------------
1
                   11
                   10
2
                   22
                   200
3
                   33
                   30
                   300
4
                   44
                   40
                   400

I spent lot of time and tried adding the code as given by in previous link,
but it is NOT giving the output as expected (always it shows each groups 1 record only, not sure why!),

Telerik.Reporting.TableGroup group1 = new Telerik.Reporting.TableGroup();
group1.Name = "Col1";
group1.Groupings.Add(new Telerik.Reporting.Grouping("=Fields.Col1"));

Please let me know.
if you can modify the attachment ,that will be a real help for us.

NOTE:
I have renamed the 'TableAtRunTimeCSharp_Updated.zip' file
to 'TableAtRunTimeCSharp_Updated.gif' for attachment purpose.

Kindly rename it back to '.zip' for your review.

with regards,
Krish TS

[Stef]

Hello Krish,

Let assume we have the original demo project where row groups are defined as follows:

//create a dynamic row group
Telerik.Reporting.TableGroup DetailRowGroup = new Telerik.Reporting.TableGroup();
DetailRowGroup.Groupings.Add(new Telerik.Reporting.Grouping(null));
DetailRowGroup.Name = "DetailRowGroup";
table1.RowGroups.Add(DetailRowGroup);
//add a row container
table1.Body.Rows.Add(new Telerik.Reporting.TableBodyRow(Telerik.Reporting.Drawing.Unit.Inch(0.5)));

When we add a parent row group in the VS Report Designer through the Group Explorer, the auto-generated code looks as follows:

//extracted row groups definitions from designer.cs file
//the detail row group
tableGroup4.Groupings.Add(new Telerik.Reporting.Grouping(null));
tableGroup4.Name = "detail";
//the parent row group
tableGroup3.ChildGroups.Add(tableGroup4);
tableGroup3.Groupings.Add(new Telerik.Reporting.Grouping("= Fields.GroupName"));
tableGroup3.Name = "groupName";
tableGroup3.ReportItem = this.textBox7;//the grouping cell
this.table1.RowGroups.Add(tableGroup3);
//corner cell
 this.table1.Corner.SetCellContent(0, 0, this.textBox8);

Thus the modified code from the demo should looks as follows:

//create a dynamic row group
//corner cell
var cornerCellTextBox = new Telerik.Reporting.TextBox
{
    Name = "cornerCellTB",
    Value = "Corner",
    Size = new Telerik.Reporting.Drawing.SizeU(Telerik.Reporting.Drawing.Unit.Inch(1D), Telerik.Reporting.Drawing.Unit.Inch(0.5))
};
table1.Corner.SetCellContent(0, 0, cornerCellTextBox);
table1.Items.AddRange(new Telerik.Reporting.ReportItemBase[] { cornerCellTextBox });
//detail
Telerik.Reporting.TableGroup DetailRowGroup = new Telerik.Reporting.TableGroup();
DetailRowGroup.Groupings.Add(new Telerik.Reporting.Grouping(null));
DetailRowGroup.Name = "DetailRowGroup";
//parent row group
Telerik.Reporting.TableGroup ParentRowGroup = new Telerik.Reporting.TableGroup();
ParentRowGroup.Groupings.Add(new Telerik.Reporting.Grouping("=Fields.col1"));
ParentRowGroup.Name = "ParentRowGroup";
var groupCellTextBox = new Telerik.Reporting.TextBox
{
    Name = "groupCellTB",
    Value = "=Fields.col1",
    Size = new Telerik.Reporting.Drawing.SizeU(Telerik.Reporting.Drawing.Unit.Inch(1D), Telerik.Reporting.Drawing.Unit.Inch(0.5))
};
ParentRowGroup.ReportItem = groupCellTextBox;

table1.Items.AddRange(new Telerik.Reporting.ReportItemBase[] { groupCellTextBox });
ParentRowGroup.ChildGroups.Add(DetailRowGroup);
table1.RowGroups.Add(ParentRowGroup);
//add a row container
table1.Body.Rows.Add(new Telerik.Reporting.TableBodyRow(Telerik.Reporting.Drawing.Unit.Inch(0.5)));

Further adjustments can be made on column groups, where you can skip the already used for grouping field e.g.:

//add columns
           int cellOrder = 0;
           for (int i = 0; i <= data.Columns.Count - 1; i++)
           {
               if (data.Columns[i].ColumnName != "col1")
               {
                   //add a column container
                   table1.Body.Columns.Add(new Telerik.Reporting.TableBodyColumn(Telerik.Reporting.Drawing.Unit.Inch(2)));
                   //add a static column group per data field
                   Telerik.Reporting.TableGroup columnGroup = new Telerik.Reporting.TableGroup();
                   table1.ColumnGroups.Add(columnGroup);

                   //header textbox
                   Telerik.Reporting.TextBox headerTextBox = new Telerik.Reporting.TextBox();
                   headerTextBox.Name = "headerTextBox" + i.ToString();
                   headerTextBox.Size = new Telerik.Reporting.Drawing.SizeU(Telerik.Reporting.Drawing.Unit.Inch(2), Telerik.Reporting.Drawing.Unit.Inch(0.5));
                   headerTextBox.Value = data.Columns[i].ColumnName;
                   headerTextBox.Style.BackgroundColor = Color.Yellow;
                   headerTextBox.Style.BorderStyle.Default = Telerik.Reporting.Drawing.BorderType.Solid;
                   //  headerTextBox.Style.BorderWidth.Default = Telerik.Reporting.Drawing.Unit.Pixel(1);
                   columnGroup.ReportItem = headerTextBox;

                   //field that will be displayed
                   Telerik.Reporting.TextBox detailRowTextBox = new Telerik.Reporting.TextBox();
                   detailRowTextBox.Name = "detailRowTextBox" + i.ToString();
                   detailRowTextBox.Size = new Telerik.Reporting.Drawing.SizeU(Telerik.Reporting.Drawing.Unit.Inch(2), Telerik.Reporting.Drawing.Unit.Inch(0.5));
                   detailRowTextBox.Value = "= Fields.[" + data.Columns[i].ColumnName + "]";
                   table1.Body.SetCellContent(0, cellOrder++, detailRowTextBox);

                   //add the nested items in the Table.Items collection
                   table1.Items.AddRange(new Telerik.Reporting.ReportItemBase[] {
           headerTextBox,
           detailRowTextBox
       });
               }
           }

To verify the dynamically generated structure, you can serialize it in XML and then store it in a TRDX file that can b previewed in the Standalone Report Designer.


If you need further help, please use the support ticketing system, which guarantees you a response by Telerik representative in a timely manner.

Regards,
Stef
Telerik