General

Re: Calculate totals of a Sharepoint List repeating table

ErnestoM — Thu, 06 Jun 2013 12:30:05 GMT

Awesome!

Re: Calculate totals of a Sharepoint List repeating table

Richter Belmont — Thu, 06 Jun 2013 06:44:00 GMT

Thanks Ernesto i got it working now. :)

sum(xdXDocument:GetDOM("Work Order System")/dfs:myFields/dfs:dataFields/dfs:Work_Order_System/@Labor_Hours_Total[msxsl:string-compare(../@Labor_Min_Date, xdXDocument:get-DOM()/my:myFields/my:SummaryReport/my:LR_StartDt) >= 0 and msxsl:string-compare(../@Labor_Min_Date, xdXDocument:get-DOM()/my:myFields/my:SummaryReport/my:LR_EndDt)

Re: Calculate totals of a Sharepoint List repeating table

Richter Belmont — Thu, 06 Jun 2013 05:47:07 GMT

Yes it works without condition during preview. i attached the screenshot. totals of all items were pulled, not the correct total because we dont have condition.
sum(xdXDocument:GetDOM("Work Order System")/dfs:myFields/dfs:dataFields/dfs:Work_Order_System/@Labor_Hours_Total)

Re: Calculate totals of a Sharepoint List repeating table

ErnestoM — Wed, 05 Jun 2013 18:56:22 GMT

Have you tried summing without a condition?

sum(xdXDocument:GetDOM("Work Order System")/dfs:myFields/dfs:dataFields/dfs:Work_Order_System/@Labor_Hours_Total)

Does that work OK?

I guess I'm trying to figure out if the xpath is incorrect or if the condition is incorrect, or if there's a problem in the data that prevents it from being summed.

Re: Calculate totals of a Sharepoint List repeating table

Richter Belmont — Wed, 05 Jun 2013 12:50:17 GMT

Thanks Ernesto, i tried both but still the output is zero, i think your sample is not applicable for data that is coming from a Sharepoint List.
I tried this but still no luck
sum(xdXDocument:GetDOM("Work Order System")/dfs:myFields/dfs:dataFields/dfs:Work_Order_System[number(translate(translate(translate(@Labor_Min_Date,'-',''),' ',''),':','')) > number(translate(translate(translate(/my:myFields/my:SummaryReport/my:LR_StartDt,'-',''),' ',''),':',''))]/@Labor_Hours_Total)

Re: Calculate totals of a Sharepoint List repeating table

ErnestoM — Wed, 05 Jun 2013 12:17:24 GMT

I would suggest trying intermediate steps to slowly build your total sum. So if your goal is to fulfill two conditions in the sum, perhaps start off with

sum(xdXDocument:GetDOM("Work Order System")/dfs:myFields/dfs:dataFields/dfs:Work_Order_System[condition #1]/@Labor_Hours_Total)

then try

sum(xdXDocument:GetDOM("Work Order System")/dfs:myFields/dfs:dataFields/dfs:Work_Order_System[condition #2]/@Labor_Hours_Total)

Once the two above work OK, it seems like you would just need to add one to the other.

Hope this helps!

Re: Calculate totals of a Sharepoint List repeating table

Richter Belmont — Tue, 04 Jun 2013 13:11:16 GMT

Thanks Ernesto for your reply,. but i can't get the correct formula, can you help? i tried using this formula below and im getting 0.0 value for Grand Total Hours, i am filtering the repeating table base on the dates, these two dates are outside of repeating table, Start Date and End Date,
sum(xdXDocument:GetDOM("Work Order System")/dfs:myFields/dfs:dataFields/dfs:Work_Order_System[@Labor_Min_Date < /my:myFields/my:SummaryReport/my:LR_StartDt or @Labor_Min_Date > /my:myFields/my:SummaryReport/my:LR_EndDt]/@Labor_Hours_Total)

Re: Calculate totals of a Sharepoint List repeating table

ErnestoM — Tue, 04 Jun 2013 12:17:04 GMT

I believe your sum will need to use the same filter. For example, if you're hiding rows where my:TotalHours > 0, your sum would look like this:

sum(my:row[my:TotalHours > 0]/:my:TotalAmount)

I'm attaching a quick sample that you can look at in Design and Preview. Hope this helps!

Calculate totals of a Sharepoint List repeating table

Richter Belmont — Tue, 04 Jun 2013 07:06:01 GMT

Hi Guys - I was able to manage to show my sharepoint list using a repeating table that i seen in different blogs. My problem now is i can't find a way to add a total column in the footer. Im filtering the sharepoint list data in my view by hiding the rows. I just want to calculate the totals of the rows that is not hidden. Any idea?