Fields from two different dimensions

[fml55555]

Hi Gurus,

I have two dimensions connected to a Fact. I am trying to write a case statement in Dimension1 which looks like this

If(Dimension1.field1='xxx')
Then
(Dimension2.field1)
Else
(Dimension1.field1)

I am trying to do this in 'calculations' of dimension 1 by creating a 'new query item'. However, when I test it with the fact (Click on both fact and this dimension and click 'test') I get only zeros in 'new query item'. I have also tried creating a Model query item with required fields from both the dimensions. All of these give me the same error (zeros in the 'new query item)

[Francis aka khayman]

sounds like bad database design. you might need to reorganize your data.

however, using only the info stated, and your current database structure, you can create a third table or view (dimension) and implement the dim1-dim2-fact relationship in the database. import the dimensions you need in your FM and the rest is standard Cognos FM design.