Using Pivot Tables in Relational Tables

[Mustafa Kuyucu]

Hi,

I need to design a page using Blazor Pivot Table. On the examples, I could not see an example designed with relational tables. 

I need to create a pivot table by linking my relational tables to the pivot table.

Help!

Thanks in advance

[Rajeshkannah G]

Hi Mustafa,

We believe that your requirement is to bind a relational table from a database; you can bind data from a database table to a pivot table. Please look at the following UG documents to learn how to connect Blazor Pivot tables to different types of databases.

MySQL : https://blazor.syncfusion.com/documentation/pivot-table/connecting-to-data-source/mysql

MS SQL: https://blazor.syncfusion.com/documentation/pivot-table/connecting-to-data-source/microsoft-sql-server

PostgreSQL: https://blazor.syncfusion.com/documentation/pivot-table/connecting-to-data-source/postgresql

Oracle:  https://blazor.syncfusion.com/documentation/pivot-table/connecting-to-data-source/oracledb

MongoDB : https://blazor.syncfusion.com/documentation/pivot-table/connecting-to-data-source/mongodb

Elasticsearch : https://blazor.syncfusion.com/documentation/pivot-table/connecting-to-data-source/elasticsearch

SnowFlake: https://blazor.syncfusion.com/documentation/pivot-table/connecting-to-data-source/snowflakedb

Furthermore, if you want to connect relational data from a local data table to the Blazor Pivot table, we are looking into the possibilities and will provide further details tomorrow (2 August 2023).

Please let us know if you have any queries.

Regards,

Rajeshkannah G

[Rajeshkannah G]

Hi Mustafa,

Thanks for the patience. Because the DataSource property is of the IEnnumerable type, our Pivot Table does not directly accept the DataTable as a datasource. Thus, to bind the local data table to the Pivot Table, you need convert them to a collection of a defined class type and then bind it to the Pivot Table. Please look at the code example below. In this example, we have converted the data table to a list of specific type based on the fields available and bind it to the Pivot table.

Code Example:

@page "/c"

@using Syncfusion.Blazor.PivotView;

@using System.Data;

@using System.ComponentModel;

 

<SfPivotView TValue="Employee">

    <PivotViewDataSourceSettings TValue="Employee" DataSource="dataSource">

    </PivotViewDataSourceSettings>   

</SfPivotView>

 

 

@code {

    private List<Employee> dataSource { get; set; }

 

    protected override void OnInitialized()

    {

        dataSource = Employee.GetDataTableAsList();

    }

 

    public class Employee

    {

        public int ID { get; set; }

        public int Age { get; set; }

        public string Name { get; set; }

 

        public static List<Employee> GetDataTableAsList()

        {

            DataTable dataTable = GetDataTable();

            return (from DataRow data in dataTable.Rows

                    select new Employee()

                        {

                            ID = Convert.ToInt32(data["ID"]),

                            Age = Convert.ToInt32(data["Age"]),

                            Name = data["Name"].ToString(),

                        }).ToList();

 

        }

    }

 

    private static DataTable GetDataTable()

    {

        // Create a new DataTable

        DataTable dataTable = new DataTable("SampleDataTable");

 

        // Define columns for the DataTable

        dataTable.Columns.Add("ID", typeof(int));

        dataTable.Columns.Add("Name", typeof(string));

        dataTable.Columns.Add("Age", typeof(int));

 

        // Add rows to the DataTable

        dataTable.Rows.Add(1, "John Doe", 30);

        dataTable.Rows.Add(2, "Jane Smith", 25);

        dataTable.Rows.Add(3, "Bob Johnson", 40);

 

        return dataTable;

    }

}

 

Meanwhile, we have prepared a sample for your reference. Please find it in the attachments.

Regards,

Rajeshkannah G

Attachment: Pivottable_2c077e67.zip