Modifying Detailed Sales Report Query

[Former Member]

Hello All --

Can we adjust the Query below so that the current Company, Address, City, State and Zip have the letters BT in front (for Bill To)?

Then, can we add new columns of the Company, Address, City, State and Zip for the Ship To with ST in the front of the names?

Thanks!!

Mike

[Former Member · Accepted solution]

Here you are:

SELECT 'Invoice' "Invoice/AR CM", T0.CardCode
"BP Code", T0.CardName 'Company',
T0.Address 'BT Address', T2.City 'BT City', T2.State1 'BT 
State', T2.ZipCode 'BT Zip', T0.Address2 'ST Address',
T3.Name, T0.DocDate,T0.DocNum,
T0.DocTotal 'Gross Amt', T0.TotalExpns 'Freight/Svc Charge',
(T0.DocTotal - T0.VatSum-T0.TotalExpns) 'Net Order',
T1.SlpName 'Sales Rep',
T1.Commission '% Commision',
((T0.DocTotal - T0.VatSum-T0.TotalExpns)*(T1.Commission/100))
'Commission Amount', T4.GroupName 'Group'
FROM dbo.OINV T0 LEFT JOIN dbo.OSLP T1 ON T0.SlpCode = T1.SlpCode
LEFT JOIN dbo.OCRD T2 ON T0.CardCode = T2.CardCode
LEFT JOIN dbo.OCPR T3 ON T0.CntctCode = T3.CntctCode
LEFT JOIN dbo.OCRG T4 ON T4.GroupCode = T2.GroupCode
WHERE (T1.SlpName BETWEEN '[%0]' and '[%1]') AND 
(T0.DocDate BETWEEN '[%2]' AND '[%3]') 
UNION ALL
SELECT 'Credit Memo' "Credit", T0.CardCode, T0.CardName,
T0.Address, T2.City, T2.State1, T2.ZipCode, T0.Address2,
T3.Name, T0.DocDate,T0.DocNum,
-T0.DocTotal, -T0.TotalExpns,
-(T0.DocTotal - T0.VatSum-T0.TotalExpns),
T1.SlpName,
T1.Commission,
((T0.DocTotal - T0.VatSum-T0.TotalExpns)*(-T1.Commission/100)),
T4.GroupName 'Group'
FROM dbo.ORIN T0 LEFT JOIN dbo.OSLP T1 ON T0.SlpCode = T1.SlpCode
LEFT JOIN dbo.OCRD T2 ON T0.CardCode = T2.CardCode
LEFT JOIN dbo.OCPR T3 ON T0.CntctCode = T3.CntctCode
LEFT JOIN dbo.OCRG T4 ON T4.GroupCode = T2.GroupCode
WHERE (T1.SlpName BETWEEN '[%0]' and '[%1]') AND 
(T0.DocDate BETWEEN '[%2]' AND '[%3]') 

Thanks,

Gordon

[Former Member · Accepted solution]

Hi Gordon --

Just used this Query. When I exported it into Excel, noticed that the header row was incomplete...it got cut off around the BT State and created a second row on the Excel sheet for the remaining header titles.

Can we correct this so all headers show up on the top field?

Thanks!!!

Mike

[Former Member · Accepted solution]

No. It is not available for some headers to Excel. I myself want that too but somehow B1 just omit all the manual headers (the header for formular fields) completely.

Thanks,

Gordon

[former_member583013 · Accepted solution]

Mike,

It seems BT and the State are in two seperate lines on the Query. Please check the following

SELECT 'Invoice' "Invoice/AR CM", T0.CardCode
"BP Code", T0.CardName 'Company', T0.Address 'BT Address', T2.City 'BT City', T2.State1 'BT State', T2.ZipCode 'BT Zip', T0.Address2 'ST Address', T3.Name, T0.DocDate,T0.DocNum, T0.DocTotal 'Gross Amt', T0.TotalExpns 'Freight/Svc Charge',
(T0.DocTotal - T0.VatSum-T0.TotalExpns) 'Net Order', T1.SlpName 'Sales Rep', T1.Commission '% Commision',
((T0.DocTotal - T0.VatSum-T0.TotalExpns)*(T1.Commission/100)) 'Commission Amount', T4.GroupName 'Group'
FROM dbo.OINV T0 LEFT JOIN dbo.OSLP T1 ON T0.SlpCode = T1.SlpCode
LEFT JOIN dbo.OCRD T2 ON T0.CardCode = T2.CardCode
LEFT JOIN dbo.OCPR T3 ON T0.CntctCode = T3.CntctCode
LEFT JOIN dbo.OCRG T4 ON T4.GroupCode = T2.GroupCode
WHERE (T1.SlpName BETWEEN '[%0]' and '[%1]') AND 
(T0.DocDate BETWEEN '[%2]' AND '[%3]') 
UNION ALL
SELECT 'Credit Memo' "Credit", T0.CardCode, T0.CardName,
T0.Address, T2.City, T2.State1, T2.ZipCode, T0.Address2,
T3.Name, T0.DocDate,T0.DocNum,
-T0.DocTotal, -T0.TotalExpns,
-(T0.DocTotal - T0.VatSum-T0.TotalExpns),
T1.SlpName,
T1.Commission,
((T0.DocTotal - T0.VatSum-T0.TotalExpns)*(-T1.Commission/100)),
T4.GroupName 'Group'
FROM dbo.ORIN T0 LEFT JOIN dbo.OSLP T1 ON T0.SlpCode = T1.SlpCode
LEFT JOIN dbo.OCRD T2 ON T0.CardCode = T2.CardCode
LEFT JOIN dbo.OCPR T3 ON T0.CntctCode = T3.CntctCode
LEFT JOIN dbo.OCRG T4 ON T4.GroupCode = T2.GroupCode
WHERE (T1.SlpName BETWEEN '[%0]' and '[%1]') AND 
(T0.DocDate BETWEEN '[%2]' AND '[%3]') 

[former_member583013]

Mike,

This is a simple example of how you could add any charecter/s before your columns

SELECT T0.CardName, 'ST '+T0.Address2 AS 'Ship to' FROM [dbo\].[ORDR\] T0

SELECT T0.CardName, 'BT '+T0.Address AS 'Bill to' FROM [dbo\].[ORDR\] T0

The above query would give you the customers name on the sales order and his complete address with the ST prefix.

NOTE: On the document the Address is stored in one single field so inividual fields cannot have the ST before them.

If you need the 'ST' prefix on street, City individually then..

This is for the Shipping Address

SELECT DISTINCT T0.DocNum, T0.CardName, 'ST 'T1.Address, 'ST 'T1.Street, 'ST 'T1.Block, 'ST 'T1.City, 'ST '+T1.ZipCode FROM [dbo\].[ORDR\] T0 INNER JOIN [dbo\].[CRD1\] T1 ON T1.Address = T0.ShiptoCode AND T1.CardCode =T0.CardCode

WHERE T1.AdresType = 'S'

[Former Member]

Suda - thanks!

Gordon - this is the Query we are currently using below, would be easier for us if we can just modify this one...thanks!

SELECT 'Invoice' "Invoice/AR Credit Memo", T0.CardCode

"BP Code", T0.CardName "Company",

T0.Address, T2.City, T2.State1, T2.ZipCode, T2.Phone1,

T3.Name, T0.DocDate,T0.DocNum,

T0.DocTotal "Gross Amt", T0.TotalExpns "Freight/Svc Charge",

(T0.DocTotal - T0.VatSum-T0.TotalExpns) "Net Order",

T1.SlpName "Sales Rep",

T1.Commission "% Commision",

((T0.DocTotal - T0.VatSum-T0.TotalExpns)*(T1.Commission/100))

"Commission Amount", T4.GroupName 'Group'

FROM dbo.OINV T0 LEFT JOIN dbo.OSLP T1 ON T0.SlpCode = T1.SlpCode

LEFT JOIN dbo.OCRD T2 ON T0.CardCode = T2.CardCode

LEFT JOIN dbo.OCPR T3 ON T0.CntctCode = T3.CntctCode

LEFT JOIN dbo.OCRG T4 ON T4.GroupCode = T2.GroupCode

WHERE (T1.SlpName BETWEEN '[%0]' and '[%1]') AND

(T0.DocDate BETWEEN '[%2]' AND '[%3]')

UNION ALL

SELECT 'Credit Memo', T0.CardCode, T0.CardName,

T0.Address, T2.City, T2.State1, T2.ZipCode, T2.Phone1,

T3.Name, T0.DocDate,T0.DocNum, -T0.DocTotal,

-T0.TotalExpns, -(T0.DocTotal - T0.VatSum-T0.TotalExpns),

T1.SlpName, T1.Commission, ((T0.DocTotal - T0.VatSum-

T0.TotalExpns)*(-T1.Commission/100)), T4.GroupName

FROM dbo.ORIN T0 LEFT JOIN dbo.OSLP T1 ON T0.SlpCode = T1.SlpCode

LEFT JOIN dbo.OCRD T2 ON T0.CardCode = T2.CardCode

LEFT JOIN dbo.OCPR T3 ON T0.CntctCode = T3.CntctCode

LEFT JOIN dbo.OCRG T4 ON T4.GroupCode = T2.GroupCode

WHERE (T1.SlpName BETWEEN '[%0]' and '[%1]') AND

(T0.DocDate BETWEEN '[%2]' AND '[%3]')

[Former Member]

Mike,

If you have many ship to, the report will look ugly.

What kind of report do you expect? Only the default ship to?

Thanks,

Gordon

[Former Member]

Hi Gordon,

Actually - just want the one ship to where the package actually went. Not all the ship to in the BP database.

Thanks!

Mike

[Former Member]

Post the query you need to modify. I will see what I can do.