Creating and Linking User-Defined Queries (UDQs) in SAP Business One, Web Client

Objectives

After completing this lesson, you will be able to:

  • Create user-defined queries
  • Link a user-defined query to use it as a data source for analytics

An Overview of User-Defined Queries in SAP Business One, Web Client

User-Defined Queries (UDQs) are an important part of the analytics functionality of SAP Business One, Web client.

You can create SQL scripted queries (UDQs) in both SAP Business One, Web client and the Desktop client. A UDQ written in either client can be used in both clients. However, update queries or parameter type queries written in the Desktop client will not be visible in the Web client.

When a UDQ is opened in the Web client it is viewed via a list view screen, with all the standard list view functionalities associated with it, such as the table view, chart view, card view, date offset, date aggregation, and view saving functionality.

When a UDQ created in either the Desktop or Web clients is edited in the Web client, you can apply additional settings to it. You can perform the following:

  • Change the field names.
  • Apply links to fields where appropriate, for example, when data is viewed, choosing the link will open the data source associated with it.
  • Change numeric fields, which by default are set to be measure fields, to be treated as dimension fields.
  • Set numeric fields totals to, for example, count, sum, average, min, max. This will affect the list view display of table view grid totals and grouping totals.
  • Set the rounding display of numeric fields to different rounding options.
Note

UDQ Categories, which also control query authorization access, are currently set up and managed in SAP Business One, Desktop client.

In this video, you’ll learn how to create user-defined queries in SAP Business One, Web client.

Video summary

  • To create a user-defined query (UDQ) in SAP Business One, Web client,use the User-Defined Queries app.

  • For a new query, enter a name and choose a category. Then in the SQL Statement, type the query syntax. While typing, the word prompter appears, with suggested field, tables, and key words for you to choose from. In this example the query statement we typed was:

    Select:

    T0."DocEntry", T0. "DocNum", T0. "ObjType", T0. "DocTotal",

    T0. "CardCode", T0. "CardName", T0. "DocDate", T0. "DocDueDate", T0. "TaxDate"

    From OINV T0

    SELECT and then the required field names (or star to select all table fields)

    FROM to specify the table you want to select the fields from.

    Call the table you specified table Zero T0.

  • When you complete the SQL Statement and click away, the Columns section populates with the user-defined query information:
    • The fields names in the Description column can be edited.
    • A field type can be either a dimension or measure. Dimension is a type of a non-numeric field, and it can't be edited. Dimensional fields contain qualitative values, such as names or dates. It is common to group by this field type.
    • Measure fields contain quantitative values that you can measure. For numeric fields ,the type can be either Measure or Dimension, but the default type is Measure.
    • Measure fields have aggregation methods available for them, and the field values can be rounded to the decimal using a set the decimal places definition.
  • You can link a field to another database table. Some fields automatically connected.
  • Every user-defined query in the Web client can be presented as a list view in all types: table view, chart view, and a card view. You can add any filters from the list view fields.

  • You can adjust the available table fields just as in any other list view and you can choose to see more details in columns containing a link.

Note

When setting a UDQ to be active, it will be available to select as data source field for cards on the General Overview and a list view in the Linked List Views app.

How to Link a User-Defined Query to Use as a Data Source

In the Linked List Views app, you can link a list view of previously created query to the Master Data and Document Objects.

The following steps provide an outline of the task demonstrated in the Linking a User-Defined Query to Use as a Data Source for Analytics video. In the video, we linked the list view of a previously created query to the Business Partner master data.

Steps

  1. Choose the Business Partner tile, and from the list, choose a customer record.

  2. Choose Views.

  3. Choose Manage or if this is the first link to this object, choose Define New.

  4. Choose Create New Link.

  5. In the Link Name field, enter a link name.

  6. Choose the List View dropdown menu, and select a pre-defined user defined query. In the video, we used a query dealing with the A/R invoices table.

  7. In the View field dropdown, select the relevant view.

  8. In the List View Filter Fields column, open the dropdown, and select a field from all the fields that can be filtered in the user-defined query list view. In the video, we chose the Customer Code field.

  9. In the Object Fields column, open the dropdown, and select an object field. In the video, we chose the BP code.

  10. To save the new link, choose Save.

  11. Go back to the business partner master data record.

  12. From the Views dropdown, select the linked query list view. Here you will see that the query list view is filtered by the customer code, and therefore all the invoices in the list belong to this customer.

Result

You have linked a user-defined query list view. This will provide business-relevant information to the user when they are reviewing master data or processing documents.

Log in to track your progress & complete quizzes