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
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
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))
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
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 >.<
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: [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: 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!