How to change date from daywise to monthwise

Question

Post reply

How to change date from daywise to monthwise

SkyBVI

SSCoach

Points: 15128
More actions
February 11, 2011 at 9:15 am

#234343

Hi,
I made a cube in ssas. I am using it in the excel pivot table.
When i put the date column in the report filter and want to filter it by some month,...
the date comes for each day ...for ex....2010-01-01, 2010-01-02,....
so ther are many entries in hte filter column,.
wat i need is to select the date column by month for ex sep, oct etc...
so dat i can see the reports for the whole month not for each day separtely.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP

Viewing 11 posts - 1 through 10 (of 10 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Have you attempted to use the DATEPART function?

DATEPART ( datepart , date )

Where datepart is mm as

DATEPART ( mm , date )

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

SkyBVI SSCoach Points: 15128 More actions · Answer 2

@ bucket

Are you trying to tell me to change the underlying table in prod database or

from the excel file itself

or inside the cube

from where can i change tht??

Thanks.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 3

Any thoughts.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 4

Surely your Calendar Dimension has Year, Quarter, Month and Day Attribute Hierarchies?

If it has then simply make the Month Hierarchy visible and replace it as a filter on the Spread sheet.

If you dont have this then you really need to look at creating them in the underlying data base or as part of a Named Query in the DSV.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

SkyBVI SSCoach Points: 15128 More actions · Answer 5

I cant find how to change the hierarchy of that column to monthwise in hte cube.

Regards

Sushant

Regards
Sushant Kumar
MCTS,MCP

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 6

The month should already exist as a seperate column that has already been created on the underlying table. You then would take that information and turn it into a property.

BTW I have found it useful to have two columns to do this, one with month names unique, eg May 10, and one with just the name of the month, eg January.

If you are still not sure about this, I would recommend that you consult Ralph Kimball's work on creating a date dimension, as it is VERY important to get it right.

SkyBVI SSCoach Points: 15128 More actions · Answer 7

RonKyle (2/16/2011)
The month should already exist as a seperate column that has already been created on the underlying table. You then would take that information and turn it into a property.
BTW I have found it useful to have two columns to do this, one with month names unique, eg May 10, and one with just the name of the month, eg January.
If you are still not sure about this, I would recommend that you consult Ralph Kimball's work on creating a date dimension, as it is VERY important to get it right.

Where can i get ralph kimball work??

Regards

Sushant

Regards
Sushant Kumar
MCTS,MCP

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 8

You might be able to find information on line. If you are going to do dimensional modeling, however, I would recommend The Data Warehouse Toolkit. I have found this invaluable in my data warehouse work.

SkyBVI SSCoach Points: 15128 More actions · Answer 9

dude thanks

but thts a whole book to read 🙁

I wish i had tht muc time 🙂

Anyways, thxs

I appreciate ur help

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 10

Typing "SSAS Date Dimension" into the google search bar yielded a lot of results.