Skip to Content
avatar image
Former Member

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 02, 2008 at 08:08 PM

    I am not quite able to reproduce the same issue...You may try to use the CAST on the DocDate field

    like CAST(T0.DocDate AS VARCHAR)

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Guys --

      Thanks very much! Gordon, followed your steps and it looks like that took care of the issue. I did have a different date format set up in the Control Panel then SAP.

      Mike

  • avatar image
    Former Member
    Oct 02, 2008 at 09:41 PM

    Hi Mike,

    What you need is not change this query but your regional date format under control panel. It should be consistant. What you get is actually some of dates have not been recongnised by Excel. That is all.

    Thanks,

    Gordon

    Add comment
    10|10000 characters needed characters exceeded