Thank you for your response.
When I created an extract to test your suggestion, the grouped items were ungrouped right away on Tableau Desktop as well This is probably why I saw the same thing on Online since it creates an extract when I publish. Like you said it may be a bug - I will add a support case.
I created a few more groups and tried a few scenarios with both Live and Extract and it was working fine for all of them.
It's just this specific group from the screenshot that's getting ungrouped. Below I have the steps as I can remember it from the last time I tried this scenario, in case it helps anyone else trying the same thing (although I couldn't reproduce this again with the below steps for a different dimension):
On a live connection to SQL server,
- I split the dim into multiple columns
- combined the column values through a calculated field (concatenated values)
- created a grouped field from the calculated field
- created a hierarchy and added the grouped field to it
- hid the split fields as well as the calculated field
- added the grouped field to rows in a new worksheet
- published the workbook to Tableau Online and saw that the grouped field was ungrouped (creating an extract on Tableau Desktop and adding the grouped field to rows also did the same thing)
Again, I couldn't reproduce this scenario on a different field. It's either fixed or I didn't try the exact same scenario as last time (I did a lot of random things on this field, I may have missed something in the above steps)
Thanks again Diego for helping with this.
Tableau support got back with an explanation of why I had the issue:
When I split the dimension into multiple fields, some values in the field were empty strings. And I was trying to concatenate string values and empty string values together.
And MS SQL Server has some rules about concatenation when it comes to NULL fields and empty strings. While using the "+" operator for string concatenation in SQL Server, it is not able to handle null values hence the result of concatenating a string value and a null string is always null.
A workaround for this is to alter the concatenation calculated field to account for nulls before sending the query to SQL Server.
For example: Instead of concatenating the fields as: [Split 1] + " " + [Split 2] + " " + [Split 3]...
Using the function LEN() to check for empty strings worked:
[Split 1] +
IF LEN([Split 2]) = 0
ELSE " " + [Split 2]
For each additional split, add the IF THEN statement to parse the nulls.
However, the original issue of items getting ungrouped when switching from Live to Extract did not completely get solved.
The above workaround in calculation worked when I did it on an already extracted version.