cancel
Showing results for 
Search instead for 
Did you mean: 

Creating Sales Analysis Report in B1

Former Member
0 Kudos

Can someone please help me with writing a query to pull Sales Analysis Report sorted and grouped by Sales Employee and Business Partner.

The report contains the following columns:

Sales Employee Name and Number, Business Partner Name & Number, Current Month Sales (or the month specified in the report parameter), Same Month's sale last year, YTD-Current Year, YTD-Previous Year

Also, if you are interested in working on small Business One projects, please contact me with your resume, rate and location. You can work remotely.

Thanks

Accepted Solutions (1)

Accepted Solutions (1)

former_member206488
Active Contributor
0 Kudos

http://wiki.scn.sap.com/wiki/display/B1/SQL+Queries

you can use query mentioned in link above as base query and modify it as required

Thanks

Navneet

Answers (5)

Answers (5)

Former Member
0 Kudos

Hi Sam

I am interested to work in doing small SAP projects .. Pls give me ur mail id

Regards,

K.Ramasamy

Former Member
0 Kudos

Please contact me at SamAngel7@yahoo.com and we take it from there.

Former Member
0 Kudos

Hi Sam,

i think i can help you in your problem, and i am interested to work w/ you in your small SAP project.

please drop me an email and send me the details to contact you.

regards,

jeff

Former Member
0 Kudos

Please contact me at SamAngel7@yahoo.com and we take it from there.

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

If you got answer, please close this thread by marking correct answer.

Please check your email.

Thanks & Regards,

Nagarajan

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this query:

SELECT T0.CardCode, T0.cardname, T1.SlpCode, t1.Slpname,
(SELECT SUM(T2.Debit) - sum(T2.Credit) FROM JDT1 T2
INNER JOIN OJDT T4 ON T4.TransID = T2.TransID AND month(T4.Refdate) = month(getdate())
WHERE T2.ShortName = T0.CardCode AND T2.TransType in ('13','14')) AS 'Current month',


(SELECT SUM(T2.Debit) - sum(T2.Credit) FROM JDT1 T2
INNER JOIN OJDT T4 ON T4.TransID = T2.TransID AND Year(T4.Refdate) = 2014
WHERE T2.ShortName = T0.CardCode AND T2.TransType in ('13','14')) AS 'Sales 2014',

(SELECT SUM(T2.Debit) - sum(T2.Credit) FROM JDT1 T2
INNER JOIN OJDT T4 ON T4.TransID = T2.TransID AND Year(T4.Refdate) = 2013

WHERE T2.ShortName = T0.CardCode AND T2.TransType in ('13','14')) AS 'Sales 2013'

FROM DBO.OCRD T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
WHERE T0.CardType = 'C'

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Thank you so much Nagarajan, I will give it a try.

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Please check your email and reply.

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Hi Nagarajan,

The query didn't produce the correct results. I think I need to add up other columns besides DEBIT and CREDIT. Also, I changed month(T4.Refdate) = month(getdate()) to T4.refdate between '06/01/2014' AND '06/30/2014'.

Regards,

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

What is your expected result? Compare this query result with standard sales report in B1. Let me know the difference.

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Hi Sam,

Please check below links and modify according to your requirement.

by below link you can take reference of Queries.

Hope this helps

--

--

Regards::::

Atul Chakraborty