SAP B1 TOP SELLING ITEMS

[Former Member]

Hi Guys I'm trying to make a query that can generate top selling Items per month by Customer Group Name how can i get it when I run my query it say invalid column here my query below.

SELECT TOP 20 T1.ItemCode, T2.ItemName, cast(sum(T1.Quantity) as numeric) as 'Quantity Sold', sum(T1.LineTotal - T1.VatSum) as 'Total Net Value', sum(T1.LineTotal) as 'Total Gross Value', T1.Currency

FROM dbo.INV1 T1 inner join OINV T3 on T1.docentry = t3.docentry
LEFT OUTER JOIN OITM T2 on T1.ItemCode = T2.ItemCode INNER JOIN OCRD T4 ON T2.CardCode = T4.CardCode INNER JOIN OCRG T5 ON T4.GroupCode = T5.GroupCode

WHERE T1.ItemCode is not null and t3.docdate between [%0] and [%1] and t3.series = '229' and T5.GroupName = [%2]

GROUP BY T1.ItemCode, T2.ItemName, T1.Currency

order by sum(T1.Quantity) desc

thank you for helping

[former_member375912 · Accepted solution]

Hi,

I found your mistake :

----------------------------------------------------------------------------------------------------------------------

SELECT TOP 20 T1.ItemCode, T2.ItemName, cast(sum(T1.Quantity) as numeric) as 'Quantity Sold', sum(T1.LineTotal - T1.VatSum) as 'Total Net Value', sum(T1.LineTotal) as 'Total Gross Value', T1.Currency

FROM dbo.INV1 T1

INNER JOIN OINV T3 on T1.docentry = t3.docentry
LEFT OUTER JOIN OITM T2 on T1.ItemCode = T2.ItemCode

NNER JOIN OCRD T4 ON T3.CardCode = T4.CardCode

INNER JOIN OCRG T5 ON T4.GroupCode = T5.GroupCode

WHERE T1.ItemCode is not null and (t3.docdate between [%0] and [%1]) and t3.series = 229 and T5.GroupName = '[%2]'

GROUP BY T1.ItemCode, T2.ItemName, T1.Currency

order by sum(T1.Quantity) desc

----------------------------------------------------------------------------------------------------------------------

It should T3.CardCode = T4.CardCode not T2.CardCode = T4.CardCode

Hope this helps 🙂

Regards,

Rizki

[Former Member · Accepted solution]

Thank you! It's Working!

But I think It didn't less the cancelled transaction(ar credit memo)

how can I do that any Idea?

sorry I'm Just new to SAP B1 I barely not know yet the flow of the database of it.

can you help me thanks in advance.

[former_member375912]

Hi,

Just Add this in where clause :

-----------------------------------------------------------------------------------------------------------------------------

Where .... and T3.Canceled='N' and T1.TargetType<>14

-----------------------------------------------------------------------------------------------------------------------------

T3.Canceled = 'N' --> Show Invoice that is not canceled only

T1.TargetType<>14 --> Target type 14 for AR Credit Memo, so we need to remove it.

Hope this helps 🙂

Regards,

Rizki W

[Former Member]

Thank you!

it works!

[former_member375912]

Hi,

You just need to make revision in where clause :

SELECT TOP 20 T1.ItemCode, T2.ItemName, cast(sum(T1.Quantity) as numeric) as 'Quantity Sold', sum(T1.LineTotal - T1.VatSum) as 'Total Net Value', sum(T1.LineTotal) as 'Total Gross Value', T1.Currency

FROM dbo.INV1 T1 inner join OINV T3 on T1.docentry = t3.docentry
LEFT OUTER JOIN OITM T2 on T1.ItemCode = T2.ItemCode INNER JOIN OCRD T4 ON T2.CardCode = T4.CardCode INNER JOIN OCRG T5 ON T4.GroupCode = T5.GroupCode

WHERE T1.ItemCode is not null and (t3.docdate between [%0] and [%1]) and t3.series = 229 and T5.GroupName = '[%2]'

GROUP BY T1.ItemCode, T2.ItemName, T1.Currency

order by sum(T1.Quantity) desc

Hope this helps 🙂

Regards,

Rizki

[Former Member]

Hi Rizki,

It executes but there no data found it say on the query what should I do?

ty