Sorting data in PivotGrid

6 posts, 0 answers
  1. Simon
    Simon avatar
    6 posts
    Member since:
    Oct 2012

    Posted 03 Oct 2012 Link to this post

    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
  2. Andrey
    Admin
    Andrey avatar
    836 posts

    Posted 08 Oct 2012 Link to this post

    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.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Simon
    Simon avatar
    6 posts
    Member since:
    Oct 2012

    Posted 08 Oct 2012 Link to this post

    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. 
  5. Andrey
    Admin
    Andrey avatar
    836 posts

    Posted 09 Oct 2012 Link to this post

    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.
  6. Simon
    Simon avatar
    6 posts
    Member since:
    Oct 2012

    Posted 09 Oct 2012 Link to this post

    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
  7. Andrey
    Admin
    Andrey avatar
    836 posts

    Posted 10 Oct 2012 Link to this post

    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.
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017