Customizing Invoice Paid Report Data Source
| 1. | Right click on the custom report and click the Edit Report Settings. |
| 2. | In the Report Settings form, click the Lookup button to open the Report Script form. |
| 3. | Add a query to the SQL Query window and save the changes. |
, (select top 1 strTaxcode from tblarinvoicedetail where strordernumber = tblARInvoice.strordernumber) as TaxCodeID
Here’s the complete query.
SELECT TOP 100 PERCENT
vyu_SMReportPreference.*,
(select top 1 strReportDescription from tblSMreport where intReportID = 123) as strReportDescription,
tblARInvoice.dtmDate as dtmDate,
isnull(tblARInvoice.strOrderNumber,'') as strOrderNumber,
isnull(tblARInvoice.strCustomerID,'') as strCustomerID,
isnull(strTransactionType,'') as strTransactionType,
strCustomername = case when tblARInvoice.strBillToCompany = '' then
isnull(tblARInvoice.strBillToFirstName + ' ' + tblARInvoice.strBillToLastName,'')
else
tblARInvoice.strBillToCompany
end,
isnull(tblARInvoice.strBillToFirstName,'') as strBillToFirstName,
isnull(tblARInvoice.strBillToLastName,'') as strBillToLastName,
isnull(dblOrderTotal,0) as dblOrderTotal,
isnull(dblOrderTotal,0) - isnull(dblFreight,0) - isnull(dblOther,0) -
isnull(dblMHRTotal,0) - isnull(dblSalesTaxAmount,0) - isnull(dblSalesTaxAmount2,0) AS dblSubtotal,
isnull(dblAmountPaid,0) as dblAmountPaid,
isnull(ysnPaid,'') as ysnPaid,
isnull(ysnShipped,'') as ysnShipped,
isnull(strType,'') as strType,
isnull(strShipVia,'') as strShipVia,
isnull(strTerms,'') as strTerms,
isnull(strSalesperson,'') as strSalesperson,
isnull(ysnPosted,'') as ysnPosted,
isnull(dblDiscount,0) as dblDiscount,
isnull(strOriginalOrderNumber,'') as strOriginalOrderNumber,
dtmShipDate as dtmShipDate,
dtmDate AS dtmDatePaid,
isnull(dblFreight,0) as dblFreight,
isnull(dblOther,0) as dblOther,
isnull(tblARInvoice.strBillToCompany,'') as strBillToCompany
, isnull(tblARInvoice.strVendorInvoices, '') as strVendorInvoices
, isnull(tblARInvoice.dblSalesTax, 0) as dblSalesTax
, (select top 1 strTaxcode from tblarinvoicedetail where strordernumber = tblARInvoice.strordernumber) as TaxCodeID
FROM
vyu_SMReportPreference,
tblARInvoice
LEFT JOIN tblCMContactBillTo
ON tblARInvoice.strCustomerID = tblCMCOntactBillTo.strContactID
WHERE
ysnPosted = 1
AND ysnPaid = 1
AND strTransactionType = 'Invoice'
4. Go back to the Report Manager and right click on the custom report then select Edit Report Design.
5. Add Tax Code column. Then on DataField make sure the TaxCodeID is selected from the drop down list. Save the Report layout.
This is the equivalent TaxCodeID that has been added in the Report Query form:
, (select top 1 strTaxcode from tblarinvoicedetail where strordernumber = tblARInvoice.strordernumber) as TaxCodeID
Here is the preview of the custom report. Notice the Tax Code column
To verify:
Invoice 290977 used Tax Code 1
