Crystal Report On All A/R Transaction
source link: https://community.sap.com/t5/enterprise-resource-planning-q-a/crystal-report-on-all-a-r-transaction/qaq-p/13605757
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Thursday
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.
- SAP Managed Tags:
- SAP Crystal Reports
Accepted Solutions (0)
Answers (2)
Thursday
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:
- 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.
- 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.
- 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.
- 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.
2 hours ago
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.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK