Dataset
Last updated
Last updated
KNOWINS allows to define them in the template. You use the filter section of the config sheet to do that:
In column A (Table Name), you have to write the word "RELATIONSHIP". The case is unimportant, but we use a convention of all upper-case.
In column B (Source Name), you write the Master and the Detail tables that form the relationship, separating them with an arrow. The Master goes first, then the arrow, and finally the Detail: ** Master -> Detail**.
In column C (Filter), you write the fields in the Master that relate to the fields in the Detail. If there is a single field that relates the Master and the Detail (the most common case), you would just write MasterField->DetailField. If there are multiple fields in the relation, you separate each with a semicolon: MasterField1->DetailField1;MasterField2->DetailField2
Example:
You can "split" one table into a master and a detail table, in order to group the data. If you have the choice on how to supply the report data is better to read different tables for master-report (to avoid duplicate data). But if you already have the data joined into one table, the Distinct and Relationship tags can help you get it back into different tables. Follow these steps:
Use the "DISTINCT" filter to create a new master dataset with the unique records on a table.
You can use the DISTINCT filter on more than one column, by separating them with ";". Only records that have any of the columns different will be included.
Use the "RELATIONSHIP" tag on the configuration sheet to relate two datasets in master-detail.
The Relationship syntax is a little more complex than distinct. You need to write "RELATIONSHIP" on the column table name, then "Master->Detail" on source name (where Master is the parent dataset and Detail the child). After that, you need to write the field names that relate both datasets on the "Filter" column. You can relate more than one field, with the syntax: MasterTableField1->CDetailTableField1;MasterTableField2->CDetailTableField2...etc.
You might want to split datasets into groups of n records. For example, you might want to create a 5 column report, and you need to split the master dataset into groups of 5 records in order to fill the columns.
This is where the Split(source, number of records) tag can be useful. In short, you write this tag on the “Source Name” column in the config sheet, specifying the table to split on the “source” parameter, and how many records you want on each group on the “number of records” parameter. This will create a new table that you will name on the “Table Name” column of the config sheet. You can then use this new table as the master on a master-detail relationship with the original table. The generated master has no columns, but you can use the pseudocolumns (#RowPos or #RowCount) just fine. Each record of the master is related to “number of records” records on the detail.
Note that the generated master table is a “pseudo table” n the sense that it has no columns or data, but it has (DetailRecords.Count – 1) / NumberOfRecords + 1 rows. Also the relationship between the master and the detail is not on real columns, since there are no columns on the master. This creates a limitation on how you can use those tables, and it is that the master should have the detail as a direct child.
Example:
When doing a report for printing, you can use the <#SPLIT> tag on the config sheet to for example fit 40 records on each page.
Use the SPLIT tag to create a master-detail relationship of a dataset with itself, where each detail has a fixed number of records.
The SPLIT tag is a little special in that it does not create a real dataset, but it filters the data on the fly instead. This has some restrictions, the most important being that the master dataset and the clhild must be in direct relationship one with the other. If MasterOrders and Orders are both master and detail, you can not have MasterOrders/Other/Orders ranges each one inside the other. You must have Other/MasterOrders/Orders or Orders/MasterOrders/Other. This restriction normally is harmless.
You could use a parameter on the "record count" parameter of the split tag. For example: <#Split(table;<#splitcount>)> and dynamically change the split range on the application before running the report.
You normally should filter the data when retrieving it from the data view. But if this is not possible, you can use the Top(source; number of records) tag to filter this from the template.
Sometimes you want to ensure that at least one record of a table exists, and if it doesn’t, provide a default value for the missing fields.
You might do this with the AtLeast(source; minimum number of records; [default value]; [multiple of]) tag. In a way, this is the opposite of the TOP(…) tag discussed above.
For example, you might define the following in the config sheet:
In this example, the dataset detail_1 will have at least 1 record. If detail is empty, as we didn’t specify a default value, it will return a record with all null values. The dataset detail_5 will have at least 5 records, and if detail has less, all records after the detail record count will return "---" in all their fields.
Sometimes you might need a simple table with no columns and one or more rows to use as datasource of the report. For example, if you want to repeat a range n times, you could create an empty dataset with 3 rows, and put the range inside a __dataset__ name. You can do that directly from the config sheet, by defining a datasource to be ROWS(N). A dataset with a single row could be defined in the config sheet as Dual = Rows(1).
