SAP Business One Query Generator Tutorial

Подписаться 3,3 тыс.

Просмотров 51 тыс.

50% 1

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

Наука

Опубликовано:

29 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 31

@anselvanheerden3074 2 года назад

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

@user-ki7ni1ri5r 10 месяцев назад

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

@cvpawar 4 месяца назад

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

@beomkomap 6 лет назад

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

@amroalmaqablehabs6503 2 года назад

Thank you so much, this was very helpful and useful

@akoj3262 3 года назад

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

@Rumulous 3 года назад

Great Work. Thank you, very beneficial webinar.

@ProjectlineCa 3 года назад

Glad you found it helpful!

@jesusbrea8807 3 года назад

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?

@victorfernandes4484 2 года назад

Awesome, thank you

@TheInterestedable 2 года назад

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

@ProjectlineCa 2 года назад

Happy to hear this was helpful!

@jon-uo3dk 5 лет назад

A few handy tips, thanks

@ProjectlineCa 5 лет назад

Glad you found it helpful!

@Icoholic 3 года назад

Good one.

@nelsonkyobya8759 3 года назад

extremely helpful. Kindly share Inner Join on different tables

@ProjectlineCa 3 года назад

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 6 лет назад

Good one☺👍👌

@ProjectlineCa 6 лет назад

Thanks! Glad you found it useful!

@sameerpangat2797 3 года назад

Good one✌️

@ProjectlineCa 3 года назад

Thank you, glad you enjoyed!

@pdcode1069 4 года назад

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

@giancarlocerza9159 2 года назад

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

@Lizwi8476 2 года назад

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

@ProjectlineCa 2 года назад

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

@fewrose 4 года назад

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

@ProjectlineCa 4 года назад

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!

@fewrose 4 года назад

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

@ProjectlineCa 4 года назад

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 4 года назад

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