I am trying to create a report that will list vendors with and without balances but once Vendor DAC is joined with APHistoryByPeriod, only vendors in APHistoryByPeriod are shown. I am not sure who to translate such condition to the report designer.
Like if [Vendor.AcctCD] found in [APHistoryForReport.VendorID] then show $VendorBegBal otherwise 0. I have attached report for reference. Thanks so much in advance.
Best answer by plambert
If you reorder your joins to have everything Left joining onto Vendor, then you won’t be excluding records by use of the inner join. A setup like this should work:
Vendor Left APHistoryForReport
APHistoryForReport Left BaseAPHistoryByPeriod
BaseAPHistoryByPeriod Left BAccount
Vendor Left LocationExtAddress
Since APHistoryForReport might be null (and you expect that since some Vendors don’t have it) then you’ll have to use Left joins for the tables that depend on it. I suppose LocationExtAddress could be an inner, but then you will be excluding Vendors that don’t have a record in that table.
Unfortunately, I’m unable to get this report to run in my demo instance but I looked at the build schema in report designer. Have you tried using a Left join when joining APHistoryForReport to Vendor? Inner is looking for vendor IDs that appear in both DACs.
Unfortunately, I’m unable to get this report to run in my demo instance but I looked at the build schema in report designer. Have you tried using a Left join when joining APHistoryForReport to Vendor? Inner is looking for vendor IDs that appear in both DACs.
Thank you for taking the time to look into my question.
I tried doing this:
which I am currently encountering an error in probably because I move the Vendor table relationship from bottom to top. However, I was thinking of putting an IIF condition in the report like in Generic Inquiries, I am not sure where to place it, should it be placed in the report section or just the output text field? Thank you in advance for any input.
+2If you reorder your joins to have everything Left joining onto Vendor, then you won’t be excluding records by use of the inner join. A setup like this should work:
Vendor Left APHistoryForReport
APHistoryForReport Left BaseAPHistoryByPeriod
BaseAPHistoryByPeriod Left BAccount
Vendor Left LocationExtAddress
Since APHistoryForReport might be null (and you expect that since some Vendors don’t have it) then you’ll have to use Left joins for the tables that depend on it. I suppose LocationExtAddress could be an inner, but then you will be excluding Vendors that don’t have a record in that table.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
