Hi Karl Grambow,
Based on your latest update, you need to display data in a matrix style with categories, subcategories, and month-year, with a UI similar to an OLAP grid that includes the ability to expand categories. We recommend using the PivotGridControl for this purpose. One correction to your latest update is that you don’t need a cube to load data into the PivotGridControl. You can use a table fetched from the SQL database and load it directly into the PivotGridControl. Additionally, you do not need to know the number of columns available in the database beforehand; the control will automatically generate columns based on the available column.
Here, we have used a table structured as follows:
Please find the code snippet and image reference below:
Code Snippet:
public MainWindow() { InitializeComponent(); dataTable = new DataTable(); string connectionString = "Your_ConnectionString"; using (SqlConnection connection = new SqlConnection(connectionString)) { string query = "SELECT * FROM PivotGridSource"; connection.Open(); // Create a SqlCommand to execute the query using (SqlCommand command = new SqlCommand(query, connection)) { // Create a SqlDataAdapter to fill the DataTable using (SqlDataAdapter adapter = new SqlDataAdapter(command)) { // Fill the DataTable with the results of the query adapter.Fill(dataTable); } } } pivotGrid.ItemSource = dataTable; } |
Image Reference:
We have prepared a sample based on your requirements. Please find it attached.
If this solution does not meet your needs, we will need some details about the column structure of the table in the SQL database, such as how the columns and their values are organized. We understand you may not know the exact number of columns, but could you please confirm if the table you have is structured as shown below? If not, please provide details about the column structure in your database, if possible.
|
Category |
Sub category |
June 2024 |
July 2024 |
August 2024 |
September 2024 |
|
|
|
|
|
|
|
Regards,
Sreemon Premkumar M.
Hi Sreemon,
I just wanted to follow up on this and let you know that I'm successfully using the PivotGrid and it gives me what I need. However, I have few implementation questions that I was hoping you could help with.
I'm loading in 20,000 plus records and so:
<syncfusion:PivotGridControl x:Name="gridSalesByCategory" Grid.Row="2" Grid.RowSpan="8"
ItemSource="{Binding SalesByCategory}"
AutoSizeOption="None"
SortOption="All"
>
<syncfusion:PivotGridControl.PivotRows>
<syncfusion1:PivotItem ShowSubTotal="False" FieldHeader="Category" FieldMappingName="Category" TotalHeader="Total" />
<syncfusion1:PivotItem ShowSubTotal ="False" FieldHeader="Subcategory" FieldMappingName="SubCategory" TotalHeader="Total" />
<syncfusion1:PivotItem ShowSubTotal="False" FieldHeader="Region" FieldMappingName="Region" TotalHeader="Total" />
<syncfusion1:PivotItem ShowSubTotal="False" FieldHeader="Product" FieldMappingName="Product" TotalHeader="Total" />
</syncfusion:PivotGridControl.PivotRows>
<syncfusion:PivotGridControl.PivotCalculations>
<syncfusion1:PivotComputationInfo AllowSort="True" FieldName="Spend" Format="C" SummaryType="Sum"/>
<!--<syncfusion:PivotComputationInfo CalculationName="Total" FieldName="Quantity" SummaryType="Count" />-->
</syncfusion:PivotGridControl.PivotCalculations>
</syncfusion:PivotGridControl>
