Aggregation/Converting Multiple Entries to a Single Entry

[lolobak]

Hi,

I made a Cognos report with the following info:  food code, food, measure

Each food is associated to a food code and to multiple measures
for eg:
food code: 1      food : apple       measure: density, yield, cubic inch
food code: 2      food : salmon     measure: yield, cubic inch

I want to add a 4th column (and eventually hide the measure) entitled density missing.
So basically, the food that has no density associated to it would have an X and vice versa, so apple would have a blank entrie, salmon would have an X. I want to do this at the food level.

Also, the issue is that food code and food are grouped but obvioulsy the measure isn't (so I have multiple entries).

Any ideas on what to do? Do I have to create a query calculation, 2nd querie?

Any advice would be great.

Thanks :)

[bdbits]

What is the underlying reason you have multiple entries? I would guess there is another grain to your facts, e.g. a date or something. If so, you need to tell Cognos what to do with the multiple rows. You may want to apply an aggregate function like sum, average, etc.

As for your "density missing", it looks like that could simply be an expression. If it is specific to this report, it can go in the report. If you will be using it on multiple reports, you should get it in the model, perhaps even the underlying data source. How do you define missing? Is the density measure null if it is missing?