Does anybody have experience using a formula column to reference another table in a report? How did you make it happen? Is it possible to do this with a LookupFromPath? Or what are the other options??

Example

Table 1

Table 2

Table 3 (aka the result I actually want)

Hi there @Devon Bilsing, I think a simple lookup formula would do the trick for you here.

Simply add a formula column for both of the cost columns you're trying to lookup.

Costs 1

=lookup(My Units, Table 1, My Units, Costs 1)

Costs 2

=lookup(My Units, Table 2, My Units, Costs 2)

Hi @Kyle Castro, I should have specified this in the question but the "tables" are tables which I have created in my report. Can these report tables still be used in a lookup? 

This is something you'll want to configure in the TBM Studio's data layer. I'm not aware if we can reference tables on the reporting layer for lookup formulas - but it sure would be sweet if this were possible.

Also, are Tables 1 & 2 reported from Model Object Tables? If so, then you can also consider creating a report table locked to those 2 objects. This will only work if there are allocation lines flowing between those two objects.

Hello @Devon Bilsing!

The clarification helped. You can totally create a column in a Report Table that uses one of the lookupXXX function to lookup up the value of a metrics from another object (in effect from another table).  

You can use one of the following functions to get what you need:

LookupFromPath function : Use this to get a value from a report component from any project. It is normally used in report components. 

LookupObjectTotalAllocated function : This gives you the total value of the metrics that is sent from a source object to a target object.

LookupObjectTotalValue function : This gives the total value of a metric for an object - it even allows you to specify that you want the value of the individual drivers for that object.

LookupObjectUnitAllocated function : Total value of a metric allocated from a source unit to a target unit*.
(*Unit : think of a unit as each line in the backing table of the object that is uniquely identified by the model identifier and assigned the value of the metric though the driver)

LookupObjectUnitValue function : This gives you the value of a unit of an object. You can also say that you want the specific value of a driver. For example - if you have a drill report involving the Servers object and it has a storage driver and you want to report the storage cost, you can use this to lookup the total cost of the storage driver. 

If you are getting stick in getting these to work - hit me up and I will help you out.

Regards,

Sanjay V.

This is really helpful, Sanjay - thank you for sharing this. I'll give this a shot myself!

@Sanjay Valiyaveettil how do you specify the driver for LookupObjectUnitValue? This is from the community site but its not working for me:

LookupObjectUnitValue(object,metric,targetCol,lookupCol[,driver])

This is my code currently:

=LookupObjectUnitValue(Application Allocation,Cost,Business Unit Allocation Master Data.Business Unit,Application Allocation.Business Support Organization L1)

The driver I want is called "(IT Resource Towers) Business Direct"

Hi @Devon Bilsing,

I would suggest that you first get the basic function working without the driver. And then get it working.

=LookupObjectUnitValue(Application Allocation,Cost,Business Unit Allocation Master Data.Business Unit,Application Allocation.Business Support Organization L1)

Looking at your function I am guessing that you are on a report backed by the Business Unit Allocation object and for each Business Unit, you would like to get the total Cost of the BU from the Application Allocation object in the Cost model. (yes I realize that you want the Business Direct driver - we will get to that)

So first things first - are you getting the total cost by BU?

If yes - then the next step is to add the driver:

=LookupObjectUnitValue(Application Allocation,Cost,Business Unit Allocation Master Data.Business Unit,Application Allocation.Business Support Organization L1,Business Direct)

The name in parenthesis is the name of the source object I think and does not need to be specified. When you click on the driver, the name that you are able to edit is the name you should use in the function.

Hope this helps!

Regards,

Sanjay Valiyaveettil 

@Sanjay Valiyaveettil This is great!! Thank you so much for your help

Hi Sanjay, I have a similar requirement. I have a report with several table components (assume Staff Data, Cost Center Data & Application Data) and couple of slicers. What  I want to be able to do is capture the value of all the slicers into the table. Can LookupfromPath help me?