APAC SD Team
Agents
NA Business Team
Category
Report Builder
Product Version
7.3
Group Data for 'Other' or NULL values
Idea Logged
I personally do not like using the 'group data' feature only because of one limitation that I am experiencing. My issue here is not being able to have a catch-all or unhandled group. For example, the original report creator wants the report to display an 'Other' group if the data is not already assigned a group. In our case it's locality. We have all the localities in our area grouped but every time a new locality is added we have to manually add them to the 'Other' group. Nulls are also unhandled. Again, I do not use this feature because of those limitations but I have other report writers that are not SQL savvy and would like to use this feature.
I like this idea 
Hi Larry,
This sounds like an idea more than a question, so I am going to convert it over.
As a side thought, you could probably achieve what you are seeking by creating a calc field to convert all nulls to the literal string "Other", and then apply grouping after.
Is there a reason this will not work?
Regards,
Nathan
Hi Larry,
This sounds like an idea more than a question, so I am going to convert it over.
As a side thought, you could probably achieve what you are seeking by creating a calc field to convert all nulls to the literal string "Other", and then apply grouping after.
Is there a reason this will not work?
Regards,
Nathan
Yes, the calc field works. I personally try to find something in the data that I can coalesce to using a case statement or use your suggestion "Other" or "N/A", etc. For report writers that know about the grouping but aren't necessarily going to know how to handle NULL then they may not be able to without extra effort. That being said, a calc field is the best way to handle NULLs because you shouldn't report blanks even though it happens. I would LOVE to see an ELSE thrown in there or the ability to reuse a value if the NOT operator is selected. That may be the quickest solution? We have a report that reports based on locality. Multiple towns can reside in a county and we report on about 12 towns in 5 counties. Everything else we want to label as other.
Yes, the calc field works. I personally try to find something in the data that I can coalesce to using a case statement or use your suggestion "Other" or "N/A", etc. For report writers that know about the grouping but aren't necessarily going to know how to handle NULL then they may not be able to without extra effort. That being said, a calc field is the best way to handle NULLs because you shouldn't report blanks even though it happens. I would LOVE to see an ELSE thrown in there or the ability to reuse a value if the NOT operator is selected. That may be the quickest solution? We have a report that reports based on locality. Multiple towns can reside in a county and we report on about 12 towns in 5 counties. Everything else we want to label as other.
Hey Larry,
Sorry for the delay here, I agree that there should be a more user friendly alternative to handling nulls here, so hopefully this idea enhancement will address that.
Can you elaborate a bit on what you mean by the "ELSE" or "reuse value" aspects? If you want, we can create these as separate ideas?
Nathan
Hey Larry,
Sorry for the delay here, I agree that there should be a more user friendly alternative to handling nulls here, so hopefully this idea enhancement will address that.
Can you elaborate a bit on what you mean by the "ELSE" or "reuse value" aspects? If you want, we can create these as separate ideas?
Nathan
Having an option to handle null / blank values in the Group Data field would be a good enhancement.
The only workaround for this at the moment is to use the not-so-user-friendly-for-complex-calculations formula editor, or a free-hand SQL statement that creates a field that is treated as a third-class citizen in the report builder.
Given that Grouped Data field generates a relatively simple CASE statement in report SQL, I do not see this being a complex to implement. All that is required is to add a couple of UI features in the Grouped Data configuration which gives the user options on how null/blank values should be grouped (e.g. label null/blank values with "Blank". "Unknown", etc.), and then some code that converts this option into an additional in in the case statement for the field.
Having an option to handle null / blank values in the Group Data field would be a good enhancement.
The only workaround for this at the moment is to use the not-so-user-friendly-for-complex-calculations formula editor, or a free-hand SQL statement that creates a field that is treated as a third-class citizen in the report builder.
Given that Grouped Data field generates a relatively simple CASE statement in report SQL, I do not see this being a complex to implement. All that is required is to add a couple of UI features in the Grouped Data configuration which gives the user options on how null/blank values should be grouped (e.g. label null/blank values with "Blank". "Unknown", etc.), and then some code that converts this option into an additional in in the case statement for the field.
Hi Steve/Larry,
I have gone ahead and logged this as an official enhancement:
#10355
Nathan
Hi Steve/Larry,
I have gone ahead and logged this as an official enhancement:
#10355
Nathan
Replies have been locked on this page!