Summing calculated columns in group by in Sharepoint list + Dataview 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 🙂

Advertisements

24 thoughts on “Summing calculated columns in group by in Sharepoint list + Dataview webpart

Add yours

  1. 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.

    1. 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.

    1. 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

        1. 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

  2. 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

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

  4. I have a different issue with my list. There are multiple entries against each person to display their learning courses and hours. Now i am unable to sum the total learning hours per person. Data view just doesn’t give me the option. Any inputs on how to do it?

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: