Horizontal Axis Order

Question

Horizontal Axis Order

paul.farnell

SSC Eights!

Points: 810
More actions
January 22, 2015 at 3:56 pm

#302499

Hi
I have created a chart in Report Builder looking at a data cube where the horizontal axis is the month of the year (in a word, eg September). However the months are in alphabetical order not in month order, how can I fix this ?
Paul

Viewing 4 posts - 1 through 3 (of 3 total)

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

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

I don't even know how to spell "SSRS" or "MATRIX" in SSRS, but the following article is how I'd do it in T-SQL. Perhaps it will help your situation.

http://www.sqlservercentral.com/articles/T-SQL/71511/

--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)

pietlinden SSC Guru Points: 63510 More actions · Answer 2

I'm not reporting on a cube, but if you use a number for the month (or better a date column) in your report instead of a string, SSRS will see the date as a number, and then you can just format/group on whatever you want.

The only way I could get SSRS to see a date as a string, and achieve the alphabetical sorting you're seeing, was to force the evaluation of a month as a string. If you have absolutely no other option, you could do something like this:

=Switch(Fields!NameOfMonth.Value="January",1,Fields!NameOfMonth.Value="February",2,Fields!NameOfMonth.Value="March",3,Fields!NameOfMonth.Value="April",4)

But I would just include a date column from your cube. The performance of a Switch function in SSRS on a cube with an obscene number of members is going to be hideous.

Spiff SSCertifiable Points: 7826 More actions · Answer 3

Depends how the dimension attribute was created but try changing the sort order for the group to use Fields!Month.Key