SAP Business One Query Generator Tutorial

In this SAP Business One tutorial you'll learn how to:
- Use the query generator to create custom SQL queries
- Create alerts from saved queries
- Create dashboard KPIs from saved queries
Find more tutorials on SAP Business One reporting: support.projectline.ca/hc/en-...

[@anselvanheerden3074]

This is a great video even for the more experienced report writer. Query Manager is great for setting up the initial base queries.

[@beomkomap]

H. It's very useful. Especially for those newbies like me. Thumbs up!

[@user-ki7ni1ri5r]

Great explanation. Really, this video is one of the best clips on KZbin that explains all the features of Query Generator in SAP Business One Thank you man

[@cvpawar]

Nice presentation! Its really helpful for beginners to understand the concept. Thank you

[@amroalmaqablehabs6503]

Thank you so much, this was very helpful and useful

[@Rumulous]

Great Work. Thank you, very beneficial webinar.

[@ProjectlineCa]

Glad you found it helpful!

[@victorfernandes4484]

Awesome, thank you

[@jon-uo3dk]

A few handy tips, thanks

[@ProjectlineCa]

Glad you found it helpful!

[@akoj3262]

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

[@TheInterestedable]

Excellent presentation...it has helped me to cleared some basic confusion...

[@ProjectlineCa]

Happy to hear this was helpful!

[@jesusbrea8807]

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?

[@Icoholic]

Good one.

[@nelsonkyobya8759]

extremely helpful. Kindly share Inner Join on different tables

[@ProjectlineCa]

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!

[@varunpatil5187]

Good one☺👍👌

[@ProjectlineCa]

Thanks! Glad you found it useful!

[@sameerpangat2797]

Good one✌️

[@ProjectlineCa]

Thank you, glad you enjoyed!

[@pdcode1069]

I try to enter ORDR on my SAP query generate but It does not show autocomplete like you. what's the issue? Thanks

[@giancarlocerza9159]

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

[@fewrose]

Could you provide the month wise sale employees subtotal value in one report.

[@ProjectlineCa]

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?

[@fewrose]

@@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

[@fewrose]

Is it possible During Sales Order and AR Sales invoice automatically pdf generate.

[@ProjectlineCa]

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!

[@Lizwi8476]

Can you Kindly please share the query generator table formula used to generate Production Order planned vs Actuals report

[@ProjectlineCa]

Do you mean the database tables that related to planned versus released production orders?

[@amitbansal5137]

This is a great video even for the more experienced report writer. Query Manager is great for setting up the initial base queries.