IBM Apptio

IBM Apptio

A place for Apptio product users to learn, connect, share and grow together.

 View Only

The Pro's Guide to Assembling a Report from Other Reports 

Thu March 05, 2015 03:58 PM

The Pro’s Guide to Assembling a Report from Other Reports

                                                                 Or

How to Pick and Choose your Reportable Costs from Anywhere in the Model

 

What’s my use case?

            Let’s say in your Cost model you have the concepts of RTB, GTB, and TTB identified within several objects. As your dollars allocate upwards, your RTB+ concepts mix and allocate over each other. Now let’s say your Application Owners ask you, “I need to know the mix of RTB, GTB, and TTB costs that comprise my application costs. Can you build a report that shows that?”

            You may be able to tell them their breakdown further down the model in four different objects, and you know how to make four Application to Each-of-your-Four-objects drill reports:

 

But how do you get all those numbers to line up and add together on a single report?

 

Enter the LookUpFromPath Factor

               You can use the LookUpFromPath formula to pull in cost columns from other reports (basically points in the model) and put them on a single report table by a single concept. In our scenario, we’ll be using Application ID and the concepts RTB, GTB, TTB.

  1. Build a simple report from your first object where you’ve identified your concepts.
  2. Add Application ID to Rows, Cost to Values, and just filter by the one concept at a time:
  3.                                                                                                Right click your Ad Hoc Query, select Show Full Data Path, and copy the contents.
  4. Open the included spreadsheet, paste into A1, and run the spreadsheet’s macro to generate a path for your LookUpFromPath formula.
  5. Copy the generated Path and use it in this format:
    1. =LookUpFromPath(“Path”, TABLE.ID, OBJECT.TABLE.ID, Rollup Value)
    2. Example: =LookUpFromPath(“long path here”, Application Master.App ID, Applications.Application Master.App ID, Cost)
  6. Use the formula in 5a for your first object’s RTB column in your Master Report (the Master Report being the final report you want to assemble with an Application ID from the Applications object, and inserted formula columns for the rest).

 

Repeat steps 1-6 for each object’s RTB, GTB, and TTB columns, and your Master Report will look like:

        The Totals columns can all be created by inserted columns that add up the respective RTB, GTB, and TTB columns you’ve created. Now you can reply to your Application Owners, “Yes, I can do that report. It’s a breeze with Apptio!” and receive much acclaim thereby. Enjoy!





#CostingStandard(CT-Foundation)

Statistics
0 Favorited
4 Views
2 Files
0 Shares
3 Downloads
Attachment(s)
docx file
Pro Guide to Reporting on Reports.docx   280 KB   1 version
Uploaded - Tue October 29, 2024
xlsm file
FullDataPathtoLookUpFromPath.xlsm   16 KB   1 version
Uploaded - Tue October 29, 2024

Comments

Fri February 19, 2016 04:08 PM

Let's have a look at your lookupfrompath formula. That should show what's going on.


#CostingStandard(CT-Foundation)

Fri February 19, 2016 04:04 PM

Interesting. Here is a section of my "source table" that I built: This is a table that has service name in the row and cost in the values, filtered by IT Resource Tower name:

 

Here's what I get when I do the lookup formula column: it has service name as the row and the second column is meant to look up the cost from above so that it's locked. what do you think I'm doing wrong?

 


#CostingStandard(CT-Foundation)

Fri February 19, 2016 11:00 AM

Hi, Kyle. Your first column's formula shouldn't be impacted by the addition of more formula columns. That's how the sample report was built. Data refresh timing can sometimes introduce oddities into ad hoc tables you're working on. You might want to start with a fresh table. Bring in your row. Bring in your first formula column. Save it. See how it looks in view mode and that the formula saved properly. Then try your second column. Let us know if that doesn't work. Thanks! -Fred


#CostingStandard(CT-Foundation)

Fri February 19, 2016 10:55 AM

I've been playing around with this with some success. However, when I add in a second formula column, my first formula column reverts back to something like IT Resource Tower Cost Driver, and the formula gets broken. When I use the macro to create the path from two different Ad Hoc Queries, it looks like the long path is the same. That doesn't make sense that they'd be the same, would they?


#CostingStandard(CT-Foundation)