Column Grouping with specific order

Question

Is it possible to change order of records/groups in a result-set from a query using GROUP BY?

I have this query:

SELECT Category,
    Subcategory,
    ProductName,
    CreatedDate,
    Sales
FROM TableCategory tc INNER JOIN
    TableSubCategory ts ON tc.col1 = ts.col2 INNER JOIN
    TableProductName tp ON ts.col2 = tp.col3   
GROUP BY Category,
    SubCategory,
    ProductName,
    CreatedDate,
    Sales

Now, I am creating a SSRS report where Category is Primary row group, then SubCategory is its child row group. Then ProductName is a Primary Column Group.

[Broken link to screenshot: http://www.(freeimagehosting).net/uploads/8035123725.jpg]

It works perfect, but it shows the ProductNames in alphabatic order. I want it to show the ProductNames in custom order (defined by me) like:

ProductNo5 in 3rd column,  
ProductNo8 in 4th column,  
ProductNo1 in 5th column   
... and so on!

Gerbrand Gerbrand · Accepted Answer · 2010-05-27T10:20:05

I don't know the product ssrs, but I do know general SQL. In SQL you'd have to add an extra column. You could add a column myOrder of type Numeric/number to the ProductNames table and fill the myOrder column so it corresponds to whatever ordering you want. After that you could do something like that:

SELECT Category, 
    Subcategory, 
    ProductName, 
    CreatedDate, 
    Sales 
FROM TableCategory tc 
INNER JOIN TableSubCategory ts 
ON tc.col1 = ts.col2 
INNER JOIN TableProductName tp 
ON ts.col2 = tp.col3
GROUP BY Category, 
    SubCategory, 
    ProductName, 
    CreatedDate, 
    Sales 
ORDER BY TableProductName.myOrder

Column Grouping with specific order

1 Answers