We have a custom field called “Invoice Type” to indicate whether or not an issue is chargeable of nonchargeable and some other options. This is a radio-button field in jira.
Now it’s important for us to see when this field value was changed and preferrable from what, to what.
I’ve searched and tried several things, but cannot seem to get it right.
The field is currently being imported as:
[jira.customfield_10100]
name = "Invoice type"
check_calculated_value = true
data_type = "string"
measure = true
dimension = true
changes = true
javascript_code = '''
When I try to import it in the normal “Source” settings, it throws an error when trying to import as measure. Telling me that cannot put a String as measure… But like above, it seems to work, as I have “Issue Invoice Type” available as a measure now.
However, there is no history it seems. When I add time as a measurement column, it splits up my “Invoice Type” over every month, but it just shows the same value for every month, even months-to-come. So… That’s not working.
Optimally I would want one of the following:
-
Only show issues that had their value changed at all (after their initial value was set) and in that case, show the issue as a row, and the value it changed into (the invoice type measure) in columns of time (either day, or month or whatever).
-
If that is not possible, I would even settle for all issues (even the ones that had no change since start), but just the value of the Invoice type (measure) and what the respective value was at given time period column.
As an example I have a screenshot what I have now, except that it does not reflect “what the value was at that given time”, instead it just shows in every time column, the “value what it currently is”.
Help greatly appreciated!
Robin
Hi @sliekerr!
The Invoice type custom field should be imported as a dimension with value changes. This dimension, together with Issues history and Time dimension, would show how many issues had which value by the end of each period. The settings should be as follows:
[jira.customfield_10100]
separate_table = true
changes = true
When you change the settings in your environment, please unselect the field and run the import without it selected first. Then update the import settings and set it as dimension and value changes again. In this way, we can make sure all other configuration is removed from the custom field.
If you wish to create a report similar to the screenshot on the issue-level, you can calculate the Invoice type name where Issues history is greater than zero. Here is an example from my test environment
This is the formula I used for the Invoice type history measure:
NonEmptyString(Generate(
Filter(
Except(Descendants([Invoice type].CurrentMember, [Invoice type].[Invoice type]),
[Invoice type].[(none)]),
[Measures].[Issues history] > 0
), [Invoice type].CurrentMember.Name
))
Lauma / support@eazybi.com
Thanks for the detailed response!
I’m just a bit worried about unselecting the field, importing, and then reselecting it with new settings. Other people use this cube too, with reports using that field. Will it cause their reports to no longer function? Or will it be fine after I’ve configured it all? (in other words, will current functionality of that field not change for them?)
Kind regards,
Robin
Also, can confirm that in a small test cube at least, it’s working exactly as expected right now, which is perfect!
Robin,
The field should continue working when re-imported.
The only problem could be if it were selected as Measure somewhere, but the import should have failed there as string should not be imported as Measure. Let me know if you do experience any problems in any account, and we will fix those!
I’m happy to hear the solution is working!
Lauma / support@eazybi.com
So… I tried to apply it in our main cube… got the following surprise:
An error when opening the report(s):
Calculated member formula is not valid
Formulas are not valid for 44 calculated members [Measures].[% Non Chg Hrs],
… etc…
[Measures].[No Invoice Type Spent], [Measures].[Invoice Type History] . Please fix them or delete
those members.
Please help asap, because I tried to revert back to the old settings, and this does not fix it!
Ok scrap that. Couldn’t let it wait till tomorrow. I’ve updated and fixed each and every calculated field surrounding the invoice type… the problem was that somehow they all referred to the “old location” and gave an MDX error trying to get data. Considering the data was now in a “seperate table”, it does make sense. Did give me a bit of scare though. I’ll need to keep monitoring the upcoming days and also check other cubes if no errors are thrown. But for now it seems to be good…
1 Like
Update from this morning:
Got word in from colleagues that several of our reports are no longer working. Looked into it, and it seems that the defined Custom Fields withing the “Invoice type” field are now gone! Is there any way to recover this? We dont even know which ones were defined there initially, but there are none there, and several other calculated fields refer to for example [Invoice type].[ExcStandbyNonWork], and it’s no longer there. This is a major problem it seems. And it does not give errors, so I need to go through each individual report that is not working, see what fields it’s using, open those calculated fields, find out which other calculate they are referring to, and then try to deduct what definition is missing from there.
Hi @sliekerr!
I am sorry I did not think of a scenario that there might be calculated members defined in the Invoice type dimension itself. Is this the case? All Measures referencing to the Invoice types should still be there and working. Maybe clicking on it and re-evaluate the formula might be necessary (just click on edit and then update).
The only other way, except manual, to restore calculated members in the Invoice type dimension is if you have database backups. If you need assistance with restoring the calculations from the backup, please contact support@eazybi.com
I truly apologize for the trouble this is causing you!
Lauma / support@eazybi.com
The simple “Update” is what removed the errors last night, but it did not resolve the problem that many of our reports are now empty, as they are missing calculated fields…
I’ve been busy all day (and still on it), trying to recover the lost definitions. I dont think there is a specific backup we have, unless it’s on by-default…
And if that is the only way to recover it anyway, then I am in a bit of pickle here, to say the least… Strongly doubt that eazybi support can help salvage the situation at this point, the damage is done… exactly that which I was scared of to begin with. ><
While eazyBI has quite a lot of features we are not there to deliver fail-proof solutions when it comes to advanced options. For now, we decided to go for more features instead of limited fail-proof functionality. At some times it might come at some cost for rebuilding.
We are truly sorry for the problems you have for now.
Removing custom fields and importing them back is the way how to fix the data structures for the custom field and minimize problems in the future. However, with this eazyBI:
- mark calculations as invalid, if they are using this dimension. The fix is to update them after importing back the dimension as you already did it.
- delete the calculated members of the particular dimension. You would like to restore them manually. Define a new user-defined calculated member with the same name and logic. We do not have a simple option to restore them. Sorry for this. Typically there are a couple of calculated members if any at all.
You can export definitions and check specific member usage from this dimension if you need to identify the missing calculated members. You can share report definitions with us to support email (support@eazybi.com) and we can help to identify all members from this dimension addressed in reports.
For example, if you have a missing calculated member [Invoice type].[ExcStandbyNonWork] affecting several formulas, you can define the new calculated member in Invoice Type dimension with the name ExcStandbyNonWork. I assume it could be something like this:
Aggregate(
Except(
[Invoice type].[Invoice type].Members,
{[Invoice type].[Standby],
[Invoice type].[NonWork]}
)
)
And all measures where you addressed this calculated member should work as expected.
If there are still problems getting some calculated members working, please share the problematic formulas with us and we could lead you further.
Daina / support@eazybi.com
Hi Diana,
Thanks for your response. I understand there are some limitations with regards to how fail-proof something can be. And that’s ok. But if I knew up-front that the calculated fields would have to be re-created, then i would have stored the info somewhere, to place them back manually after the redesign.
The problem now is that there were god-knows how many calculated members, and they’re all gone. And recovering them is hard. I need to do calls now with each individual report-maker to try and find out which fields they had (but I found I can get the names from the report-definition export option), and most importantly, what was in it.
I already did find out that as soon as I place the calculated members back with exactly the same name they had, the report works without any further issues. So, at least that’s a plus.
Oh well, the damage is done. Learning experience for the next time, I suppose.
Thanks for the feedback anyway.
Kind regards,
Robin
Keeping track of changes, reverting to previous versions, branches, etc. is really a little bit cumbersome in eazyBI. Using a backup of the DB is the last life line to throw. However, in almost all cases this is a lot of effort. And of course nothing which is under control of individual report creators.
Just to drop an idea: we’re talking about MDX expressions and report configurations. It is source code. It is state of the art to utilize some source code repository. Would be great if these configurations could be maintained with GIT.
Best regards,
Johannes
@Johannes,
You are absolutely right and I hear you on this one! Being able to see changes and past versions of reports and measure formulas would definitely be a helpful improvement. This improvement idea is added to our backlog; we will keep you posted when anything new regarding this appears.
Lauma / support@eazybi.com
