3 Review(s)

Overview of Mastering SQL Queries for SAP Business One Practical SAP query examples from an SAP Business One expert Detailed steps to create and troubleshoot SQL queries for Alerts, Approvals, Formatted Searches, and Crystal Reports Understand the importance and benefit of keeping SQL queries simple and easy to understand Benefit from special tips and tricks related directly to SQL queries within SAP Business One SAP Business One is an integrated Enterprise Resource Planning (ERP) software which offers an ideal solution for growing small to midsized businesses. For such companies, retrieving the most relevant information from their business data can be key to standing out from the competition. SAP Business One is a rapidly growing software package and this book is timely in giving those businesses an advantage in the area of Business Intelligence. Having SQL query skills in- house is the most important and cost-effective move you can make in this growing field. This practical guide will provide you with the skills to gain more specific business information from SAP Business One by using SQL queries. It will provide you with solutions for solving complicated report related problems, covering basic tools like the Query Generator and Query Wizard. More advanced content like using queries with Crystal Reports will also be delved into. SQL query is one of the advanced tools available in SAP Business One which is easily learned and quickly utilized. By referring back to and applying the many examples in this book, you will be able to create and run correct, and therefore effective, SQL queries to help your business. The book begins by teaching a clear definition of the SQL query, and covers the data dictionary and table links. Coverage will then jump to a higher level of complex SQL queries, discussing features like FMS. Along the way more advanced SQL Query topics will be covered, such as extending the scope of basic SQL queries for more complicated cases. You will ultimately gain in depth query knowledge to bring more Business Intelligence into SAP Business One. Gain the skills to provide much needed business information within your company with SQL queries for SAP Business One What you will learn from this book : Quickly get up and running with SAP's Query Generator and Query Wizard Develop best practices for creating SQL queries for use with SAP Business One Discover how "keeping it simple" will lead to a better SQL query Assign SQL query security within your company Learn how to get user queries ready for approval Optimize user queries for alerts through step-by-step instruction Build special queries for Formatted Search Create the core part of the query to utilize the SBO Transaction Notification stored procedure Master Query Print Layout design Improve your Crystal Reports with SQL queries Employ expert-level query methods Reduce your learning curve by following real-world examples and expert advice Achieve advanced SQL query building and troubleshooting skills Approach This is a practical guide providing comprehensive solutions for SQL query problems, and is full of concrete real-world examples to help you create and troubleshoot your SQL queries in SAP Business One. Who this book is written for If you are a system administrator who uses SQL query as your tool of choice for solving specific problems throughout SAP Business One, then this book is for you. It may also be useful if you are a developer or consultant using this technology, and can benefit end users by improving your search for important business information. A rudimentary knowledge of SAP Business One and SQL Server is required to use this book efficiently. Examples covered are relevant to SBO 2007A users, for which the 8.8 release is mostly compatible. All SQL query examples within the book are verified under SQL Server 2005, so they are guaranteed to run under this release, in addition to SQL Server 2008. Non-SAP Business One users can also gain knowledge from the many examples throughout the book. It is hard to find another book with so many SQL query examples. About the Author Gordon Du studied System Engineering & Computer Science at the Nankai University in Tianjin, China. He has more than 25 years of experience with diversified information technology fields. His experience with SQL goes back to 1987. He has passion for helping others whenever he can. He is the top contributors on the SAP Business One forum ever since August 2008. Over 25% of all his points are related to solving SQL questions and problems posted by other forum members. Gordon has worked and trained in China, Singapore, USA and Canada. In China, he is the first person to successfully implement an international software package in a domestic company. He is also a Microsoft Certified System Administrator, Microsoft Certified System Engineer and Microsoft Certified Database Administrator. Table of Contents Preface Chapter 1: SAP Business One Query Users and Query Basics Who can benefit from using SQL Queries in SAP Business One? Consultant Developer SAP Business One end user Non-SAP Business One users SQL query and related terms RDBMS Table Field SQL T-SQL Subsets of SQL Query Data dictionary SAP Business One—Database tables reference Naming convention of tables for SAP Business One Three letter words "O" tables "A" tables Document header tables Document line tables Important table examples Table links—the key for the right query Primary key Foreign key Example of table links within SAP Business One Base tables versus target tables Keeping it simple—The key to build a good query Summary Chapter 2: Query Generator and Query Wizard Query Generator Query Generator overview Left part of Query Generator form Middle and right parts of Query Generator form Executing a query from Query Generator form Query Wizard Query Wizard overview Step 1—Splash screen Step 2—Select tables for the report Step 3—Select fields and sort orders Step 4—Conditions and relations Step 5—Query Wizard completion What is the difference between Query Generator and Query Wizard? Benefitting from built-in system queries Summary Chapter 3: Query Manager and Query Statements Query Manager user interface Display all existing queries Creating and saving user queries Deleting user queries Managing query categories Commonly used statements SELECT—first statement to retrieve data The scope of the value that can be retrieved The numbers of columns to be included Column name descriptions Clauses can follow this statement DISTINCT—duplicated records can be removed TOP—number of lines returned by ranking FROM—data resource can be assigned A single table A group of linked tables Multiple tables separated by commas JOIN—addition table or tables can be linked Inner Join Outer Join WHERE—query conditions to be defined BETWEEN—ranges to be defined from lower to higher end IN/EXISTS—the value list that may satisfy the condition LIKE—similar records can be found GROUP BY—summarizing the data according to the list HAVING—conditions to be defined in summary report ORDER BY—report result can be by your preferred order UNION/UNION ALL—to put two or more queries together Some important functions to return values ISNULL() predicate SUM() function MAX() function MIN() function COUNT() function DATEDIFF() function DATEADD() function DATEPART() function CAST()/CONVERT() function CASE expressions IF expressions Summary Chapter 4: Query Examples Why three categories have been chosen Defining variables for queries Case 4-R1: Four variables in one query Case 4-R2: Variables first or last Date function—where the most problems emerge Case 4-D1: Balance of production for a month Case 4-D2: How to input a fixed date range Orange arrow—an excellent tool for drill down Case 4-O1: Make it simple Case 4-O2: Sales order updating alert with drill down Getting a subtotal from the query Case 4-T1: By Union ALL Case 2: By running total Query for marketing documents Case 4-M1: Overview of BP with selection of realized balance Case 4-M2: Top five items sold Case 4-M3: A filter by notes from OCRD Case 4-M4: Adding sales employees' names to a query Case 4-M5: A case for solution just from deduction Case 4-M6: Goods Receipt PO within 10 days Case 4-M7: Quantity purchased, received, and returned Case 4-M8: Customized sales analysis report Case 4-M9: Average sales per month Case 4-M10: Credit Memo user check Case 4-M11: Delivery date on sales order Case 4-M12: Reducing from two to one line for the sales summary Case 4-M13: Tax code summary Case 4-M14: Sales by states Case 4-M15: Many linked tables in one query Case 4-M16: Sales Order with PO Query for inventory transactions Case 4-I1: Adding stock total to the query Case 4-I2: Adding a total to the query bottom Case 4-I3: Items not delivered within 15 days Case 4-I4: Active item list Case 4-I5: How to find stock taking details Case 4-I6: Query on price updates Case 4-I7: Planned quantity versus in stock Case 4-I8: Adding to the production orders list from a sales order Case 4-I9: Complete item list with or without transactions Query for financial transactions Case 4-F1: Top five customers Case 4-F2: Incoming payment Case 4-F3: Linking an incoming payment with an invoice Case 4-F4: Listing both types of payment transactions Case 4-F5: Incoming payment filtering User query for alert Case 4-A1: Creating a right alert without duplicated lines Case 4-A2: Alert for invoice without base document Case 4-A3: A/R Invoice past due alert Case 4-A4: Special ship to alert for Sales Order Case 4-A5: Open Sales Opportunity alert User query alert guide Miscellaneous query examples Case 4-X1: Query related to service call Case 4-X2: Concatenating two text columns Summary Chapter 5: Securities and Approvals How to handle securities for query usage Giving only a few users the capability to build a query report Creating queries under different categories Query Groups: a tool to assign user permissions How to use query for approval procedures Creating approval stages Creating approval templates Originator Documents Stages Terms Examples of user queries for approval Case 1—Approval for adding delivery document Case 2—"On Account" outgoing payment approval Case 3—Approval for invoice to special customer groups Case 4—Approval for over booking sales order Case 5—None cash outgoing payment approval Summary Chapter 6: SQL Query for Formatted Search (FMS) Formatted Search and User-Defined Values How to work with User-Defined Values Search in existing User-Defined Values according to the saved queries Where do the $ values come from? How to get the value you need from, and for, the FMS query Can you run FMS queries directly? What is the negative sign's function in FMS query? Search in existing User-Defined Values only A typical FMS query application: auto code creation BP code auto generation Item code auto generation Special code auto generation General FMS query examples Case 1—Double quotes should be avoided Case 2—Price value validation on line level Case 3—Populating a UDF from OITM in a UDF on quotation Case 4—Difference between two UDFs into another UDF Case 5—Displaying warehouse name beside warehouse code Case 6—Showing purchase order due date on sales order Case 7—Auto populating the profit center code Case 8—Calculation by three user-defined fields Case 9—Open order reminder in new order Case 10—Commitment checks for warehouse in stock Case 11—Multiplying a field from OITM with a field on order line Case 12—Multiplying two UDF values from two tables Case 13—Last sales price for a customer Case 14—Calling a UDF value in the BOM to Production Order Case 15—Multiplying a UDF value with a system field value Case 16—Eliminating the duplicate lines returned by FMS query Case 17—Getting the sales rep code assigned to an activity form Case 18—FMS query for User-Defined Table (UDT) Summary Chapter 7: SQL Query for Reporting Tools Query Print Layout Designer (QPLD) and its usage Simple query report printing Query Print Layout Designer Working with a QPLD report Creating a QPLD report Editing a QPLD report Saving a QPLD report Printing a QPLD report Deleting a QPLD report Recreating the QPLD report Direct query for Crystal Reports (Command) Working with Standard Report Wizard Creating a new database connection Adding a Command to a report Working with a Command Basic formatting for a Crystal Report Summary Chapter 8: SQL Query for a Stored Procedure Why Stored Procedure is included in this book SBO_SP_TransactionNotification overview How to work with SBO_SP_TransactionNotification Some example queries for this SP Case 1—Blocking an outgoing payment for a specific BP Case 2—Restricting outgoing payments above 20,000 Case 3—Blocking goods receipt entry Case 4—Blocking a sales quotation if no value in row level UDF Case 5—Blocking invoice based on GL account and project Case 6—Blocking GRPO if quantity is more than PO quantity Case 7—Blocking, adding, or updating an order for duplicated BP ref # Case 8—Blocking sales documents based on dates Case 9—Validation service type A/R credit memo Case 10—Blocking goods issue for none super user Case 11—Blocking Goods Receipt PO if no based PO Summary Chapter 9: More Complicated SQL Query Topics The Case expression usage Case 9-C1—Displaying Transtype as code instead of a number Case 9-C2—Combining two queries with a Case expression Case 9-C3—Showing discount percentage for each interval Case 9-C4—Item wise subtotal in a goods receipt Case 9-C5—Updating UDF with different dates Working with a subquery Case 9-S1—Item groups not in use Case 9-S2—YTD sales for two years Case 9-S3—Checking only the similar records Case 9-S4—Showing the last A/P invoice document date for items Using PIVOT to simplify a cross tab style queries Case 9-P1—Monthly sales by geography Case 9-P2—Complete list of all items with/without sales Database query for Excel Creating a new data source New data source added within Excel New data source added from the control panel Query wizard for database query in Excel Microsoft Query window Avoiding pitfalls while building queries Creating a query before knowing the data table structure Complicating the logic instead of simplifying it Trying to do too many things in one query Relying on others' help only Summary Appendix Original transaction list by code Original transaction list by name Object codes and names Index