null values

[en1]

Hi!
It is possible show null values in crosstab without case is null ... in expression?

[Lynn]

Hi!
It is possible show null values in crosstab without case is null ... in expression?

I don't understand the question. You can set the data format and specify missing values characters. Is that what you're after?

[en1]

when no value for intersection, i need show 0. in this case i use formula like this
case when total (...) is null then 0 else total (...) end
but it reduce performance .

[clamus17]

You can try setting the missing value characters to 0 in the data format property just like Lynn stated.

[en1]

members for this values invisible

[MFGF]

members for this values invisible

I can't make any sense of this post. Can you explain what you mean?

MF.

[en1]

when a column has no data, it is being suppressed

[colt]

Coalesce might help you. coalesce(measure1,0) : if measure1 is NULL then it gets value 0.

[Lynn]

members for this values invisible

when a column has no data, it is being suppressed

Wow, poster of very few words.

Is your source relational by chance? If so you'd have gotten a better answer initially if you'd pointed that out.

[en1]

Thx for your replies! package is dimensional.
i have try coleasce(,) and it surprisingly not work   :o
case is null work ok? but i assume it may reduce performance...

[Lynn]

Thx for your replies! package is dimensional.
i have try coleasce(,) and it surprisingly not work   :o
case is null work ok? but i assume it may reduce performance...

Now I'm really confused. If the members aren't appearing because you have suppression turned on but you want them to be shown then just turn off the suppression and set the missing value character using the data format property.

Or am I still missing what the issue is?

[en1]

supression is off but members with null value for them are supressed.
it is main problem of this topic. second is that coleasce(,) not work.
some expert tell me that its standart cognos problems and i should use case is null..

[Lynn]

supression is off but members with null value for them are supressed.
it is main problem of this topic. second is that coleasce(,) not work.
some expert tell me that its standart cognos problems and i should use case is null..

Ok, then your source must be DMR dimensional?

[en1]

yes

[Lynn]

The behavior you see is due to absence of data in your relational database. A true OLAP source would always show all members in the set regardless of whether there are metrics for it or not.

Imagine you have a query that returns NO ROWS but you want to perform a substring on a particular column....it makes no sense because you can't perform that operation when there isn't anything in the result set to act upon. In your case you are getting data only where an intersection exists in the source and getting nothing for intersections where there are no rows to be found. Thus, you can't do coalesce. There is simply no row in your source for the intersection in question. You'd have to actually have a row returned in the result set upon which you could then specify a coalesce.

I'm not sure I agree with the advice your expert offered. I'd suggest you explore a two-query approach. First query retrieves all the members you want to show regardless of whether there are metrics for them or not. The second query would be what you already have. Next, outer-join the two queries to create a third, making sure you populate members based on the first query and then all other elements based on the second query.

Finally, point your crosstab at the third query and set the missing values property as desired. You could do the coalesce in the third query as an alternative to the data format property change.

[en1]

Thanks!!