Sum a calculated field in Report Writer

(1) Share

Report

Posted on by Community Member

I'm wondering if/how to sum a calculated field in Report Writer using Dynamics GP 2013. My understanding is that I cannot simply set the report field options to sum because that returns the total sum for the page, ignoring the calculated statement.

I should mention that the calculated statement is a line item. This for a 'picking ticket' report in our parts department. We need to know the total value of what is being shipped in order to insure it accordingly. The challenge is simply that some items may be back-ordered, so taking the sum of the entire order isn't accurate.

We've created a conditional statement, so that the line item returns the correct subtotal

*This post is locked for comments

I have the same question (0)

All responses (15)

Answers (1)

Community Member on at

Like (1)

Report

How about taking "QTY Fulfilled" on each line item? Fulfilled will always be less backordered from total line quantity ordered. You don't need a calculated field either in this case.

I may have understood your requirement/scenario wrongly. More details about this report might help us understand that better, in that case.

Was this reply helpful? Yes No
Justin Thorp 2,265 on at

Like (1)

Report

Hi,

You should be able to place the calculated field in the Report Footer section, and change it's Display Type to SUM.

If this doesn't work, please send me a screenshot of the layout of the report, along with the definition of your calculated field.

Thanks,
Justin

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Vaidhyanathan, firstly, thank you for replying. I'm afraid though that the QTY fulfilled isn't giving the desired result. When I reference the SQL data, QTY fulfilled is always zero and is not a currency. I believe this to be because QTY fulfilled is only populated once the item is billed and seeing that we ship items prior to invoicing, this field is not correct here.

A little more detail: This report is generated when an order comes in. We get an order for parts, then the 'picking ticket report' prints, so the employee in the parts department knows what to 'pick' from inventory to put in the box and ship. We are trying to have the report generate the total value of the 'picked' items in the box, so the employee will know how much to insure the shipment for.

Example: customer orders 120 widgets. We have 20 widgets in stock, so 100 are back ordered. The shipping employee 'picks' 20 widgets, packs and sends them out. If the widget costs $1, then he knows the total value in the box is $20. The challenge is when there are 50+ different parts, quantities, all with various pricing... etc.

Thank you again

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Justin. Thanks for helping out. I can't simply change the Display Type to Sum because it yields all items whether backordered or not.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

( SOP_LINE_WORK.QTY - SOP_LINE_WORK.QTY To Back Order ) * SOP_LINE_WORK.Unit Price

Thank you for reviewing

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

This the subtotal calculated field in the Price column. Thanks again.

Was this reply helpful? Yes No
Almas Mahfooz 11,009 User Group Leader on at

Like (1)

Report

Just create a calculated field of type currency, use same formula as you mentioned above. Drag and drop the calculated field in Report Footer Area just besides Subtotal field. you don't need to set any other property.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Almas, if only mine would do that! I'm afraid that doesn't yield the correct result on my report though. What are the details of your calculated field? My field already has a result type of currency. I changed the default display type of 'last occurance' to 'data,' but that didn't change the result.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Justin, this is what I get when I simply change the display type to SUM. The unit price i s$19.91, so the field should yield the same $457.93 as shown. Thanks again.

Was this reply helpful? Yes No
Suggested answer

Almas Mahfooz 11,009 User Group Leader on at

Like (0)

Report

Your display type should be running sum not the last occurrence.My calculated filed is same as yours.

See screen shot as it's not allowing be to upload here because of file size.

https://www.dropbox.com/s/dk959piniq9wtbi/Capture22.PNG?dl=0

Was this reply helpful? Yes No