Dates get alphabetized when report is shown

I've built a report from a cube that I have had made. After selecting a few dimensions, the columns will be showing a drill down action related to different dates. Problem is, when you preview the report, the dates get alphabetized; they don't show up in an order like dates, days should.

ex: monday, friday, thursday, tuesday, wednesday

or april, august, july, june, may

How can this be changed, or is it related to the dimensions in the way they were made? Possibly something from the tables then? If more information is needed, please specify.

Im running Sql 2005 Developer Edition, with BIDS.

Sounds like your dates are set up with a string data type so you'll want to change that in the database/cube or you could try a cdate() in reporting services to convert it to a date (e.g. cdate(Fields!Month.value, "MMM"). But if you're going to use the cube alot (and the dates) it would be better to alter it in the back end.

Regards,

Ali

|||Do the dates come back in that order in the query designer? If so, you need to go back to the cube, and change your dimensions to order properly. You can do that by using the OrderBy property on the dimension attribute. If you are using a numeric key that has the proper order (1 for Quarter 1, 2 for Quarter 2, etc.), you can order by the key. If not, you can add a related attribute that contians the proper sort order, or you may need to redesign the dimension somewhat.|||

I guess you are using a matrix. Assuming that, set the sorting of the corresponding groups in the matrix to sort by the date field itself instead of the weekday name or month name.

Shyam

|||

Shyam Sundar wrote:

I guess you are using a matrix. Assuming that, set the sorting of the corresponding groups in the matrix to sort by the date field itself instead of the weekday name or month name.

Shyam

yes I am using a matrix, but Im not sure how to do this.

I am new to sql 2005, and all of these suggestions thus far sound good. I will be on the phone with these specifics to my associate who made the cube. I'll need to have those dimensions tweaked some more. Thanks!

I'll report back as to what the issue turned out to be.

|||

Right click on the appropriate row group cell and click on Edit Group. Go to Sorting tab and select the date field under Expression and select Ascending under Direction.

Shyam