My company defines our priority in a non-alphabetic/numeric order. Let’s say we use {H, M, L} for high, medium and low. We set priority on the Epic, and I have a Custom Field that maps that epic’s priority value, called “Epic Priority”. I have it in my cube as both a dimension and a property. I’m building a table of stories and I’d like to display the Epic Priority and sort them in the order H, M, L. As a measure (property), I can sort that field in ascending or descending, but I can’t do a custom sort order.
Within Jira, we have these priorities defined in the order H, M, L, so they always show up in that order in dropdowns. I don’t know if we have access to that data to help sort or not.
You can create another custom calculated member that concatenates the values in the order of priority that you desire, sort that column, and then deactivate it so that the sorting remains while the column no longer shows up in the table view.
The custom calculated member may create a value such as “High 001”, “High 002”, “Medium 001”, “Low 001” which would sort in that order. You can then inject whatever key/value pairs you desire and sort on the third column in a reliable manner. You can combine multiple fields together to create more complex sorting patterns as you wish.
Does that make sense?
– Malik Graves-Pryor
1 Like
OK, so I could a second field, not displayed, called Priority2 and it would map
Epic Priority Priority2
High 001 High
Medium 002 Medium
Low 003 Low
I can then sort on this to get my sort order, then remove this measure from the report, so we don’t see it. I think that would work. One question. Would you recommend created a scripted field in Jira for Priority2 and mapping it into eazyBI or doing a Javascript field in eazyBI directly, so we don’t crowd our Jira DB with extra, confusing fields?
The MDX solution sounds perfect, but I don’t “see” how I would implement it in eazyBI. But MDX always confuses me.
You can really do it either way in terms of creating a scripted field or just using what you have. That’s a preference more than anything else I think.
But yes, how you’ve described the mapping is exactly how you can build the string structure and then sort on it as a custom ordering.
That said, the link has some good examples for using the ORDER function. It’s pretty straightforward in practice, though as you’ve mentioned MDX can be a bit of a headache to visualize 
– Malik Graves-Pryor
You can use a function Aggregate and list members in a particular order as well.
We suggest bookmarking members and then pick them for the MDX formula, adding as a comma-separated list:
Aggregate({
[Priority].[H],
[Priority].[M],
[Priority].[L]
})
Then you can use this calculated member instead of a default All Priorities selection in the report.
Daina / support@eazybi.com
1 Like