Hey,
I’m trying to calculate values with Tempo Billed Hours by multiple criteria.
For example, I want to collect all hours which have a specific Epic Link or Issue Name (where the epic link is not available) and then use these to calculate a quote.
The report should be on a monthly basis for the last 6 or 12 months. Per day is not so important, but it should be listed per Logged by.
It’s working sometimes and mostly only for 3 Months. As I understood, it’s because I filter on all Issues which makes it’s damming slow and lead to frequent timeouts.
I don’t know how to optimize it. It’s my first contact with eazybi
Can you help me?
Example 1 - OOO Hours
CACHE(
Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
[Issue].CurrentHierarchyMember.Level.Name = 'Issue'
),
CASE WHEN
[Issue].CurrentHierarchyMember.Level.Name = 'Issue'
THEN
CASE
WHEN IsEmpty([Measures].[Tempo billed hours])
THEN 0.0
WHEN CoalesceEmpty([Issue].CurrentMember.Name,'') MATCHES '^OUT.*'
THEN IIf(IsEmpty([Measures].[Tempo billed hours]),0.0,[Measures].[Tempo billed hours])
END
END
)
)
Example 2 - Billable Hours
Cache(
Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
[Issue].CurrentHierarchyMember.Level.Name = 'Issue'
),
CASE WHEN
[Issue].CurrentHierarchyMember.Level.Name = 'Issue'
THEN
CASE
WHEN IsEmpty([Measures].[Tempo billed hours])
THEN 0.0
WHEN CoalesceEmpty([Issue].CurrentMember.Name,'') MATCHES '^OUT.*'
THEN 0.0
WHEN CoalesceEmpty([Issue].CurrentHierarchyMember.get('Epic Link'),'') MATCHES '^INT.*'
THEN 0.0
ELSE
[Measures].[Tempo billed hours]
END
END
)
)
Example 3 - Non-Billable Hours
CACHE(
IIf(
[Measures].[Total Hours (Clean)]*[Measures].[Billable Hours]> 0,
100/[Measures].[Total Hours (Clean)]*[Measures].[Billable Hours],
0.0
)
)
Thanks & bests
Fabian