Calculating total Hours Spent for Logged By and Time

image

I am trying to create a report where the pages allow the selection of the [Logged By].[User] and the [Time Weekly].[Week] . The Report has [Issue].[Issue] for rows and uses [Measure].[Hours Spent]. This is working and reporting the hours logged for that user for the week selected. I am trying to add 2 additional custom measures.

Total Hours for Issue:

Cache(
CASE WHEN [Issue].CurrentMember.Level.Name = “Issue”
THEN val(([Time].DefaultMember,[Measures].[Hours spent]))
ELSE
– if the current Issue dimension level is not Issue, then sum the values
Sum(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
[Measures].[Hours spent] > 0
AND
[Measures].[Issues closed] > 0),
([Time].DefaultMember, [Measures].[Hours spent])
)
END)

This is giving the same result as Hours Spent (Sum of time for the week requested) I am trying to get all time spent on the issue. I thought the [Time].DefaultMember, [Measures].[Hours spent] would give me all time., but it is not.

The second measure I want is all the time spent on the issue for the selected logger. I have not attempted that yet as I am wrapped around the axle on the total time.

Thanks

Updating as I have not gotten an answer in 2 weeks

Hi,

When Time and user is selected in Pages, measures “Hours spent” shows logged hours for this issue logged by this user in the selected time period.

If you want to get all logged hours by this user (in any time period) for the issue, you may want to create a calculated measure that overwrites selected time with Time DefaultMember() - you were absolutely correct about that.

The only thing is: as Time Weekly dimension is used in the report rows, Time Weekly dimension also should be used in the formula or, a more universal way, you may refer to CurrentHierarchy() and then retrieve this default member. Read more here: Dimension, hierarchy, level and member names.
The measure could be with the following calculation formula:

([Measures].[Hours spent],
 [Time].CurrentHierarchy.DefaultMember)

Similarly, you may overwrite the selected user to get all issue logged hours (by any user) in the selected time period:

 ([Measures].[Hours spent],
  [Logged by].DefaultMember)

You may also create a measure to override both Page selections to get total hours logged for this issues (in any time period, by any user) similarly.

Ilze / support@eazybi.com