Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Commission Report Query

Hello All --

We use this Query below:

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

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

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,

T2.State1,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]')

One question -- when we export the results to Excel, the DocDate column shows two different formats:

2007-01-08

and

07/21/08

We like the second format -- this is correct. The first one should actually be 7/1/08.

It looks fine in SAP but once it is exported into Excel, Excel changes the format of the dates -- but it's not even consistent with how it changes the date format. Some it converts to the 1st and others the 2nd. The 2nd is how it appears on SAP.

Please advise -- we really need it to export the date format so that it is consistent in Excel.

Thanks!

Mike

Former Member
Former Member replied

Under Control Panel, there is a regional and language option. Make sure the short date is the same as B1 Date Format under System Initialization - General Settings - Display.

Thanks,

Gordon

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question