I have a report by components (in rows).
In jira my components contains taks and subtasks, I’m trying to create a eazybi report by components but i’d like to count the number of task by status in my reports (but not only).
I also already have the percentage of progress of my component.
If I just add “status” in columns, my problem is that my percentage are also devided by status, what does not make sense.
What I’d like to have is :
Col1 : percentage based on measure, Col2 : nb of Open taks, Col3 : nb of in progress tasks, …
row : component
I hear what you are saying. You would like to represent open issues by their status and percentage only in one column for all statuses. And the challenge is that both measures are represented with each status combination.
In this case, you might want to create a new calculated measure with a condition to show a measure of interest for a specific column.
For example, to show percentage only for all statuses, you may create a calculated measure with condition CASE WHEN status is All statuses on columns or status dimension is not present in the report THEN calculate and show a percentage. For the rest of the columns with particular statuses, a measured value won’t be displayed. The formula might look like this:
CASE WHEN -- All Statuses in columns
[Status].CurrentHierarchy.CurrentMember IS [Status].CurrentHierarchy.DefaultMember
THEN --calculate percentage
case when [Measures].[Open issues] <> 0
then
[Measures].[Open issues] /
SUM(
VisibleRowsSet(),
[Measures].[Open issues])
end
END
In the report, choose the option to hide empty columns. The report might look like in the picture below:
With eazyBI version 5.3.0. we included an option to build reports where you can use several measures on columns and split (drill into) only one of them to see more details. No workarounds with custom calculations suggested above are required anymore.
In this report, you can use Open issues in the Columns. Then click on the measure and select an option Add calculated and select % of Total to get a percentage of open issues for each row (component) based on all rows (all components). Then you can rearrange the measure Open issues after Open issue %. After this, click on the measure Open issues once again and select option Drill into. Select dimension Status and level Status.
The formula using VisibleRowSet() is needed for the cases, if you would like to use a formula in quite a general way - apply for any selection on Rows.
In any previous version, there was an option to calculate the same. However, they were not as general calculations and you might need to define calculations hardcoding the same set of member you see on rows.
The simplest use case, would be defining calculating a % of total for allmembers. For example, how many issues of a particular priority is from all priorities:
case when [Measures].[Open issues] <> 0
then
[Measures].[Open issues] /
( [Priority].DefaultMember,
[Measures].[Open issues])
end
I used tuple with priority default memebr and measure. The formula will give you expected results if you will use Priority on Rows. But it will not work for any other dimension, you would need to define a similar formula for any other dimension.
