Data Band |
  
|
Data Band |
|
Your entry form is dynamic. It's content doesn't fix. For example, when you create a new Good Receipt Form, Phoebus doesn't create an empty form. Instead, Phoebus ask you the PO, against which the new GRN created for. When you select the PO, GRN have all details of your PO. You only need to input the receiving quantity.
It works the same way as the reporting does. You design the GRN form the same way as designing the reporting form.
In order to present data to the form, you need a data band.
Named Ranges
While Tags allow you to replace complex expressions inside a sheet, with them alone we can only make “Fill in the blanks” type reports. That is, reports that are static, like a form, and where cells with tags will be replaced with their corresponding values.
Now we are going to introduce the concept of “Data 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 A1:C1, and associate it with the table “Customer”. Then, on cells A1:C1 you will have the first customer, on cells A2:C2 the second and so on. All cells that were previously on A2:C2 will be moved down after the last record of the dataset.
If table customer has six registers, you will get something like:
Template:
Generated form/report:
On Phoebus we use Named Ranges to indicate the bands. If you are not used to Excel Named Ranges, take some time to familiarize with them, as they are one of the things that confuses most people starting with Phoebus. 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 Menu->Insert->Name->Define.
Or in Excel 2007, go to the “Formulas” tab, then choose “Name Manager”:
Once there, we can define a Band __Customer__ on cells A1:C1. And once the name is defined, we can easily see it on the Names combo:
Note the “__” at the beginning and at the end of the range name. We use this to indicate FlexCel that this is a horizontal range that inserts full rows down.
The rest of the name (“Customer”) should be the name of an existing datatable, or a custom table defined on the config sheet.
Range Types
You can define four different kinds of bands on FlexCel:
“__” Range:
This range moves down and inserts full rows.
“_” Range:
This range is similar to “__” but cells outside of the range won't move down.
“II_” Range:
This range moves to the right and inserts full columns.
“X” ranges:
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:
It will behave exactly the same as a normal range, but once it is expanded it will erase the last row (or column if it is a column range)
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.
Fixed Bands
By default, Phoebus will always insert cells when expanding ranges, and this is what you would normally want. If you have a template:
A1:Title
A2:<#data>
A3:Footer
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 with Phoebus. 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 30. You cannot do this with normal ranges, since you would be inserting rows. For this you can define a “__SALE__FIXED” named range, which will not insert any records.
Fixed “N” Bands
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 “__SALE__FIXED10__” will overwrite the first 10 rows, and insert (data.RecordCount – 10) rows for the rest.