Hello Experts,
We’re seeing some strange behaviour with a custom formula in a table. We’re using a DBRW to return a string attribute from one of the row dimensions (from the }ElementAttribute cube), however, it’s returning only 1 in 10, if that. No errors in the formula, and the elements definitely have the attribute populated.
What’s even more strange is that is we point the formula to a different dimension and different attribute then everything is returned as expected, it’s just with one specific dimension that it’s not correctly returning values.
In the screenshot below, the “Position” formula is referencing the elements in column B, and all of these elements have the attribute populated but you can see it’s only returning the attribute for one. The “Company Code” column is referencing column C and is returning all attributes correctly:
Has anyone else experienced this kind of behaviour before? Am I missing something obvious?
Thanks,
David
Just an update on this issue, using the same DBRW formula in Excel works as expected, it’s just in UX that it isn’t working correctly. Has anyone come across this issue before?
Thanks,
David
The clue is probably in the element naming of the dimension (Employee column B) where the DBRW isn’t working. Understandably you blanked this out for good reason, but without knowing how the elements are named it’s difficult to do any diagnosis or even guess.
Do the elements in this dimension contain any special characters? Could the element names be interpreted as numbers? Do the element names have leading zeros? any special characters like “+”? Is column B a stacked dimension on rows or a measure from the columns? (If it’s a stacked row dimension which I would guess it is then the DBRW will see the principal name not the displayed value).
1 Like
@cw-ch-scott thanks for the reply. There aren’t any special characters in the element names, they are all numbers, some with leading zeros. Still, even with the ones with leading zeros some will return the attributes and some won’t. And while for the most part it’s the same elements returning attributes and the same ones not, for some attributes it’s a different set of elements returning attributes.
To make things even more confusing, I’ve just found one instance where UX is returning the wrong attribute value, I don’t even know how that’s possible! In the first screenshot below you can see the DBRW formula in UX clearing referring to the attribute “Last known Cost Centre” but if you compare to the second screen shot you can see that elements 00025773, 00026206, and 00026605 are returning a different attribute altogether, “Cost Centre”
These attribute values are all static values, nothing is rule-derived. How is this even possible?
Thanks,
David
Just looking at your Employee elements, it may be because of the left zeroes on the Element Name, I think the DBRW for some reason is identifying the ID as numeric and removing the leading zeroes.
I’ve have seen this behavior in the past.
You can check this by just doing the formula “=A1”, and you will notice that it will display as number.
Not sure if there is a way to always recognize the element as it is for those cases…
Check if the employee code already exists as another element ID without the leading zeroes (maybe you have the same Employee code 2 times in the dimension, one with leading zeroes, one without) that could be the cause of bringing different Attribute values maybe?
An alternative solution for this is to create an alias of the Employee ID without the leading zeroes, and the DBRW formula should work then!
@rmazziero that’s it! So we do have elements with and without leading zeros (we should be able to delete one set), so UX converting the element name into a value is the problem. I tried using the TEXT formula to add back in the leading zeros but couldn’t get that to work in UX unfortunately. Regardless, we’ll work on removing the duplicated elements.
Thanks so much for pinpointing the issue!
Cheers,
David
1 Like
Nice, Glad to hear it helped!
@cw-ch-scott do you think there’s something we can do on these cases on the Grid, to keep the leading zeroes, treating Element principal names from dimensions always as Text? or is it a default behavior on the handsontable? Thanks!
