Author Topic: Total into one row if a field is duplicated  (Read 887 times)

Offline slipperyfin1

  • Associate
  • **
  • Join Date: Oct 2019
  • Posts: 1
  • Forum Citizenship: +0/-0
Total into one row if a field is duplicated
« on: 28 Oct 2019 11:46:18 am »
I've had a hard time figuring out how to word this, so it's best if I visualize it.

Here is what I have:

NAME        # of Apples         # Hours Worked
Mike               8                            1
John               3                            1
John               4                            1
Steve              5                           1
Mary               7                           1

Here is what I want:

NAME        # of Apples         # Hours Worked
Mike               8                            1
John               7                            2
Steve              5                           1
Mary               7                           1

John has two separate entries, but I only want one entry to appear and I want it to be the total of the two.

This is for a list report. Any help is greatly appreciated.
« Last Edit: 28 Oct 2019 11:50:29 am by slipperyfin1 »

Offline adam_mc

  • Community Leader
  • *****
  • Join Date: Mar 2012
  • Posts: 246
  • Forum Citizenship: +12/-0
Re: Total into one row if a field is duplicated
« Reply #1 on: 28 Oct 2019 12:04:28 pm »
Assuming that both # of Apples and # Hours Worked are defined as measure and as numerical, you should just need to set the Aggregation property of both data items to be Totals.
Then, Cognos will automatically roll-up to the lowest level of detail you are showing.

Hope this helps.
Thanks,
Adam.

Online dougp

  • Statesman
  • ******
  • Join Date: Jul 2014
  • Posts: 445
  • Forum Citizenship: +21/-1
Re: Total into one row if a field is duplicated
« Reply #2 on: 28 Oct 2019 12:13:42 pm »
Is this in a package or an uploaded spreadsheet?

If this is a spreadsheet, "Update file" and make those columns measures.

If this is in a package, talk to your admin about making those columns measures and applying a default aggregation method in the model.

Offline Francis aka khayman

  • Statesman
  • ******
  • Join Date: Jun 2009
  • Posts: 500
  • Forum Citizenship: +25/-4
    • CognosM
Re: Total into one row if a field is duplicated
« Reply #3 on: 28 Oct 2019 12:27:35 pm »
most probably you are using a dimensional model. there are two entries with caption = 'John' with different ids. that is the reason why you see two Johns.

check this entry in my blog it may help:

http://cognosm.blogspot.com/2015/12/stripping-dimensionality-of-memeber.html


 


       
Twittear