How to print subtotal in body of report, based on last occurance of item number

Posted on 2009-07-06
Last Modified: 2012-05-07

I use Dynamics GP 10, and Crystal Reports 2008 to print my sales documents (via Forms Printer from Accountable Software).  I'd like to be able to print a subtotal in the body of the sales quote form, based on the last occurance of a certain item number.

For example, I have a large quote that references several areas of a building.  I would like to show a subtotal for each area.  The last line item in each area is a part number called "ROOM".  This part number signifies the end of the equipment for that room.

Any help you can provide is greatly appreciated!
Question by:michaelleffew
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
LVL 17

Expert Comment

ID: 24785691
Do you have area numbers, ids or some kind of unique key value for each area. I would use that field to group my report and add a subtotal in that group.
However, from your explanation it does not seems that way. From top my head (not tested) I am think of having a unique identifier for each group. So to accompalish that you can have something like this:
1. Create a formula and call it what ever you want and put this text in there:
Shared Numbervar val;
IF {Table.PartNumberField} <> "ROOM" Then val
ELSE val := val+1;
2. Place the formula in your detail section and create a group using this formula.
3. This formual should create an incrementing unique id for each group and should group each area. Add your subtotal to this group.
Hope this helps.
LVL 100

Expert Comment

ID: 24786221
You can't group on printtime formulas like that since they are calculated after grouping is done.

Try this.

Put the subtotal on the report.
Right click it
Click the COMMON TAB
Click the FORMULA button to the right of SUPPRESS
{Your Field} = "Room"

If you are trying to do a subtotal for just that room it may require other formulas.


Author Comment

ID: 24786807
Thanks for your feedback!  I am trying to do a subtotal for just that room.  I would need subtotal to show at the end of each room.  Each quote has several rooms in it.
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now


Author Comment

ID: 24786979
so, i was playing around with the running total field suggested by mlmcc, and got it to look pretty good, with one exception...  I've told it to reset the total based on the item number "ROOM".  Now, the total shows 0.00.  Is there a way to tell it to reset after the item number "ROOM" ?

Thanks for your continued help!!
LVL 17

Expert Comment

ID: 24787003
Can you not declare a local variable, assign the value to the local variable, reset the total and print the local variable.

Author Comment

ID: 24787855
i'm afraid I don't follow :(
LVL 100

Accepted Solution

mlmcc earned 500 total points
ID: 24790887
You can't based on that record since the running total will update before you display the value.


Author Comment

ID: 24793432
Thank you for your help!!

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Value Lookup 10 61
Date is within the current week in Crystal Reports 2 119
business objects to connect to MSSQL 6 113
Dates formatted Differently on another pc 2 39
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
Desired Skill Set for Microsoft Dynamics CRM Technical Resources – Part II
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit If you want to manage em…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question