Sometimes it is necessary to create a relation in the report to extract correct data from the Microvellum database. How will you know when this is necessary? It will help you to know when this is necessary if you understand a basic fact about databases.

One of the goals of database design is to eliminate redundant data. To accomplish this goal, related data is separated into tables. In Microvellum, you have a table with data relating to Products, such as the name of the product, the size of the product, etc. Since that table has product-related data, it should contain nothing relating to individual parts of the product – that data is contained in the Parts table instead.

Within the Microvellum Report Designer, you use ‘databands’ to define and locate what data is displayed where on the report. The databand must point to a data source within the Microvellum database. If you want to display part related data, that data source will look to the Parts table. Often though, there is data in another table that, while not Part level data, is somehow related to the parts and should be displayed on the report. An example might be that you want to display which product item number each part belongs to. This item number data is kept in the Products table.

The problem is that if you simply specify {Products.ItemNumber} as the expression, all the parts will have the first item number encountered in the products table. You don’t want that. Instead, you want all the parts that make up the product with item number 1.01 to display that number, all the parts that make up the product with item number 1.02 to display that number, and so on. This is how you know it will be necessary to create a relation in the report.

You create a relation between data sources in the data dictionary by selecting the child data source, in this case, Parts, and right-clicking to display the context menu.

 

 Click the menu item New Relation in the context menu of a data source.

 The screenshot below shows the New Relation dialog box.

 

Notice that there are nine fields, which define the relation parameters.

In the field Name in Source, the name of a relation is specified. Microvellum naming convention is: parent table name (in this case Products), followed by a hyphen, and then the child table name (in this case Parts).

By this name, the relation can be found in the data Dictionary, in a hierarchical structure, under the child table name if that’s the way it was set up.

If the relation between data sources will be created based on a relation in the source database, as is true here, then this name will coincide with the Name field (in this case, Products). This is a required field and is used to specify the name of a relation that is used to refer to this relation in the report.

The field Alias contains a separate user-defined name or hint for the relation that is displayed to the user. This is a required field. In the Microvellum report, it is set up to be identical to the Name field.

The field Parent DataSource contains the parent data source for this relation. This is a required field. Also, the field Child Data Source contains a detail or child data source for this relation. This is also a required field

The fields under Parent Columns display the selected column-keys from the parent data source. Enter the desired linking fields from the right box to the left box. This can be either one field or multiple fields.

The fields under Child Columns display the selected column-keys from the child data source. Enter the desired linking fields from the right box to the left box. This can be either one field or multiple fields.

The data in the Parent Columns and Child Columns area must be matching key data from the database tables to create the relation accurately.

You will now be able to drill from the Parts table, into the Products table, and specify {Parts.Products.ItemNumber} as your expression to correctly display the item number for each part.