Change in Tempo Rates for calculating billed hours

Questions & Answers
1

Hello,

I am aware that Tempo Rates are not imported on Jira Cloud. So as a workaround I am doing this.

Tempo REST API > Google App Script to populate a Google Sheet > Import in eazyBI.

These are the fields in my Google sheet.
userid, project, cost-rate,bill-rate effective date, rate-current-or-not
sdfs24234, PRJA, 55, 65, 12/12/2021,0
sdfs24234, PRJA, 75, 85, 01/01/2022,1
sdfs74445, PRJA, 65, 75, 01/01/2022,1
and so on

Import mappings
userid > Assignee
Project > Project
Effective Date > Time
rate-current-or-not > Measure in Issue Dimension
cost-rate and bill-rate > Measure in Issue Dimension

This workaround somehow works as long as the user rate is same because I am then able to multiple rates by their Tempo billed time and create forecast. I say somehow because I need to ensure that in the reports I need to include assignee, issue and time dimension. However when rate changes for a user then reports don’t behave nicely.

So my question.

  1. Is there a better way to do this. Especially the mappings. For a particular project if I want to calculate total billed amount then I need to include assignee, issue and time which is not ideal for showing consolidated number.

  2. How to manage change in rates.

  3. Is there a plan to include Tempo rates in eazyBI anytime soon?

Has someone solved a similar problem before?

Thanks,
Ravi

2

Hello @ravisagar,

  1. Calculating totals without putting users in the report would require iteration through the Assignee and other relevant dimensions and then summing up the totals. Please see the possible construction at the end of the post.

  2. If you have mapped the rates to specific dates, it is possible to retrieve the relevant rate in a specific month.
    If the name for the imported rate measure is “Contractval”, the expression for the “Last effective rate” might be as follows.

Cache(DefaultContext((
    [Measures].[Contractval],
    [Assignee].CurrentMember,
    -- find last month with cost rate history
    Tail(Filter(
      [Time].[Month].Members.Item(0):
      Tail(Descendants([Time].CurrentMember,
        [Time].[Month])).Item(0),
      -- filter months with cost rate history
      Cache(NOT IsEmpty(DefaultContext((
        [Measures].[Contractval],
        [Assignee].CurrentMember,
        [Time].CurrentMember
      ))))
    )).Item(0)
  )))

You might create a new calculated measure with this expression and use it together with the Time dimension on report rows, columns, or tuples.

  1. We have a feature request in our backlog for developing even closer integration with Tempo, including Tempo rates. However, I cannot tell when that might be implemented.

The construction for calculating the total (historic) rate without specifically showing Assignee and/or Time on the report might be as follows. It requires the Project dimension to be on the report.

Sum(
 Filter(
--set of all relevant users
  DescendantsSet(
   [Logged by].CurrentHierarchyMember,
   [Logged by].[User]),
--filtering only users with logged hours
 [Measures].[Hours spent]>0),
--numeric measure for sum by users - total cost per user for the whole time
   Sum(
--filter only relevant time periods   
    Filter(
      DescendantsSet(
        [Time].CurrentHierarchyMember,
        [Time].[Month]),
--condition for relevant month - logged time by the user in this month        
     [Measures].[Hours spent]>0),
--numeric measure for sum - rate that is relevant to month, project and assignee
     DefaultContext(
       (
--retrieve relevant Assignee by the current Logged by       
       [Assignee].[User].GetMemberByKey([Logged by].CurrentMember.Key),
       [Time].CurrentMember,
       [Project].CurrentMember,
       [Measures].[Last effective rate]
       )
     )
   )
)

Regards,
Oskars / support@eazyBI.com

3

Hi @oskars.laganovskis

Thanks for your reply. Let me try this.

Yes, it would be great if Tempo rates can be imported.

Ravi