Create calculations for your KPI Dashboard in three simple steps

Working with CCH Tagetik with many of you, we found several tips ’n tricks for working with the application we would like to share with you. The purpose of this particular edition is to create reusable calculations for your KPI Dashboard in three simple steps using the FST functionality of CCH Tagetik. In this example we are going to create information to feed into the KPI Net Sales per FTE, and we already have reported amounts on both FTE and Net Sales on an entity level. The desired result is a single line that we can incorporate on our KPI dashboards without having to enter the components of our formula and hiding rows each time the KPI information is presented.

This method can of course be applied to more types of calculation. By following this guide but interchanging the ingredients you can whip up your own perfect cake for a KPI Dashboard.

Step 1:

First we locate the codes that we need for our two main ingredients: Net Sales and the FTE account. In this example FTE is a singular account in the application, Net Sales is either a hierarchy or an already existing FST item.

Look for the account that represents your FTE amounts by looking in Tagetik web > Setup & Admin > Dimensions > Account elements. In this case we find A.000001.

KPI Dashboard, Net Sales FTE

Now I am going to find the P&L hierarchy element for Net Sales in Dimensions > Selecting the Profit and Loss hierarchy. In this case I write down PL and R.510000.

KPI Dashboard, Profit and Loss hierarchy

Step 2:

After having written down all the components for our calculation, we navigate to the FST & KPI section and create a new FST (skip this if you want to incorporate the KPI calculation into an existing FST). In the newly created (or the already existing FST) we create two new FST items. The first of the two should be a parent item. Create the parent item by pressing the + symbol and give it a relevant code.

KPI Dashboard, FST

Underneath this parent item we are going to include the hierarchy node of the PL tree, for the Net Sales node (R.510000), we do this by clicking Relationships. There we add the PL node we’ve written down earlier, afterwards you should save and close the Relationships menu.

Finally we are going to create the formula that we will use in the reports. Create another FST item, this time choose the Item type Formula Standard.

Inside the formula box we are going to enter the following formula:
{V,,'V.000001'}/{C,'A.000001'}

Substitute the account A.000001 with the account for FTE’s in your application. The syntax for these formulas can be broken down into the following explanation:

  1. It opens with a curly bracket.
  2. The first character defines which type of data we are looking at. In this case the ‘V’ stands for ‘Voce’ which is Italian for ‘Voice’, but apparently also FST item. The ‘C’ stands for ‘Conto’ which is Italian for account.
  3. The ‘Voce’ function has two commas before it enters the code instead of one comma for ‘Conto’. This is because you can link to other FST items codes by entering their FST code in there. When you keep it empty it references the FST item you are currently working in. The reference {V,’BS’,’BS.001000′} would link to the balance sheet FST, element BS.001000 for example.
  4. End the syntax by entering the code you want within a pair of singular apostrophes, followed by a closed curly bracket.

Save and deploy your work before continuing to step 3.

Step 3:

Now that we have created the FST item, we can use it in our reports. Within a Matrix Editor, peel the dimension tree off until you reach the FST item that we’ve just created and insert our new element ‘Net Sales per FTE’ into any of your reports. If our newly created element does not appear yet, click the highlighted button on the top of you Matrix Editor called ‘Refresh Dimension Tree’.

KPI Dashboard, FST formula

The FST formula is a dynamic element. If your account hierarchy is updated your results will update accordingly, since the contents of the FST are run when generating the report and the contents of the parent item are refreshed on each deployment.

Rate this tip!

Did you like this tip? Did you think it was rubbish? Let us know! With your feedback we can increase the effectiveness of our future content.

Want to know more?

Are you curious about the possibilities of CCH Tagetik and Finext? Have a look at our CCH Tagetik page! Want to know how Tagetik is used for planning and budgeting? Watch our webinar!

If you have any questions, don’t hesitate to let us know. Get in touch with our CCH Tagetik specialists!