SQL view for Crystal Reports to show open AP invoices from multiple companies in GP

In my experience Crystal Reports really doesn’t like it when you have spaces in your field names. I’ve gotten a request for a ‘Crystal’ version of the Multicompany Open AP view I posted up a few days ago, so here it is. Please follow the in-line directions in green to change the generic company names and database ID’s I used to yours.  And if you need to add more companies, copy lines 58 through 80 below for each additional company (and change the names accordingly).

create view view_MultiCo_AP_Open_for_Crystal
as
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_MultiCo_AP_Open_for_Crystal
-- Shows all open AP invoices for multiple companies.
-- Created on 11/10/2008 by Victoria Yudin - Flexible Solutions, Inc.
-- For updates see https://victoriayudin.com/gp-reports/
-- Does not take Multicurrency into account.
-- Assumes hard coding the companies.
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

-- ***** Replace Company1 with 1st company name *****
SELECT 'Company1' as Company,
C1.VENDORID as 'VendorID',
C1M.VENDNAME as 'VendorName', C1.DOCDATE as 'InvoiceDate',
C1.DOCNUMBR as 'DocumentNumber',
CASE C1.DOCTYPE
WHEN 1 THEN 'Invoice'
WHEN 2 THEN 'Finance Charge'
WHEN 3 THEN 'Misc Charge'
END AS 'DocumentType', C1.VCHRNMBR as 'VoucherNumber',
C1.DOCAMNT as 'DocumentAmount', C1.CURTRXAM as 'CurrentTrxAmount',
C1.TRXDSCRN as 'TransactionDescription', C1.DUEDATE as 'DueDate'

-- ***** Replace Comp1 with 1st company database ID *****
FROM Comp1..PM20000 C1
LEFT OUTER JOIN

-- ***** Replace Comp1 with 1st company database ID *****
Comp1..PM00200 C1M
ON C1.VENDORID = C1M.VENDORID
WHERE C1.DOCTYPE in (1,2,3)

UNION ALL

-- ***** Replace Company2 with 2nd company name *****
SELECT 'Company2' as Company,
C2.VENDORID as 'VendorID',
C2M.VENDNAME as 'VendorName', C2.DOCDATE as 'InvoiceDate',
C2.DOCNUMBR as 'DocumentNumber',
CASE C2.DOCTYPE
WHEN 1 THEN 'Invoice'
WHEN 2 THEN 'Finance Charge'
WHEN 3 THEN 'Misc Charge'
END AS 'DocumentType', C2.VCHRNMBR as 'VoucherNumber',
C2.DOCAMNT as 'DocumentAmount', C2.CURTRXAM as 'CurrentTrxAmount',
C2.TRXDSCRN as 'TransactionDescription', C2.DUEDATE as 'DueDate'

-- ***** Replace Comp2 with 2nd company database ID *****
FROM Comp2..PM20000 C2
LEFT OUTER JOIN

-- ***** Replace Comp2 with 2nd company database ID *****
Comp2..PM00200 C2M
ON C2.VENDORID = C2M.VENDORID
WHERE C2.DOCTYPE in (1,2,3)

UNION ALL

-- ***** Replace Company3 with 3rd company name *****
SELECT 'Company3' as Company,
C3.VENDORID as 'VendorID',
C3M.VENDNAME as 'VendorName', C3.DOCDATE as 'InvoiceDate',
C3.DOCNUMBR as 'DocumentNumber',
CASE C3.DOCTYPE
WHEN 1 THEN 'Invoice'
WHEN 2 THEN 'Finance Charge'
WHEN 3 THEN 'Misc Charge'
END AS 'DocumentType', C3.VCHRNMBR as 'VoucherNumber',
C3.DOCAMNT as 'DocumentAmount', C3.CURTRXAM as 'CurrentTrxAmount',
C3.TRXDSCRN as 'TransactionDescription', C3.DUEDATE as 'DueDate'

-- ***** Replace Comp3 with 3rd company database ID *****
FROM Comp3..PM20000 C3
LEFT OUTER JOIN

-- ***** Replace Comp3 with 3rd company database ID *****
Comp3..PM00200 C3M
ON C3.VENDORID = C3M.VENDORID
WHERE C3.DOCTYPE in (1,2,3)

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.