dimension level doen't show row's name beacuse of null value

[stam]

Hi,
I have a ranges dimension which is based on this table:
-------------
c_range lower_range upper_range
1 0 100
2 101 200
3 201 <null>
-------------

I want the dimension to show the ranges, i.e.,
0-100, 101-200 and so on. So i wrote in dimension editor-
member name col:
convert(char,&quot;dbo&quot;.&quot;TABLE_NAME&quot;.&quot;N_LOWER_RANGE&quot
+'-'+ convert(char,&quot;dbo&quot;.&quot;TABLE_NAME&quot;.&quot;N_UPPER_RANGE&quot

But in the last row it shows nothing because I've added
&quot;dbo&quot;.&quot;TABLE_NAME&quot;.&quot;N_UPPER_RANGE&quot; which is null.
what do I need to change?
thanks.

 

[LutzMorrien]

Have you tried something like this?

iif(&quot;dbo&quot;.&quot;TABLE_NAME&quot;.&quot;N_UPPER_RANGE&quot; is NULL,&quot;Infinite&quot;,convert(char,&quot;dbo&quot;.&quot;TABLE_NAME&quot;.&quot;N_UPPER_RANGE&quot)

(Infinite is just a word i put in since 0 does not make any sense here)

Lutz

 

[netDev007]

(1) surely just enter the largest value in the NULL upper_range row.

(2) Given your ranges are all class intervals of 100, isn't it better to have a view on the source table that defines the Range

nRange = 100*int(value/100)
cRange = 1+convert(char(4),100*int(value/100)+'-'+
100+convert(char(4),100*int(value/100)

15 0 1 - 100
101 100 101 - 200

...Rather than having a table, and all that join overhead

Mind you the ascii sort of the cRange always niggles me.