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

Sorting data in PivotGrid

5 Answers 222 Views
PivotGrid
This is a migrated thread and some comments may be shown as answers.
Simon
Top achievements
Rank 1
Simon asked on 03 Oct 2012, 10:18 AM
Hi,

Is it possible to sort aggregateField in Server-side.

I tried to add PivotGridSortExpression but that doesn't work.

var aggregateField = new PivotGridAggregateField();
rpgCA.Fields.Add(aggregateField);
aggregateField.DataField = "CA";
aggregateField.UniqueName = "CA";
aggregateField.DataFormatString = "{0:C0}";
aggregateField.TotalFormatString = "{0:C0}";
aggregateField.GrandTotalAggregateFormatString="{0:C0}";
aggregateField.TotalFormatString = "{0:C0}";
 
 
rpgCA.Rebind();

Thank you for your reply

Simon Chamaillard

5 Answers, 1 is accepted

Sort by
0
Andrey
Telerik team
answered on 08 Oct 2012, 12:30 PM
Hello,

Where you are executing this code? You could try to use the RadPivotGrid SortExpressions collection and the AddSortExpression method:

pivotGrid.SortExpressions.AddSortExpression();

Then check whether you get the expected behavior.

Regards,
Andrey
the Telerik team
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
0
Simon
Top achievements
Rank 1
answered on 08 Oct 2012, 01:29 PM
Thank you for your reply,

I pasted a wrong code...

I tried with the code below:
var sortField = new PivotGridSortExpression();
sortField.FieldName = "NbCommande";
sortField.SortOrder = PivotGridSortOrder.Ascending;
rpgCA.SortExpressions.Add(sortField);
sortField = new PivotGridSortExpression();
sortField.FieldName = "NomClient";
sortField.SortOrder = PivotGridSortOrder.Ascending;
rpgCA.SortExpressions.Add(sortField);

that doesn't work...

I want to sort tha data not the rows or the columns.

I have programmed the PivotGrid on button click, the pivot grid can change dynamically. 
0
Andrey
Telerik team
answered on 09 Oct 2012, 01:54 PM
Hello,

Where you are executing this code? If you are executing on PreRender stage you need to Rebind RadPivotGrid in order to get the new sort order.

This code works on my side:

protected void Page_PreRender(object sender, EventArgs e)
{
    var sortField = new PivotGridSortExpression();
    sortField.FieldName = "Year";
    sortField.SortOrder = PivotGridSortOrder.Ascending;
    RadPivotGrid1.SortExpressions.Add(sortField);
    RadPivotGrid1.Rebind();
}

Give this approach a try and check whether you get the desired behavior.

All the best,
Andrey
the Telerik team
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
0
Simon
Top achievements
Rank 1
answered on 09 Oct 2012, 02:46 PM
Hello Andrey,


I program the PivotGrid in serve-side.
To populate the PG, the user has to check Checboxes to select row and aggregate fields.
I add two RadioButton with which the user can sort by the aggregate chosen field.
After that, he has to click on a button.


The onClick action populates the PG with the selected data. (that's work !)
At the end of the onClick action I do a rpg.Rebind() but when the user has clicked on a RadioButton, the PG is not well sorted...

protected void rpgCA_NeedDataSource(object sender, PivotGridNeedDataSourceEventArgs e)
{
    Bind();
}
 
private void Bind()
{
    var liste = new List<Stat>();
 
    var requete = new StringBuilder();
    requete.Append("SELECT distinct DATE(c.dateCommande) as date, DATE_FORMAT(c.dateCommande, '%d/%m/%y') as dateCourt, DATE_FORMAT(c.dateCommande, '%H') as heure, DATE_FORMAT(c.dateCommande, '%d %m %Y %H') as dateHeure, cl.codeClient, cl.nomClient, if(cl.representant='', s.nom, cl.representant) as representant, m.nom as nomMarque, s.codeSociete, s.nom as nomSociete, wf.codeFamille, wf.nom as nomFamille, b.idBPI, b.nom as nomBPI, g.nom as nomGroupe, c.idCommande as nbCommande, SUM(lc.prixNet_HT) as CA FROM ccp_commandes c INNER JOIN ccp_lignescommandes lc ON lc.idCommande = c.idCommande INNER JOIN ccp_clients cl ON cl.idClient = c.idClient LEFT JOIN ccp_bpi b ON b.idBPI = c.idBPI LEFT JOIN ccp_salons sa ON sa.idSalon = b.idSalon AND b.idSalon=?idSalon LEFT JOIN FINDIS_Societes s ON s.codeSociete = cl.codeSoc LEFT JOIN FINDIS_Articles a ON a.idArticle = lc.idArticle LEFT JOIN FINDIS_Fournisseurs f ON f.idFournisseur =  a.idFournisseur LEFT JOIN FINDIS_Marques m ON m.idMarque = f.idMarque LEFT JOIN WINNIX_Familles wf ON wf.codeFamille = a.codeFamilleWinnix LEFT JOIN FINDIS_Groupes g ON g.idGroupe = b.idGroupe ");
    requete.AppendFormat("WHERE c.Validee=1 AND DATE(c.dateCommande) BETWEEN ?debut AND ?fin {0}", (Contexte.Utilisateur.IdGroupe > 0) ? string.Format(" AND g.idGroupe={0} ", Contexte.Utilisateur.IdGroupe) : string.Empty);
 
    requete.Append("GROUP BY c.idCommande ");
 
    using (var myCon = new MySqlConnection(ConfigurationManager.ConnectionStrings["blabla"].ConnectionString))
    {
        myCon.Open();
 
        using (var myCmd = new MySqlCommand(requete.ToString(), myCon))
        {
            myCmd.Parameters.AddWithValue("?idSalon", rcbSalon.SelectedValue);
            myCmd.Parameters.AddWithValue("?debut", rdpDebut.SelectedDate.Value.ToString("yyyy-MM-dd"));
            myCmd.Parameters.AddWithValue("?fin", rdpFin.SelectedDate.Value.ToString("yyyy-MM-dd"));
            using (var myReader = myCmd.ExecuteReader())
            {
                while (myReader.Read())
                {
                    var stat = new Stat();
 
                    stat.Date = myReader.GetDateTime("date").ToShortDateString();
                    stat.Heure = myReader.GetString("heure");
                    stat.CodeClient = myReader.GetString("codeClient");
                    stat.NomClient = myReader.GetString("nomClient");
                    stat.Representant = myReader.GetString("representant");
                    stat.NomMarque = myReader.GetString("nomMarque");
                    stat.CodeSociete = myReader.GetString("codeSociete");
                    stat.NomSociete = myReader.GetString("nomSociete");
                    stat.CodeFamille = myReader.GetString("codeFamille");
                    stat.NomFamille = myReader.GetString("nomFamille");
                    stat.IdBPI = myReader.GetString("idBPI");
                    stat.NomBPI = myReader.GetString("nomBPI");
                    stat.NomGroupe = !DBNull.Value.Equals(myReader["nomGroupe"]) ? myReader.GetString("nomGroupe") : string.Empty;
                    stat.NbCommande = myReader.GetString("nbCommande");
                    stat.CA = !DBNull.Value.Equals(myReader["CA"]) ? myReader.GetString("CA") : "0";
 
                    liste.Add(stat);
                }
            }
        }
    }
 
    rpgCA.DataSource = liste;
}
 
protected void btValider_Click(object sender, EventArgs e)
{
    // Vider la radPivotGrid
    rpgCA.Fields.Clear();
 
    // Afficher les lignes sᅢᄅleectionnᅢᄅes
    if (cbNomClient.Checked)
    {
        var rowField = new PivotGridRowField();
        rpgCA.Fields.Add(rowField);
        rowField.DataField = "NomClient";
        rowField.UniqueName = "NomClient";
        rowField.Caption = "Nom client";
    }
    if (cbMarque.Checked)
    {
        var rowField = new PivotGridRowField();
        rpgCA.Fields.Add(rowField);
        rowField.DataField = "NomMarque";
        rowField.UniqueName = "NomMarque";
        rowField.Caption = "Nom marque";
 
    }
    if (cbFamille.Checked)
    {
        var rowField = new PivotGridRowField();
        rpgCA.Fields.Add(rowField);
        rowField.DataField = "NomFamille";
        rowField.UniqueName = "NomFamille";
        rowField.Caption = "Nom famille";
    }
    if (cbSociete.Checked)
    {
        var rowField = new PivotGridRowField();
        rpgCA.Fields.Add(rowField);
        rowField.DataField = "NomSociete";
        rowField.UniqueName = "NomSociete";
        rowField.Caption = "Nom sociᅢᄅtᅢᄅ";
    }
    if (cbRepresentant.Checked)
    {
        var rowField = new PivotGridRowField();
        rpgCA.Fields.Add(rowField);
        rowField.DataField = "Representant";
        rowField.UniqueName = "Representant";
        rowField.Caption = "Reprᅢᄅsentant";
    }
    if (cbBPI.Checked)
    {
        var rowField = new PivotGridRowField();
        rpgCA.Fields.Add(rowField);
        rowField.DataField = "NomBPI";
        rowField.UniqueName = "NomBPI";
        rowField.Caption = "BPI";
 
    }
    if (cbJour.Checked)
    {
        var rowField = new PivotGridRowField();
        rpgCA.Fields.Add(rowField);
        rowField.DataField = "Date";
        rowField.UniqueName = "Date";
        rowField.Caption = "Jour";
    }
    if (cbHeure.Checked)
    {
        var rowField = new PivotGridRowField();
        rpgCA.Fields.Add(rowField);
        rowField.DataField = "Heure";
        rowField.UniqueName = "Heure";
        rowField.Caption = "Heure";
    }
 
    // Afficher les donnᅢᄅes sᅢᄅlectionnᅢᄅes
    if (cbNbCommande.Checked)
    {
        var aggregateField = new PivotGridAggregateField();
        rpgCA.Fields.Add(aggregateField);
        aggregateField.DataField = "NbCommande";
        aggregateField.UniqueName = "NbCommande";
        aggregateField.Caption = "Nb commande";
        aggregateField.Aggregate = PivotGridAggregate.Count;
        aggregateField.GrandTotalAggregateFormatString = "{0}";
        aggregateField.TotalFormatString = "{0}";
    }
    if (cbCA.Checked)
    {
        var aggregateField = new PivotGridAggregateField();
        rpgCA.Fields.Add(aggregateField);
        aggregateField.DataField = "CA";
        aggregateField.UniqueName = "CA";
        aggregateField.Aggregate = PivotGridAggregate.Sum;
        aggregateField.DataFormatString = "{0:C0}";
        aggregateField.TotalFormatString = "{0:C0}";
        aggregateField.GrandTotalAggregateFormatString = "{0:C0}";
    }
 
    if (rbTriNbCommande.Checked)
    {
        rpgCA.SortExpressions.Clear();
        var sortField = new PivotGridSortExpression();
        sortField.FieldName = "NbCommande";
        sortField.SortOrder = PivotGridSortOrder.Ascending;
        rpgCA.SortExpressions.Add(sortField);
 
    }
 
    rpgCA.Rebind();
}

I just tried the onPreRender stage, but it's doesn't work...
Although, My data are not sorted, the PG sort automatically by row field.

Thank You for your help
0
Andrey
Telerik team
answered on 10 Oct 2012, 01:57 PM
Hello,

You could try to set the DataSource property of RadPivotGrid to  the null value prior firing the Rebind method. This will force the NeedDataSource method to be called and thus your code logic will be executed in accordance.

Give this approach a try and check whether you get the desired behavior.

Regards,
Andrey
the Telerik team
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
Tags
PivotGrid
Asked by
Simon
Top achievements
Rank 1
Answers by
Andrey
Telerik team
Simon
Top achievements
Rank 1
Share this question
or