The report designer has different views, which are divided into index cards. You can switch between the views "Data", "Calculations", "Design" and "Preview" at any time. Here you will find a brief overview; the exact description of the individual index cards can be found in the following chapters.
File card data
Each printout requires data for printing. The compilation, sorting, field selection and concatenation of data is called data source.
These embedded data sources can be easily created or modified in the report designer for new report templates and most report templates. You can access the Report Designer from the Quick Launch toolbar via the Report Designer module point.
Index card - Data
Creating a Data Source Using the Wizard
After switching to the "Data" tab, the query wizard can be opened in the "Data" menu under "New".
The query wizard guides you through the table and field selection. In the wizard, you can calculate new field values, group data, sort data, and set filter conditions.
Open the wizard
Select table or view
The query wizard starts with table selection and view selection.
The table or view can be accepted by double-clicking or by selecting and pressing the"right" arrow between the two boxes. Usually only one data source will be selected.
If all fields of the table are to be selected and you do not want a filter or sort, you can already complete the data source.
Which table is used for what?
Names that contain a space or an underscore are views, all other names are tables. Views are used to obtain optimized combinations of fields for a specific purpose. While tables only contain the fields that are actually stored, views can also contain calculated fields, for example, the month name for an invoice date. Views also allow you to use field values from other tables.
Views that begin with"Print" have been specially optimized for printing. These contain a large number of calculated fields. For addresses, for example, variables with the names Print name1,2,3 are available in addition to the usual table field names Name1,2,3. These print names save the name lines in such a way that any blank lines contained are at the beginning of the lines. Invoices always contain positive amounts via print views (=print views or print views), regardless of invoice or credit memo.
Views with"master" in their names are complex views with many calculated fields.
Views with the name "Pivot" are available for statistics based on cross-tables. These optimize all groupable calculations.
In the online help, you will find a chapter entitled"Field Descriptions" in a separate manual entitled"Microsoft SQL Server". There you will also find further information on the table and view names.
Add a second table
If, in exceptional cases, several tables are required, these are selected on the left-hand side. An additional dialog is used to query a common feature that links the two tables together in a meaningful way. Both tables are displayed as one data source after merging. This is useful, for example, if you also want to use the substitute data in a single data source for the address data.
If the second data source is selected, the wizard suggests a series of connections for the commonness. These are displayed in the lower area. As suggested, they cannot be used. For this reason, these lower entries are marked and deleted using the "Remove" button. A single connection is required, from the ID of the second table - here representative - to the representative ID of the address.
The connection type is set to"Left Outer". This means that data is also loaded for which there is no substitute. If the"Inner" standard were used, only addresses would be loaded for which there is always a substitute.
EULANDA® supports connections to the tables via ID's. Each table has its own ID, which can be simply described as the storage space of the data record. If one table is logically dependent on another, an ID with the name of the dependent table is used as the"connection ID". For example, a substitute is supported in addresses. The substitute ID thus points to the ID of the substitute table; for invoices, the header ID of the item points to the ID of the header data record and the address ID points to the ID of the address belonging to the header data record.
All tables are connected in this way in a network of dependencies. This simple logic now allows additional information to be"loaded" at any time by defining a data source with two or more tables and specifying the dependency.
In the next dialog of the wizard you can determine whether all fields of the new data source are to be used or whether you want to make a selection of specific fields.
If you have saved many data records, it always makes sense to select only the fields you really need. This minimizes the memory area enormously - and what is certainly just as important - the operation can be executed faster.
The fields can be added by double-clicking or by selecting and pressing the arrow "right" between the two boxes. The sequence of fields for the data source can also be changed afterwards. For this purpose, the arrows up/down are available on the right outside.
If several tables have been selected and the field names are identical, as in the case of a match, for example, addresses and substitutes, you can refer to the name of the table.
You can use the calculation dialog of the query wizard to calculate new fields, such as totals. Calculations only make sense if the data records are grouped. The sum of the balance of a single address would not make sense. The sum of all addresses of certain countries, however, is more likely.
The decision as to whether calculations should be carried out or not is made in a preselection.
If you have chosen Calculations in the preselection, you can now select the field on the left with which the calculations are carried out.
After selecting the field, the type of calculation is set in the additional dialog.
Here you have the option of setting the number, average, minimum or maximum value in addition to totals.
In the next dialog you can select the grouping fields. If a calculation was previously selected, all fields of the field selection are automatically entered in the group list.
You can include other fields in the group, but at least the fields of the field list must be included.
The previously set calculation is performed for all group fields. All the same field contents are combined according to the calculation. In this example, the matchcode and the city. A total is calculated for all the same locations and matchcodes. In total, only the combinations of field values are retrieved from the SQL server.
Grouping in the SQL server is mainly suitable for short statistics and overviews.
Set filter condition
If you do not want to retrieve all data from the SQL server, you can set restrictions using filter conditions.
If you have decided not to retrieve all data, you can define the restrictions in the next dialog.
Using the right mouse menu, you can insert the criteria, i.e. the conditions, in the white part of the filter box.
In the criteria dialog box, select whether a field value must be smaller, larger, equal, or fulfill another condition. In this way, you can insert any number of criteria into the selection. In addition to selecting criteria, the right mouse menu also has the option to set parentheses and use logical operators such as AND and OR.
It is not necessary that the field that must fulfill a condition also exists in the data source. The condition is executed in the SQL server. By setting reasonable conditions, such as time periods, the number of data records that have to be transported over the network can be greatly reduced.
Set sort order
The data can be sorted by each field. Sorting can be ascending or descending. It is also possible to sort in several levels.
Once you have decided to sort the data, you can select the fields in the next dialog.
You select a field by double-clicking or by selecting and pressing the right arrow between the two selection boxes. For multi-level sorting, simply select additional fields. You can use the arrows on the right-hand side to subsequently move the ranking order for multi-level sorting.
If you want certain fields to be sorted in descending order, select the corresponding field on the right and then select the desired sort using one of the two buttons below the right selection box.
Specify the name of the data source
The wizard has now completed the queries. In the last dialog you can specify the name of the new data source. By default, the name of the selected table is used here.
Choose your name
Here you can switch to the data workspace after completion and check the data source, change the print preview or change the query structure. When the query structure is changed, the standard input for defining the data source is called. Usually the default setting is used here.
The finished data source
The data source can now be used in the layout. You can check the data beforehand in a browser (=list display). To do this, call up the first symbol of the data source - the symbol with the white sheet and the magnifying glass.
The other symbols can be used to change the data source individually. If an option was overlooked in the wizard, this can be revised here.
Ready data source
Connect data source
If there are several data sources in reports, it is possible to "link" them.
For non-embedded data sources, this is done in the data configurator for the respective data source under "Link" (character string) in the following syntax:
Data Source_A:Field_of_Source_B=Field_of_Source_AIn the example below we are in the data source pPos (invoice items), and the field HeaderID of this data source is linked to the field ID of the data source pHeader (invoice header).
For reports with embedded data sources, you can insert the links using drag and drop. The example below would correspond to the following link in pZE (Incoming payments) in the data configurator.
With embedded data sources, in addition to this convenient method of linking, it is also noticeable that you can access further settings by double-clicking on the link (the line that represents the link).
At this point you can add another link - which is rarely needed - delete the existing link and - that's the key - choose between two properties of the link.
The header ID of a payment made always corresponds to the ID of the corresponding invoice. We can make use of this by linking the data sources invoice header and incoming payments. What we need is a link from the header ID of the incoming payments to the ID of the invoice. In other words, if a new invoice is printed in the detail area, the ID naturally changes and with it the header ID of the incoming payments (is linked) and therefore only incoming payments of the current invoice are listed.