Data Format for Columns within Hierarchy

pask_HN

Could someone please help me change the data format for a column within a hierarchy?  Since the data format is for the entire hierarchy, I can't figure out how to change the format for a single level.  In our date hierarchy, year is being displayed as a percentage, and we would obviously like to display it as a number.

Thank you!

Gianni Ceresa

You can customize each level of the hierarchy.

Expand the hierarchy (in the Criteria tab) and set the format for the levels you want.

pask_HN

Year is pulling through as plain text, when it is defined as a smallint.  Do you have any insight in how I would get it to pull through as a number?  Year pulls through as a number outside of the hierarchy.

Also, thanks for the quick response!

Gianni Ceresa

You aren't looking at it from the right point of view ....

Your time hierarchy contains multiple data types, so what is the most basic and common format to represent multiple data types? Text ...

OBIEE work assigning a single type to each column, so the hierarchy being represented as a single column they need to use text to make it possible.

pask_HN

Okay, fair enough. I did misunderstand what you were saying.

But I still need to be able to get year to not be pulled through as a percentage.  Do you know how I would go about that?

Gianni Ceresa

The "Year" of your time hierarchy is returned as a percentage? How did you get there? (do you maybe have a screenshot? Because I'm not sure to be able to imagine where the % is ...)

pask_HN

Screenshot as requested:

It is set as the key for the year level and has data type of smallint:

Gianni Ceresa

Interesting

I must admit it's the first time I see a year as a %.

Assuming you didn't do that yourself on the hierarchy (or you wouldn't be asking), do you maybe have a system wide default setting "Year" or any SMALLINT column as % ?

pask_HN

Yes, unfortunately, sometime ago, smallint was selected as percent for system wide default.  We don't want to change the system wide default at this time, as to not interfere with reports that have already been created.  I tried to set the system default for the column year to number, but it doesn't translate into year in the hierarchy.

Any other ideas to get it to work?

Gianni Ceresa

Open the RPD and change the "year" physical column to INT instead of SMALLINT, so you get rid of the % default format. As inside the hierarchy you can't force it to something else that would solve your issue in theory (hoping you don't have a weird default format for INT).

Plan B: duplicate the column in the RPD (the "Year" column), and cast it to a different type (varchar or int) and use that one in the hierarchy as "display column" for the year level. It's a bit less clean but end users will probably not pay attention to it ...

That's the issue with "system wide defaults" : you pay the price if you have even a single exception.

pask_HN

Thank you.  I will give this a try!