Can i sort a cross tab horizontally?

  • jeffshelix

    SSCrazy

    Points: 2242

    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

  • Jeff Moden

    SSC Guru

    Points: 995109

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Jeff Moden

    SSC Guru

    Points: 995109

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88170

    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.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • jeffshelix

    SSCrazy

    Points: 2242

    thanks!  i will give that a try!

  • drew.allen

    SSC Guru

    Points: 76658

    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 3188

    SSC Guru

    Points: 88170

    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.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

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

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