SRSS: Group-Sorting gets ignored

Question

if you have a look on this link SSRS 2008 - Sorting within a group it should be possible to sort at a Tablix and overwrite this behavior on the Grouping. So my structure is basically: Tablix --> Grouping --> Details Group

In the Grouping, I want to sort (and group ofc) at the PbiID ascending, but the Items without PBI-Number or Number = 0 should come last.

So before I sort to PBINr I added another sorting expression at the Group:

=
switch(
Fields!PBINr.Value is nothing, 2,
Fields!PBINr.Value = 0, 1,
1 = 1, 1)

I even tried some strange stuff like:

=
switch(
Fields!PBINr.Value is nothing, "zzzzzzzzz",
Fields!PBINr.Value = 0, "yyyyyyy",
1 = 1, "aaaaaa")

But nothing seems to work. So is the sorting itself wrong or can't I sort at the Grouping?

Thanks in advance.

Matthias

Couple of things... Is there any sorting at the Tablix or Details Group that is confusing things? Secondly, if you are checking for NULL values I would use IsNothing(Fields!PBINr.Value) instead of what you have above. — Ian Preston
Thanks for the hint with the IsNothing, sadly it didnt change anything. Since the order is still NULL,0, PBIIds, I guess my sorting gets ignored at all. — Matthias Müller

Mike Honey Mike Honey · Accepted Answer · 2014-04-11T05:20:00

As mentioned by Ian the correct syntax is: IsNothing(Fields!PBINr.Value)

But I also suspect you need to move that sort expression to the Details group. Otherwise it will only look at the value from the first record in the group.

SRSS: Group-Sorting gets ignored

1 Answers