Group Data for 'Other' or NULL values

Larry Beasley shared this idea 2 years ago
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.

Comments (5)

photo
1

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

photo
1

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.

photo
1

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

photo
1

No worries, sorry for my delay as well. The reason I wrote "ELSE" is because the Group Data option creates a CASE statement that begins with CASE WHEN 1 = 0 and the "ELSE" defaults to the view field. The Group Data dialog box options are In List, Not In List but it's missing an ELSE for those that are not keen on writing a case statement. So, once the distinct value is "used" or shifted over from the left to the right the report writer cannot use it again. I'm asking that they can reuse it if they are choosing the Not In List.

Here's an example. A database currently consists of numbers 0-10 and I need to group them by Prime < 10, Composite Up to 10 and All Others.


Prime: (1,2,3,5,7)

Composite: (4,6,8,9,10)

All Others:(0)

If my database grows and I only care about prime and composite numbers 10 and under then I have to keep adding values to "All Others" in order to catch them all. If I could reuse 1-10 then I could do this:

All Others: Not In List(1,2,3,4,5,6,7,8,9,10)

A report writer with no SQL capabilities would be able to easily grasps this and apply it to their report. With that said, I'd like it too because it would keep me from hard-coding a CASE statement.

photo
1

Thanks for the elaboration, that does make sense. I have created a second idea ticket out of this:

https://community.yellowfinbi.com/agent/object/7638


Nathan

photo
photo
1

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.

photo
1

Hi Steve/Larry,

I have gone ahead and logged this as an official enhancement:

#10355

Nathan