Band
Last updated
Last updated
The important concept you need to understand to exploit all the powerful capability of KNOWINS reporting engine is Band.
A band is just a range of cells that is repeated for each record of a table. Imagine that you define a band on the cell range A2:C2, and associate it with the table Customer. Then, on cells A2:C2 you will have the first customer, on cells A3:C3 the second and so on. All cells that were previously on A3:C3 will be moved down after the last record of the dataset. For example, if table customer has six registers, and you have a template as follows:
Then after running the report you will get something like this, noted that the text - This is some text below the band - in cell A3 is moved down after a band:
We use Named Ranges to indicate bands. If you are not used to Excel Named Ranges, take some time to familiarize with them. Different from tags that you can immediately see when you open a workbook, named ranges are a little more hidden.
To create a band on A1:C1, we would go to the Formulas tab on Excel, then choose Name Manager:
Once there, we can define a band CUSTOMER on cells A1:C1:
Note the __ at the beginning and at the end of the range name. We use this to indicate that this is a horizontal range that inserts full rows down.
The rest of the name CUSTOMER should be the name of an existing data source (table, array of data, etc), or a custom table defined on the config sheet.
Named ranges can be placed inside each other, and a master-detail relationship will be automatically created. For example, if you define a range __Customer__ and inside it a range __Orders__ and then create a relationship between Customer and Order tables, it will automatically group your orders by customer.
On the following example, the yellow cells are the range __Customer__ and the blue ones are the range __Orders__
After running this report, you will get something similar to:
As you can see, Orders are filtered for each customer, based on the Data Relationships defined in the <#Config> sheet, and on the nesting on the ranges. In general, any range that is inside another is filtered by all of its parents. You can have as many levels of master-detail relationships as you wish, and each master band filters all of its children.
For example, if we wanted to group the customers by country we could define a Countries named range on A1:F6, and it would automatically filter the data on its child and grandchild. (Customer and Order)
As a general rule, bands in KNOWINS can’t intersect. You can have separated bands, and they will each expand separately.
You can also have one band completely inside another, and the inside band will be a detail of the outside band.
But if the bands intersect:
It is not really possible to do a report with this data. There is no master and no detail here, and if we run those two bands separately, one band would overwrite the results of the other. It is not possible to know if cell C3 should have data from Band 1 or Band 2.
But there is one special case where KNOWINS allows you to have two bands that intersect: If the bands form a cross, and the vertical leg of the cross is a column range, while the horizontal leg is a row range:
This will create a bidirectional band, that grows both to the right and to the bottom at the same time. The conditions for creating a bidirectional band are:
One band must grow horizontally and the other vertically.
The bands must form a cross. This means that the top of Band 2 must be smaller or equal than the top of Band 1, the bottom of Band 2 must be bigger or equal than the bottom of Band 1, the left of Band 1 must be smaller or equal than the left of Band 2, and the right of Band 1 must be bigger or equal than the right of Band 2. You will normally make Band 1 a full row range and Band 2 a full column range.
If both conditions are met, KNOWINS will create a master-detail report where Band 1 is the master and Band 2 the detail.
As the vertical band is the detail, you can have multiple vertical bands for the same horizontal band master. That is, you can have something like this:
And both Band 2 and Band 3 will be details of the master Band 1.
One issue that might appear when defining named ranges is how formulas on other ranges change when inserting the new cells.
Let's imagine we want to make a simple report on a list of items and their prices.
So, we create a new template, define a title row, and insert a named range on A2:B2 to propagate the data. But we also want to know all the total cost of all items, so we add a formula on B3:
When you run this report, rows will be inserted between row 2 and 3, but the formula Sum(B2:B2) won't change. Nothing has been inserted between B2 and B2, so the sum range will remain constant.
So, we need to have a Sum range that can expand. We will define:
Now, when rows are inserted between row 2 and 3, the formula will be updated to reflect the new rows.
In this particular case, this solution might be enough. Just leave an empty row after the range so formulas expand, and then you can hide the extra row or just leave it there.
But if we want to delete the empty row then we can use the “X” ranges.
X ranges are normal named ranges with an “X” at the end. On this case, instead of “__Item__”, we would call the range “__Item__X” It will behave exactly the same as a normal range, but once it is expanded it will erase the row immediately after the range (or column if it is a column range). So if we try the last example with “__Item__X”, row 5 on the last screenshot will be deleted, and the formula would be “=SUM(B2:B4)”. Just what we were looking for.
By default, KNOWINS will always insert cells when expanding ranges, and this is what you would normally want. If you have a template:
You would expect that the generated report will have the Footer for example on cell A33 (if we had 30 data records), but not on A3.
But there is a situation where this is not what you expect, and this is on Fixed Form reports. Let's imagine that you want to fill out a form. Most fields will be just simple expressions, not related to datasets, but we might have a table too:
Here, no matter if the dataset has 1 record, two or 10 (it should not have more than 10) you want the “Total” line to be at row 23. You cannot do this with normal ranges, since you would be inserting rows. For this you can define a “__TopOrders__FIXED” named range, which will not insert any records.
KNWOINS will treat any range that ends with the word "FIXED" as a fixed range, as long as the characters before the range mean the end of a range. That is, II_Range_IIFIXED will be a fixed range, since "_II" is the end of a vertical range. But OrdersFixed is not a fixed range.
Sometimes, you might want the records to overwrite the first n cells (like in a “fixed” band), but after those rows are overwritten, insert the rest of the records (like in a normal report).
You can get this by using a “FixedN” range, where “N” is the number of rows or columns you want fixed. For example “__data__FIXED2” will overwrite the first 2 rows, and insert (data.RecordCount – 2) rows for the rest.
In some rare cases, you might want to use the same dataset twice in the same sheet. One way to do it is to define an alias in the config sheet, so you have, for example:
DataSet1
NewDataSet1 -> Alias to DataSet1
And then, in the report, you define a DataSet1 and a NewDataSet1 range.
But in some cases, like, for example, when you are creating a report from a list of objects and there are nested objects, you can't rename those nested objects. If a Customer object has a Orders nested object, then you can't rename Orders in the config sheet. You would need to define two Orders names, but only one is allowed by Excel.
To solve this, you can use the ..Alias.. postfix. Just write the word ..ALIAS.. (case insensitive) after the name, and then write whatever you want.
You could define the names as Orders..ALIAS..First and Orders..ALIAS.._Second.
Alias are always defined at the end, and everything after the ..ALIAS.. word is ignored. So if you want to define say a fixed name, it would be defined as Orders__FIXED..ALIAS..MyOrders, and not as __Orders..ALIAS..MyOrdersFIXED.
Besides using names for the bands, KNOWINS also allows the use of Excel tables as bands. To use an Excel table as a band, you need to follow the steps in this image:
Insert a table with two data rows. You need an extra empty row after the row with the data so when the report runs the table will grow with the inserted records. Bands defined from tables behave like X Ranges and they erase the empty row after the report is completed.
With the cursor inside a cell of the table, go to the "Design" tab to edit the name of the table.
Name the table "__Data__" as you would with any other "__" name. You can also name it as "_Data_" and FlexCel will not copy the full rows, just as it does with "_Names_". "I_" and "II_" names are allowed too but make no sense in tables, since tables are always organized to have the data in rows and the columns to hold different fields. You might also name the table "__Data__X" but as explained in point 1. table datasources are always "X" so there is no need to write the extra "X" at the end.
Excel-Table bands are exactly the same as an "X" named-range band defined over the data part of the table, so everything you can do with name bands you can do also with table bands. You can for example store a table inside a name to create a master-detail report, or anything you can do with normal name bands.
Sometimes you might want to create a report that once generated looks like the following:
But if you try to do this by creating a Block1 and Block2 parallel ranges, you will actually end up with something like this:
Because when _Block1_ and _Block2_ grow down, the cells are inserted only in the columns that are used by those ranges.
But there is a case where KNOWINS doesn’t work this way, and that is in master-detail. When you are doing a detail, cells are inserted in all the columns so they end at the same place, and the next master record won’t be broken. So the solution in this case is to create a dummy “master” dataset with a single row, and use it as a master for both _Block1_ and _Block2_ ranges.
The last cell in every column of the “master” dataset will be copied down so all columns inside the master insert the same number of cells.
You can use the “ROWS” function in the config sheet to create a single row datasource.
The row (or column) deleted will be the one after the range, not the last row in the range. So if you define a range "__MyBand__X" in rows 2 and 3: KNOWINS will repeat the 2-row range for each record, and then delete the row after the range, as seen below: This is not likely what you want. To have one row per record, the __MyBand__X name must have one row only, and then the row after the band will be deleted.
