How can I do a Check Register Type Report on Peachtree BI?
I am trying to do a Money In/Out Report for a given month, basically a Peachtree Check Register Report UNION to a Payment Received Report. Basically Payments Received (sales), Payments Made (invoices paid and Payroll checks).
I am working on the connector, and I am having trouble identifying the fields from the Journal Row table, especially the Payroll fields.
Is there a Database reference, which has the different database fields and corresponding Peachtree Fields? Is there any documentation on the Peachtree database?
Regards,
Edwin
Views: 32
Replies to This Discussion
-
Permalink Reply by Dave Smith on -
A couple of tips to help you get going in the right direction, Edwin. In your WHERE clause of your SELECT statement, add this >> WHERE "JrnlHdr"."JournalEx" in (3, 5, 7, 13). Then you may want to use a CASE END SQL statement to separate and identify the Receipts from the Payments. CASE ~ WHEN "JrnlHdr"."JournalEx" = 3 THEN 'Receipt' ~ and so on END
Cash Receipt = 3
Cash Disbursement = 5
Write Check = 7
Payroll = 13
-
Permalink Reply by Edwin Rodriguez on -
I got It. The query is:
SELECT JrnlHdr.Reference, JrnlHdr.MainAmount, JrnlHdr.TransactionDate, JrnlHdr.JrnlKey_Journal, JrnlHdr.TrxName, Employee.EmployeeName, JrnlRow.RowNumber FROM (JrnlRow LEFT OUTER JOIN JrnlHdr ON JrnlRow.PostOrder=JrnlHdr.PostOrder) LEFT OUTER JOIN Employee ON JrnlRow.EmpRecordNumber=Employee.EmpRecordNumber WHERE JrnlHdr.Reference<>NULL AND (JrnlHdr.TransactionDate>= #2012-03-01# AND JrnlHdr.TransactionDate <= #2012-03-31#) AND (JrnlHdr.JrnlKey_Journal=1 OR JrnlHdr.JrnlKey_Journal=2 OR JrnlHdr.JrnlKey_Journal=5) AND JrnlRow.RowNumber=0 ORDER BY JrnlHdr.Reference
Regards,
Edwin
-
Permalink Reply by Dave Smith on
-
Feels good to nail it, doesn't it Edwin! Nice job, Cheers Dave
-
Permalink Reply by Edwin Rodriguez on
-
You betcha, yes it does feel really good! :)
Follow Alchemex on twitter
Like Alchemex on facebook
© 2012 Created by Admin.
