Grid Calculated Column use column total as data field.

12 posts, 0 answers
  1. Sigifredo
    Sigifredo avatar
    9 posts
    Member since:
    Oct 2011

    Posted 19 Dec 2012 Link to this post


    Hi,

    I have a grid with a bound column "Sales" and need to add a calculated column "Percentage" which will be the result of  Sales/Sum(Sales)*100. How can I accomplish this?

    I've tried different ways but I can't get it to work. Below is an example of what i am trying to do...  

    <telerik:GridBoundColumn DataField="Sales" DataType="System.Decimal" HeaderText="Sales"
     SortExpression="Sales" UniqueName="Sales" Aggregate="Sum">                 
    </telerik:GridBoundColumn>
    <telerik:GridCalculatedColumn HeaderText="Sales %" UniqueName="Percentage"
    DataFields ="Sales, sum(Sales)" Expression="{0}/{1}" DataType="System.Double" Aggregate="Sum" >
    </telerik:GridCalculatedColumn>

    Thanks in advance,

    Regards,
    Sigi Perez
  2. Shinu
    Shinu avatar
    17764 posts
    Member since:
    Mar 2007

    Posted 19 Dec 2012 Link to this post

    Hi,

    aspx:
    <telerik:GridCalculatedColumn UniqueName="Name" HeaderText="Calculated Column" DataFields="Sales, Sum" DataFormatString="{0:d}%" Expression="{0}*10">
    </telerik:GridCalculatedColumn>

    Thanks,
    Shinu.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Sigifredo
    Sigifredo avatar
    9 posts
    Member since:
    Oct 2011

    Posted 20 Dec 2012 Link to this post

    Hi Shinu,

    Thanks for the response but I don't think what you send me is what I am looking for. Maybe, I wasn't clear on the issue I am having... 

    I have a grid with one bound column "sales" and has the aggregate=Sum (see below)... 

    <telerik:GridBoundColumn DataField="Sales" DataType="System.Decimal" HeaderText="Sales"
      SortExpression="Sales" UniqueName="Sales" Aggregate="Sum">                 
    </telerik:GridBoundColumn>

     I need a calculated column to show the percentage of the "sales". The calculation will be based on the "sales" divided by "the total sum of sales"...

    Example: 
    Sales Sales %
    34,995 5.2%
    2,332 0.3%
    387,515 57.7%
    4,198 0.6%
    243,122 36.2%
    672,162
    100.0%


    When I use the sample you send me I get and the error: Cannot find column [Sum]. 


    Thanks 
    Sigi Perez
  5. Shinu
    Shinu avatar
    17764 posts
    Member since:
    Mar 2007

    Posted 21 Dec 2012 Link to this post

    Hi,

    Please try following code snippet to show Percentage value in GridCalculatedColumn.

    C#:
    protected void RadGrid1_PreRender(object sender, EventArgs e)
    {
        GridFooterItem footerItem = (GridFooterItem)RadGrid1.MasterTableView.GetItems(GridItemType.Footer)[0];
        string footer = footerItem["Number"].Text;
        string sum = Regex.Replace(footer, "[^0-9]+", string.Empty);
        double sumvalue = Convert.ToInt32(sum);
        foreach (GridDataItem DataItem in RadGrid1.Items)
        {
            string BoundColumnValue = DataItem["Sales"].Text;
            double value = Convert.ToInt32(BoundColumnValue);
            double Calculate = (value / sumvalue) * 100;
            DataItem["Percentage"].Text = (Calculate.ToString()+"%");
            
        }
    }

    Thanks,
    Shinu.
  6. Sigifredo
    Sigifredo avatar
    9 posts
    Member since:
    Oct 2011

    Posted 21 Dec 2012 Link to this post

    Thanks Shinu.

    This should do the trick. I will implement it on my project but for what i see it will work.

    Regards,

    Sigi
  7. Manish
    Manish avatar
    10 posts
    Member since:
    Oct 2012

    Posted 08 Mar 2013 Link to this post

    I have a related problem

    I am using a Grid Calculated Column for Calculating the Percentage

     

     

    DataFields="B,A"

     


    So my

    Expression

     

     

    ="(({0}-{1})/{1})*100"
    SO there are SCenarios where A is Zero , so how to handle the Divided by Zero Problem here.

    also there are scenarios where B-A is a Negative Number  as a numerator , so how to handle the percent calculation for -Ve number.



    I dont want to do all this on ItemDataBound Event, so i used this Grid Calculayed Column which is much cleaner.

    Any help on this will be appreciated.

    Thanks
    Manish

     

  8. Shinu
    Shinu avatar
    17764 posts
    Member since:
    Mar 2007

    Posted 10 Mar 2013 Link to this post

    Hi,

    Check the following forum thread which discussed similar scenario.
    Divide By Zero On RadCalculatedColumn

    Thanks,
    Shinu
  9. Manish
    Manish avatar
    10 posts
    Member since:
    Oct 2012

    Posted 14 Mar 2013 Link to this post

    can you please provide any solution to export the GridCalculateColumn when doing export to excel , without doing the manual calculation in the app code.
     my expression is ="(({0}-{1})/{1})*100"

    so suppose -( (10-5)/5) * 100)  , it should be 100 %  , but it shows 1 % , so somehow , its not multiplying by 100 when exported to excel

    is there  a simple/ automatic way to export the gridCalculateColumn to Excel

    any help is appreciated

    Thanks
    Manish
  10. Kostadin
    Admin
    Kostadin avatar
    1709 posts

    Posted 19 Mar 2013 Link to this post

    Hi Manish,

    Please check out the following forum post.

    Greetings,
    Kostadin
    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.
  11. Manish
    Manish avatar
    10 posts
    Member since:
    Oct 2012

    Posted 20 Mar 2013 Link to this post

    everything is same for me  , but export to excel is not showing the percent column properly , its not doing part of the calculation when exporting to excel. also found that EqualTo filter is also not working for GridCalculated column with 

     

    DataFormatString

     

     

    ="{0:###,##0.00}%"   or

     

    DataFormatString

     

     

    ="{0:c}%"

     

     

     



    do you see any problem in this expression

     

    Expression

     

     

    ="iif({1}=0,0,iif(({0}-{1})=0,0,(({0}-{1})/{1})*100))"

     



    however this expression is giving me a perfect output on Grid display

    see the entire column

    <

     

     

    telerik:GridBoundColumn UniqueName="A" DataField="A" HeaderText="A"

     

     

     

    ItemStyle-HorizontalAlign="Right" HeaderStyle-HorizontalAlign="Right" DataType="System.Decimal" Visible="true"

     

     

     

    AllowFiltering="true" FilterControlWidth="45px" DataFormatString="${0:###,##0.00}" />

     

     

     

    <telerik:GridBoundColumn UniqueName="B" DataField="B" HeaderText="B"

     

     

     

    ItemStyle-HorizontalAlign="Right" HeaderStyle-HorizontalAlign="Right" DataType="System.Decimal" Visible="true"

     

     

     

    AllowFiltering="true" FilterControlWidth="45px" DataFormatString="${0:###,##0.00}" />

     



    <

     

     

    telerik:GridCalculatedColumn HeaderText="Percent" HeaderStyle-HorizontalAlign="Right" ItemStyle-HorizontalAlign="Right"

     

     

     

    UniqueName="Percent" DataType="System.Double"

     

     

     

    DataFields="A, B" Expression="iif({1}=0,0,iif(({0}-{1})=0,0,(({0}-{1})/{1})*100))" FooterText="" AllowFiltering="true" FilterControlWidth="30px" Aggregate="Avg"

     

     

     

    DataFormatString="{0:###,##0.00}%" FooterStyle-HorizontalAlign="Right" FooterStyle-Font-Bold="true" />

     


    tried DataType="System.Double" too, no luck
    any idea?
  12. Manish
    Manish avatar
    10 posts
    Member since:
    Oct 2012

    Posted 21 Mar 2013 Link to this post

    Solved the equal to filter issue mentioned above  by using the convert in the expression, now using

    Expression="iif({1}=0,0,iif(({0}-{1})=0,0, CONVERT( (({0}-{1})/{1})*10000, System.Int64 ) / 100  ))"

  13. Kostadin
    Admin
    Kostadin avatar
    1709 posts

    Posted 25 Mar 2013 Link to this post

    Hi Manish,

    An answer could be found at following forum post.

    Please note that opening multiple forum threads with the same issue makes tracking the tickets history very hard. In this regard, I suggest you to stick to the other forum post and close this one. This will enable us to respond more quickly and efficiently to your posts.

    Greetings,
    Kostadin
    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