IBM Apptio

IBM Apptio

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

 View Only
Back to discussions
Expand all | Collapse all

Customized Report - Labor - Staffing and Rate Details

  • 1.  Customized Report - Labor - Staffing and Rate Details

    Posted Fri December 18, 2020 07:49 AM

    Hi community,

    I'm struggeling with customizing a report and appreciate help/advice to resolve my problem :-)

     

    From the default report "Labor - Staffing and Rate Details" I want to customize reporting on FTE (Full-time equivalent) instead of Headcount. In my Labor object I have FTE and I've made a copy of the default report.

    Replacing Headcount with FTE is straight forward in the trend chart and also in the Details sections where I replace Internal/External Headcount with Internal/External FTE (Internal/External Headcount is calculated and added to my Labor object).

    Not so straight forward is however to configure values (see figure below) for the Details tables in the report; I want to replace 'Average Rate' with an 'Average Rate FTE' where the average rate is calculated from Internal/External Labor OpEx divided by Internal/External FTE (for each of the two Details tables). The 'Average Rate' from the default report is Internal/External OpEx divided by Internal/External Headcount. 

    So my question is - How do I calculate my 'Average Rate FTE' and add it to the value section?

    Regards,

    Svein


    #CostingStandard(CT-Foundation)


  • 2.  RE: Customized Report - Labor - Staffing and Rate Details

    Posted Fri December 18, 2020 09:24 AM

    Hi @Svein Selstø, have you tried creating the additional column in the table?  You could also click on Insert Formula Column on the report side (Data tab in the ribbon).


    Original Message


  • 3.  RE: Customized Report - Labor - Staffing and Rate Details

    Posted Fri December 18, 2020 10:00 AM

    Hi  @Jenny Franklin, sorry, but I'm not sure I understand what you mean by 'additional column in the table'. Do you mean to create a column 'Average Rate FTE' in the table in the report? 

    Your second suggestion I am not able to do, it seems ... as the Insert Column is disabled (greyed) for some reason ...


    Original Message


  • 4.  RE: Customized Report - Labor - Staffing and Rate Details

    Posted Fri December 18, 2020 10:35 AM

    Yes, you can create it in the table backing the report.  Pinging the experts to see if they can assist: @Debbie Hagen  @Jenny Goodwin :-)


    Original Message


  • 5.  RE: Customized Report - Labor - Staffing and Rate Details

    Posted Sun December 20, 2020 07:11 AM

    Thanks  @Jenny Franklin for taking the time :-) Do you know where I can find the formula behind 'Average Rate'? 


    Original Message


  • 6.  RE: Customized Report - Labor - Staffing and Rate Details

    Posted Sun December 20, 2020 09:11 AM

    Where are you pulling the columns to add to the report?  If you pulled it in from Perspectives, it's a column somewhere.  Could be a Metric, too - try looking in there to see if there's one called Average Rate.  If it's not in Metrics, it's probably a column in a table.  Unfortunately, Apptio's UI doesn't have a search feature yet.  The only way around that is the laborious task of documenting the entire model (all tables, all columns, etc.) - that way you can run search the document at least.  Sorry I'm not much help here.. :-(


    Original Message


  • 7.  RE: Customized Report - Labor - Staffing and Rate Details

    Posted Tue December 22, 2020 06:43 AM
    Hi @Jenny Franklin, in the default report these columns are pulled from Perspectives (mostly)​ and Average Rate is one of them. I think @Guillermo Cuadrado has some very interesting information in his post below :-) ​
    Original Message


  • 8.  RE: Customized Report - Labor - Staffing and Rate Details

    Posted Mon December 21, 2020 07:11 AM

    What  @Jenny Franklin was saying is that sometimes it's easier to do the calculations in the Transform table you're pulling data from in the report table. We do this all the time: in the reporting surface we cannot always do what we want, and there are formulas that won't work well.

    Conversely, there are functions that don't work in a Transform pipeline step, e.g. LookupObjectUnitValue().

    Looking inside the report you mention,   @Svein Selstø, I can see the following (Show Full Data Path in the table you want):

    !NEWCOLUMN[{Average Rate}=Cost/Labor Headcount][=Currency({$_},"#,###")]/

    There you see what it's doing. This is really arcane lore, and I barely understand what it says. It is NOT in the backing table (Labor), nor is it calculated as a regular formula in the reporting surface.  As I said, this is what I might call Apptio's black magic. 

    It seems to be dividing the value in the Cost Metric by the one in the Labor Headcount (calculated) metric. Maybe a more advanced magician apprentice can help you further.


    Original Message


  • 9.  RE: Customized Report - Labor - Staffing and Rate Details

    Posted Tue December 22, 2020 06:49 AM
    Hi @Guillermo Cuadrado, Thanks for you interesting information! I did not think about the 'Show full data path' option. I will try to create a metric for the FTE Average Rate and see if this resolves my issue :-) Thanks :-)

    Regards,
    Svein​
    Original Message


  • 10.  RE: Customized Report - Labor - Staffing and Rate Details

    Posted Tue December 22, 2020 06:59 AM
    All the credit-if any-goes to @Gulcin Menekse, our great CSM, who has been hammering us with the Show Full Data Path option in many report queries we've had.​
    Original Message


  • 11.  RE: Customized Report - Labor - Staffing and Rate Details

    Posted Mon December 21, 2020 01:13 PM

    @Richard Coronado;  @Oliver Smith,  Didn't one of you do something like what Svein is asking about?  I seem to remember a discussion about it quite some time back.



    ------------------------------
    Debbie Hagen
    Apptio
    ------------------------------

    Original Message


  • 12.  RE: Customized Report - Labor - Staffing and Rate Details

    Posted Tue December 22, 2020 07:46 AM
    Edited by System Admin Tue November 05, 2024 06:18 PM
    Hi all, and thanks to @Guillermo Cuadrado - I think this worked out just fine :-)

    So to sum up what I wanted to do was to add calculations for FTE to the default report 'Labor - Staffing and Rate Details' This report is a drill down from the report Labor Analysis. FTE is based on worked hours rather than Headcount and I could easily map my calculations for FTE to Headcount in the Labor Master Data (in fact that is what I started to do) to get what I wanted _but_ someone wanted to see both Headcount and FTE in the reports ;-) so then I had to add FTE separately.

    This is my configuration now:

    First I made a copy of report 'Labor - Staffing and Rate Details'

    Next I added columns for FTE, Internal FTE and External FTE to my backing table and mapped all 3 to Labor Master Data


    Then I created a metric for the FTE Average Rate

    And finally I replaced values in my custom report (the copy of 'Labor - Staffing and Rate Deatils') - here showing only the values section for the external labor table in the report but you can see the values for the internal labor table as well.

    One thing I discovered is that Filter needs to remain unchanged. I tried to use Internal FTE and External FTE in the filters but then my Internal Labor OpEx and External Labor OpEx calculations displayed wrong numbers. I haven't figured out why yet but I need to look into this and will post an update if I do.


    Original Message


  • 13.  RE: Customized Report - Labor - Staffing and Rate Details

    Posted Tue December 22, 2020 01:46 PM
    .
    Original Message


Related Content