Pivot table programatically

[Subrahmanya Kadiyala]

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..

[IvanY]

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

[Subrahmanya Kadiyala]

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,

[IvanY]

Hi Subrahmanya,

The code snippet seems to be correct but we cannot tell for sure since we do not have the whole code. Please also note that this cannot be the full code of the table since you are missing the static rows/columns here. Therefore we can advise you to open a support ticket and attach your report there so that we are able to view it locally and advise you more accordingly.

Regards,
IvanY
Telerik

[Subrahmanya Kadiyala]

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,

[IvanY]

Hi Subrahmanya,

Thank you for the provided sample. However please note that it will be best if you are able to send us a sample report definition - it will give us the option to examine the whole code locally and debug it if needed. Therefore we can suggest you to open a support ticket and send us a runnable sample that exhibits the issue so that we are able to advise you more accordingly.,

Regards,
IvanY
Telerik

[Subrahmanya Kadiyala]

Hi Ivan,

I raised a support ticket and attached the entire code the support ticket ID is 715615.

Thanks,