How to add new row dynamically in SSRS?

Add row number to outer group in t-sql or ssrs

• I have a query that returns data with group category and some details like this: Category | Title ================== cat1 --- titlex cat1 --- titley cat2 --- titley cat3 --- titlez cat3 --- titlex cat4 --- titlex I want to display a table that has row number on outer group (Category) like this: RN | Category | Title ====================== 1 cat1 titlex titley 2 cat2 titley 3 cat3 titlez titlex 4 cat4 titlex The problem is, when I add RN column as ROW_NUMBER in sql query or ROWNUMBER SSRS function (tried NOTHING, Group and Details as a scope, just in case), I always get numbers like 2 1 2 or 1 3 4 6 for RN column. EDIT Sql Query (table names and properties changed for simplicity) SELECT -- this rownumber does not work, counts every occurrence of category --ROW_NUMBER() OVER ( --PARTITION BY c.Name -- tried this too, this resets on each cat --ORDER BY c.Name) AS RN, c.Name, p.Name FROM Products p INNER JOIN Categories c ON p.CategoryId = c.Id GROUP BY c.Name, p.Name ORDER BY c.Name, p.Name

• Answer:

  You don't want the row numbers (as you've observed, the row numbers are assigned to every... um... row). Maybe you want http://msdn.microsoft.com/en-us/library/ms173825.aspx? SELECT DENSE_RANK() OVER (ORDER BY c.Name) AS RN, c.Name, p.Name FROM Products p INNER JOIN Categories c ON p.CategoryId = c.Id GROUP BY c.Name, p.Name ORDER BY c.Name, p.Name As to your desired output, I wouldn't attempt to achieve that in SQL - use a reporting/formatting tool to get the final layout.