Creating Custom Queries in SAP Business One

There are two tools for creating custom queries in SAP Business One, both of which will walk you through making your own query:

1. Query Generator
2. Query Wizard

The end result of both tools listed above is a results window with the actual query statement in the top portion of the window and any data that was retrieved in the bottom section. I generally use an external query writer to create my Query (SQL) statements and to work out the final results because the screen editor does not allow you to re-size the query statement work area, which makes it hard to work on a query that is long.

So, assuming you are using an external editor, the only way I have found to get a new query into SAP Business One is to:

1. Start the Query Generator (located under the Reports menu on the main menu) and click the Execute button when the window opens (beginning in v2007, go to Tools on the drop down menu and select Queries).
2. The Query Results window is displayed, but there is nothing in the results. In the query statement box above, you will see "SELECT *’. We will be replacing this with your query.
3. Click the pencil icon to the left of the query statement window. This allows you to edit the query.
4. Position your cursor in the grey area where the current query statement is and remove the "SELECT *’ or modify it to match your current query. The field supports common editing (Copy, Cut, Delete, and Paste) by right clicking the grey area. put your query in that space.
5. Save your query. There is a button in the lower right hand side of the window. The Query Manager window will appear. Enter in your query’s name and select a category.

Thanks to Ed Monk of SBONotes.com

« SAP Business One The Anatomy of a SQL Statement: Rename your columns and tables (Part 3) | Home | XL Reporter »

• 
  
  Need help? Click Here.