Baan Query Tool 2.

7
User Guide Baan LN
NAZDAQ Nazareth Data Quest

10

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

Contents
1 2 2.1 2.2 2.3 2.4 2.5 2.6 2.7 2.8 3 Introduction .......................................................................................................................................... 3 Make a Query ....................................................................................................................................... 4 Create a new Query.......................................................................................................................... 4 Delete Query ................................................................................................................................ 5 Rename a Query ........................................................................................................................... 5 Add Fields to Query .......................................................................................................................... 5 Printing a simple Query .................................................................................................................... 8 Add Enumeration, Text and Array Fields ....................................................................................... 10 Add Fields from a second Table (Table Linker).............................................................................. 12 Add Formula fields ......................................................................................................................... 15 Customizing the Query form .............................................................................................................. 18 3.1 3.2 3.3 4 5 6 Hiding Fields from Query ........................................................................................................... 18 Save Defaults in the Form .......................................................................................................... 18 Zoom into a Session ................................................................................................................... 19

Sorting output .................................................................................................................................... 20 Customizing Query Layout ................................................................................................................. 21 Output Options................................................................................................................................... 22 6.1 Wide Reports .............................................................................................................................. 22

7 8 9

Organizing Queries ............................................................................................................................. 23 Run-only Query................................................................................................................................... 24 Automating Queries ........................................................................................................................... 25 9.1 9.2 9.3 Run Query inside Baan Run Program ........................................................................................ 25 Run Query From Command-Line ............................................................................................... 26 Run Query From BECS ................................................................................................................ 27

Page 2 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

1 Introduction
There is a new and elegant way to create queries and ad-hoc reports on your Baan data: Baan Query Tool. This Baan session comes with an easy to use graphical interface which allows you to create dynamic queries from your Baan database.

Pick a Query

Chose a table

Use to quickly find a field

Query Selected Fields Remove field from query

Add a field to query

Change fields order

Print Query

Page 3 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

2 Make a Query 2.1 Create a new Query

In Baan Query Tool you can create a query that display a table, with the field you want, from one or more tables in the database. To create a Query in Baan Query Tool:
1) You need to open Baan Query Tool session, If you dont know how ask your administrator 2) The following screen will be displayed:

To start working in a new Query click on menu bar: Query -> New and insert a query name 3) Now you have a new query with a new name 4) You can view all your queries from this drop down control under Queries for user<username>

Page 4 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

2.2 Delete Query

You can delete the query you created by loading the query and click on menu: Query -> Delete

2.3 Rename a Query

You can rename the query you created already by loading the query and clicking on menu: Query -> Rename, insert a new name and click ok

2.4 Add Fields to Query

Now after you have created a new query, you are ready to add fields to it. To add fields to query:
1) Choose the table

Find icon to quickly find your table

If you have the table name insert it on the table field, if not click on find icon and a selection window appears:

Page 5 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest Display All Tables will go to the standard Baan session Display Tables Display Tables by Description will open a new window that will allow searching for the table by description and table code Display Current Query Tables will display only the tables that are used in the current query. For example if we click on Display Tables by Description, a window like this appears, in Search field we insert Sales order

Select a table from the list and click Ok 2) Add fields Add the fields you want from the table, by selecting the field and click the add button

Add button

Page 6 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

You can also find fields without knowing the 4 letter code. You can simply search for a certain field using the Search that performs a search on both the field name and field description. As an example, if we want to add the Delivery Date for a sales order line but are not sure of the name of the field, we can simply search for Date as below and the only fields that contain Date in their names will appear, making our life easier in choosing a field.

Page 7 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

2.5 Printing a simple Query

Queries created can be printed to Baan devices. The following is an example of printed a simple query.

1) To Print a Query, press Print Result

2) A windows like this appears (Query Form)

Page 8 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

3) Click Continue button and you get the option to choose a device

4) After we specify the device, query results will be printed (Query Report Output).

The Query Form and the Query Report output can be customized to enable greater possibilities and flexibility as we can see in the following chapters.

Page 9 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

2.6 Add Enumeration, Text and Array Fields

Baan Query Tool supports working with Enumerated fields and Text fields and array fields, unlike non-Baan query and reporting tools that do not recognize these fields. As an example, if you add field tcibd001.kitm (Item Type) to your query, Baan Query Tool recognizes this as an enumerated field. In the Form, you will be able to choose between Manufactured, Purchased and so on. In the report output, these values will appear instead of the number (between 1 and 6).

If we choose a field that is a Text field, the text that is associated will be printed in the query report (and not the text number).

Page 10 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

You can also chose a field that is an array and Baan Query Tool will let you print a specific range of each value of the array, field used tdpur400.ratf

Array will also be visible in output results

Page 11 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

2.7 Add Fields from a second Table (Table Linker)

In the same query you can add fields from more than one table, and link these tables together using a common field that you can select whenever a field from another table is added. After you added fields from the first table to your query, you can add fields from another table by these steps:
1) Find the second table that you wish to have fields from it 2) After you have the table selected, add fields to query and a window will be opened Table already added before Table you want to link

3) Specify a field from both tables that is common for both of them to establish the link using it. You can browse tables already linked in this query, and browse field inside the selected table

4) After this you can add any field from this second table Note: You can repeat this by adding a third or more tables to query

Page 12 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

To view or maintain a link between two tables in the query: On menu click: Query -> Table Linker

The possible actions for each link are the following: Add Link add another link to query (same as we did above) Link Type Change an already existing link with 3 link types available:

Remove Link we can remove already exists link from this query

Example: We want to create a query that displays Sales Orders lines with customer name and Item description with price and quantity for each line In order to display all this information we need to link 3 tables together: - Tdsls401 That contains Sales order Lines. - Tccom100 that we will link to in order to get customer name - Tcibd001 we link this table to get item description

Page 13 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

Here is an example of the query in Baan Query Tool (To view field details on menu click: View -> View Fields Details):

Print Results for this query:

Page 14 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

2.8 Add Formula fields

In Baan Query Tool, we can define formulas that calculate a result from table fields and other formulas. To create a new formula and add it to a query:
1) On menu click: Field -> Add Formula

Form fields: a. Column name name of formula field. Default is generated by BQT. b. Column Description Description of column, as it will be displayed in the report. c. Column Type Data Type of the formula field: Possible values are Date, Long, Double d. Column Width specify the column width as it will be displayed in the report. e. Column alignment formula alignment in report. Options are: Default, Center, Right and Left

Page 15 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

f.

Column format formula format. You can chose from a list of formats that are supported in your Baan system. Sample expression of 1234.00 (or 1234,00) is displayed to ease the choice of the format.

2) Specify the formula

Formulas may include 2, 3 or 4 fields.

<Field1> <Operator> <Field2> Or (<Field1> <Operator> <Field2>) <Operator> (<Field3> <Operator><Field4>)

Operators supported are +, -, *, / Example of a formula: We want to display all the items we have in database, with their Material Cost and Standard Cost and also to create another formula to calculate the percentage of material in the Standard Cost. The formula is (Material Cost / Standard cost) * 100

Page 16 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

Table we will use is tdsls401.The Query is the following:

Formula named: Formula Discount

Now output results will be like this:

Page 17 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

3 Customizing the Query form

When Printing Query results, we acquire this window where we can specify the ranges of each field:

3.1 Hiding Fields from Query

All query fields are automatically populated to the form. We may decide to hide some fields or show them. This is done by field settings -> uncheck Include field in form

If all the fields in the query have this option unchecked, we will not have a form at all. If we have a form with few fields, but wish to hide it for a special reason (run in job for example), we can hide it by going to the menu: Options -> Device and check Hide Print Form. Once we uncheck this option, the form will return as it was before.

3.2 Save Defaults in the Form

If you made some changes to a form and want them to be saved for further use, click on Save Defaults button. Every time we print this query, the form will receive the same values we have saved. We may decide to hide the fields we have added values to. This is a way to give hard coded constrains on the query.

Page 18 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

3.3 Zoom into a Session

For fields that are related to tables, we can specify a zoom session, in order to better browse the table for a list of values available for this field For example: If the Item is one of our form fields, we can specify to zoom to session tiitm0502s000. Inserting a Zoom session is done manually or by zooming to the session Display Sessions. It is recommended you find the zooming session in a standard Baan session and copy the code number.

When printing this query, we get a zoom icon next to Item field

Using this we are able to browse for giving a better range constrain on our query

Page 19 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

4 Sorting output
You can sort the output results by specific fields. To do this on menu click: Options -> Sort, a window like this appears

You can zoom for what field you want to sort by:

Ascending and descending sorting are available. Note: You cant sort according to a formula field

Page 20 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

5 Customizing Query Layout

The appearance of every field in the query report can be customized for better presentation. In order to customize a certain field appearance, double click on the field on the left pane, and a window similar to this will appear

The following options are available. Rename Column Description to a new description Specify a specific size for column width to appear in output (to override default that may be too big) Change column alignment in output results (including column data and column title). The following options are available: Default Field default from Baan/ERP Ln Center, Right, Left Specify the format of the field as it will be displayed in the result. The formats available are the ones defined in the system.

Include field in results If this option is checked the field will appear in output result (Default is Yes for all fields). In order to hide a field from the output, uncheck it.

Page 21 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

6 Output Options
For automating the process of generating queries, you can save your default print device and the output file for each query (the parameter that is passed to the device). This is particularly useful in jobs.
1) In Baan Query Tool click on menu: Options -> Print 2) You will acquire this window

When printing, the query will automatically be sent to the device (B2Win in Excel format in this case) and the user wont be prompted to enter the device name. If the Print form is also hidden (Hide Print Form), pressing on the query will result in producing an Excel Sheet with no questions being asked on ranges and print devices.

6.1 Wide Reports

Standard Baan devices have a limitation of 300 characters in width. To bypass this limitation, and in order to make queries with unlimited number of characters in the width, Wide queries can be defined. Wide reports need to go to a Baan device that supports more than 300 characters. Printing Wide reports to standard devices (Like D or Printers) will not produce the desired results. The following are a sample of two Print devices that support wide reports: ASCIF Writing to an ASCII file on the Baan server (Standard device) B2Win With B2Win (Add-on from NAZDAQ), you can print your wide query output into Excel Use B2Win 6.6 or higher
Page 22 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

7 Organizing Queries
Queries are organized in the order they have been created. In order to change the order of your queries, you may wish to use the Query Organizer. This is useful in Run-Only Query Mode (covered in the next chapter) To organize your queries, on menu click: Query -> Organizer

From this window you can change the order of your queries by selecting a query and clicking on Up or Down.

Page 23 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

8 Run-only Query
Administrators and Key Users can create queries. Regular users should not be able to change queries, but only run them and use the results. For regular users, a run-only query session is available - tccom2bqt27p Usage: Open session: tccom2bqt27p
View users list Print Query

List of queries for user bsp

Page 24 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

9 Automating Queries 9.1 Run Query inside Baan Run Program

After you have created a query you can run it automatically without opening the Baan Query Tool interface. You can do that by giving the query number as an argument to Baan Query Tool Run only session as in the following: tccom2bqt27p BQ000000000124 You can acquire the Query number from Baan Query Tool session:

If you wish to automate a query that is written by a different user, the query owner user name should be passed in parameter as in the following example: tccom2bqt27p BQ000000000124,bsp

Page 25 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

Queries can be added to the Baan menu as in the following

9.2 Run Query From Command-Line

Queries can be run from a command line. Here is an example: call "C:\Program Files\Infor\BW\ERPLN\bin\bw.exe" "c:\Program Files\Infor\BW\ERPLN\lib\User\Rain.bwc" tccom2bqt27p BQ000000000037,baan

Page 26 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

9.3 Run Query From BECS

You can create shortcut to your query inside BECS To do this: 1) 2) 3) 4) Open BECS Click on Menu: File -> New to create a new configuration Insert Hostname, user, (According to your system) Go to: BShell Environment tab, In command field insert: tccom2bqt27p <Query code>,<user> (Where Querycode is the query you want to run from this shortcut, and user is the user id that created the query )

5) Click on save as and you will have a new shortcut inside BECS, named TestQuery as we specified for example

6) Launch TestQuery will print the query in the device you chosen

Page 27 of 28

Baan Query Tool 2.7

User Guide NAZDAQ Nazareth Data Quest

Warning

Trade Marks
Baan Query Tool is a trade Mark of NAZDAQ Ltd. All other referenced company and product names may be trademarks or registered trademarks of others.

NAZDAQ
For Support, please contact [email protected] Phone Fax +972-4-608-0023 +972-4-647-0112

Our office Hours are Monday - Thursday 10:00 19:00 and Friday 10:00 17:00 GMT +2 Check our website for other Baan and ERP Ln Related Products www.nazdaq-it.com

December 2010

Page 28 of 28