none
How to sum column values to be used in a Chart WP RRS feed

  • Question

  • Hello,

    I have a list called "Budget" with the following columns: Username, Planned Budget, Used Buget, Remaining Budget. All of the Budget columns are calculated columns.

    I need to summarize each Budget Column in order to be able to create a Chart like the one in the picture below.Budget overview

    When I create the chart selecting the "Budget" list as the source, I can't sum the different Budget columns but get a value per item/row as the picture below:

    Wrong Budget overview

    where Blue is planned budget, orange is used budget and green remaining budget.

    I've tried the following:

    1- Created a list called "Budget Overview" with Columns "Title" and "Total"

    2- Added three items to this list where the "Budget Name" column is Pl. Budget, Us. Budget, Rm. Budget. The "Total" column is empty

    3- Added three columns to the original "Budget" list to record old values

    4- created a workflow to sum the budget and then sending the result to the "Total" column in the second list.

    The problem is that I can't figure out how to set the value to the correct row, because there is now common column between the two lists, any help in the right direction would be appreciated.

    Thanks in advance.

    Friday, May 11, 2012 1:32 PM

Answers

  • I've resolved this using SPD workflows.

    1- I added three columns to my Budget list called Old_Planned, Old_Used and Old_Remaining.

    2- Created 3 lists called Planned Budget, Used Budget and Remaining Budget, all with the same columns named "Budget" and "Total"

    3- Added one item to each list that i created in the second step with the corresponding Budget name leaving the "Total" column empy (this will be filled by the workflow later)

    4- Created anothet list called Budget vs Actuals where the total values of the three lists will be copied and updated. This list have the following columns: Budget, Pl. Budget, Us. Budget and Rem. Budget.

    5- Added three item to the Budget vs Actuals list where the "Budget" column was filled with the corresponding Budget type leavind the rest of the columns empyt (these will be filled by another workflow later)

    6- Created a workflow on the Budget list to calculate in three steps the sum of the corresponding budget type and then pas the result to the corresponding list (Planned Budget, Used Budget or Remaining Budget)

    7- Created a Workflow on eacht one of the three Budget types lists to pass their "Total" value to the "Budget vs Actuals list"

    8- Added a Chart Webpart and connect it to the  "Budget vs Actuals" list.

    I hope this help someone.

    Monday, May 21, 2012 3:48 PM

All replies

  • Below pictures of the two lists

    Budget

    Budget Overview

    Friday, May 11, 2012 1:33 PM
  • I've resolved this using SPD workflows.

    1- I added three columns to my Budget list called Old_Planned, Old_Used and Old_Remaining.

    2- Created 3 lists called Planned Budget, Used Budget and Remaining Budget, all with the same columns named "Budget" and "Total"

    3- Added one item to each list that i created in the second step with the corresponding Budget name leaving the "Total" column empy (this will be filled by the workflow later)

    4- Created anothet list called Budget vs Actuals where the total values of the three lists will be copied and updated. This list have the following columns: Budget, Pl. Budget, Us. Budget and Rem. Budget.

    5- Added three item to the Budget vs Actuals list where the "Budget" column was filled with the corresponding Budget type leavind the rest of the columns empyt (these will be filled by another workflow later)

    6- Created a workflow on the Budget list to calculate in three steps the sum of the corresponding budget type and then pas the result to the corresponding list (Planned Budget, Used Budget or Remaining Budget)

    7- Created a Workflow on eacht one of the three Budget types lists to pass their "Total" value to the "Budget vs Actuals list"

    8- Added a Chart Webpart and connect it to the  "Budget vs Actuals" list.

    I hope this help someone.

    Monday, May 21, 2012 3:48 PM
  • Hi, Im trying to pass total values of a column to a different list and I am interested in the Workflow that you used to do this.

    Could you please provide more information on the workflows used in sections 6 & 7?

    Thanks

    Thursday, May 31, 2012 9:42 AM
  • Hi Stoox,

    Sorry for the very late response. I created the workflows in Dutch but below is how the workflow in step 6 looks like:

    Created first three variables: new_planned, new_used and new_remaining

    1- Set Workflow Variable new_planned to Budget list:Planned Budget

    2- Calculate new_planned plus Current Item:Planned Budget (output to Variable: calc)

    3- Calculate calc minus Current Item: Old_Planned (output to Variable: new_planned)

    4- Update item in Planned Budget list ("Total" column with "new_planned" value)

    5- Set Workflow Variable Old_Planned to Current Item: Planned Budget

    Repeated steps 1 to 5 replacing the variables and list for the other types of budgets.

    The workflow of step 5 is just one step:

    1- Update item in Budget vs Actuals ("Planned Budget" field with "Total" value)

    Repeated this on the other two list (Used Budget and Remaining Budget)

    I hope this help,

    Tuesday, June 26, 2012 9:16 AM
  • Hi,

    I'm in similar situation  .... I would like to sum a  SharePoint list column by category in other column of same list. I would like to feed the aggregated totals to chart webpart.

    Example of my list columns  and values are belwo;  If I connect this list to Chart web part, I get 3 bars for Jan and so on.. however I would like to represent single bar with total for Jan i.e. Jan=50. I did some research and started to create a workflow but failed to do so  ... any help is highly appreciated.

    Month Total Hour
    Jan 20
    Jan 30
    Jan 39
    Feb 12
    Feb 11
    Mar 2
    Mar 12
    Mar 14

    • Edited by Anand2012 Thursday, August 30, 2012 12:20 AM
    Wednesday, August 29, 2012 4:03 AM
  • Hello Anand2012,

    I don't know if you still need help, but here is what you can do.

    1- Add a number column to your current list to hold old values, by ex. "Old_Hours"

    2- Create another list (let's call it "Total Month Hours"), where your items just have one value, in your example 12 months with a corresponding "Total Hours" column, if you want you can assign it a start value of "zero". In your example you need to create two columns that have the exact name and type as the ones in the source list.

    3. Then open SPD and create a List Workflow on the source list. In the workflow define a "new_hours" variable of number type. Then add this to your workflow:

    set new-hours to Total Month Hours:Total Hours, where Total Month Hours:Month = Current Item:Month

    calculate new_hours + Current Item:Total Hours in variable:calc

    calculate variable:calc - Old_Hours in variable:new_hours

    set Total Month Hours:Total Hours to variable:new_hours where Total Month Hours:Month = Current Item:Month

    set Old_Hours to Current Item:Total Hours

    Save your workflow and set it to start when items are created or edited.

    4. attach the "Total Month Hours" to your Graph webpart and configure it to your needs,

    See the screenshot below.

    "Voltooid" means "Completed/Finished"

    Sorry for the very late response, I hope this help,

    Jimmy

    Monday, October 1, 2012 7:39 PM