Need help expanding a trail balance crystal report

[Former Member]

Hi All,

I'm new to B1 (v8.8), but have been writing reports for years.

I just finished up a trail balance report.

Account / Currency / Debit / Credit / Balance (Debit - Credit)

I would like to add more detail to it.

I would like to group the report by the account, then by the customer, then by items (that make up the debit/credit totals for the account) I've tried using the inv1 table, but the totals where way off. Can this be done? If so, what tables/joins are needed..

Thank you for any help given.

[Former Member]

Hi,

I don't think you can group the report by the account, then by the customer, then by items easily. Explain what process is in your report for 3 level summaries.

Thanks,

Gordon

[Former Member]

Thanks for the reply.. I wanted the 3 levels in an attempt to verify data.

In sort, we want two reports.. Total sales by customer, and total sales by territry.

I created the total sales by territry using (OINV/INV1/ OCRD/OTER). Grouped the report by territry and generated the total with this formula: {INV1.Quantity} * {INV1.Price}

Looks great. My co-worker ran the trial balance report to verify that our totals matched up, they didn't we are off by about 3000 (My crystal report has the higher totals). If the trail balance report is indeed correct, I need to figure where the faults are.

I guess the title of this post should be.. How can I verify my totals

[Former Member]

You may try {INV1.LineTotal} to see.

[Former Member]

How does the LineNum work?

[Former Member]

LineNum is only the order of the line. What do you need to use it for?

[Former Member]

Above you stated that I may try {INV1.LineTotal} to see. And I was seeking more information on how it was used.

[Former Member]

Linetotal is posted to JE directly or by summary of it. It is better than Price * Qty.

[Former Member]

Hi Brian,

A few thing to consider:

1. Is Discount used in Document Total section? If so, using LineTotal will not give you the value. you need to write a formula to get that.

2. Is Freight used in the Document Total? Again, {INV1.Quantity} * {INV1.Price} will not include that. And you need to check whether the Freight is going into the same accounts as your sales.

3. Is it single currency or multicurrency transactions?

Eg: the company currency is USD and you are dealing with a customer in EUR.

LineTotal will display in USD. If you sum that up (assuming no Document Freight or Discount as in point 2), you figure will be right.

But the field Price might be in EUR, depending on the currency on the price list.

[Former Member]

Thanks for everyones help.. After 4 days of looking at numbers and with the help of the replies received here, I was finally able to create a cr report wher the the totals match those generated by the trail balance report. It has over 20 formulas and 7 running totals. I'm sure there is an easier way of doing this, but I couldn't figure it out..