Business
Data & Analytics
SAP Business One
SQL Query Training for SAP Business One
4.4 (126 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
571 students enrolled

SQL Query Training for SAP Business One

No jargon course on queries by a friendly SAP Business One consultant
4.4 (126 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
571 students enrolled
Created by Michael Taylor
Last updated 7/2018
English
English [Auto-generated]
Preview this course
SQL Query Training for SAP Business One
Current price: $34.99 Original price: $49.99 Discount: 30% off
5 hours left at this price!
Buy now
30-Day Money-Back Guarantee
This course includes
  • 2.5 hours on-demand video
  • 15 articles
  • 72 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Assignments
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Design SQL queries for data or export
  • Build basic and advanced SQL queries
  • Find data and create quick ad-hoc reports for managers
  • Create SQL views and stored procedures for use a Crystal Report datasources
Requirements
  • You should be familiar with SAP Business One and want to build queries / reports
  • A working SAP Business One environment (SQL or HANA)
Description

My goal is to take any user and make them into an SQL query expert.  Without overly technical jargon and unnecessary complexity.

I include real-world experience and examples of queries written for actual customers for reference.  I show you exactly how I build queries, find tables and how to get your information out of SAP quickly to generate ad-hoc reports.

We will also be covering more advanced topics including SQL views, stored procedures and how to use SQL datasources in Crystal Reports.

If you have already done my Crystal Reports course, this course is a logical next step and a great way to improve your skills.  Do reports yourself, save money and be in control of your data.

This course includes the ability to ask me questions and get real answers.  I monitor the Q&A section constantly and will reply generally within 24 hours with guidance.

Who this course is for:
  • SAP Business One users and consultants who want to learn how to build SQL queries.
Course content
Expand all 36 lectures 02:52:13
+ – Introduction
1 lecture 02:19

Learn what SQL is, why you would want to use it, what a query is and how to think of building SQL queries for your business or customers.

What is SQL? What is a Query?
Preview 02:19
+ – SQL Query Design
5 lectures 23:51

Where to start building a query, how I design queries and how to scope a query.

Where to Start / Query Design
Preview 02:51

How to find data in SAP Business One.  Using "System Information" to identify tables and columns and using Query Generator to track down table joins.  I also describe my process for planning and show a simple example of a query map.

Finding Data in SAP Business One: Basic
Preview 08:29

Learn the tools I use and the process I follow to build queries quickly and effectively. Staying organized and using the best tools will increase development speed dramatically.

My Process & Tools
07:28

Get started with step-by-step instructions on downloading and setting up your tools.

Activity: Getting Your Tools Together
02:50
Practice what you have learned so far. Use the lecture material to find a list of fields and information.
Find and Identify Fields
4 questions

Learn some powerful introductory tips and tricks to accelerate your learning of SQL.

Article: Advanced Tips & Tricks
02:12
+ – SQL Query Basics
9 lectures 39:33

Learn the SELECT, FROM, WHERE and ORDER clause format and how to use each section.  How to do a basic query format and my pro-tips for making an easy to read query.

Basic SQL Syntax
06:45

More detail on the FROM clause.  Learn how to join different tables together and how the structure of your query will affect your output.

SQL Table Joins
02:42

More detail on the WHERE clause.  Learn how to filter your query and how to use SQL operators (AND, OR, LIKE, etc.).

SQL Query Filtering
06:12

Filtering dates is a common request for SQL queries. This lecture goes deeper into the techniques for handing a WHERE clause with date functions.

SQL Query Filtering with Dates
06:20

SAP Business One uses a specific format for adding parameters.  Learn my pro tips for interacting with these parameters and my trick for pulling dates properly.

SAP Business One Parameters
06:56

Save queries into the Query Manager.  Learn how to set authorizations and how to filter and export your data.

Deploy Your Queries to SAP
06:04

Every loves to manually manipulate cells in a spreadsheet.  This lecture covers various methods to do this in SAP Business One.

Exporting Your Data to Excel
02:01

If you enjoy using this course, please consider leaving a review... (Read More)

RATE AND REVIEW PLEASE
00:27
Activity: Write and Deploy Your First Query
02:06
A couple of exercises for practicing your SQL query writing skills.
SQL Query Basics
5 questions
+ – SQL Query Intermediate Topics
8 lectures 51:17

Learn about how to structure alert queries.  **WARNING** do not put SAP parameters into alerts, it will break your alert system.  You must use a query that can be written with no human input.  See my lecture for more.

Alert Queries
05:47

Add custom coding for most fields in SAP Business One.  Allows you to perform calculations to output various dollar amounts, strings or other pieces of information dynamically on-screen.  See how it's done!

Formatted Searches
14:19

IF statements are not used inline for normal SELECT statements.  CASE is how you would evaluate different conditions.  It's very useful.

CASE Satements
07:12

Use SUM, MAX, COUNT and learn how to group queries to get totals.

Aggregate Functions and Grouping
08:14

Want to learn more about aggregate functions?  This short article explains a little more on the topic.

Article: Aggregate Functions in Detail
01:08

Be a more efficient "Script Kitty" by taking queries from the SAP Business One forum.  Learn how to understand them for yourself, adapt them and make them work for your purposes.  Tips and tricks for getting started learning from more advanced queries.

Article: Understanding and Adapting Queries from SAP Forums
02:26

The COALESCE() function will save your day.  Coalesce will avoid many mistakes and headaches as you learn to build queries and is used in almost every one of my queries.

Coalesce Will Save the Day
05:53

Step-by-step activity to practice the material from the intermediate lectures.

Activity: Create a Customer Statistics Query
06:17
Some practice questions to work on topics in the intermediate section.
SQL Query Intermediate
5 questions
+ – SQL Query Advanced Topics
7 lectures 49:09

Learn how to avoid messy joins by using subqueries.  And learn how to build your own tables with a derived table structure.  Two very useful functions with regards to building SAP Business One queries.

Subqueries and Derived Tables
09:52

How to create and deploy an SQL view.  How to use UNION ALL to make stacks of queries.

SQL Views
09:58

Declare and use variables to simplify your query structure.

Declaring SQL Variables
05:04

The king of complex derived and custom functions.  This technique will give you the ultimate in query building excitement.  Have your cake and eat it too.

Stored Procedures
10:20

Learn how to deploy SQL views and StorProcs (Stored Procedures) with your Crystal Reports.  This powerful technique works around some table linking limitations and gives you maximum control over the results pulled and presented in your reports.

Views and Stored Procedures for Crystal Reports
11:29

Why you should use B1UP to create all of your queries.  It solves all the common SAP Business One out-of-the-box limitations and provides convenient and logical enhancements for all SQL query building.

Article: B1 Usability Package Query Advantages
02:06

The logic between SQL and HANA are very similar, some of the syntax changes slightly.  This lecture contains resources on the difference between SQL and HANA syntax including 130 SQL --> HANA converted query examples.

Article: HANA Differences + 130 Examples
00:20
A couple of exercises for practicing your advanced SQL query writing skills.
SQL Query Advanced
5 questions
+ – Downloadable Resources
1 lecture 00:06

A list of course resources and examples.

Document Resources
00:06
+ – Common Query Examples with Documentation
5 lectures 05:56

This one is a great general purpose query.  It allows the user to get basically any typical piece of BP information out of the system.  Contains Customers and Leads.

Business Partner Master Data Raw Export v1.0
01:56

Everyone occasionally needs a raw list of their items.  I include the most important fields, you could always add to it but it should give you 90% of the likely needed columns including prices for up to two price lists (just duplicate the subqueries for more price lists, includes a price list code finder query).

Item Master Data + Price Lists Raw Export v1.0
00:53

A query to pull invoice and credit note information for a specific date range.  Works within SAP using the date parameter hack.

Sales Analysis by Date Range Export v1.0
02:06

Simulates the inventory audit report functionality.  Perfect to match your audit report or add more fields. Great for understanding back dating of inventory principles.  If you need to run stock for last week, last month or last year this query is a good start.

Inventory Audit Report Export v1.0
00:31

Pull stock and availability information for items currently in your warehouse.

Current Stock in Warehouse v1.0
00:29