Browse cube filtering

Question

I am new to SSAS and MDX concepts so pls help me with understanding the MDX builder window that comes up when chosing the MDX option from the filtering row of Browse tab of the cube. Thats my first question, how can I write MDX from this window..

I have datetime as one of my dimensions and I want to be able to filter the data based on datetime specified.

Another way that I tried to do is was from the operate and filter tab

When I chose Range inclusive as an operator  and go to the filter tab, it shows me some dates and then at the bottom it says to form a filtering, when I click on that.. a new window opens (filtering memember, children ALL )and in the first dropdown list I see property drop down list.

I think I am way too off here, I want to be able to give a range of dates to show me the data for in the pane.

Help pls.

Thanks

-Sarah 

Answer 1

Can you be more specific. You are saying you are not able to see the date you want in the start and end date ranges?

 

 

Answer 2

Thanks for your help. I want to be able to see data in the browse tab within some date ranges.

I guess my question is how can I give the date range, here is what I picked  for the columns

Dimension= My date attribute

Hierachy = Date Hierachy

Operator = Range

Filter expression = ??????????

Not sure how to set the filter expression..

 

Answer 3

Dimension= My date attribute

Hierachy = My date attribute.Date

Operator = Range

Filter expression = StartDate:EndDate

Try this!!!

Answer 4

StartDate:EndDate, are these some variables? Also I cant type in that filter expression area, have to select something..

Answer 5

When you select Date in the Hierarchy , you automatically get the available date in the filter expression. 

For ex: if you select '=' in operator you can select one date , but you requirement is to select range so when you select the there will be two drop down menus for start and end dates..

 

Hope this solves your problem.

Answer 6

Yups there are two drop down menus, one for start and another for end. But I cant type in them and when I look at the drop down list, it shows me some date options and in the end there is a link that says "Filter Member"

Upon hitting that it opens a window called "Filter expression"

I have no idea what to select under Property, Operator and Value fields there.

what is caption, Name, Unique Name under property..

 

InShort, I still dont know how to put the range of dates..

Thanks for your help on this.

-Sarah

 

Answer 7

 

Just enter the as shown in the screenshot below.until operator fieldUnder filter expression. click on drop down list> at the last when u scroll down to the bottom u see filter members under the march 25,2004 (you cant see it here...)but when u scroll down little u shud be able to see).

.

when click on filter membersu get the following screen: so enter as shown I dont know your date so i just selected contains in the operator here but if you know the date format you can enter directly with = sign selected there.After you select the date click OK. and now this selected date shud show in the drop down list. So, Please dont forget to select there in the drop down list also. (So u need to hit OK twice totaly)otherwise it wont filter.  And do the same thing for the end date:).

And you are good to go. Please mark as answer if found useful!!!!!

 

 

Answer 8

Firstly, thanks Spartaa for giving such a detail snapshot, @Sarah, you can refer to this link to learn more about this scenario http://www.packtpub.com/article/creating-matrix-report-using-analysis-services-cube

In addition, you can refer to this link to learn more about the difference between these operators

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 9

Thanks much Sparta for your help. The strange thing is, the dates that I am asking it to pick are not getting shown in the window, but those dates are in the record set that ges pulled over from the view. Look at the bottom, it says only first 1000 members are shown.Image 1..

 

Answer 10

 If you look at image 2, I tried putting in the dates that I want and it doesnt like that like, Look at the error that it gace me at the end. And I go haave data for teh year 2010. I am not sure if I should use Caption, UniqueName or Name under property.. But these images can tell ya that it isnt liking my dates..

 

And so when I hit ok, I dont see anyting in the filter boxes.

 

 

 

Answer 11

See this is what I get when I put in the dates that I need

 

 

 

 

 

Answer 12

Anyone on this..???

Answer 13

You can select Caption, Equal, then to the value, please select the value you want to filter, please keep the same format like 2008-08-10 07:00:00.000

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 14

Thanks much, that helped. Problem solved.. Thanks much Challen Fu..