on 02-15-2024 1:48 AM
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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:
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
99 | |
9 | |
9 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.