Need calculation syntax for a few Measures

H_B · October 13, 2020, 8:09pm

Hi, I am trying to get a syntax for few measures to enable my reporting needs in easyBI.

Last 5 Sprints
Last 2 months, 3 and 6 months.
Sprint Points Planned in a sprint (Sprint points committed MINUS Sprint points that spillover from Last Sprint/ Previous Sprint)
Cumulative Story Points created - this is needed so it can calculate total story points at a Initiative Level across various Epics.
Also how do I download Report, Dashboard in PDF format or any other format from easyBI.
Is there anyway to change Font type or color of Data Label in Reports to make it easily visible?

Thank You for your help with these.

martins.vanags · October 15, 2020, 9:10am

Hi @H_B

Welcome to our community.
Let me help you with your questions

try the approach described in another community post to filter the report by last 5 sprints
Filter your eazyBI for last 5 closed sprints
for calculated members you can always explore our Demo account:
https://eazybi.com/accounts/1000/cubes
It has a lot of good examples for calculated members
Here you would need a calculated measure that iterates through Issues dimension members to find issues that were removed from the sprint
```
 NonZero(
```
SUM(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
[Sprint].CurrentMember.Name = [Measures].[Issue sprints]
AND
[Measures].[Sprint Issues committed]>0
),
[Measures].[Sprint Story Points committed]
)
)

Don’t forget to enable “Nonempty” cross join when selecting this calculation.

To add cumulative calculations try using standard calculations for a measure.
https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-Addstandardcalculationsbasedonaselectedmeasure
Please find how to export eazyBI reports
https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-Savereportandtoolbarbuttons
Also, don’t miss the feature to subscribe for dashboards and receive dashboard in PDF file automatically to your email address on a regular basis.
https://docs.eazybi.com/eazybi/publish-reports/create-dashboards#Createdashboards-Dashboardemailsubscription
Unfortunately, you can not change the font type or color in eazyBI reports from UI.
The only customization can be done with font size.
https://docs.eazybi.com/eazybi/analyze-and-visualize/create-charts#Createcharts-Datalabels

Martins / eazyBI team

H_B · October 15, 2020, 2:37pm

Thanks Martins.

I tried using solution for #3. I am getting error as noted below.

To clarify the ask I have is- Have ability to calculate what is Core Commitment for just an Active sprint excluding issues/ points which come from previous sprints. Probably need two measures, one for Issue Count, another for SP.
Please advise.

#ERR: mondrian.olap.fun.MondrianEvaluationException: Exception while executing function NonZero: mondrian.olap.MemoryLimitExceededException: Mondrian Error:OutOfMemory used=705338208, max=716177408 for connection: Jdbc=jdbc:postgresql://cft-jiraeazybi.cpxlp0s4s23s.us-east-1.rds.amazonaws.com:5432/jiraeazybi?tcpKeepAlive=true&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory&sslmode=verify-full; JdbcDrivers=org.postgresql.Driver; UseContentChecksum=true; CatalogContent=’<?xml version="1.0" encoding="UTF-8"?> <Table na

H_B · October 15, 2020, 2:38pm

I could not paste the full error due to limitations here.

H_B · October 15, 2020, 2:56pm

for Last 5 Sprints, I am not getting any data when I use that calculated Measure Under ‘Sprint’ dimension.
Striked out Team, Sprint Names.

martins.vanags · October 15, 2020, 3:09pm

Calculated measures should be created only in “Measures” dimension.

In the 3.point, make sure that you save your report with “Nonempty” cross join before selecting the report and doublecheck that your formula is as follows:

NonZero(
SUM(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
[Sprint].CurrentMember.Name = [Measures].[Issue sprints]
AND
[Measures].[Sprint Issues committed]>0
),
[Measures].[Sprint Story Points committed]
)
)

Martins / eazyBI support

H_B · October 15, 2020, 3:20pm

Yes, I created this measure in Calculated Measures under ‘Measures’ dimension itself. Still getting error

H_B · October 15, 2020, 3:20pm

Earlier comment was about creating Last 5 Sprints measure in Sprint Dimension. Let me know what maybe the resolution there. or am I doing something incorrect?

martins.vanags · October 15, 2020, 3:57pm

Hi,

The measure “last 5 sprints” should be a user-defined measure in “Measures” dimension not a member in the “Sprint” dimension.

And the error in your screenshot is because the formula in point 3 is built to work only for “Sprint” dimension members.

Best regards,

H_B · October 15, 2020, 4:20pm

ok.

So can you provide what shall I use for ‘Last 5 Sprints’.

Also, please provide formula for finding out SP/ Issues committed only in a given sprint excluding any prior sprints carryover issues/ SP. and will this be created under ‘Calculated Measure’ or in ‘Sprint Dimension’. Sorry, a little confused, hence seeking clarity further.

thanks

martins.vanags · October 20, 2020, 12:14pm

Hi,
Try this formula for a calculated measure “Last 5 closed sprints”

Case when
[Sprint].Currentmember.Level.Name = 'Sprint'
Then
Case when [Sprint].CurrentMember.Name <> '(no sprint)'
Then
NonZero( Rank(
[Sprint].CurrentMember,
Tail( Order(Filter(
[Sprint].[Sprint].Members,
[Measures].[Issues created] >0
AND [Measures].[Sprint closed?]="Yes" -remove line to include all sprints with startdate
), 
[Sprint].Currentmember.Get('Start date'), BASC) --this is the property by which sprints are sorted
,5))) --use other value to show more/less than 5 sprints
    End
    Else
    [Measures].[Issues created] 
    End

And use this code to count issues that were committed only to the displayed sprint.

NonZero(
Count(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
[Measures].[Issue sprint] = [Sprint].currentMember.Name
AND
[Measures].[Sprint issues committed]>0
)
)
)

Simiarly, you could calculate Story points

NonZero(
Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
[Measures].[Issue sprint] = [Sprint].currentMember.Name
AND
[Measures].[Sprint issues committed]>0
),
[Measures].[Sprint story points committed]
)
)

Martins / eazyBI team

H_B · October 20, 2020, 8:05pm

Thanks Martins.

When using code for calculating SP for work committed only in a given sprint, I am getting error. I am using it as a Calculated Measure. what should I do differently?

Also, how is ‘Last 5 Sprints’ measure expected to be used? my thought is to be able to use it to see performance / data for last 5 sprints for any team/ teams. How do I execute that?

martins.vanags · October 22, 2020, 6:33am

Hi,

Can you save the report with the error when calculatng work commited in new report and share the definition of the report? And let us know what kind of error do you get exactly?

Please find the “last 5 closed sprint” used in the report:

Filter your eazyBI for last 5 closed sprints

Martins / eazyBI support

H_B · October 23, 2020, 1:17am

hi, this is the error I face. I am not sure if I can share report definition as it may contain employer specific data which I am not permitted to share externally.

martins.vanags · October 23, 2020, 2:54pm

Hi,

If you find the report definition sensitive, please reach out to support@eazybi.com and provide it via email to us.
Without that context, it is hard to tell correctly what went wrong with your calculation.

Martins / eazyBI