Sorting data in PivotGrid

[Simon]

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

[Andrey]

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

[Simon]

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. 

[Andrey]

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

[Simon]

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

[Andrey]

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