We have recently migrated from Cognos 10 to Cognos 11 and in two reports I have some aggregation/field type issues.
Both reports are very simple! The first report displays one dimension field and the others are all fact fields. One of the fields is a tekst field. However, this is displayed as a 1 or 0 (has or has no value) rather than the text that I do see when I query the DB directly.
And if I take a look at the SQL of query that is created I see:
count("T_FT_MARGINING"."COMMENTS") "Comments"
The same goes for the other report. Where I would like to see a simple list just displaying all separate values I see stuff like this in the query:
sum("T_FT_DEAL_FACTS"."ID_GT_DEAL") over () "Deal_ID" ,
Obviously I don't want to sum an ID.
Quote from: Plantje on 16 Mar 2017 05:03:04 PM
We have recently migrated from Cognos 10 to Cognos 11 and in two reports I have some aggregation/field type issues.
Both reports are very simple! The first report displays one dimension field and the others are all fact fields. One of the fields is a tekst field. However, this is displayed as a 1 or 0 (has or has no value) rather than the text that I do see when I query the DB directly.
And if I take a look at the SQL of query that is created I see:
count("T_FT_MARGINING"."COMMENTS") "Comments"
The same goes for the other report. Where I would like to see a simple list just displaying all separate values I see stuff like this in the query:
sum("T_FT_DEAL_FACTS"."ID_GT_DEAL") over () "Deal_ID" ,
Obviously I don't want to sum an ID.
Hi,
What is the Usage property of these two items set to be in Framework Manager? It sounds like they might both be set to a usage of Fact? If so, your reports will try to aggregate them...
MF.
Played around with those settings but didn't fix it. Problem was caused by using only fields from a measure "dimension". Then, no matter what settings, it automatically aggregates. Which, in BI sense, is logical since the level of granularity is defined by the dimensions.
Still some fields get a 0 or 1 rather than the text value. The solution should be in framework manager?
Ok, I have done some more checks.
The usage of the field is set to attribute. Should I set it to identifier? Yes, it is in a measure dimension, but isn't it possible at all to use text columns in a measure dimension? If that column is used just display all records and lookup involved dimensions as modelled?