String parsing and aggregation in a Calculated Measure

Questions & Answers
,
1

Hi,

in the fix version in jira I have the team and the capacity (hours per person) that is working on the fix version like this:
“Person A: 122h,Person B: 152h, Person C: 22h”

I would like to extract the hours and add them together in the calculated measure (I know I can do it during import as a custom field but I would highly prefer to do it in the calculated measure as the format of the string is still in flux so I can easily update this without reimport etc).

Using ExtractString I can extract the hours (and the names). Unfortunately, I have found no way to iterate over the hours (the number of team members is not constant but small, so below 10).

I got it working by unrolling the iteration (I extract 10 hour strings from the team string and add these, as there is less then 10 and as empty hours are counted a 0 I can just add these and get what I want) but this is really ugly:

CASE WHEN (ExtractString(ExtractString([Measures].[Fix Version Description],'.*Team:(.*)',1),'([^:]*: *(\d+)h,?){11}+',2)) <> ''
THEN
  -1 -- throw error:'team cannot have more then 10 members ' || ...
ELSE
  -- ugly hack: currently we support at most 10 team members as I have not found a 
  -- way to extract and accumulate the hours from the team string inside just one measure
  Val(ExtractString(ExtractString([Measures].[Fix Version Description],'.*Team:(.*)',1),'([^:]*: *(\d+)h,?){1}+',2))+
  Val(ExtractString(ExtractString([Measures].[Fix Version Description],'.*Team:(.*)',1),'([^:]*: *(\d+)h,?){2}+',2))+
  Val(ExtractString(ExtractString([Measures].[Fix Version Description],'.*Team:(.*)',1),'([^:]*: *(\d+)h,?){3}+',2))+
  Val(ExtractString(ExtractString([Measures].[Fix Version Description],'.*Team:(.*)',1),'([^:]*: *(\d+)h,?){4}+',2))+
  Val(ExtractString(ExtractString([Measures].[Fix Version Description],'.*Team:(.*)',1),'([^:]*: *(\d+)h,?){5}+',2))+
  Val(ExtractString(ExtractString([Measures].[Fix Version Description],'.*Team:(.*)',1),'([^:]*: *(\d+)h,?){6}+',2))+
  Val(ExtractString(ExtractString([Measures].[Fix Version Description],'.*Team:(.*)',1),'([^:]*: *(\d+)h,?){7}+',2))+
  Val(ExtractString(ExtractString([Measures].[Fix Version Description],'.*Team:(.*)',1),'([^:]*: *(\d+)h,?){8}+',2))+
  Val(ExtractString(ExtractString([Measures].[Fix Version Description],'.*Team:(.*)',1),'([^:]*: *(\d+)h,?){9}+',2))+
  Val(ExtractString(ExtractString([Measures].[Fix Version Description],'.*Team:(.*)',1),'([^:]*: *(\d+)h,?){10}+',2))
END

Second question, is there a way to “throw” and error in a calculated measure? It seems the types of both THEN and ELSE branch of the case statement need to be the same. I would want to “throw” an error in the first case so a string message which is displayed instead of the number telling the user (in the table). So that users are aware of the issue.

Finally, while asking about basics. Is there a way to define a temporary local variables inside a calculated measure for expressions that I want to reuse?
E.g. ‘ExtractString([Measures].[Fix Version Description],’.Team:(.)‘,1)’ in my code above I would want to comput eonly once and then reuse.

2

Hi @aherz,
​
​You are right about not being able to iterate through values within MDX.
​This is the limitation of this language and is going to stay the same. If you need to iterate through the string to retrieve and sum up values - you need to do it during the data import.
Iteration through the field values once for each issue during the ​data import is more efficient than iteration during each report execution.
​
​The measure should return the data in the same format in all conditional branches.
If you only want to display the value or error message, you might consider displaying a text string created from the measure value via the CAST() operator or error message in the case of an error. The numbers would not look very nice as they will be aligned to the left. Moreover, you might not be able to further re-use that measure in any other calculations without re-converting it back to the numeric value. This option is far from perfect, but might work in desperate need.
​
​You might use the Cache() function to wrap reusable code. The next time, when the same code is executed - the eazyBI will check if the value exists and then re-use it instead of re-launching the same code.
​Please read more about that here - Cache.
​
​Regards,
​Oskars / support@eazyBI.com

3

Hi Oskars,

a few things I discovered (for the benefit of the community mostly):

  • Cache does not really help readability, real local variable (or Measures that can have e.g. set types and maybe even parametric measures (aka functions)) would be quite helpful sometimes
  • Using aggregate and generate I was able to iterate over existing sets, so by aggregating over Logged By.Users I was able to ExtractString all the units and hours from the string and compute the desired value. Tricky was that I can only iterate over known users so if a user is misspelled it will not be found at all. But I can count the number of found users and I can (with some pain) count the number of commas in the string and give a warning if they do not match (optionally attaching the warning to the actual cell output).
  • If you would add the ability to generate sets of numbers {0,…,n} (maybe with a function GenerateSet(0,n)) then much better error handling would be possible using the methods described above.
  • parsing on import seems to be limited to adding data to jira issues, I am looking up the data from the fix version description and sometimes do not even have an issues. So in order to access the data I parsed on import I would need to look up the set of issues in a fix version and then pick the first one and then I could extract the info parsed on import (and I would duplicate the info into all the issues instead of having it at the fix version where I need it). Or is there a way to attach the parsed data to the fix version?

Does Cache(expression) only work when the expression is literally the same (same string)?

Thx,
Alex