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

Hello,

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!
michaelleffewAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pssandhuCommented:
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:
WhilePrintingRecords;
Shared Numbervar val;
IF {Table.PartNumberField} <> "ROOM" Then val
ELSE val := val+1;
val
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.
P.
0
mlmccCommented:
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 FORMAT OBJECT
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.

mlmcc
0
michaelleffewAuthor Commented:
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.
0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

michaelleffewAuthor Commented:
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!!
0
pssandhuCommented:
Can you not declare a local variable, assign the value to the local variable, reset the total and print the local variable.
P.
0
michaelleffewAuthor Commented:
i'm afraid I don't follow :(
0
mlmccCommented:
You can't based on that record since the running total will update before you display the value.

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
michaelleffewAuthor Commented:
Thank you for your help!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.