What is a Layout?

A Layout is a sub set of the data from the parent DataView, with a limited number of columns, custom formatting, sorting and optionally Calculated Columns.

Creating a New Layout

Layouts can be created:

• From the Sources Manager Tree, right click a DataView Node or a Layout Node and select New Layout

An existing Layout can be cloned:

• From the Sources Manager Tree, right click a Layout Node, select New Layout and confirm the duplication.

Layouts can be edited:

• From the Sources Manager Tree, right click a Layout Node and select Edit Layout
• Click the Edit Layout button from the bottom toolbar of the main window.

Configuring the Layout

Specify a Name  for the Layout, this is the name as displayed in the Sources Manager Tree.

Use the reorder buttons to reorder the columns of the Layout: move one or multiple columns down, at the bottom, up or at the top. You can also Remove columns if they are calculated columns.

In the Show column you can specify whether each column is visible or not. Note that the data of a hidden column is stilled pulled from the DataSource every time the DataSource is refreshed, when possible you should limit the number of column that are pulled from the DataSource by configuring the DataView.

Add a calculated column to a Layout

The Column Name is read only, it can only be changed for Calculated Columns. Some DataView may support column renaming, refer to the DataView configuration.

The Column Data Type is read only, it can only be changed for Calculated Columns. Some DataView may support editing of the column type, refer to the DataView configuration. When the Column Data Type is 'Text',  BimSens can format the column to display hyperlinks that you can click, with a menu to select from multiple links if you enter multiple value delimited by a semi colon.

The cell Alignment. When the alignment is on NotSet the cell alignment is the default for BimSens's grid. The other values are combination of vertical and horizontal alignment, respectively Top, Middle, Bottom and Left, Centre, Right.

The Display Format allows you to specify formatting for numeric and date values, for instance to round up decimal numbers, display as percentages, add units etc. The standard formats are defined by one letter for the type of number and optionally one digit for the precision. For instance the format C displays a Currency with default precision while C2 displays a Currency rounded to 2 decimals.

The Auto Size mode of the column is used to adjust the column width based on the cells content or when the grid is resized:

• Not Set: the column width is set by BimSens's grid
• None: the column width is fixed
• Column Header: the column width is adjusted to the content of the header
• All Cells Except Header: the column width is adjusted to the content of all cells, except the header
• All Cells: the column width is adjusted to the content of all cells, including the header
• Displayed Cells Except Header: the column width is adjusted to the content of all visible cells in the grid, except the header
• Displayed Cells: the column width is adjusted to the content of all visible cells in the grid, including the header
• Fill: the column width is adjusted to fill the remaining space (shared between all columns set to Fill)

The Preferred Width allows you to specify a fix column width when the column Auto Size is set to None or a minimum width when the column Auto Size is set to Fill and there is not enough visible space to display all columns without scrolling.

When the selected column is a calculated column you can edit the column formula

Creating a New Calculated Columns

To create a new calculated column, click the New Column button.

A calculated column requires the following information:

• A Name, it must be unique for the entire DataView and cannot conflict with existing columns.
• A Data Type, it will be used by BimSens to format the displayed values.
• A Formula, in plain text, returning a result of the specified Type. See the section About Formulas inLayout  for more information

About Formulas in Layout

Layout formulas support all the default Microsoft features for DataColumns:

• basic operators (*, /, +,=,>...) between numeric columns (Dates can be compared to one another but addition and subtraction of days are not supported)
• basic functions such as IIF(Boolean test, ValueIfTrue, ValueIfFalse)
• Text must be in-between quotes
• Column names must be enclosed in square brackets [ ]
• Dates must be in a standard format such as #12/25/2016# or '2016-12-25'

See http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx for a list of features supported by Microsoft.

In addition to the standard Microsoft features, BimSens implements a couple more functions:

• Today(), returns the date of the day when the Layout is displayed
• DateAdd( PeriodToAdd, NumberToAdd, Date ), returns the Date offset by the specified number of periods, for instance DateAdd( Week, 2, '2016-10-30' ) returns '2016-11-13'

• PeriodToAdd is the date part to add to the base Date, it can be: Year, Quarter, Month, Week and Day
• NumberToAdd is the number of periods to add, it can be a positive or negative integer
• Date is the base date to use, format 'yyyy-MM-dd' e.g. '2016-12-25'

• PeriodStart( Period ), returns the start date of the specified Period that is currently running. For instance PeriodStart( Month ) returns the date of the beginning of the current month
• Period is the part for which you want the beginning date, it can be: Year, Quarter, Month, Week and Day

Custom BimSens functions are evaluated before the normal Layout formula is evaluated, during pre-processing. For this reason, they do not have access to other operators and functions. You can nest BimSens functions together and you can use the result of their evaluation for standard functions, but not the other way around.

For example:

• The nesting: DateAdd( Day , 5, Today()) will work and return the date 5 days after today
• The hybrid: IIF( Today() > '2016-12-25' , 'Christmas 2016 is gone' , 'Christmas 2016 is near' ) will work since the function Today() will be evaluated during pre-processing, then the result will be used in the standard function IIF( , , ) during normal evaluation
• The hybrid: DateAdd( Day, 5, IIF( A > B , '2016-12-01' , '2017-01-01' )) will not work because the function IIF( , , ) will not have been evaluated when the pre-processor tries to evaluate the function DateAdd( , , ), and it will fail to parse the Date parameter
• The use of column name: DateAdd( Day, 5, [PlannedDate] ) will not work because the columns do not exist in the context of the pre-processor, and it will fail to parse the Date parameter

For more examples of advanced formulas, check the Look ahead colour filter walk through.