This is a migrated thread and some comments may be shown as answers.

Pivot table programatically

6 Answers 538 Views

General Discussions

This is a migrated thread and some comments may be shown as answers.

Subrahmanya Kadiyala asked on 06 Jun 2013, 11:31 PM

pivot-table.png

Hi,

I am trying to use column groups and row groups to pivot the data programatically. Following is the code snippet I am working on
crosstab1.RowGroups.Add(tableGrouprow);
                textboxGroup1 = new Telerik.Reporting.TextBox();
                textboxGroup1.Style.BorderColor.Default = Color.Black;
                textboxGroup1.Style.BorderStyle.Default = BorderType.Solid;
                textboxGroup1.Value = "=Fields." + ds.Tables[0].Columns[0].ColumnName;/***giving column index on the column we want to group****/
textboxGroup1.Size = new SizeU(Unit.Inch(1.1), Unit.Inch(0.3));
                tableGrouprow.ReportItem = textboxGroup1;

but i am unable to do grouping.I have attached the screen shot of the table I want to pivot.I want pivot on month(column group) and drug(row group).I couldn't find any sample projects on this.can you please give some hint on this or a working sample would be more useful.

Thanks you..

6 Answers, 1 is accepted

answered on 11 Jun 2013, 01:40 PM

Hi Subrahmanya,

The best option to learn how to create such tables programmatically would be to have a look at the InitializeComponent(). I will try here to explain the basics, but you will still have to play a little with the generated code so that you can achieve your specific task.

This is how you can add static columns (and rows, respectively):

this.crosstab1.Body.Columns.Add(new TableBodyColumn(Unit.Inch(1.15D)));

Each table and crosstab need at least a single static row and column, otherwise an exception will be thrown.

This is how you create a table group. A table group can span multiple columns or rows at runtime (it is not static):

tableGroup1.Groupings.AddRange(new Grouping[] { new Grouping("=Fields.City") });

tableGroup1.Name = "City1";

tableGroup1.ReportItem = this.textBox1;

tableGroup1.Sortings.AddRange(new Sorting[] { new Sorting("=Fields.City", SortDirection.Asc) });

Once you have created the table group you will have to add it to the respective row or column group in your crosstab. This is the code:

this.crosstab1.RowGroups.Add(tableGroup2);

This sets the content of the corner of the crosstab:

this.crosstab1.Corner.SetCellContent(0, 0, this.textBox3);

This sets a selected cell content:

this.crosstab1.Body.SetCellContent(0, 0, this.textBox4);

You will have to add all items to the crosstab regardless of the fact that you have already added them to a table group or a cell:

this.crosstab1.Items.AddRange(new Telerik.Reporting.ReportItemBase[] { this.textBox1, this.textBox2, this.textBox3, this.textBox4 });

The below snippet sets some easy to understand properties, like size, data source, etc:

this.crosstab1.DataSource = this.sqlDataSource1;

this.crosstab1.Location = new PointU(Unit.Inch(0.7D), Unit.Inch(0.6D));

this.crosstab1.Name = "crosstab1";

this.crosstab1.Size = new SizeU(Unit.Inch(2.3D), Unit.Inch(1.7D));

The following sets the style of the crosstab (you can view in the crosstab wizard the predefined tabs):

this.crosstab1.StyleName = "Normal.TableNormal";

You can also create nested table groups. The basic idea is that you create two table groups and then add one of them as child group to the second one. Then you add the second table group as row or column group to the crosstab. Check this code to see how this is done:

// Create the first table group

tableGroup1.Groupings.AddRange(new Grouping[] {newGrouping("=Fields.AddressLine2")});

tableGroup1.Name = "AddressLine21";

tableGroup1.ReportItem = this.textBox1;

tableGroup1.Sortings.AddRange(new Sorting[] {new Sorting("=Fields.AddressLine2", SortDirection.Asc)});

// Create the second table group and add the first one as child

tableGroup2.ChildGroups.Add(tableGroup1);

tableGroup2.Groupings.AddRange(new Grouping[] {new Grouping("=Fields.AddressLine1")});

tableGroup2.Name = "AddressLine11";

tableGroup2.ReportItem = this.textBox2;

tableGroup2.Sortings.AddRange(new Sorting[] {new Sorting("=Fields.AddressLine1", SortDirection.Asc)});

// Add the second (parent) group to the column or row groups of the crosstab

this.crosstab1.ColumnGroups.Add(tableGroup2);

Regards,
IvanY
Telerik

Have you tried the new visualization options in Telerik Reporting Q1 2013? You can get them from your account.

answered on 11 Jun 2013, 05:13 PM

Hi Ivan,

Thanks for replying

I tried the code you suggested.Following is the code snippet I worked

Telerik.Reporting.TableGroup tableGroup2 = new Telerik.Reporting.TableGroup();
tableGroup2.Groupings.AddRange(new Grouping[] { new Grouping("=Fields.Month") });
tableGroup2.Name = "City1";
tableGroup2.ReportItem = this.textBox1;
tableGroup2.Sortings.AddRange(new Sorting[] { new Sorting("=Fields.Month", SortDirection.Asc) });

this.crosstab1.RowGroups.Add(tableGroup2);

this.crosstab1.Corner.SetCellContent(0, 0, this.textBox3);
this.crosstab1.Body.SetCellContent(0, 0, this.textBox4);
this.crosstab1.Items.AddRange(new Telerik.Reporting.ReportItemBase[] { this.textBox1});

I am getting the following error

"An error has occurred while processing Table 'crosstab1': Table Body has 1 rows but 2 are expected"

thanks,

answered on 08 Jul 2013, 06:56 PM

Hi Ivan,

Thanks for the response.Here is the full codefor the table.I am defining the rows and columns programatically.

private void crosstab1_ItemDataBinding(object sender, EventArgs e)
{

Telerik.Reporting.Processing.Table processingTable = (sender as Telerik.Reporting.Processing.Table);

Telerik.Reporting.TextBox textboxGroup;
Telerik.Reporting.TextBox textboxGroup1;
Telerik.Reporting.TextBox textBoxTable;

string connectionString = "Data Source=10.0.0.18;Initial Catalog=sample;User ID=sqlserver2;Password=sqlp42";

// string selectString = "SELECT " + FormatArray((object[])processingTable.Report.Parameters["TableColumns"].Value) + " FROM " + processingTable.Report.Parameters["TableName"].Label;
String selectString = @"SELECT * FROM discount1";

SqlDataAdapter sqlAdapter = new SqlDataAdapter(selectString, connectionString);
DataSet ds = new DataSet();
sqlAdapter.Fill(ds);
processingTable.DataSource = ds.Tables[0];

int ColCount = ds.Tables[0].Columns.Count;
int z = ds.Tables[0].Rows.Count;
Telerik.Reporting.TableGroup tableGroup2 = new Telerik.Reporting.TableGroup();
tableGroup2.Groupings.AddRange(new Grouping[] { new Grouping("=Fields.Month") });
tableGroup2.Name = "City1";
tableGroup2.ReportItem = this.textBox1;
tableGroup2.Sortings.AddRange(new Sorting[] { new Sorting("=Fields.Month", SortDirection.Asc) });

this.crosstab1.RowGroups.Add(tableGroup2);

this.crosstab1.Corner.SetCellContent(0, 0, this.textBox3);
this.crosstab1.Body.SetCellContent(0, 0, this.textBox4);
this.crosstab1.Items.AddRange(new Telerik.Reporting.ReportItemBase[] { this.textBox1 });
for (int i = 0; i <= ColCount - 1; i++)
{

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

crosstab1.ColumnGroups.Add(tableGroupColumn);

textboxGroup = new Telerik.Reporting.TextBox();
textboxGroup.Style.BorderColor.Default = Color.Black;
textboxGroup.Style.BorderStyle.Default = BorderType.Solid;
textboxGroup.Value = ds.Tables[0].Columns[i].ColumnName;
textboxGroup.Size = new SizeU(Unit.Inch(1.1), Unit.Inch(0.3));
tableGroupColumn.ReportItem = textboxGroup;

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

}

}

can you please give me a sample application that has the pivot table created dynamically.

Thanks,