Question about cell formatting and "log work" hours

Hello,

I’m building a table that shows the hours logged in each Jira Story per week. This should show that people are working around 40 hours per week.

If people logged less than 40 hours, the cells should be RED. And GREEN if they work over 40.
I couldn’t find a cell formatting to do “greater than” or “less than”, so I used a range with 0 to 39.999 being RED and 40.001 to 120 being GREEN.
(Although, in my example in the image I used 8 hours instead of 40 for my tests)

My questions are:

  • Is there a Calculated code to use “greater than” and “less than”?

  • When I expand the weeks into days, the same values are affecting RED and GREEN. Can I define different formatting values for days and for weeks? (For weeks the target is 40 and for days it’s 8)

  • If the above is not possible, can I hide the ability to expand weeks into days?

Thank you.

Hi @guillolb
Welcome to the eazyBI Community, and thanks for posting your question!
You can define greater/less than in the Custom formula tab; see the example in the screenshot below.
When you access the conditional formatting, switch to the “Custom formula” tab (learn more here - Conditional cell formatting) and paste the formula below. Note that no formatting will be applied in the current setup for a round value of 40.

CASE
WHEN
[Time].CurrentHierarchyMember.Level.Name = "Week" AND
[Measures].[Hours spent] < 40
THEN
"Red"
When
[Time].CurrentHierarchyMember.Level.Name = "Week" AND
[Measures].[Hours spent] > 40
THEN
"Green"
END

Then select the “Exact value” tab as marked in the screenshot below and type two values, selecting the corresponding background colors.

Best wishes,

Elita from support@eazybi.com

That worked great!

I expanded it to use different values for days, and also to only highlight the totals in the rows when expanding “Customer - Program”.

CASE
WHEN
  [Time].CurrentHierarchyMember.Level.Name = "Week" AND
  [Customer - Program].CurrentHierarchyMember.Level.Name <> "Customer - Program" AND
  [Measures].[Hours spent] < 40
  
THEN
  "Red"
WHEN
  [Time].CurrentHierarchyMember.Level.Name = "Week" AND
  [Customer - Program].CurrentHierarchyMember.Level.Name <> "Customer - Program" AND
  [Measures].[Hours spent] > 40
THEN
  "Green"
WHEN
  [Time].CurrentHierarchyMember.Level.Name = "Day" AND
  [Customer - Program].CurrentHierarchyMember.Level.Name <> "Customer - Program" AND
  [Measures].[Hours spent] < 8
THEN
  "Red"
WHEN
  [Time].CurrentHierarchyMember.Level.Name = "Day" AND
  [Customer - Program].CurrentHierarchyMember.Level.Name <> "Customer - Program" AND
  [Measures].[Hours spent] > 8
THEN
  "Green"
END

Now I’m playing with this to paint the “day” cells gray for when I expand the weeks.

1 Like

Hello,

I expanded the report above to show the hours logged by Team/User.

And I have the same cell formatting. But I’m trying to modify it to not affect “Team totals” only individual users. But I haven’t been able to do it.
Can you help me?

I know I need to change the following line:
[Customer - Program].CurrentHierarchyMember.Level.Name <> “Customer - Program”

To something like:
[Logged by].CurrentHierarchyMember.Level.Name = …

However, what I want to address are Calculated members of “[Logged by]”

This is the formatting Code:

CASE
WHEN
  [Time].CurrentHierarchyMember.Level.Name = "Week" AND
  [Customer - Program].CurrentHierarchyMember.Level.Name <> "Customer - Program" AND
  [Measures].[Hours spent] < 40
  
THEN
  "Red"
WHEN
  [Time].CurrentHierarchyMember.Level.Name = "Week" AND
  [Customer - Program].CurrentHierarchyMember.Level.Name <> "Customer - Program" AND
  [Measures].[Hours spent] > 40
THEN
  "Green"
WHEN
  [Time].CurrentHierarchyMember.Level.Name = "Day" AND
  [Customer - Program].CurrentHierarchyMember.Level.Name <> "Customer - Program" AND
  [Measures].[Hours spent] < 8
THEN
  "Red"
WHEN
  [Time].CurrentHierarchyMember.Level.Name = "Day" AND
  [Customer - Program].CurrentHierarchyMember.Level.Name <> "Customer - Program" AND
  [Measures].[Hours spent] > 8
THEN
  "Green"
  WHEN
  [Time].CurrentHierarchyMember.Level.Name = "Day"
THEN
  "Gray"
END