Cognos RUNNING-COUNT() function - how do I specify the ORDER BY criteria?

[psrpsrpsr]

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

[New_Guy]

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

[Kiran Kandavalli]

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))

[psrpsrpsr]

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

[hespora]

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 >.<

[Revan]

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 Expand

[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 Expand

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!