Optimization for a report

Questions & Answers
1

Hi,
i have a report that takes a lot of time to load, its a project with 107230 tickets, but with filters, and there oare ther reports, so they do load mostly - this is the table

צילום מסך 2025-04-21 112008
צילום מסך 2025-04-21 1120081087×578 30.2 KB

and this is one of the calculations that i think takes time to load :Cache(
CASE WHEN
– Only calculate for individual issues to reduce complexity
[Issue].CurrentHierarchyMember.Level.Name = ‘Issue’
THEN
– Cache the total days calculation to avoid recalculating it
CASE WHEN
Cache(
Sum(
[Transition Status].[Transition Status].Members,
[Measures].[Days in transition status]
)
) > 0
THEN
– Calculate percentage for current transition status
[Measures].[Days in transition status] /
Cache(
Sum(
[Transition Status].[Transition Status].Members,
[Measures].[Days in transition status]
)
)
END
END
) Maybe you have a faster solution? The goal of this table is to show the tickets their percentage in each status transition, the time to resolution, sla also the average time in transition. Thank you in advance noa

1 Like
2

Hi @noa1483 ,

Your report is likely slow because the formula is iterating through all transition status members multiple times. Here’s a much more efficient way to calculate the same percentage:

[Measures].[Days in transition status] / 
([Measures].[Days in transition status], 
[Transition Status].CurrentHierarchy.DefaultMember)

This formula divides the days in the current transition status by the total days across all statuses in a single operation, avoiding the expensive iteration through all transition status members.

Also, I’d recommend using the “drill into” functionality for your measures instead of showing all transition statuses for every measure. This way, the Transition Status dimension would only appear for the specific percentage measure you’re interested in, not for all measures in your report: https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-Drillintomeasurebyanotherdimensionlevel
To do this:

  1. Remove Transition Status from your report columns/rows
  2. Right-click on your percentage measure
  3. Select “Drill into” > “Transition Status”

Best,
Gerda // support@eazybi.com

1 Like
3

thanks gerda , much appreciated

1 Like