Sum a calculated field in Report Writer

(1) Share

Report

Posted on by Community Member Microsoft Employee

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

All responses (15)

Answers (1)

Suggested answer

Almas Mahfooz 11,006 User Group Leader on at

Like (0)

Report

RE: Sum a calculated field in Report Writer

You noticed correct about different reports (Y).

and your solution worked for shorinsamurai, great. (Y)

Just want to add, even for Picking Ticket we can use simple calculated field, no need for conditional calculated field, we only need to change calculated field formula a little.

Calculated field : sopIndividuapickTicketTemp.QTYFullfilled*SOP_Line_Work.UnitPrice.

Display Type will remain same as running total.

so if someone is having difficulty understanding conditional field, they can also have sum like that.

https://www.dropbox.com/s/gkas5xfg6b9or8x/cap24.png?dl=0
Community Member Microsoft Employee on at

Like (0)

Report

RE: Sum a calculated field in Report Writer

Justin, I'm working on the same report as you and I'm on GP2013.

I've run a few tests and the expression that you just posted seems to have worked! I can't thank you enough. I'm not sure that I completely understand what that worked though. I'm assuming the "suppress Detail Line" field is an indicator of items in stock?

Thank you all so much for the help.
Verified answer

Justin Thorp 2,265 on at

Like (1)

Report

RE: Sum a calculated field in Report Writer

Hi shorinsamurai and Almas,

I think we are potentially working on different reports.

shorinsamurai and Almas:

1.) What is the exact name of the report you are modifying as seen in Report Writer?

2.) What is the exact version of GP you are using?

I am modifying the "SOP Blank Picking Ticket Order Entered" (and I'm on GP2010 SP2 for the record).

===============

If one is using "SOP Blank Picking Ticket Order Entered", then the exact solution I've tested is:

Create a conditional expression (Note that the in the expression I reference "Suppress Detail Line"...this is an existing calculated field):

Result Type: Currency

Expression: Suppress Detail Line = "Print Me"

True: 0.00000

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

Then place this field in the PF and/or RF and make it's display type = SUM

Thanks,

Justin
Suggested answer

Almas Mahfooz 11,006 User Group Leader on at

Like (0)

Report

RE: Sum a calculated field in Report Writer

just added serial number item, SCAN100F with two serial numbers 001 and 002. Still showing correct sum.

https://www.dropbox.com/s/d3jek8xswcm3uky/cap23.png?dl=0
Suggested answer

Justin Thorp 2,265 on at

Like (1)

Report

RE: Sum a calculated field in Report Writer

When working with the SUM display type, it's useful to put the same field in the body section of the report...this will make it visually easier to understand what is happening.

In your case, it's taking the 457.93, and because you have 2 serial numbers, it takes 457.93 again + 457.93 again, which is giving you the total of 1373.79. Almas's report is working fine because she hasn't, I suspect, entered serial or lot numbered items. If she did, she would be experiencing the same issue as you.

My recommendations is to create a conditional statement in report writer that basically says:

Result Type = Currency

Condition: Serial/Lot Number = ""

TRUE: (SOP_LINE_WORK.QTY - SOP_LINE_WORK.QTY To Back Order ) * SOP_LINE_WORK.Unit

FALSE : 0.00000

===================

Then place this new conditional statement in the Header (DATA), Body(DATA), and Report Footer (SUM)...and let me know the results.

Thanks,

Justin
Suggested answer

Almas Mahfooz 11,006 User Group Leader on at

Like (0)

Report

RE: Sum a calculated field in Report Writer

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
Community Member Microsoft Employee on at

Like (0)

Report

RE: Sum a calculated field in Report Writer

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.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Sum a calculated field in Report Writer

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.
Almas Mahfooz 11,006 User Group Leader on at

Like (1)

Report

RE: Sum a calculated field in Report Writer

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.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Sum a calculated field in Report Writer

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