Hi Gurus,
I am facing an issue with Sorting and grouping in Cognos
I have three fields
1. Item
2. Prod Order
3. Prod Date
Data should be sorted on Prod Date and grouped on Item (Cognos List in Report Studio )
There is one problem - Cognos groups first and then sorts
Example : if data is like below
Item Prod Order Prod Date
A 001 4 - Apr
B 002 5 - Apr
A 003 6 - Apr
A 004 7 - Apr
Cognos report with grouping on item and sorting on Prod Date will produce following output
Item Prod Order Prod Date
A 001 4 - Apr
003 6 - Apr
004 7 - Apr
B 002 5 - Apr
Prod Order 001, 003 and 004 are grouped under Item A, which is not what i am expecting
Where as expected output is like below
Item Prod Order Prod Date
A 001 4 - Apr
B 002 5 - Apr
A 003 6 - Apr
004 7 - Apr
Regards,
Mandeep
Quote from: ManuN on 04 Apr 2016 03:33:50 AM
Hi Gurus,
I am facing an issue with Sorting and grouping in Cognos
I have three fields
1. Item
2. Prod Order
3. Prod Date
Data should be sorted on Prod Date and grouped on Item (Cognos List in Report Studio )
There is one problem - Cognos groups first and then sorts
Example : if data is like below
Item Prod Order Prod Date
A 001 4 - Apr
B 002 5 - Apr
A 003 6 - Apr
A 004 7 - Apr
Cognos report with grouping on item and sorting on Prod Date will produce following output
Item Prod Order Prod Date
A 001 4 - Apr
003 6 - Apr
004 7 - Apr
B 002 5 - Apr
Prod Order 001, 003 and 004 are grouped under Item A, which is not what i am expecting
Where as expected output is like below
Item Prod Order Prod Date
A 001 4 - Apr
B 002 5 - Apr
A 003 6 - Apr
004 7 - Apr
Regards,
Mandeep
Hi Mandeep,
As you correctly pointed out, grouping takes precedence over sorting within your report. This means you're going to need to group on Prod Date first, then on Item.
Cheers!
MF.
To add to the muppet's response, you can set the group span property on the date to "none" so it will repeat to match your desired layout.
Thanks MF !
If i group on date, last two rows of my example will not be grouped since they are not on same date !
A 001 4 - Apr
B 002 5 - Apr
A 003 6 - Apr
004 7 - Apr
any other way out ?
Regards,
Mandeep
Quote from: ManuN on 04 Apr 2016 07:05:25 AM
Thanks MF !
If i group on date, last two rows of my example will not be grouped since they are not on same date !
A 001 4 - Apr
B 002 5 - Apr
A 003 6 - Apr
004 7 - Apr
any other way out ?
Regards,
Mandeep
Hi,
How can the last two rows be grouped - they are on different dates?
MF.
Hi,
Yes i agree that grouping can't be done on date.
But as per my requirement, i want the rows to be grouped which come under same item. Last two rows in the example are for same item 'A', so i want to display them as group .
Regards,
Mandeep
Quote from: ManuN on 04 Apr 2016 07:52:01 AM
Hi,
Yes i agree that grouping can't be done on date.
But as per my requirement, i want the rows to be grouped which come under same item. Last two rows in the example are for same item 'A', so i want to display them as group .
Regards,
Mandeep
Hi,
I'm still not understanding. There are three rows for the same item 'A', not two. Surely all three would need to be in a group for item A? What is the logic behind just the last two being grouped and not the first?
MF.
Hi MF,
Here is the idea,
I want the list to be sorted on date first.
Then grouping should happen based on Item.
ie., in sorted list if there are any repeated instances of Item, they should be grouped
I will put it into 3 steps
1. Assume source data to be like
Item Order Date
A 001 04-Apr
B 004 07-Apr
C 005 10-Apr
A 003 05-Apr
A 002 09-Apr
B 009 08-Apr
2. Step 1 in report studio (sorting on date)
Item Order Date
A 001 04-Apr
A 003 05-Apr
B 004 07-Apr
B 009 08-Apr
A 002 09-Apr
C 005 10-Apr
3. Step 2 in report studio (grouping on Item)
Item Order Date
A 001 04-Apr
003 05-Apr
B 004 07-Apr
009 08-Apr
A 002 09-Apr
C 005 10-Apr
Regards,
Mandeep
I don't think you can do what you want using grouping, but you could conditionally suppress repeated items based on a running-count calculation.
Attached is a spec from the great outdoors sample. Create a running count calculation based on Date and Item. Select the Item column and set an advanced conditional style on the Item column using the expression that identifies anything with a running-count greater than 1. For these rows set the visible property in the style to "No". I imagine you could further play around with the borders in the conditional style to make it look more like a group effect.
running-count( 1 for [Date], [Product line] )
Thanks Lynn !
That helps if i am going to display as a list.
sorry for dragging the conversation, but what if I want the column 'Item' in section header as in the screenshot.
-Mandeep
Quote from: ManuN on 05 Apr 2016 01:20:52 AM
Thanks Lynn !
That helps if i am going to display as a list.
sorry for dragging the conversation, but what if I want the column 'Item' in section header as in the screenshot.
-Mandeep
Well that is a bit different than what you asked for at the outset.
You could try to use the same concept and play around with conditionally rendering header rows. I suspect the next thing will be how to get sub-totals for your non-grouped item that is simulating a group and you're already on a slippery slope as it is with the approach I've suggested. Perhaps re-think the idea and/or re-evaluate the requirement. Maybe others will chime in with alternatives.