SSRS, Limit Fixed number of Columns in Matrix within a Tablix - issue in sorting matrix data

0
votes

I am converting a crystal report to ssrs report, i am trying to implement "Format With Multiple Columns" feature of crystal report to ssrs.

I have a Main tablix that has two Two Groups

  • 1st Group - PolicyType
  • 2nd Group - WeekNumber

Under Week Number group, i have inserted a rectangle and a matrix within it. The matrix will show the policy number generated in every week. The matrix should only contain 6 columns and the grouping for matrix is as below

  • Row Grouping : Ceiling(RowNumber("WeekNumber") / 6)
  • Column Grouping : Ceiling(RowNumber("WeekNumber") MOD 6)

I need a output like below

InsuranceType1
Week 1
Policy Number
1001 1002 1003 1004 1005 1006
1007 1008 1009 1010
Week 2
Policy Number
2001 2002 2003 2004 2005 2006
2007 2008 2009 2010


I am able to get a similar output but the sorting within the matrix is not working, ie., i get the following result
InsuranceType1
Week 1
Policy Number
1002 1003 1001 1004 1007 1006
1005 1008 1009 1010
Week 2
Policy Number
2008 2009 2003 2004 2005 2006
2007 2001 2002 2010


i have not used any sorting on the Matrix, nor in the group of matrix the only sorting is on the main tablix groups PolicyType and WeekNumber and both are in ascending order.

What i have tried is:

  1. Sort Matrix using PolicyNumber, but that will only output first row and first column (very strange!!!).
  2. Add an additional sort of PolicyNumber in Group WeekNumber (did not work either)


i have no option of modifying the stored procedure to return the row number back to the report as the client is strict on SP modification as they want to get this report backward compatible.

Please help!!

1
matrixreporting-servicescrystal-reports
Have you tried just setting the column sorting to the same expression as the grouping expression you showed in your question? – Alan Schofield
I cannot add the same expression in column sort of the matrix as the group expression uses RowNumber Function. – Vinayak

1 Answers

0
votes

I have managed to resolve this issue, thought i'll be helpful for others. The order needs to be on the main tablix and not on the inner group or Matrix.