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!