Hello,
I have a value prompt for an year which gets pulled from a dimension table. The underlying dim table has values from 2000 till 2020. In the fact, I have data only from 2005 till 2017. Without using filter (in prompt query), how do I ensure that my prompt will retrieve values based on the dim-fact join?
I cannot make prompt from fact table as there is no year in the presentation area of fact.
I also tried adding a fact column to the prompt query. But it is not performing the join when pulling the value for the prompt.
Cognos will generate the SQL/MDX based on the fields being used in the data container. In this case simply bringing the measure into the query pane isn't sufficient, as it's ignored during SQL generation.
Two things to do.
1. Create a detail filter in the prompt query. Something like: [Measure] is not null
When the SQL is created, it will include the detail filter in the where clause.
2. Add the measure to the properties of the prompt. This forces Cognos to include the measure when generating the SQL, and will ensure the JOIN is created.
Works well. Thank you for the help.