Query Designer

Queries, just as any other object in Dynamics NAV, have their own designer or editor.

To open Query Designer, perform the following steps:

  1. Open the Microsoft Dynamics NAV development environment.
  2. The Object Designer window opens.
  3. On the left pane of the Object Designer window, click on Query to see the list of existing queries, as shown in the following screenshot:
    Query Designer
  4. Select the query 9150 My Customers (or any other existing query).
  5. Click on Design.
  6. The Query Designer window opens as shown in the following screenshot:
    Query Designer

The Query Designer window looks a lot like the Page Designer or the new Report Dataset Designer. This will make it easier to get used to developing queries.

In the Query Designer window, we can select one or more DataItems to define the database table from which we want to retrieve data for the query. Through properties, we can define the relationship between different DataItems values. We can also select the columns or fields that will be included in the query and specify the totaling methods and grouping for the fields. Finally, using properties, we will be able to define filters and to modify the behavior of certain columns, such as reversing their sign.

We will see the fields and properties of the Query Designer window by creating our first query.

Defining our first query

In our first query, we will try to retrieve the items that our customers buy per month. To do so, we will use the Item Ledger Entry table as our main data source, but we will also use the Customer and the Item tables to get additional information from customers and items, such as their name or description.

Let's first define the main data source and the fields that will be retrieved:

  1. Open the Object Designer window in the Microsoft Dynamics NAV development environment and select the Query object type on the left pane of the Object Designer window.
  2. Click on New to create a new query.
  3. An empty Query Designer window opens.
  4. On the first line, in the Type column, choose DataItem from the drop-down list.

    Note

    The first line in the Query Designer window must be a DataItem field and not a Column field.

  5. Select Item Ledger Entry in the Data Source column.

    Tip

    You can choose the up arrow that will appear on the right-hand side of the Data Source column when you select it to see a table list and select the desired table. You can also type in the name or the number ID of the table (if you know the name or the number ID of the table) you want to use on your query.

  6. The Name column will be automatically populated once a Data Source value has been selected. Default names are usually fine, but you can change them if you want to.

    Note

    Names in queries must be Common Language Specification (CLS)-compliant. The first character must be a letter. Subsequent characters can be any combination of letters, integers, and underscores.

  7. Display the Properties window for the data item. To do so, select the DataItem row and click on View | Properties (or press Shift + F4).
  8. Select the property DataItemTableFilter and click on the Assist Edit button. The Table Filter window will open. Set Field to Entry Type, Type to CONST, and Value to Sale. Click on OK.

    Back at the Properties window, the value for the DataItemTableFilter property should be what is shown in the following screenshot:

    Defining our first query

    Using the DataItemTableFilter property, we have applied a filter so that only entries of type Sale are retrieved on this query. We are analyzing sales, we do not want other types of entries to be shown in our query.

  9. Close the Properties window.
  10. For the Item Ledger Entry data item, select fields Item No., Posting Date, Quantity, and Source No. as Column in the rows below DataItem.

    Once you have selected all those fields, the Query Designer window should look like the following screenshot:

    Defining our first query
  11. For the row Posting Date, select Date as Method Type and Month as Method.
  12. For the row Quantity, select Totals as Method Type and Sum as Method.

    Notice that right after a Totals method type is selected, the Group By field is automatically selected for all the other columns in the query that are not of type Totals. This defines how the results of the query will be grouped.

    Defining our first query

    Note

    Group By is a read-only field that is automatically calculated. The value of this column cannot be modified.

  13. In the Properties window of the Quantity field, select Yes for the property ReverseSign.

    We are analyzing sales. Sales represent a decrease in the item's inventory. Being a decrease, the Quantity field for entries of type Sale is a negative value. We want to reverse this sign because we want to see quantities sold as positive values.

  14. Save and compile the query. To do so, click on File | Save (or press Ctrl + S).
  15. We will be asked for an ID and a name for the query. We will set the ID attribute to 50000 and the Name attribute to My First Query.
  16. The Query Designer window will be closed and we will be taken back to the Object Designer window. We will now run the query and take a look at the results. To do so, select Query 50000 My First Query and click on the Run button.
  17. The Windows client will open and the result of the query will be shown as follows:
    Defining our first query

So far so good! We have defined a pretty simple query with a single data item, but we have already seen how to filter the results, the different method types, and how the results are grouped.

Adding complexity to the query

We will go further into the example by adding a couple of extra data items to the query.

  1. In the Object Designer window, select Query 50000 My First Query and click on the Design button. The Query Designer window will open with the query we were creating.
  2. On the first empty row, enter a DataItem value for the table Item.
  3. Open the Properties window for the Item data item.
  4. Click on the Assist Edit button for the property DataItemLink. Select No. as the field, the Item_Ledger_Entry data item as reference DataItem, and Item No. as the reference field. Click on OK.

    Back at the Properties window, the value for the DataItemLink property should be what is shown in the following screenshot:

    Adding complexity to the query
  5. Close the Properties window.
  6. For the Item DataItem, select the field Description as the Column type in the rows below the DataItem field.
  7. On the first empty row, enter a new DataItem and select Customer as the Data Source.
  8. Open the Properties window for the Customer DataItem.
  9. Click on the Assist Edit button. For the property DataItemLink, select No. as the field, the Item_Ledger_Entry data item as the reference data item, and Source No. as the reference field. Click on OK.
  10. Close the Properties window.
  11. For the Customer DataItem, select the fields Name and Customer Posting Group as the Column type in the rows below the DataItem field.

    The final query should look like this:

    Adding complexity to the query
  12. Save and compile the query.
  13. Run the query to see the results:
    Adding complexity to the query

The DataItemLinkType property

Queries have other properties that did not come out in the query that we have created as an example. We will not go through all of them. We will however, explain an extra property. The DataItemLinkType property can be found only on the row of type DataItem. It plays an important role when two or more DataItem values exist on the same query and it has to be defined on the lower DataItem rows.

The DataItemLinkType property

This property has three possible options:

  • Use Default Values if No Match: This is the default value of the property. When this option is selected, the resulting data set will contain all the records from the upper DataItem, even if the record does not have a matching value in the linked field of the lower data item, as specified by the DataItemLink property.
  • Exclude Row If No Match: When this option is selected, the resulting data set will only contain records from data item tables that have matching values for the fields that are linked by the DataItemLink property.
  • SQL Advanced Options: When this option is selected, a new property called SQLJoinType appears on the Properties window. This new property has five possible options. All of them refer to a type of Join in SQL:
    The DataItemLinkType property
    • Left Outer Join: This is the default value of the property. It provides the same behavior as the option Use Default Values if No Match for the property DataItemLinkType.

      The following illustration shows a Left Outer Join type between tables A and B. The shaded area indicates the records that are included in the resulting data set.

      The DataItemLinkType property
    • Inner Join: This option provides the same behavior as the option Exclude Row If No Match for the property DataItemLinkType.

      The following illustration shows an Inner Join type between tables A and B. The shaded area indicates the records that are included in the resulting data set.

      The DataItemLinkType property
    • Right Outer Join: When this option is selected, the resulting data set will contain all the records from the lower data item, even if the record does not have a matching value in the linked field of the upper data item, as specified by the DataItemLink property.

      The behavior is similar to the one provided by the Left Outer Join option. The Left Outer Join option sets all the upper data item records as the result while the Right Outer Join option sets all the lower data item records as the result.

      The following illustration shows a Right Outer Join type between tables A and B. The shaded area indicates the records that are included in the resulting data set.

      The DataItemLinkType property
    • Full Outer Join: When this option is selected, the resulting data set will contain all the records from the upper data item and also all the records from the lower data item, including records that do not have a matching value for columns that are linked by the DataItemLinkType property.

      It's like selecting Left Outer Join and Right Outer Join at the same time.

      The following illustration shows a Full Outer Join type between tables A and B. The shaded area indicates the records that are included in the resulting data set.

      The DataItemLinkType property
    • Cross Join. When this option is selected, the resulting data set will contain rows that combine each row from the upper data item table with each row from the lower data item table. Cross joins are also called Cartesian products.

      In this type of join, there is no comparison between fields of the two involved data items, so the DataItemLink property must be left blank.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset