Adding Custom Columns and Custom Expressions to a Live Access RDBMS Source

This topic pertains to Live Access RDBMS connections.

You may need to add a custom column to a Live Access table. For example, if a measure for Revenue does not exist directly in a Live Access table that you have imported into Birst, you can add a new column to the table and specify a formula such as: Quantity*UnitPrice.

You can use SQL expressions in the column definition in the same way you can define custom measures and attributes to use against the user data store (warehouse). Birst converts the expressions you define to the select statements issued against your database; so you can leverage any SQL compliant function in your column expressions. For example, you can use SQL functions such as CASE, DATEDIFF/DATEADD/DATEPART, SUBSTRING, and CAST.

To add a new column or custom expression to a Live Access source in Data Flow

Right-click the Live Access source in the Data Flow page and select Manage Sources.

In the Modify Data Source dialog box, click Add Column. A new column called "New Column" appears at the bottom of the list of columns.

Click in the Name field and enter the name you want to use for your column.

For the Formula field, enter the formula you want to use as in the example below:

In another example, you could create a column called "# of Beverages" and use a CASE statement in the column expression to count only Beverages products as shown below:

Tip: The formula needs to be syntactically correct for the database the source is pointing to. For example, if it is Oracle, then you need to use Oracle specific syntax.

Set the other column properties, such as the Type, as appropriate for the new column.

Click Save, then Close.