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)
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.