Crystal Report On All A/R Transaction

[Simon3]

I'm trying to create a report on all A/R sales transactions, (A/C Invoices, A/R Credit Memos & A/RCorrection Invoices, or as I Say, The Good, The Bad and the Ugly).

I have used the Crystal Report linker and it gives be the following SQL Query:

SELECT "OINV"."DocNum", "ORIN"."DocNum", "OCSI"."DocNum", "OINV"."DocDate", "ORIN"."DocDate", "OCSI"."DocDate", "OINV"."CardName", "OCSI"."CardName", "ORIN"."CardName", "OACT"."BPLName"

 FROM   (("Database_Name"."dbo"."OINV" "OINV" INNER JOIN " Database_Name "."dbo"."OACT" "OACT" ON "OINV"."BPLName"="OACT"."BPLName") INNER JOIN " Database_Name "."dbo"."ORIN" "ORIN" ON "OACT"."BPLName"="ORIN"."BPLName") INNER JOIN " Database_Name "."dbo"."OCSI" "OCSI" ON "OACT"."BPLName"="OCSI"."BPLName"

 WHERE  ("OCSI"."DocDate">={ts '2023-12-01 00:00:00'} AND "OCSI"."DocDate"<{ts '2024-01-01 00:00:00'}) AND ("ORIN"."DocDate">={ts '2023-12-01 00:00:00'} AND "ORIN"."DocDate"<{ts '2024-01-01 00:00:00'}) AND ("OINV"."DocDate">={ts '2023-12-01 00:00:00'} AND "OINV"."DocDate"<{ts '2024-01-01 00:00:00'}) AND "OACT"."BPLName"=N'My Name'

But I get no results at all, I can do reports for each of the A/R transactions separately but I want one report  for one client over a user defined period.

I think I've got my links wrong or it can't be done.

Any help at all would be greatly appreciated.

Many thanks in advance.

Simon.

[Simon3 · Accepted solution]

Thank you, my friend , but I was hoping for one report with no sub reports thus no loss of functionality …. So I soldiered on …. And hay! I got the answer! is below :

SELECT "OINV"."DocNum", "OINV"."DocDate", "OINV"."CardName", "INV1"."Dscription", "OINV"."CardCode", "OINV"."BPLName", "INV1"."LineTotal", "OACT"."AcctCode"

FROM   ("XXXXXXXX"."dbo"."OACT" "OACT" INNER JOIN "XXXXXXXXY"."dbo"."INV1" "INV1" ON "OACT"."AcctCode"="INV1"."AcctCode") INNER JOIN "XXXXXXXX"."dbo"."OINV" "OINV" ON "INV1"."DocEntry"="OINV"."DocEntry"

UNION

SELECT "OCSI"."DocNum", "OCSI"."DocDate", "OCSI"."CardName", "CSI1"."Dscription", "OCSI"."CardCode", "OCSI"."BPLName", "CSI1"."LineTotal", "OACT"."AcctCode"

FROM   ("XXXXXXXX"."dbo"."OCSI" "OCSI" INNER JOIN "XXXXXXXX"."dbo"."CSI1" "CSI1" ON "OCSI"."DocEntry"="CSI1"."DocEntry") INNER JOIN "XXXXXXXX"."dbo"."OACT" "OACT" ON "CSI1"."AcctCode"="OACT"."AcctCode"

UNION ALL

SELECT "ORIN"."DocNum", "ORIN"."DocDate", "ORIN"."CardName", "RIN1"."Dscription", "ORIN"."CardCode", "ORIN"."BPLName", "RIN1"."LineTotal", "OACT"."AcctCode"

FROM   ("XXXXXXXX"."dbo"."ORIN" "ORIN" INNER JOIN "XXXXXXXX"."dbo"."RIN1" "RIN1" ON "ORIN"."DocEntry"="RIN1"."DocEntry") INNER JOIN "XXXXXXXX"."dbo"."OACT" "OACT" ON "RIN1"."AcctCode"="OACT"."AcctCode"

However I've run into another issue, and that is that the A/R CM (credit memo) are appearing as positives rather than the negitive value. Thus the report is out by twice the credit memo.

How do I show the positive as a negative?

Regards,

Simon.

[JWiseman]

Hi Simon3, one solution for this is to create subreports for the 2 additional datasets. I.E. If you consider the A/R Invoices data the main dataset, then subreports would be created for A/R Credit Memos & A/RCorrection Invoices. 

High level steps:

1. Create a new report based on only the A/R Invoices data. I am assuming that this dataset will contain all of the customers you need.
2. Create a group on the customer name or the ID depending on how you want it to be ordered. Insert 2 Group Header sections below so that there are now 3 GH1 sections.
3. Insert a Subreport and using the wizard, add the data for A/R Credit and the subreport link will be on the customer ID. Place this subreport in the 2nd Group Header section.
4. Report step 3 but use the A/R Correction dataset.

This method works great if you can keep the 3 datasets independent and don't need the data available in one object...i.e. one crosstab or chart.