I am new to eazyBI and I am trying to create a report that shows a monthly total count of issues that were in a set of custom statuses within a month grouped by another set of types of issues.
The report looks like this (ignore the meeting count line, my boss just wants a table that will show the other status numbers):
All SAs would be all issues of type “Advisement” or “Advisement - RA” that were created or in any of these statuses during that month:
Aggregate({
[Status].[In Progress],
[Status].[Ready for Review],
[Status].[Awaiting IT Completion of Security Controls & Activities],
[Status].[Awaiting Questionnaire from IT],
[Status].[Awaiting Information/Documentation from IT]
})
Once I have total SAs, then I need to slice that up to issues with their “Advisement Type” field set to sets of possible values. An example would be the SDLC line is all tickets with “Advisement Type” set to “On Prem” or “Hybrid” or “External”.
You have a great progress. Yes, you would like to use calculated members or measures to create groups of specific statuses.
The function Aggregate works well there. I would suggest creating this calculated member in the dimension Transition status, though.
Aggregate({
[Transition Status].[In Progress],
[Transition Status].[Ready for Review],
[Transition Status].[Awaiting IT Completion of Security Controls & Activities],
[Transition Status].[Awaiting Questionnaire from IT],
[Transition Status].[Awaiting Information/Documentation from IT]
})
Transition status will represent the historical status of the issue. Status will always represent the current status of the issue.
With Transition status, you would like to use historical measures. I would suggest using measure Issues history. It will show issues in a specific status at any moment back in time.
You can use Transition status dimension on Pages and select this calculated member or use multiple selections of the needed statuses.
Then add Issue type dimension to Pages as well. Use option multiple and select issue types Advisement and Advisement - RA.
Use measure Issues history on Columns. Add dimension Advisement Type to columns as well and select Advisement Type level in All hierarchy level members. Adding a dimension to columns will split values on the report.
Use dimension Time on Rows - select the needed level in all hierarchy level members.
Here I have a similar report using some default dimensions:
The question is, will tickets get counted more than once if I use the Transition Status dimension? We have tickets bouncing back between the active states a lot through the month. We don’t care what state they were in through the month. What we want is a precise count of tickets in the set of states on the last day of the month for each of the months.
Measure Issues history counts issues in a specific status on the last day of the period. The measure will not double count issues bouncing back and forth into the same set of statuses. It will count all the activities in the current and previous periods and will give the resulting status of issues only. If the issue was active during the period but was closed at the end it will be counted as closed at the end of the period.
One last question. How do you add the same dimension to pages and columns? I see in your example report you have Time in both pages and rows. When I drag time down I can only put it in Pages or in Rows. I do not see how to add it to both.
I tried to change the calculated measures to use Transition Status. But, that produces zero results no matter how I try and select the time element for the page.
While [Status] returns a value. That doesn’t act as you describe with Transition Status showing the count on the last day of the selected page period. Like this:
You are addressing members of Transition Status and Status dimensions only in this calculation. eazyBI requires some measure. If there is no measure used in the report/calculation, eazyBI will use a default measure Issues created. This measure represents a current value in the issue and does not work with a historical dimension Transition status.
We suggest using measures explicitly in any report or formula.
I would suggest using the formula with Transition status and define it as a new calculated member in Transition status dimension:
If I understand what you said, I think I got the reports working.
To report on the number of hours a ticket is in a set of statuses I have to do two separate numerical calculations and add them like this:
(
[Measures].[Days in transition status],
[Issue Type].[Advisement],
[Transition Status].[Ready for Review]
)
+
(
[Measures].[Days in transition status],
[Issue Type].[Advisement],
[Transition Status].[In Progress]
)
And in order to get the accurate count of the number of issues with a specific value for a custom field or status, again do separate calculations and add or subtract them from the total. Like this:
NonZero(
(
[Measures].[Transitions to status issues count],
[Issue Type].[Advisement],
[Advisement Type].[CLOUD]
)
-
(
[Measures].[Transitions to status issues count],
[Issue Type].[Advisement],
[Advisement Type].[CLOUD],
[Advisement Assignee].[(none)],
[Secondary Assignee].[(none)]
)
)
Yes, you can sum up two tuples if you would like to get the total days spent in two statuses for issues with those transitions.
I am not sure if the next measure where you count issues by transitions works as intended. While the concept to use subtraction will work well please check if the measure Transitions to status issues count is the correct one. This measure will count only issues with status changes.
If you would like to count issues changing a value in the specific custom field you would need to use a bit different measure. We import a default hidden measure Transitions to and Transitions to issues count. You can access changes to a specific custom field only via dimension Transition field. Please make sure you enabled and imported value changes for the particular custom field to make it work, though.
(
[Measures].[Transitions to issues count], -- count any changes to a specific value
[Transition field].[Advisement Type], -- in this custom field only
[Issue Type].[Advisement],
[Advisement Type].[CLOUD]
)
If you would like to count issues in this status, issue type, and advisement type, the measure Issues history will be more accurate. You can subtract tuples if you would like to remove None values in two fields.
Add the same dimension on Rows/Columns and Pages. Please move the dimension to Rows or Columns. Then add them to Pages from there. There is a special button Pages visible when you expand the dimension block added to Rows/Columns.
We have a small video on how to do this for Time in our documentation.
