Keep Duplicates and Auto Group & Summarize

[dennish]

Hi All -

Is it possible to keep duplicate records while still auto-aggregating? My report body uses select fields from the main query. Because the report body does not have the field that makes each row unique (and I do not want it to), is there a way to have the report keep duplicates while summarizing?

I searched online for a solution and this was the closest I got:
http://businessintelligence.ittoolbox.com/groups/technical-functional/cognos8-l/keep-duplicates-even-with-auto-group-and-summarize-on-in-report-studio-84-4680765

Could anyone help me?

Thanks

[blom0344]

Can you provide a small example of your data? Keeping duplicates would essentially mean turning auto-aggregation off.
If you were to use an unique identifier for the row , then you would not aggregate at all. Somehow I am sure that you do not want that either.

If you turn auto-aggregation off, then Cognos adds a distinct clause to remove duplicates (should be optional I guess)

[dennish]

Yes, you're correct, I would like to keep auto aggregation on. Oh, and this is a simple List Report, if that matters.

My query, for example, has the following fields
ID1
ID2
ID3
Fiscal Year
Period
Amount
Timestamp

I would like my report body to aggregate on all fields except the timestamp. Unfortunately, in some instances, the timestamp is the only thing that makes the row unique.

For example:

Row 1:
ID1: A
ID2: 2
ID3: Green
Fiscal Year: 2013
Period: 2
Amount: $100
Timestamp: 2/19/2013 9:00AM

Row 2:
ID1: A
ID2: 2
ID3: Green
Fiscal Year: 2013
Period: 2
Amount: $100
Timestamp: 2/12/2013 10:00PM

If I were to pull all fields except the Timestamp into the Report Body, Cognos automatically removes the value of $100 in Row 2.

Could you think of a way to allow auto-aggregation without the 'Distinct' clause?

Thanks, I appreciate your (and anyone elses) thoughts or opinions on this matter.

[blom0344]

Do you want to aggregate the amount or not?  Do you want 2 rows to be shown in the report?  It is beyond me what you are trying to attempt to do.

By the looks of it you have just 1 measure in the query. Aggregating on all fields excepts the timestamp does not make any sense to me. If you want 2 rows returned, then turn off auto-aggregation and set the aggr. for amount to none. Bring in the timestamp and if you do not want it in the layout of the list, then associate it with the list through properties of the list.

[dennish]

Sorry, I apologize for not being clear.

I would like the report body to aggregate the amount. Ideally, it would show the aggregated result of:

ID1: A
ID2: 2
ID3: Green
Fiscal Year: 2013
Period: 2
Amount: $200

However, without a timestamp field shown on the report body, Cognos removes the 'duplicate' row and returns an aggregated amount of $100. I would like to prevent that, if possible.

[dennish]

blom0344 - thanks for your help. i figured it out. i wasn't using the properties pane correctly, my mistake.