Adding columns to Business Intelligence export models

You can generate column values in a Business Intelligence export model using two primary types of token: fields and formulae. Fields are simpler to use than formulae, but there are three reasons why you might want to use a formula rather than a field to generate a value in a column:

The required value is not available as a field.
A calculation is required to obtain the required value.
You wish to mix closely-related object types in a single table.

You can also use the timephase element to change the output of a field from a fixed value to a series of values at each point in time. Click here for more information.

Mixed object types

The default scheme for a Business Intelligence export model is to have one table for each object type. However, if, for example, you do not wish to separate tasks and milestones into distinct tables, or you wish to include a bar code value with task data, you can achieve this using a formula. Mixing data types can be efficient in custom export models, but it comes at the price of a more complex design and less reusable tables.

Fields

A field is a property of the tables object type (apptype). If the object type was milestone and field was ActualFinish, this column would return the milestone's Actual Finish date.

Before hunting for a specific object property Its always a good idea to see if you can locate the value in Powerproject by adding a suitable column to the spreadsheet. Pay close attention to the line on which the value appears: the object type may not be as obvious as you think. For example, a cost could belong to a task, to a cost allocation, or it could be a resource's calculated value. By examining the spreadsheet column properties and the display options, it should be possible to confirm where the cost is actually held.

The biggest difficulty is identifying the desired property name. The property names are normally similar to the English column names in the Powerproject spreadsheet, but this is not always the case. Click here for more information.

From version 14.0.03.245 onwards, fields - but not formulae - can be chained. For example, you could have Appearance.BackgroundFillColour in a field but not in a formula:

Formulae

Formulae in Business Intelligence export models largely follow the same rules as formulae in Powerproject. Check out the example formulae in Library Explorer and in the Help on formulae for a full explanation of formulae.

The following exceptions apply:

Formulae must return a legitimate value type. For example, if a formula returns a colour, you must convert it to text or to an RGB value.
User-defined fields support an extended syntax.
Code library entries support an extended syntax.
Formulae cannot be used on library objects.

User-defined field formulae

User-defined field formulae support extended formula syntax in Business Intelligence export models.

If you know the name of your user-defined field, you can call it explicitly, as in the following example. This is the preferred method:

User-defined fields can also be identified by index, which is useful when you do not know the names of your user-defined fields in advance, or if their names are inconsistent between projects. This enables you to view the values in a spreadsheet or database, but is of little use if you need to pick one of those fields for inclusion in a report, as you will not know which field is in which column. Indexed fields can also change if new user-defined fields are added.

Be sure to add enough indexed fields to capture all the user-defined fields you are likely to find for that object type.

Note that there are no ID numbers for user-defined fields.

Code library formulae

Code libraries support extended formula syntax in Business Intelligence export models.

The two main functions are BarCodeLibrary and TaskCodeLibrary, depending on where the codes are assigned. You can use Task.BarcodeLibrary but not Bar.TaskCodeLibrary, as it will not be obvious which task on a bar you are querying.

Get code by library object ID

Object IDs are reliable, but only within a specific project or programme of projects. There is no guarantee that an ID will be the same in another project, even if they were created from the same template. Object IDs can get changed when planners reorganise code libraries.

Get code by library name

Library name is the most reliable method, but still risks that a planner may change the library name.

Get code by Index

By index is useful when you do not know the names of your libraries in advance. It enables you to view the values in a spreadsheet but is of little use if you need to pick one of those fields for inclusion in a report as you will not know which field is in which column. Indexed fields can also change if new user-defined fields are added.

Linking to codes in SQL

If you remove the ‘Text()’ conversion and change the data type, this function will return the object ID rather than the entry name.

You can build a table of code libraries and entries in each project. Click here for more information.

Using these tables it becomes possible to add features such as filtering by selected library entries to Business Intelligence export models, with a drop-down list of all known libraries, with the library tables linked to the codes column.