SSRS: Sorting by Month Where the 1 Gets in the Way

Hello:

In SQL Reporting Services (SSRS), I have a report that displays Month 1, Month 2, Month 3, etc. through Month 18.

I want to sort these months.  But, unfortunately, such a sort order begins with Month 1, Month 10, Month 11, Month 12, .... Month 18, Month 2, Month 3, etc.

The specific month is generated based on the code at the end of this posting.  How can I sort this code for SSRS so that the "normal" order of Months appears?

Thanks!

TBSupport

CAST(DATEDIFF(m,GETDATE(),RCG.dbo.POContractHeader.EndDate) + 1 AS VARCHAR(2))
LVL 1
TBSupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>such a sort order begins with Month 1, Month 10, Month 11, Month 12, .... Month 18, Month 2, Month 3, etc.

That would be text sorting, where 10 comes before 2.   In order to sort these numerically, you'll need another column(s) along for the ride, which stores the 1, 2, 3, ... 18 month / year(s) as a number format, and sort on that column(s).

If the EndDate in your quesiton is always the end date of the month we're talking about, then you can sort by that.
0
coreconceptsCommented:
To piggyback off Jim,

            you could do something like the following with a disclaimer that I assume this is a report that isn't very high volume, there's a lot of unnecessary conversion in here that I wouldn't recommend on production environments - but if you're in a bind this should work with what you want - I assume you're concatenating 'month' into the mix somewhere:

CASE WHEN DATEDIFF(m,GETDATE(),RCG.dbo.POContractHeader.EndDate) + 1 < 10
THEN CAST(CAST('0' AS VARCHAR) + CAST(DATEDIFF(m,GETDATE(),RCG.dbo.POContractHeader.EndDate) + 1 AS VARCHAR(1)) AS VARCHAR(2))
ELSE CAST(DATEDIFF(m,GETDATE(),(SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader)) + 1 AS VARCHAR(2)) 
END As 'ColumnName'

Open in new window

0
coreconceptsCommented:
TBsupport - sorry to add to the clarification: whenever the resulting month number is less than 10 (a single digit #) my solution will put a 0 in front of it.  So if in your SSRS report you're concatenating month + 'yourcolumnname' you would get:


MONTH 01
MONTH 02
MONTH 03
etc.,

If it's 10 or greater it will just use your normal expression and sorting will be fine
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

TBSupportAuthor Commented:
There is no "OrderDate" or "SalesOrderHeader" in my programming that I know of.  So, SSRS is kicking back that CASE statement with errors.

TBSupport
0
coreconceptsCommented:
Hey TB,

      Sorry that was the table I was testing:

Use
SELECT

CASE WHEN DATEDIFF(m,GETDATE(),RCG.dbo.POContractHeader.EndDate) + 1 < 10
THEN CAST(CAST('0' AS VARCHAR) + CAST(DATEDIFF(m,GETDATE(),RCG.dbo.POContractHeader.EndDate) + 1 AS VARCHAR(1)) AS VARCHAR(2))
ELSE CAST(DATEDIFF(m,GETDATE(), RCG.dbo.POContractHeader.EndDate) + 1 AS VARCHAR(2)) 
END As 'MonthNumber'

FROM [YourTableName]

Open in new window


Put the name of the table your selecting between the brackets in the FROM clause
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ValentinoVBI ConsultantCommented:
Instead of casting to varchar just let it be an integer:

DATEDIFF(m,GETDATE(),RCG.dbo.POContractHeader.EndDate) + 1

Open in new window

You can then use it for sorting and for display in your textbox without any issues.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.