The best way/approach to create a Crystal Sales Report for SAP Business One

[abasilis]

Hi!

I have experience working with Crystal reports, but I have a few months working with SAP Business One. I need to start creating some sale reports.

I have the tables OINV (Header) and INV1 (details). I already selected the following parameter {OINV.DocDate} = {?Date} and {OINV.CANCELED} = "N", and I validated the data running reports, the only difference I have are the transactions made in A/R Credit memo. I already know which tables to use, but I cannot include those tables because when you match two details tables the data is duplicated, everything is double.

Now, what would be the recommended approach?

Create he sales report and add the credit memo report as a subreport? and match it either for customer or item number?, but if the match is not true, I can have Credit Memo and no sales, and the data may not appear as negative in the main report.

Thank you for your help or guide, I really appreciate it!

[mgregur · Accepted solution]

Hi Silvio,

You can achieve what you want by using Command as source for your report, and within the Command use UNION ALL to join OINV/INV1 and ORIN/RIN1 in one single source. This will also allow you to model your data to show negative in front of credit note amounts.

BR,

Matija

[abasilis · Accepted solution]

I will try this approach.

Which field do I need to JOIN between the Invoice(s) table(s) and Credit Memo(s) Tables(s). CardCode (CustomerID)?

Also, you mentioned that I can "model your data to show negative in front of credit note amounts". In others ERP I did it by Document Type, but in both tables DocType has the value I.

Thanks!

[mgregur · Accepted solution]

Hi Silvio,

you don't JOIN Invoices and Credit Memos, instead you use UNION ALL. If you never used it, take a look at

https://www.w3schools.com/sql/sql_ref_union.asp for a short explanation. In short, a union would be something like:

SELECT "CardCode", "DocTotal", "DocDate" FROM OINV)

UNION ALL

(SELECT "CardCode", "DocTotal", "DocDate" FROM ORIN)

You are basically creating two different SELECT statements but with same columns and same column order, and then making a union of the two. You can use the same parameters in both SELECT statements.

BR,

Matija

[mgregur]

No problem. Please close this thread by marking the correct answer.

[abasilis]

Thank you very much Matija, sorry it took me a while, but I had another jobs to do.

I created a View in the Sql server database, and I used the two select (one for the invoice, and the other one for the credit memo) and I used the UNION ALL as suggested, and it worked.

Thanks again for your time and expertise.