Issue with Sorting and Grouping

[Mandeep]

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

[MFGF]

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.

[Lynn]

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.

[Mandeep]

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

[MFGF]

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.

[Mandeep]

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

[MFGF]

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.

[Mandeep]

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

[Lynn]

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.

Code Select Expand


running-count( 1 for [Date], [Product line] )


[Mandeep]

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

[Lynn]

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.