Issue pulling back relevant data only

[Revelator80]

Hi,

I have a top 5 customers report by contract that is filtered on year and category. There are some contracts for which there has been no spend at all this year but that had spend last year and vice versa. When I run the report, all contracts come back regardless of whether there has been spend in the year I selected. I want contracts only to come back where is data for them based on my parameter selections, how can I do this? I would have thought that that would have been the default position but it does no appear so.

Cheers
David

 

[blom0344]

This is probably not a Cognos issue but related to the way your model is designed. My first recommendation would be to take this up with whoever designed the model. It may well be that cardinalities between object are set in such a way that you will always have dimensions returned even if associated facts are missing..

Ties Blom

 

[Revelator80]

Well, this is embarrassing! I built the framework the report is based on - what did I miss!?

 

[blom0344]

How did you set cardinalities between facts and dimensions? If the fact contains nullable foreign keys, then normally I would expect 1:1 <--> 0:n

This ensures all fact data is returned even if not dimensional data can be fetched due to the null FK's.

If you define 0:1 <--> 0:n then you have a full outer join that will return all facts AND all dimensional values (regardless if associated with existing facts)

Ties Blom