SSRS matrix issue

• danawexler

  Right there with Babe

  Points: 736

  More actions

  February 11, 2013 at 2:51 pm

  #268485

  i've been wracking my brain trying to find a solution...it's probably right before my eyes.

  i have a list of applicants that may or may not have 2 different states of practice...i set up a matrix in SSRS to capture this data in the appropriate format. my problem is that the StateofPractice fields cascades down and to the right at a diagonal like the following brief example:

  ID SoP SoP SoP SoP SoP SoP

  --- ----- ----- ----- ----- ----- -----

  222 CA

  223 IL

  224 VA

  225 DC

  226 CA

  226 CO

  the matrix should look like:

  ID SoP SoP

  --- ----- -----

  222 CA

  223 IL

  224 VA

  225 DC

  226 CA CO

  The Column Group properties are grouped on SoP first and ID second. Can anyone help me find my problem in SSRS please? i'm sure i could adjust my code to accomplish this, but I'd really like to see if SSRS can handle this request.

  Thanks!

  Dana

  "Drats! Foiled again!"

• higgim

  SSCarpal Tunnel

  Points: 4090

  More actions

  February 12, 2013 at 3:19 am

  #1586901

  You could change you code like the example below

  WITH testdata

  AS ( SELECT 222 AS ID ,

  'CA' AS SOP

  UNION ALL

  SELECT 223 AS ID ,

  'IL' AS SOP

  UNION ALL

  SELECT 224 AS ID ,

  'VA' AS SOP

  UNION ALL

  SELECT 225 AS ID ,

  'DC' AS SOP

  UNION ALL

  SELECT 226 AS ID ,

  'CA' AS SOP

  UNION ALL

  SELECT 226 AS ID ,

  'CO' AS SOP

  )

  SELECT ID ,

  SOP,row_number() OVER(PARTITION BY id ORDER BY id) AS ElementCount

  FROM testdata

  From that you would set the column group to group on ElementCount which then should solve your problem