Pulling Data For Report

[Trebel]

I am using V8 for Peachtree. My tables are linked as follows:JrnRow to JrnHdr by Post Order. The JrnRow is also linked to Customer, Item, Cost by the Record#. I then have also linked the Item to Cost table by record number.
The report is grouped by SalesRep ID.
I have 2 detail sections. The first only has the customer name. The second has the invoice# invoice date,Item ID and the sales amount of the item. All data pulls fine up to this point.
I now want to pull the cost of sales amount on this Item to come up with the profit.
The CostCode table only offers these options:
CostCodeID, CostRecordNumber, Cost Type, Description, Inactive.
None of these options will give me the cost of goods in $'s for the ItemId in this section. I have tried several different formulas such as concantenating ItemID and CostID or JrnRow Amount with CostID but can't seem to find the proper formula.
Can anyone help? Thanks in advance
I am a struggling newbie!

 

[kenhamady]

Isn't the unit cost/Qty in the JrnlRow table? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[Trebel]

If only it were that simple. Strangely enough the Unit Cost in the Journal Row is actually the sale price of the unit. Therefore, if I mulitply the Quanity times the Unit Cost it gives me the same amount that is in my Sale Column.
Any other suggestions would be most appreciated.

 

[Trebel]

Finally found it. Peachtree calls it Labor Cost in the Item Table. Sure had to kiss a lot of frogs to find this prince.

 

[kenhamady]

According to the PeachTree docs, not all fields are available for reporting. It may be that these fields are not available. If they didn't store the unit cost in the detail, then it wouldn't be accurate to pull it from somewhere else anyway, because it might change over time.

Crystal can't tell you how your data is organized. You have to get that from PeachTree. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[kenhamady]

Trebel,

I just found out about an added function that brings in cost info. Haven't tried it yet, but if you add a formula using the following function, it is supposed to get inventory cost.
The function is called GetPeachRowAmount in the "Additional" section and it looks like this when used:


GetPeachRowAmount ({Company.CompanySpecial1a}, {Company.CompanySpecial1b}, {Company.CompanySpecial1c}, {Company.CompanySpecial1d},
{JrnlRow.RowType}, {JrnlRow.XAmt}, {JrnlRow.Journal}, {JrnlRow.PostOrder},
{JrnlRow.RowNumber}, {JrnlRow.RowDate}, {JrnlRow.ItemRecordNumber}, {Company.IsCashBasis} )

I will let you know how it works when I try it later. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[Trebel]

Ken,
Great information. I will see what it does. My manual doesn't even make reference to it altho it does mention the additional functions. This might make my life a whole lot easier-at least in the report programming area.

 

[Trebel]

Hi,
I have looked at the "GetPeachAmount()" in the additional functions. I have never used the Additional functions before and am not real sure how they work. I need these to figure Cost of Goods as my regular database uses Labor Cost instead of Goods Cost and there are some Item Id's it is not costing out. I don't know why it is dropping these Items as there doesn't seem to be a pattern that I can see.
Any info or help you can give me on how to use this additional function will be most appreciated.
Thanks
Trebel

 

[kenhamady]

You need to add a new formula field, and then double click this function to create the entire formula. The only change is that the field "XAmt" has to be replaced with Amount or possibly some other amount field. The documentation is poor. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[Trebel]

Hi Ken,
I have set up the new formula and double-clicked the "GetPeachRow(). I replaced the JrnlRow.XAmt with JrnlRow.Amount. It must be replaced because the formula isn't recognized with the XAmt in it. When I placed it in my report to see what it would do it just brings up the sales amount which is a repeat of the figure that shows up using JrnlRow.Amount from the fields available already in my table. Could you tell me where you found the documentation. Perhaps it might tell me something if I could see it. I have been using Peachtree for seven years and using Report Writer to do this same report. Sometimes I have to massage things to get them to work.
Thanks for all the help you have given me. You have certainly made my debut into Crystal Reports much easier.
Trebel

 

[kenhamady]

If you go into the help in PeachTree, near the end of the table of contents is the section on Crystal, and the section on data dictionaries. This is where I have gotten my info. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[Trebel]

Thanks Ken. I have been thru those Peachtree data dictionarys a hundred times, at least, and have printed them up as well. I missed the part about the "GetPeach" somehow and still can't find it, but it is probably pretty nebulous anyway. I will keep hunting and Im sure it will pop up at me somewhere.
All help has been greatly appreciated

 

[kenhamady]

I got something similar to work for a customer today, but it wasn't intuitive. I think I could help you troubleshoot this, based on what I did today, but it would take more time than I can devote to free TT support. Contact me through my web site if you need further help. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[Trebel]

Thank you for the offer. I have emailed you. Looking forward to your help soon.