Great explanation. Really, this video is one of the best clips on UA-cam that explains all the features of Query Generator in SAP Business One Thank you man
Thank you very much for sharing yr knowledge! At least now i can DIY to play around with the software. It will be a shame to put such functions to waste! ~ From Far Away Country @ Equator
Great video! Thank you so much for sharing. Is it possible to make math operations in the query? for example: multiply the stock of an item by the price?
Hi Nelson, there's no fully comprehensive guide to all possible joins. However, the following blog entry will show you a full list of Business One tables, their 4 letter code, and the primary key for the table - blogs.sap.com/2017/04/27/list-of-object-types/ Other than this, you need to use a combination of the query generator and the 'system information' menu item to find your way to what's associated with what. Notice how some field names in the query generator window will be in bold letters (6:58 in the video) That means that there's a natural inner join possible to another table. Click and hold your mouse button on the bold field name, and drag and drop it over top of the table code space on the left-hand side of the query generator. This will automatically join the corresponding table. Hope this helps!
EXCELLENT VIDEO. CAN ANYBODY TELL ME HOW TO ELIMINATE ZERO VALUES IN A COLUMN, EXAMPLE SALES,SO MY AVERAGES ARE NOT DILUTED? i AM DEALING WITH THIS NOW; THANKS
Hi Mohammad, there is a setting in SAP Business One that allows you to automatically generate a PDF when the SO and A/R Invoice are added. You can find it under Administration > System Initialization > Printing Preference > Per Document tab. If you select Sales Order, you'll have the option to have the system export to PDF when adding the document. Hope this helps!
Hi Mohammad, Do you mean that you'd like to run a sales analysis by sales employee for a monthly period? If so, have you tried the native 'sales analysis' tool? Perhaps you're experiencing a limitation?
@@ProjectlineCa Sales analysis for a day, MTD and YTD in a single shot along with Live Stock. SELECT T0.ITEMCODE AS "AZ Code",T0.[CodeBars], T2.ItmsGrpNam AS "Family", T0.ItemName,T0.OnHand AS "AZ In Stock", (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'JAN ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'FEB ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'MAR ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'APR ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'MAY ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 6 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'JUN ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'JUL ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'AUG ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'SEP ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'OCT ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'NOV ', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'DEC ' FROM dbo.OITM T0 LEFT JOIN dbo.INV1 T1 ON T1.ItemCode = T0.ItemCode INNER JOIN OITB T2 ON T0.[ItmsGrpCod] = T2.[ItmsGrpCod] GROUP BY T0.ItemCode,T2.ItmsGrpNam,T0.[CodeBars], T0.Itemname,T0.OnHand,YEAR(T1.DOCDATE) HAVING YEAR(T1.DOCDATE) = YEAR(GETDATE())-0 ORDER BY T0. ITEMCODE
This is a great video even for the more experienced report writer. Query Manager is great for setting up the initial base queries.
Great explanation. Really, this video is one of the best clips on UA-cam that explains all the features of Query Generator in SAP Business One
Thank you man
Nice presentation! Its really helpful for beginners to understand the concept. Thank you
H. It's very useful. Especially for those newbies like me. Thumbs up!
Thank you very much for sharing yr knowledge! At least now i can DIY to play around with the software. It will be a shame to put such functions to waste! ~ From Far Away Country @ Equator
Great video! Thank you so much for sharing.
Is it possible to make math operations in the query? for example: multiply the stock of an item by the price?
Thank you so much, this was very helpful and useful
Great Work. Thank you, very beneficial webinar.
Glad you found it helpful!
Excellent presentation...it has helped me to cleared some basic confusion...
Happy to hear this was helpful!
Awesome, thank you
extremely helpful. Kindly share Inner Join on different tables
Hi Nelson, there's no fully comprehensive guide to all possible joins. However, the following blog entry will show you a full list of Business One tables, their 4 letter code, and the primary key for the table - blogs.sap.com/2017/04/27/list-of-object-types/
Other than this, you need to use a combination of the query generator and the 'system information' menu item to find your way to what's associated with what. Notice how some field names in the query generator window will be in bold letters (6:58 in the video) That means that there's a natural inner join possible to another table. Click and hold your mouse button on the bold field name, and drag and drop it over top of the table code space on the left-hand side of the query generator. This will automatically join the corresponding table. Hope this helps!
I try to enter ORDR on my SAP query generate but It does not show autocomplete like you. what's the issue? Thanks
Good one.
A few handy tips, thanks
Glad you found it helpful!
Can you Kindly please share the query generator table formula used to generate Production Order planned vs Actuals report
Do you mean the database tables that related to planned versus released production orders?
EXCELLENT VIDEO. CAN ANYBODY TELL ME HOW TO ELIMINATE ZERO VALUES IN A COLUMN, EXAMPLE SALES,SO MY AVERAGES ARE NOT DILUTED? i AM DEALING WITH THIS NOW; THANKS
Is it possible During Sales Order and AR Sales invoice automatically pdf generate.
Hi Mohammad, there is a setting in SAP Business One that allows you to automatically generate a PDF when the SO and A/R Invoice are added. You can find it under Administration > System Initialization > Printing Preference > Per Document tab. If you select Sales Order, you'll have the option to have the system export to PDF when adding the document. Hope this helps!
Good one☺👍👌
Thanks! Glad you found it useful!
Could you provide the month wise sale employees subtotal value in one report.
Hi Mohammad, Do you mean that you'd like to run a sales analysis by sales employee for a monthly period? If so, have you tried the native 'sales analysis' tool? Perhaps you're experiencing a limitation?
@@ProjectlineCa
Sales analysis for a day, MTD and YTD in a single shot along with Live Stock.
SELECT
T0.ITEMCODE AS "AZ Code",T0.[CodeBars],
T2.ItmsGrpNam AS "Family",
T0.ItemName,T0.OnHand AS "AZ In Stock",
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 1 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'JAN ',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 2 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'FEB ',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 3 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'MAR ',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 4 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'APR ',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 5 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'MAY ',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 6 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'JUN ',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 7 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'JUL ',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 8 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'AUG ',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 9 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'SEP ',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 10 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'OCT ',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 11 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'NOV ',
(SELECT SUM(T1.QUANTITY) FROM INV1 T1 with
(NOLOCK) WHERE MONTH(T1.DOCDATE) = 12 AND T1.ITEMCODE = T0.ITEMCODE AND YEAR(T1.DOCDATE) = YEAR(GETDATE())-0) AS 'DEC '
FROM dbo.OITM T0
LEFT JOIN dbo.INV1 T1 ON T1.ItemCode = T0.ItemCode
INNER JOIN OITB T2 ON T0.[ItmsGrpCod] = T2.[ItmsGrpCod]
GROUP BY T0.ItemCode,T2.ItmsGrpNam,T0.[CodeBars], T0.Itemname,T0.OnHand,YEAR(T1.DOCDATE) HAVING YEAR(T1.DOCDATE) =
YEAR(GETDATE())-0
ORDER BY T0. ITEMCODE
Good one✌️
Thank you, glad you enjoyed!
This is a great video even for the more experienced report writer. Query Manager is great for setting up the initial base queries.