Can i sort a cross tab horizontally?

Question

Can i sort a cross tab horizontally?

jeffshelix

SSCrazy

Points: 2257
More actions
October 1, 2018 at 7:17 am

#364293

i have a report that shows top 20 sales each month for the past 12 months by product.
I have a cross tab where the months are in the columns and products are in the rows. THis works fine, but i want to sort, by month, with the current being the latest.
So for October report, i want October of 2017 to be the left-most column and September to be the right-most column. Can this be done?
thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

jeffshelix - Monday, October 1, 2018 7:17 AM
i have a report that shows top 20 sales each month for the past 12 months by product.
I have a cross tab where the months are in the columns and products are in the rows. THis works fine, but i want to sort, by month, with the current being the latest.
So for October report, i want October of 2017 to be the left-most column and September to be the right-most column. Can this be done?
thanks

Yes. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

More seriously, if you want some coded help, you need to provide a bit more information and it would really be nice if you could help us help you by providing some data in a readily consumable format. Please see the first link in my signature line below for how to do such a thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

I cannot provide anything that can be tested - but maybe something like this:

Declare @startDate datetime = dateadd(day, 1, eomonth(current_timestamp, -13));

With cteTally As ( Select N = row_number() over(Order By t.n) From (Values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) As t(n) ) , monthRange As ( Select MonthNumber = abs(t.N - 13) , StartMonthDate = dateadd(month, t.N - 1, @startDate) , EndMonthDate = cast(eomonth(@startDate, t.N - 1) As datetime) From cteTally t ) Select ... , Month01 = sum(Case When mr.MonthNumber = 01 Then 1 Else 0 End) , Month02 = sum(Case When mr.MonthNumber = 02 Then 1 Else 0 End) , Month03 = sum(Case When mr.MonthNumber = 03 Then 1 Else 0 End) , Month04 = sum(Case When mr.MonthNumber = 04 Then 1 Else 0 End) , Month05 = sum(Case When mr.MonthNumber = 05 Then 1 Else 0 End) , Month06 = sum(Case When mr.MonthNumber = 06 Then 1 Else 0 End) , Month07 = sum(Case When mr.MonthNumber = 07 Then 1 Else 0 End) , Month08 = sum(Case When mr.MonthNumber = 08 Then 1 Else 0 End) , Month09 = sum(Case When mr.MonthNumber = 09 Then 1 Else 0 End) , Month10 = sum(Case When mr.MonthNumber = 10 Then 1 Else 0 End) , Month11 = sum(Case When mr.MonthNumber = 11 Then 1 Else 0 End) , Month12 = sum(Case When mr.MonthNumber = 12 Then 1 Else 0 End) , Total = count(*) From {your tables} Inner Join monthRange mr On {your date field} >= mr.StartMonthDate And {your date field} < dateadd(day, 1, mr.EndMonthDate) Where ... Group By ...;

This gives a count for each month - you can change it to use whatever values you need.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

jeffshelix SSCrazy Points: 2257 More actions · Answer 4

jeffshelix

SSCrazy

Points: 2257

October 1, 2018 at 2:54 pm

#2007608

thanks! i will give that a try!

drew.allen SSC Guru Points: 76985 More actions · Answer 5

I think it's much simpler. You've posted in an SSRS forum, so presumably you are doing this in SSRS. I also presume that you are using a matrix. In your Column Groups, select the appropriate column group's down arrow and select "Group Properties...". From there, it should be fairly obvious what to do.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

drew.allen - Monday, October 1, 2018 3:28 PM
I think it's much simpler. You've posted in an SSRS forum, so presumably you are doing this in SSRS. I also presume that you are using a matrix. In your Column Groups, select the appropriate column group's down arrow and select "Group Properties...". From there, it should be fairly obvious what to do.
Drew

I didn't notice this was in an ssrs forum. It is much easier to do in a matrix.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs