SSRS Column Grouping with specific order

Question

Hi Experts,

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

=>I have a 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.

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!

Answer 1

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