For simplification purposes, lets say I have 3 objects in my model:

Cost Source ==> ITRT ==> Services

I am having trouble getting my Drill to reports that start a Service object level where the user clicks on an individual Service in report table that navigates to a drill to report table with the intent of carrying forward the filtering on the individual Service selected and having that drill to report table represent the Cost Source detail filtered to just the detail related to that filtered Service.

The one consistency/link between the 3 objects and backing data sets is that they all contain Cost Center and Cost Pool as that is the level at which we mapped the GL to both our IT Resource Sub Towers and Services in 2 separate exercises.  The keys that drive the allocations in the model in both cases (Cost Source to ITRT and ITRT to Services) are based on the Cost Center and Cost Pool fields/grain.

The uniqueness of each of the objects (backing data sets) is:

• Services
  • Cost Center, Cost Pool, Service
• ITRT
  • Cost Center, Cost Pool, Sub Tower

Cost Source has both Cost Center and Cost Pool and then much lower detail grain that creates its uniqueness.

Do I need to add Service to each of the ITRT and Cost Source objects?  I can back into this since as I stated above, we have mapped Cost Center - Cost Pool in the GL to both Sub Tower and Services in separate exercises.  However this seems very unnatural and would change the grain of the Cost Source and ITRT objects (backing data sets).  And if  were to do that I could just add Sub Tower and Service to the Cost Source Master Data.  In which case I wouldn't need a model and could just do all the reporting off of Cost Source.  But that doesn't follow and more or less defeats the purpose of the traditional TBM philosophy and what Apptio is mean to do.

Is this accomplished by way of publishing field(s) across two objects to the same Perspective in the Ad Hoc Report Configuration window or something?

I feel like I should know how to do this base on past experience and/or training I've taken but I am a use it or lose it type of person and rather than lose a lot of time spinning my wheels I thought I would throw this out to the community and see if I could get some quick guidance.

Let me know if I can answer questions or provide more context to help with this.

Thanks,

Steve

No need to add the extra level of detail to the lower objects in the model.

As a first step, have you tried creating a report table locked to both objects (Cost Source and Business Services) at once, to restrict the table to just the cost shared between those two objects?

Apptio will automatically calculate and display the per-service cost contributions of each cost center.

For example (dummy data):

The service named Workforce management has $85,825 cost at the Business Services object level, and the breakdown per cost center is shown in the table above (one row per cost center).

Here's its configuration window for reference:

...notice the grayed-out context near the top, indicating this table is locked to both model objects at once.

I found Service Name published in the Services perspective, and I found Cost Center in the Service Costing perspective.

Your perspectives and columns may have different names.

Thanks for getting back to me Christopher.

I have a custom object named Service Target with the field; Service Target.

I have a standard Cost Source object with the fields; Expense Center, Expense Center Name, Cost Pool, Cost Sub Pool and Journal Line Description.

There is also an ITRT and a Labor object in between those two objects but I don't think they matter for sake of this discussion.

I have published the  Service Target field to the Services perspective and locking it to the Service Target object.

I have published and the 5 Cost Source fields (Expense Center, Expense Center Name, Cost Pool, Cost Sub Pool and Journal Line Description) to the Service Costing perspective and locking it to the Cost Source object.

When I configure my table with all 6 fields (or with the Service Target field and any of the 5 fields from the Cost Source object), I do see in the configuration window that Service Target, Cost Source are grayed out and the table is locked to the both model objects at once.  However, when I go through my two drill two tables essentially setting a Service Target filter on the first and an Expense Center filter on the second when I make each drill to selection to get to this last page with the table in question, I get an error in my table stating:

     "Only filters locked to objects can be used in cross-object pivots.  You cannot use the filter 'Service Target' because it is      not locked.  To lock the filter, publish it and set the 'Lock to object' option".

As stated above I have already published the Service Target field to the Services perspective and locked it to the Service Target object.

I believe this is what you were guiding me to do but it is not giving me the expected result.  Did I miss something or do something incorrectly?

Thanks,

Steve

Report A has a table with a clickable Service Target drill column which navigates to Report B.

Report B has a table with a clickable Expense Center drill column which navigates to Report C.

Presumably you want Report C to be filtered by both the selected Service Target and the selected Expense Center.

This normally works fine in Apptio, so something about the way your three report tables are set up is affecting Apptio's ability to logically connect the report selections.

Double-check all three report tables to make sure they're using object-locked columns.

The grayed-out object context in the ad hoc query (AHQ) configuration dialog only indicates that at least one column is object-locked. It does not guarantee that all the columns are object-locked.

Metric-based columns cannot be locked to a model object, so we only need to check the non-metric columns seen in the Filters, Columns, Rows, Values sections of the AHQ configuration dialog:

1. Right-click column name in the AHQ configuration dialog.

2. Does "Publish" option appear? If so, this column is not published and is not locked to an object.

3. If "Published in..." appears, use the information to find this column in its perspective.
For instance, "Published in 'Service Costing/Cost Source" directs us to search the Cost Source folder found in the Service Costing perspective.

4. After finding the column in its perspective, right-click its name > Edit.

5. Verify the "Lock to object..." checkbox is checked.

If indeed all three report tables contain 100% object-locked columns, then something else might be amiss: Apptio Support may be able to help identify the issue.

Thanks for your help.  I think I am going to have to cut a support ticket.  I have everything published and locked as far as I can tell.  I see some are published to Services, more are published to Service Cost and another is published to Service Costing/Cost Source (not sure why that one is different or what it means).  All are locked to the objects they were sourced from.

Long story short I still get the same error on my bottom drill to table(s).

Actually i take that back.  There was one Service Target field on the Report B page table that was not published.  So I publised it and it seems to be working now.  Thank you!

It seems weird that I end up Publishing the same Field (Service Target) to the same Object (Services) as I work back up the Drill to chain.  And it says that this arleady exist and asks me to confirm I want to overwrite it.  I assume that is OK?

Another question.  Table A has 2 attributes (Service Target and Service Target Owner).  Table B has 3 attributes (Service Target, Expense Center and Cost Pool).  Service Target and Expense Center, respectively, are the two fields with Drill To and the desired fields for filtering.

However, when I make my Drill to selections for those fields, I can see in the bread crumbs that the filtering is for all of the attributes in that row where I selected a Service Target and an Expense Center.

For example, in Table A, I select Service Target = "Deskphone" but the breadcrumbs show filtering for Service Target = "Deskphone" AND Service Target Owner = "Jane Doe").

Then with that filtering, I make my Table B selection of Expense Center = "4321" but the breadcrumbs and my Table C only shows filtered results for Service Target = "Deskphone" AND Service Target Owner = "Jane Doe" AND Expense Center = "4321" AND Cost Pool = "Hareware".

I only want it to filter by the drill to selection field and only its specific value.  Thus selecting "Deskphone" and "4321" should only filter on those two fields/values not also include filtering for "Jane Doe" and especially not "Hardware".  How do I get that to work correctly?

I have played around in the Drill configuration window "Context Includes:" settings changing it from the defaults of Selected Row(s) and Current Report Context but no combination seems to give me what I want.  Is it possible to only have it filter on the value I selected for only the field enabled for drill?

Thanks,

Steve

Assuming you have Ribbon > Ad hoc > Drill > Selected Row(s) box checked, the Report A drill will send all fields seen in the Rows section of the AHQ configuration dialog as filters for Report B--as you noted above.

Two workarounds to consider:

1. Move Service Target Owner from Rows to Values in AHQ configuration dialog. This will prevent Report B from filtering on Service Target Owner, but it will prevent you from grouping the table on Service Target Owner.

2. Use EvalWiki() function instead of the Ribbon > Ad hoc > Drill feature. This function takes a bit of time to set up correctly but offers more flexibility than the ad hoc drill feature. Caveat: You'd need to copy your existing Report B and Report C components to the new report location(s) created by EvalWiki()--it cannot use Report B or Report C as its drill destination if you're passing one or more object-related filters.

Adding Service Target Owner to the Values section worked fine.  However, I cannot do that with the Cost Pool field without the grouping ability.

Regarding the EvalWiki() function, there isn't much to go on in the Help guide regarding this:

Example 2: Adding links to the cells in a table

Assume you want to have multiple columns in a table on a report, and in those columns you want links that will take the user to different places.

This can be done using EvalWiki:

1. In Studio, use the Ribbon to insert a new column in the table.
2. In the Edit Column dialog, check the Force Label option.
3. Enter a formula such as the following:

=EvalWiki("[[/myObject/!FILTER[myColumn="""&myColumn&"""]/ myReport|click here to see my report]]")

This will create a link that navigates to a filtered object report on the 'myObject' object. It will filter to only show the rows where 'myColumn' on that object report equals the value of 'myColumn' in the current table.

I don't seen a Force Label option in the Edit Column dialog.  Does that simply mean setting the Type to "Label"?

In trying to make sense of the syntax example provided and my situation this is what I came up with:

myObject = Service Target

myColumn = Service Target (sourced from Service Targets Master Data.Service Target)

myReport = myReport (btw I assume this is the name of the report page that the link will navigate to correct?)

Which i applied to the example syntax:

=EvalWiki("[[/Service Target/!FILTER[Service Target="""&Service Target&"""]/ myReport|Service Targets Master Data.Service Target]]")

But it didn't create a new field with Service Target values in it.  Rather a field with a hyperlink value of "Service Targets Master Data.Service Target" for every row.  When i clicked on any of those repetitive links it returned this error:

Not Found: !FILTER[Service Target=] (at path: cigna.com:IT Service Costing/Reports/1451606400000/CostModels/Default/.View:Service Target)(XSS_FILTERED)

Am I even close on this?  Is there more documentation to getting this to work that I am unaware of or do you have any examples of syntax and expected results?

Thanks,

Steve

This post has a few examples.

It sounds like your Report B table is locked to two objects (Service Target and Cost Source).

Apptio doesn't know which of the two objects your filter value refers to, so let's add the name of the object before the backing data table:

=EvalWiki("[[/@Service Target/!FILTER[Service Target="""&Service Target.Service Targets Master Data.Service Target&"""]|"&Service Target.Service Targets Master Data.Service Target&"]]")

Hi Christopher,

I am having trouble posting my reply to this so I will do this via email and cc: our CSM in hopes that if this doesn’t work either she can forward to your email.

Thanks,

Steve

Hi Christopher,

I am still having a lot of trouble here and a lack of documentation and exampels in the Help Guide is probably part of the problem.

Based on what you have provided, I have come up with a the following syntax for Report A and Report B

Report A - is locked to 1 object (Service Target)

=Evalwiki("[[/@Service Target/!FILTER[Service Target="""&Service Target.Service Targets Master Data.Service Target&"""]|"&Service Target.Service Targets Master Data.Service Target&"]]")

Report B - was originally locked to 1 object (Service Target) but is now locked to 2 objects (Service Target and Cost Source) which I changed after your last reply.

=Evalwiki("[[/@Cost Source/!FILTER[Expense Center="""&Cost Source.Cost Source Master Data.Expense Center&"""]|"&Cost Source.Cost Source Master Data.Expense Center&"]]")

In both tables I have Drill filters created from the ribbon for the fields Service Target (in Report A) and Expense Center (in Report B) respectively. they are working correctly with the exception of it filtering on all fields configures in the Rows section of the configuration window. I need those additional fields in the report with grouping.

Both of those ribbon configured Drills are configured with Context includes: (Selected Row(s) and Current Report Context) and desired navigation from Report A to Report B and Report B to Report C respectively.

I have inserted formula columns (Service Type in Report A and Expense Center in Report B) with Type = Label into both columns also called with formulas consistent with the syntax above.

The results get are:

Report A

- Inserted Service Target Evalwiki() function formula column shows "FILTER[Service Target=""]" as the value for every row (it is a hyperlink).

- Clicking any of those values change tab from Service Costing to Configuration with report breadcrumb to Service Target > Service Target = ""

Report B

- Inserted Expense Center Evalwiki() function formula column shows the correct Expense Center value for every row as a hyperlink

- Clicking any of the Expense Center values in that column changes the tab from Service Costing to Configuration with report breadcrumbs showing Cost Source > Expense Center ="4321".

In addition to it not navigating to the desired tab and report, it doesn't appear that it would ever carry forward the filter value from Report A along with the filtered value from Report B if it did land on the correct Report C.

Is the @Service Target in Evalwiki syntax for Report A and @Cost Source for Evalwiki syntax in Report B defining the object in the model it is using or the navigation to the target report? Or both? Or neither?

How would I define the navigation to the correct target report (correct Service Costing tab and report)? What I am seeking is that Report A Evalwiki drill navigates to Report B and B to C where Report A is the parent of Report B and B the parent to C.

Also, in the syntax, how to I ensure that when I select a Service Target in Report A and then an Expense Center in Report B that it carries forward both the Service Target and Expense Center selection as filters to Report C?

Lastly, I am sure that I have configured the table and the inserted formula column and Evalwiki () function in Report A consistent with how I did in Report B but as i said it shows the "FILTER[Service Target =""]" as the value instead of the actual correct Service Target values in Report A. Why wouldn't it show me Service Target values instead of the piece of syntax like it does in Report B with Expense Center?

Have a great holiday weekend!!!

Thanks,

Steve

EvalWiki() is an alternative to the Ribbon > Ad hoc > Drill feature.

If you get EvalWiki() working, you won't need the Drill activated on any table columns.

Your Report A is locked to just one object, so you'll want to exclude the object name from the EvalWiki() syntax.

We only need to include the object name if the report table is locked to two objects.

In your case I believe this means changing:

=Evalwiki("[[/@Service Target/!FILTER[Service Target="""&Service Target.Service Targets Master Data.Service Target&"""]|"&Service Target.Service Targets Master Data.Service Target&"]]")

to

=Evalwiki("[[/@Service Target/!FILTER[Service Target="""&Service Targets Master Data.Service Target&"""]|"&Service Targets Master Data.Service Target&"]]")

Each cell in the table column should show the relevant Service Target value (as a blue hyperlink).

You should not see "FILTER[Service Target=""]" in the table itself.

Since the connection between Reports A and B is not yet working, maybe the above fix will also help remedy the connection between Reports B and C.

Yes that works.  Should I be concerned that those new hyperlinks take me from my reporting layer on the Service Costing tab to the Configuration tab?  And is there a way to change that so that I stay on the Service Costing tab?

We can control which tab we land on by adding a bit more code to the EvalWiki() function.

For example:

=Evalwiki("[[tab:Service Costing/@Service Target/!FILTER[Service Target="""&Service Targets Master Data.Service Target&"""]|"&Service Targets Master Data.Service Target&"]]")

Thanks Christopher!

Is there a way to script this to work with more than 1 filter (like 2 or 3 fields) but not all fields in the table?

The earlier post has an example of stacking multiple filters:

=EvalWiki("[[tab:Service Costing/@.TableTransform:Labor Master Data/!FILTER[Labor ID="""&Labor.Labor Master Data.Labor ID&"""]/!FILTER[Employee Type="""&Labor.Labor Master Data.Employee Type&"""]|Click here]]")

Also keep in mind that you cannot use your existing Report B and Report C as destination targets for EvalWiki(), as noted in one of the earlier posts above, because you're passing an object-based filter from one report to another.

Once you get the display of the Report A table column values looking correct, EvalWiki() will create a new blank report when you click on one of its table links. Save the blank report (and notice the filter active, seen in the top-left navigation breadcrumbs), then circle back afterward to add any relevant components (such as copying/pasting components from the original Report B).

Similar story for Report C.

@Chris Davidson, I have a requirement where I may need to use EvalWiki but with an existing report(say "Department Cost Report") that has all the structure built, i.e., graphs, tables, KPIs, etc. Is there no way I can use EvalWiki to supply it the Filter values, such as "Cost Centre", based on which it opens "Department Cost Report" for that Cost Centre only? 

If not, is there anyway that I can create a drill in the table which points to different reports based on the value of a column?

I'm not aware of a way to force EvalWiki() to navigate to an existing destination report. EvalWiki() creates its own blank destination report (assuming there's not already a report at the navigation path it creates) when a user clicks a link in the source table. We as admin users then usually edit that newly created destination report, to lock in the new design for any other users who later on click links in the source table.

In your case, could you copy all Department Cost Report components to clipboard (at once), then click the table link, then paste all report components into the newly generated destination report?