What is a DataView?

A DataView is a table of data loaded from the DataSource. It can have associated Colour Filters and Layouts.

The DataView also stores the definition of the Data Pivot, Parameters, Calculated Columns, Actions and Read & Write permissions.

Creating a New DataView

DataViews can be created in 3 ways:

  • From the Projects Tree, right click a DataSource Node or DataView Node and select New DataView
  • From the DataSource Configuration Dialogue, click the New DataView button
  • Automatically by dropping a file onto the Projects Tree if the file type is supported by at least one DataSource


An existing DataView can be cloned:

  • From the  Projects Tree right click a DataView Node, select New DataView and confirming the duplication.


Similarly DataViews can be edited:

  • From the Projects Tree, right click a DataView Node and select Configure
  • From the DataSource Configuration Dialogue, Select the DataView in the list and click the Edit button, or double click the DataView in the list directly

Configuring the DataView

The DataView Configuration Dialogue allows you to define a Name for the DataView as you want it to be displayed to the user in the Projects Tree.

The Configuration section is specific to the DataSource provider, refer to the corresponding documentation for more details:



CONFIGURATION LIMITATIONS


Some DataViews may not be configurable.

Configuring Parameters

DataView can have parameters which can be used as input in data transfers.

To add or edit a DataView parameter, click the Configure Now button. Enter a Parameter Name for the parameter and its Default Value. Some DataViews may change the parameter values in various conditions.

When a DataView has parameters they can be used in data import instead of source columns to set constant values and to filter or configure DataView, by referring to them as {ParameterName}.

Check the parameters topic to learn more about parameters.

Configuring a Data Pivot

DataViews support multi-columns Data Pivot with rows grouping and many types of value aggregate. To configure a Data Pivot refer to the topic Data Pivot

Configuring Calculated Columns

Calculated columns allow you to add columns to your DataView that are not in the DataSource, such as calculation for look ahead and colour filters.

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.

About Formulas

DataView 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.

Extended Functions Using the Pre-Processor

In addition to the standard Microsoft features, BimSens implements a couple more functions via the Pre-Processor:

    • Today(), returns the date of the day when the DataView is refreshed
    • 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


RESTRICTIONS ON USE OF PRE-PROCESSOR FUNCTIONS


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, this works:

    • 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

but this doesn't work:

    • 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.