How to use group functionality in SSRS alongwith ORDER BY on the same field as Group?

• naisu1383

  Old Hand

  Points: 380

  More actions

  August 8, 2012 at 10:24 pm

  #279351

  In SSRS, i have to show total and sub total for one table field which is varchar. However i managed to get total and subtotal. For subtotal, i am using group while designing report through report wizard. Now i am using order by clause on the same field and this field is displaying month and year in varchar format. So to order it i have converted it into date and did ordering. If executed query alone it shows ordering in a proper way but provided group in report for subtotal doesn't give order by result in a proper way.

  Query i used is as follow:

  SELECT PM.PERSON_ID, DM.MEMBER_ID, PM.FNAME + ' ' + PM.MNAME + ' ' + PM.LNAME AS NAME, DMT.M_MONTH,

  CONVERT(varchar(10), DMT.CREATED_DATE, 105) AS CREATED_DATE FROM PERSON_MASTER AS PM

  INNER JOIN

  DAD_MEMBERSHIP AS DM ON PM.PERSON_ID = DM.PERSON_ID

  INNER JOIN

  DAD_MAIL_TRANSACTION AS DMT ON DM.MEMBER_ID = DMT.MEMBER_ID

  AND (DM.MEMBER_ID LIKE @magtype + '%' OR DM.MEMBER_ID LIKE 'X' + @magtype + '%' )

  AND (DMT.CREATED_DATE BETWEEN @From AND @To)

  ORDER BY CONVERT( datetime , '01-' + DMT.M_MONTH ), DMT.CREATED_DATE, DM.MEMBER_ID

  This query works fine but in report along with group for month no proper ordering. Any solution than how to get sub total?