Query Wizard

To start a report wizard, navigate to Lasernet > Common > Reports > Setup > Query wizard. The wizard will guide you through all the steps of the report creation process.

For a visual walkthrough of the Query wizard, watch our feature video.

Query Source

Once opened the Query Wizard, click Next to proceed to the next step.

At this point, you can choose where the query will be generated by selecting one of the available options:

• Build Query with Wizard - to create a new query using the wizard,

• AOT Query Name - to select an AOT query,

• Query from Class - to select a query from a class.

Tables 

Once you are done, click Next to proceed to the next step of the report wizard.

Selecting tables is needed for creating a report. In this form, you can configure your query's properties and structure by adding and removing tables and views.

Table List

The list of available tables is visible on the left, while the current Query structure is visible on the right in the Selected Tables section.

The table list has two available views:

• All tables – A list of all the tables in the system.

• Add related tables – The Query must have a selected table. Click the selected table from the query on the right and use “Add related tables” to see a list of tables with existing relations to the selected one.

Controls

The controls between the table list and the Selected tables enable you to make the following changes:

• Right-facing arrow - Add the selected table.

• Plus sign icon - Add new query. A report can have multiple queries, this control adds a new query to report data sources. Afterwards, you can add table(s) to the new query. Queries are always linked as outer join.

• Left-facing arrow - Remove the selected table.

• Left-facing arrow with grid - Remove all tables.

Selected Tables

On the right side of the form, we see the Selected tables section.

Relation/Layout

Relation/ Layout allows you to specify the relation type and relation fields between selected tables. You can manually define a relation between two tables or modify an existing relation. To this end:

1. Select a table in the list of the tables you have added (they are shown in the Selected tables section).

2. Click Relation/Layout.

As a result, the Relation panel opens.

The relation between the parent and child tables is shown on the Relation panel. At this point, you need to choose how to join the selected table and how many records are to be fetched. Once you are done, click OK to close the panel. When creating relations in the Query wizard, the default values for the Join and Fetch fields are OuterJoin and 1:n.

Load from report

Load from report loads the table list from a specified report.

Click Load from report to open the Load tables from the report panel. On this panel, you must specify a Report name and Load fields. Toggling the Load fields option to Yes will also load the report’s fields. Toggling it to No will only load the report’s tables.

Copy/Link Query

Copy/Link Query provides the option to either copy or link to another Query.

Using a link to a Query provides the option to maintain and use one central Query—such as a Query for the CompanyInfo. It is also possible to use a linked Query at detail/line levels containing information about item dimensions, which could be reused across multiple reports.

This is a similar approach to using a sub-report but provides better performance as it avoids round trips and initializing the report engine for the sub-report.

For a visual walkthrough of this feature, watch our feature video.

Copy/Move

Copy/Move provides the option to copy or move the data source – including the option to include all nested data sources.
 Useful when there is a need to re-structure data sources (tables) within an existing structure and useful when there is a need to insert tables in between existing tables.

Up and Down Buttons 

Can be used to move data sources on the same level up and down the query.

Labels 

Labels allow you to add multiple labels and language texts for a specific table, query, or data source.

Load defaults provide the option to reload/add labels for one specific data source/table - the labels from all the fields that have been added for this specific data source/table:

Data Sources - Properties 

Select Data Sources in the Selected tables section to see the Data sources properties.

• Parameters provide the option to include the parameters from either the SSRS/Replacement report or from fields created through the Dialog setup within the Output (XML file).

  It is a useful tool in case parameters for initializing the report should get shown as part of the document – such as when there is a requirement for showing the Account range, Date range, etc. which was used when running the report.

  It is best practice to provide the option to show which ranges have been applied when a report was executed, especially if it is an internal or a statistic report.

• Language texts provide the option to include the labels/language texts which have been added through the Report group (this would basically add the previously used context method LACLanguageTextsReport()).

• Layout provides the option to specify what should be included in the Output/XML File (Min, Medium, Maximum).
  This is extremely useful when there is a need to optimize performance as it can lower the size of the XML file by using Minimum instead of Maximum. It can especially improve performance when having a report with many details/records/lines.

  

  Using Minimum instead of Maximum in Layout would require adjustment of the Lasernet design.
  The default for the Layout when creating a new report (Query) is Minimum.
  The default when creating a replacement report is Maximum.

• Labels provide the option for adding multiple labels and language texts for a specific Query. Adding the labels or language texts for a specific Query is also shown at the Data sources (top-level).

Query/Properties

Select Query in the Selected tables section to see the Query properties.

• Name: you can rename a query using the Name field in the PROPERTIES section.

• Enabled: option for disabling as well as re-enabling all the data sources within the Query. Once the query is disabled, all the data sources of the Query will become disabled as well.
   This option can be used for troubleshooting the performance of a report. [Link to KB article from extracted info – pending]

• Group by: you can define the Group by field by specifying the Yes value in the Value column.
   This feature indicates the query to run in group by mode. All query data source fields will have to be either grouped or aggregated. Only included fields will have values in the selected record.
  The Group by functions are selected for all fields once you set the Group by property to Yes for a query. Here is a list of the Group by functions available for fields:

For more information on using Group By, read this article.

• Cross-company: setting the Cross company property to Yes in the PROPERTIES section allows you to select data across all the companies (legal entities) within Microsoft Dynamics 365 Finance and Operations.

• Disable cache: indicates to disable accessing cache on cached tables in this query. If this is marked query will always select records from DB.

• Data sources: list of data sources inside the query.

• Query in SQL: list of data sources inside the query.

• Create element: option to specify if the Query name should get added into the XML file. Create element is automatically enabled when converting a sub-report into the Query structure as this would ensure the same structure as when the sub-report was used.

• Show labels on: option to specify where and how often labels should get shown within the XML file.
   Normally a label is only required one time within a document and having a lot of repeating labels could harm performance. Show Labels on can be used to avoid repeating labels but can also ensure all labels are shown by using the top option, such as where there is a need for always showing a label even though no record exists.

• Labels: provide the option for adding multiple labels and language texts for a specific data source.
  Labels or language texts added for a specific data source are also shown at Data sources (top-level).

Selected Table - Properties

In the PROPERTIES section, it is also possible to change values for Join, Fetch, and Layout. Changing information in the PROPERTIES section is both a faster and easier approach when it is going about documentation information like join, fetch, layout, and relations between tables. This information is shown in one form.

• Name: data source name. You can rename a table using the Name field in the PROPERTIES section.

• Enabled: an option for disabling as well as re-enabling the data sources within the Query wizard. This function may be useful when optimizing performance and locating data sources (tables) causing no output. Once a parent data source is disabled, all the child data sources of this parent data source become disabled as well. The following example illustrates disabling the CustInvoiceJour data source by disabling the SalesInvoiceHeaderFooterTmp data source.

If you re-enable the parent data source, for instance, SalesInvoiceHeaderFooterTmp, you get a prompt asking whether to enable all the child (nested) data sources as well.

Before re-enabling all data sources:

After re-enabling all data sources:

• Table name: the table name is shown for each of the data sources. This is useful when tables are renamed within a query.In this example, you can see data source CustConfirmJour has been renamed to ConfirmationJournal_Header in the Name property. The Table name property shows the original data source name, CustConfirmJour.

  

• Parent: parent data source name.

• Join: join mode to parent data source.

• Fetch: record fetch mode.

• Layout: layout of the data source in XML document.

• Top records: the Query wizard proposes an option to identify how many records to fetch from a table, for example, fetching 10 records. It may be useful if TOP 10 of the top most delivered products is required. Consider also an example when a standard report (like a certificate report) is returning too many records, and only the newest records are required in the output.
  Number of records to select. 0 selects all records.

  

  To this end, define the required value (for instance, 10) for the Top records property in the PROPERTIES section. It is possible to use the Expression editor to define an expression as a Top records value. To this end, click a corresponding button in the Actions column.

  

• Top reset: indicate when top counter is reset.

• Create element: create a data source section in the report structure.

• Relations: relations to parent data source.

• Show labels on: indicates how labels must be processed. Some of the options can be used to reduce file size.

• Labels: all Query/data source-related labels.

Navigation

The interface for the following sections of the Query wizard (Select fields, Sort fields, Ranges, Group data in files) is the same: each section will have an Available fields list on the right with a Filter search bar (1) and the Selected fields on the left (3), with the Control buttons displayed in the middle of the page (2).

Search

Searching for field names allows for two options:

• Search for field name across all data sources/tables

• Search for field name within a specific table

It is possible to toggle between the two search options by using the Switch View button:

Searching across all data sources/tables:

Searching within a specific data source/table (it is possible, however, to select any of the data sources/tables within the dropdown for the table name):

Controls

• Right-facing arrow - Add the selected field.

• Left-facing arrow - Remove the selected field.

• Left-facing arrow with grid - Remove all fields.

• Expand all/ Collapse all - Expand all or Collapse all the nodes in the tree. Searching for a field within the collapsed list expands the table where the field is available automatically.

  

• Switch View – Switch view on the left between single table tree (better performance) and all tables tree. The filter will only search the fields for the table specified with single table tree view. The filter will search the fields for all of the tables in the query with the all tables tree.

Select Fields

Once you have selected the Tables, click Next to proceed with selecting the fields you need for the report.

In the Select Fields form, you can add or remove fields from your Query. The fields available for selection are the fields in the tables selected in your Query.

Record Methods

Record methods can be used to avoid adding additional tables. If your table has a record method, you can use it to return a record and select fields that belong to another table.

The following example illustrates selecting the Address field is selected belonging to SalesTable.companyInfo().InvoiceAddress). Both companyInfo() and InvoiceAddress() are record methods and return a record from which it is possible to select any additional fields and/or any methods (including additional record methods).

Load from Report

Load field list from report. This option allows you to load the fields from another report into the current report.

Properties

Label: Field label. It is possible to search for standard/system labels for the field label.

Sort Fields

Once you have selected fields for your report, click Next to proceed to the next step and sort those fields. In the Sort fields form, you can add or remove fields to sort the data in your report. Once you add a field, you can choose between Ascending and Descending sort.

If you add multiple sorting fields, you can use the Up and/or Down buttons to determine the order of sorting.

Range

Once you have sorted the fields in the order you need, click Next to proceed to the Range section. This step allows you to specify fields used for ranging. You can select multiple fields in the Available fields list and add them all with one click to the Default ranges list.

While adding ranges, you can specify whether it is a filter or a range. To this end, define properties correspondingly in the PROPERTIES section. The range is applied to the table/data source but not to the complete result set as the filter is.

The following example illustrates an output: once a non-existing ItemId is added as a filter, and once the same non-existing ItemId is added as a range. Learn the difference from the comparison table. For this example, the following link between the SalesTable and SalesLine (Outerjoin) tables is used (it is set on the step when tables are selected; see the following image).

Using a filter generates no output for a non-existing ItemId as it is filtering the complete result set and not just the specified table (the SalesLine table):

Using a range generates an output for the parent table (SalesTable) as it is a range for the specific table and not the one for the complete result set:

Default Ranges - Properties

• Visible - Indicator to display Range in Query dialogs. If set to NO, user will not be able to select or set this range in Query dialogs.

• Read-Only - Indicator that this range is read only. If Range has expression in Range value field, read only will always generate expression value, and users will not be able to change this value in Query dialogs.

• Filter - If set to YES, it will act as a filter on the select statement result set. If set to NO, it will act as a Range on select statement tables before producing result set.

• Value - Filter/Range value to select statement.

Group Data in Files

Once you have selected the fields for a range, click Next to navigate to the next step and group data in files.

Here, you can select fields that you want to group and split on. As a result, Lasernet creates several XML files based on the selected fields.

Create Report

Click Next to navigate to the Create Report section. This is the last section of the Query wizard.

The Reset modifications toggle button resets all modifications to default values like sorting, number of decimals, condition, type changes, label changes, etc. This will not remove or change your custom added fields.