Multiple group expressions in list (ssrs 2005)

Question

Hi,

I have a problem with group expressions in a list. I want to use two expressions: '=Ceiling(RowNumber(Nothing)/3)' and '=Cint(Fields!kpilevel.Value)'

They work both individually, but when I insert them together only 1 works. I inserted them like this: img718.imageshack.us/img718/736/problemxq.png

Does anyone know how to solve this?

Thanks in advance, Kris

---

EDIT: Better Explanation

Mark you are right, I didn't explain it very well.

I want to place the data in a matrix, with a maximum of three columns per row, and I want to sort my data with the expression: =Cint(Fields!kpilevel.Value). Therefore I want to use both the expressions: Ceiling(RowNumber(Nothing)/3) and Cint(Fields!kpilevel.Value).

But I cant get the right output. I tried to place my grouping and sorting expressions, in multiple ways. With different results:

Last situation: http://img257.imageshack.us/img257/5765/10569159.png

Different output:

---

List - Properties

Sorting: =Cint(Fields!kpilevel.Value) ascending

List - Grouping and Sorting Properties

Group on:

=Ceiling(RowNumber(Nothing)/3)

=Cint(Fields!kpilevel.Value)

Sorting:

=Cint(Fields!kpilevel.Value) ascending

Matrix columngroup - Grouping and Sorting Properties

Group on:

=RowNumber("list1_Details_Group")

Output: (ungrouped but sorted)

1

2

3

4

etc

---

List - Properties

-

List - Grouping and Sorting Properties

Group on:

=Ceiling(RowNumber(Nothing)/3)

=Cint(Fields!kpilevel.Value)

Sorting:

=Cint(Fields!kpilevel.Value) ascending

Matrix columngroup - Grouping and Sorting Properties

Group on:

=RowNumber("list1_Details_Group")

Output: (ungrouped & unsorted)

1

2

9

10

etc

---

List - Properties

Sorting:

=Cint(Fields!kpilevel.Value) ascending

List - Grouping and Sorting Properties

Group on:

=Ceiling(RowNumber(Nothing)/3)

Matrix columngroup - Grouping and Sorting Properties

Group on:

=RowNumber("list1_Details_Group")

Output: (grouped but unsorted)

1 2 9

4 7 3

10 etc

---

List - Properties

-

List - Grouping and Sorting Properties

Group on:

=Ceiling(RowNumber(Nothing)/3)

Matrix columngroup - Grouping and Sorting Properties

Group on:

=RowNumber("list1_Details_Group")

Sorting:

=Cint(Fields!kpilevel.Value)

Output: (grouped but strangly sorted)

1 2 9

10 11 13

3 4 7

12

---

I hope this makes it clearer, Kris

Answer 1

Following this, I have noticed some rather strange effects when grouping by functions of data items in matrixes in SSRS, rather than by data items themselves.

For this reason, in this context I would recommend:

• using a table rather than a matrix
• sorting the table (or the dataset) by Cint(Fields!kpilevel.Value)
• grouping the table by Ceiling(RowNumber(Nothing)/3)
• include group footer rows only (no table header/footer, details or group header)
• have 3 columns in the report
• have conditional output in each cell, dependant on whether RowNumber(Nothing) mod 3 equals 0, 1 or 2

Alternatively, if it has to be done in a matrix, then (assuming you're using a SQL data source) I would recommend

• grouping the query by int(kpilevel)
• ordering the query output by int(kpilevel)
• including display_row and display_column values in the query, defined as the ceiling of the rownumber/3 and the modulus of the rownumber with 3, respectively
• defining the row and column groups of your matrix as the display_row and display_column values from the query, respectively.