Summing calculated columns in group by in Sharepoint list + Dataview webpart

Posted: July 1, 2010 in Customization, Data View Webpart

When ever we create a view for a list there will be a section called Totals. We can use this section to calculate & display the total of the items in the list. This will be useful to display totals particularly when you use group-by.If you notice, you will be seeing some column names with a drop-down nearby with option “Count”. If you select this the count will be based on this column. But if you are having a calculated column in your list, it will not be displayed here. So in this case we need to create a data view web part using designer. In this case you can do total and other operations like sum etc..

  1. First create a Data view web part and apply grouping if needed (check this post).
  2. Don’t forget to select “Show Group Footer” in the Sort & group properties.
  3. Now you will get the data view displaying the list with totals for each group (as in above picture with count).
  4. If you notice the code related to count is Count : <xsl:value-of select=”count($nodeset)” />

Now I am having another column Man Hours in my list which is of Type Number. I want t0 display the sum of man hours for each group (in my case for each location i need to find the total man hours).

To do so I just replace Count : <xsl:value-of select=”count($nodeset)” /> with Total Man Hours : <xsl:value-of select=”sum($nodeset/@Man_x0020_Hours)” />

Now my list view looks like

You can notice Total Man Hours displaying total man hours for each location. You can try more functions other than Sum :)

About these ads
Comments
  1. sridhar says:

    awesome dude , you have saved me today

  2. Ahmad says:

    You made my day.. wonderful way to explain the process.

  3. Emmanuel Tanares says:

    Nice Post.
    Can you sort the groups by Cont Value?

    Best Regards and Thank you.

  4. Kian says:

    I can’t see the images. Is it possible to do it without Sharepoint Designer?

  5. Swapnil says:

    can you do this for the calculated column :

    I want to show sum of the calculated colum

  6. Shabana says:

    Hi Chanakya,

    I am new to Sharepoint and the company where I have started working with is using Sharepoint 2010. They have assigned me one task without any training. There is one list having columns title, comment, created (data type: date and time), status (values:assigned, submitted, completed and closed).
    What I need is, grouping on list using columns Created (should be in MONTH YEAR format) and Status.
    Eg : if Created column contains value 31/01/2012 then first it should be stored as January 2012 format and then grouping on this calculated column and then on Status column.

    Please help me out in this.

    • Hi Shabana,

      As I understand you just need a 2 level grouping that is possible out of box. Only thing you need to do is that creating a calculated column & get your date formatted as “January 2012″ and then you can apply grouping on this column. You can get the details of how to apply formulas for the calculated column here in this link..
      http://msdn.microsoft.com/en-in/library/bb862071%28v=office.14%29.aspx
      h
      Check the “Date and Time formulas” section.

      PS : Hope you could have achieved your requirement since its too long that u posted the comment. Apologies for the delayed reply. If replies are delayed in blogs, post your queries in MSDN so that you get immediate replies.

  7. [...] Summing calculated columns in group by in Sharepoint list + Dataview webpart | Chanakya Jayabalan&#8… [...]

    • Prity says:

      Hi Chanakya, I’m very new with SharePoint and InfoPath 2010.

      I have just created a rating list with a repeated goup (with 3 radio buttons with value 1,2 and 3) in the InfoPath form and to get the average of the 3 radio buttons. I’v added a calculated field into the list and I don’t know how to display this average value from the calculated field into the SharePoint list, its dosn’t appear in the SharePoint list.
      How can I display the value of the calculated field in the SharePoint list?

      I hope you can help me to solve this issue.

      Thanks
      Prity

      • Prity,

        Do you mean displaying in the list view?? If yes you can go to list settings open the view you want to display the column and select the column from the view and save the view.

        • Prity says:

          Hi Chanakya,
          First thanks for the quick response.
          Yes, exact I mean the list view. When I polulate my Infopath form to the SP library/list, I can not send the calculated field as a column to the SP List because I can’t add this to my InfoPathform.
          So that’s the problem why I’m not able to view this calculated field (value) as column in the SharePoint List.

          Regards
          Prity

  8. Prity says:

    Hi Chanakya,

    I will explain you what I have done till now:

    I have create a Infopath form to show the everage rating of the workers with 4 radio buttons.

    Value of the three radio buttons are 1, 2 and 3 and the 4th radio button has nil value and always active per defult) and this all as a group in the repeated field. Then I’ve added like you explain above “show footer” where I’ve added a “calculated value” field to the footer to display the average of the 3 radio button (average of 1,2 and 3) and this all in InfoPath form. And now I can’t display the value of the field “calculated value” into the SharePoint list.

    Thanks
    Prity

  9. Charles says:

    for the sum it would be

  10. Cherry says:

    Chanakya,

    I want to create a SharePoint list view where Sum less than 10 by Employee.

    Can you please see my question here http://social.technet.microsoft.com/Forums/sharepoint/en-US/f93bcf5a-9164-44dc-8dd7-6784743280cd/create-sharepoint-list-view-where-sum-less-than-10?forum=sharepointgeneralprevious

    Please help me out.

  11. Adarsh Mittal says:

    hii if i need to use the total man hour column somewhere else then is it possible?? without using excel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s