How to: Creating a Custom Column to create a new attribute

Principal Customer Solutions Consultant
Mark_Oldfield.1
updated 3 mths ago

How to: Creating a Custom Column to create a new attribute

In this usage of Custom Column, a new feature for Pyramid 2023, we will create a new column that represents the Domain of an email address supplied by a customer

Custom Columns allows users to create and share new Hierarchies and Measures against Pyramid Models at run time. This permits the dynamic extension of the Model by users to cater for unforeseen Modeling requirements in terms of model structure, measures and row level calculations.

You can read more in the Help Pages: Pyramid Help Pages on Custom Columns

We have a data model where there is a customer dimension, this has an attribute called ‘Email’, we have been asked to do an analysis on the domains of these email addresses (i.e. gmail.com) but this attribute is not available in our model. We could go back to our Data team and ask for this to be added but this may take days/weeks. What we need is something today, this is where we can use a custom column:

Video - How to create a Custom Column

Step by Step description

To do this we will use Formulate and select ‘Custom Columns’

Within the Formulate / Custom Column we can create the formula required to extract the domain from an email address

To get the domain we will represent the following logic

To get the position of the ‘@’ symbol we will use the IndexOf tool, within the tool we will select the Email attribute and look for the ‘@’ symbol as its parameter.

We can see the result give by the by clicking on the result preview

As you can see this returns the location correctly, what we can do now is extend this to return what we want to achieve. To assist in this we will go into ‘Script’ mode (this can be found in the ribbon bar).

To extract the Domain from email the following script is used:

SubString([Customers].[Email],IndexOf([Customers].[Email],"@")+1,Length([Customers].[Email]))

Running this will now return the ‘Domain’ correctly.

Set the relevant parameters (Display Folder etc.) and finally save this in your Content Management system, the column will show in the model with the name you save the file as. In addition if this stored in a shared folder, all users that have access to that folder will see the newly created column.