Skip to Content

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

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!

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    Posted on Apr 16 at 05:57 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • Posted on May 06 at 11:15 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 08 at 11:55 AM

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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.