July 6, 2004 at 10:55 pm
Hi All,
Using: Analysis Services 2000 - I have a cube with a dimension named "Call Date". This dimension has the following levels: Year, Quarter, Month, Day. The key value for the dimension members is of the type integer in the format of "yyyymmdd", for example today would have a key value of 20040706.
I am trying to write a MDX statement that will allow me to filter dynamically by Call Date. So far what I think I want is something like the following:
SELECT NON EMPTY
{FILTER([Call Date].members,[Call Date].[20031102]:[Call Date].[20031104])} ON COLUMNS,
CROSSJOIN({[Region Customers].[Region].Members},{[User IDs].Members}) ON ROWS
FROM [Billed Call Detail]
In actual use, I need to pass in variables for the member values in the Call Date dimension. However, I wanted to get the query returning proper values to start with. When I run the query above I get "Token not valid {FILTER([Call Date].members,[Call Date].[20031102]:[Call Date].[20031104]^)^} " error. Can anyone point me to where I am going wrong here?
Thank you,
Michael
mhweiss
July 7, 2004 at 2:25 pm
Michael,
My first idea would be to surround
[Call Date].[20031102]:[Call Date]:[20031104]
with curly brackets {} (what's the proper name for these things? Curly parentheses ). In other words
{[Call Date].[20031102]:[Call Date]:[20031104]}
Give that a go and reply here letting me know whether it works or not.
Regards
Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
July 7, 2004 at 10:25 pm
Thank you, Jamie. I still get the same error. I also tried various combinations of french braces and parenthesis but received the same result. Any ideas?
Thank you,
Michael
mhweiss
July 8, 2004 at 1:50 am
Ah, think I might have got it...
The 2nd argument to the filter function has to be a statement that evaluates to TRUE or FALSE (usually with use of the CurrentMember function) - thats why it isn't working.
I'm not quite sure what you're trying to do with the FILTER function but what I think you want is EXCEPT.
Try the following instead:
SELECT NON EMPTY
{EXCEPT({[Call Date].members}, {[Call Date].[20031102] : [Call Date].[20031104]})} ON COLUMNS,
CROSSJOIN({[Region Customers].[Region].Members},{[User IDs].Members}) ON ROWS
FROM [Billed Call Detail]
Look at the MDX reference with the MDX Sample Application to fully understand the nuances of EXCEPT.
If this is not what you are trying to do then can you explain what your aim is.
cheers
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
July 8, 2004 at 4:40 am
Maybe it is something simpler that is needed:
SELECT NON EMPTY
{[Call Date].[20031102]:[Call Date].[20031104]} ON COLUMNS,
CROSSJOIN({[Region Customers].[Region].Members},{[User IDs].Members}) ON ROWS
FROM [Billed Call Detail]
Is this what You are trying to accomplish ?:
SELECT NON EMPTY
{ DynamicSetOfDates } ON COLUMNS,
CROSSJOIN({[Region Customers].[Region].Members},{[User IDs].Members}) ON ROWS
FROM [Billed Call Detail]
/rockmoose
You must unlearn what You have learnt
July 14, 2004 at 2:50 pm
Thank you guys...I will try both solutions and let you know the results. What I am doing is feeding an MDX statement into an MS OWC Pivot Control on a classic ASP page. I believe if I were just diplaying the results of the MDX query I could use the slicer clause to filter the date range. However, the OWC Pivot Control will not support an MDX statement that contains a slicer clause. Therefore to get the date range members, I need to run the MDX statement, open a cellset, loop through it and load the date members contained in it into an array which I can then feed into the filter function of the OWC Pivot Control. Unless of course, anyone can show me a more efficient approach. The approach I described seems to be Microsoft's recommended approach. I am open to any ideas or suggestions though.
Thanks again, I really appreciate the help. I will post my results after I run the code you two suggest...
Michael
mhweiss
July 17, 2004 at 12:14 pm
Both solutions worked after I inserted a "&" before the call date member since I am referring to the actual key value itself. "{[Call Date].[20031102] : [Call Date].[20031104]}" becomes "{[Call Date].&[20031102] : [Call Date].&[20031104]}". Jamie, when I used your solution in the MDX Query App in Analysis Services, I get an error stating that the cellset cannot be displayed because not enough memory can be allocated for the flex grid. However, my dev machine has 1gb of RAM and my the underlying data store for the test cube only has approx 460,000 records. Is this just a bug in the MDX Query app do you think? Or something not right in the MDX statement? I can't believe that with 1gb of RAM the cellset could not be displayed with 3000 members in the row fieldset's field and four members on the column's fieldset field. Your thoughts would be appreciated.
Thanks for all of your help, guys!
Michael
mhweiss
July 17, 2004 at 5:22 pm
Actually the expression:
EXCEPT({[Call Date].members}, {[Call Date].[20031102] : [Call Date].[20031104]})
Will return more than 4(?) members, it will return all the members execept for {[Call Date].[20031102],[Call Date].[20031103],[Call Date].[20031104]} ( thats all but 3 ! )
So maybe that is the reason.
Pretty poor anyway, the flexgrid should be able to display 3000 * x000, say 10 mil.
1000 000 000 butes / 10 000 000 = 100 bytes per cell, should be enough 😉
/rockmoose
You must unlearn what You have learnt
July 18, 2004 at 12:21 pm
Sorry...I should have clarified that I was not using the EXCEPT function so I was only dealing with four members on the column axis. Which makes it even poorer performance! lol
Thanks,
Michael
PS - Do any of you guys deal with the OWC Pivot Control using MDX?
mhweiss
July 18, 2004 at 1:00 pm
Sorry, can't help You with the OWC Pivot Control.
At my shop the users only use ProClarity as front end.
We "just" build the cubes...
Yeah, maybe the MDX Query App wasn't built with performance in mind ?!
Hey, You guys... can you put something together so that we can test MDX on the AS... ?
🙂
/rockmoose
You must unlearn what You have learnt
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply