Author Topic: Cognos RUNNING-COUNT() function - how do I specify the ORDER BY criteria?  (Read 14055 times)

Offline psrpsrpsr

  • Community Leader
  • *****
  • Join Date: Sep 2016
  • Posts: 113
  • Forum Citizenship: +1/-1
I understand the RUNNING-COUNT() function in Cognos to be (almost) equivalent to the following SQL code:

RANK()/DENSE_RANK()/ROW_NUMBER() OVER (PARTITION BY [field] ORDER BY [field])                    (I'm not sure which function is employed.)

Am I able to explicitly specify the criteria to order the data in the Cognos RUNNING-COUNT() function?

I have a data set that employs a left join and I need to create a RUNNING-COUNT() that applies a 1 to the earliest combination of date and time.

In SQL I can achieve this by: ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [date],[time]). How would I achieve this in Cognos?

ID        date             time     running-count
1234    20170501    0434   1
1234    20170501    0622   2
1234    20170501    1222   3
1234    20170505    1834   4

Offline New_Guy

  • Statesman
  • ******
  • Join Date: Mar 2016
  • Posts: 309
  • Forum Citizenship: +15/-0
Hi,
Create a data item [Date time] by concatinating date and time as timestamp. Sort the list column and create a data item like running-count([Date time] for [ID] ). and hide the column if you want to show date and time columns.
Good luck
New guy

Offline Kiran Kandavalli

  • Community Leader
  • *****
  • Join Date: Jun 2011
  • Posts: 112
  • Forum Citizenship: +1/-0
1) we can apply Sorting on List properties --> Grouping & Sorting. (ID--> Date --> Time) in Ascending order
2) Take a Data item and in the Expression (running-count(time))

« Last Edit: 17 May 2017 05:39:51 pm by Kiran Kandavalli »

Offline psrpsrpsr

  • Community Leader
  • *****
  • Join Date: Sep 2016
  • Posts: 113
  • Forum Citizenship: +1/-1
Thank you for your replies. Kiran, I have a question regarding the relationship between a query and the list report in which some or all of its data fields are executed and displayed.

So here's my understanding of the relationship. A query supplies the data for lists. Lists can have one or more fields, and are simply a container for the query. So how would applying sorting at the LIST LEVEL affect the data in the query?

The reason I ask is because I need the running-count function ONLY to create a field on which I filter all but rows with a value of 1 - I don't need this in the context of the List - just the query.

If you could clarify that relationship that would be helpful. Thank you

Offline hespora

  • Statesman
  • ******
  • Join Date: Nov 2015
  • Posts: 394
  • Forum Citizenship: +23/-0
Hi psr,


if you select a data item in your query, in its properties, there's a "pre-sort" value. I find that if you want to project that query and you need sorting in the data container, the best course of action would be to create a query reference instead and project that. Working with running-count and running-total gets really iffy otherwise >.<

Offline Revan

  • Full Member
  • ***
  • Join Date: May 2017
  • Posts: 28
  • Forum Citizenship: +0/-0
Hi,
Hespora's suggestion is excellent and should work. Another thing you could try if that doesn't is to use the rank function. As New Guy said, create a new data item field that concatenates together the date and time like this:
Code: [Select]
[datefieldname]||[timefieldname]. We'll call that date_time. Then create a second new data item field and use the rank function to rank our new awesome field like so:
Code: [Select]
rank([date_time] asc for [ID]). This ranks in ascending order (you could also rank descending order using DESC) the new field we created, date_time, for all of the repeated ID numbers. You could then filter on 1, which would be the first date_time, and voila!