Wrong Chronological ordering - MDX help
-
Hi all
I am having quite a frustrating problem. I have an MDX statement that looks like this:
SELECT { Measures.members } on Columns ,
NON EMPTY {{[Business].[User].[Business Entity Id].[1583].Children}
* {[Time].[Calendar].[2002].[Quarter 2].[May]:
[Time].[Calendar].[2004].[Quarter 3].[July]}} on Rows
FROM UsageStats_Phase1
It will produce a report (using the Matrix report in .NET) that will show me all the users within Business Entity ID 1583 listed down along the left-hand side of the report, then along the top of the report it shows all the months specified within the range in the MDX statement. The actual grid of the report shows the amount of visits a user had to a website grouped according to the months, i.e.
---------------------------------------------
.................|..2002...|...2003..|TOTAL|
.................|Nov|Dec|Jan|Feb| |
--------------|----|----|----|----|-------|
Bob Smith |_3_|_2_|_1_|_6_|.........|
Jon Smith |_4_|_8_|_3_|_3_|.........|
Hal Smith |_6_|_1_|_1_|_2_|.........|
--------------------------------------------
My Time dimension for the report above is Year, Quarter,Month, Day.
The problem is that, when running the report from .NET Reporting Services, the dates are not coming out in correct chronological order! All months are showing up in the correct years, but the months aren't ordered correctly within the years. So March may come before January, or December may show up before November.
I have tried updating the Matrix report so that is also includes a field for Quarter, and not just for Year and Month (as in the report above). This has made a slight improvement, but there is still the odd month showing up in the wrong order. This makes me think the problem will need to be solved in the actual MDX statement. Does anyone have any ideas or suggestions as to how to order this correctly?
Any little suggestion will be most appreciated!!!
Thanks in advance,
Maria
***************
-
Have you tried using an ORDER BY Year, Month in your SELECT? Don't know if this is possible with dimensions but thought I would throw it ....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
You'll want a 'sort' (which does the 'order by' in mdx). Are the members in the correct order when browsing the dimension using Analysis Manager? If not, you should sort them appropriately in the level setting for Month in the dimension. Could always be a bug within the RS matrix control??
Steve.
-
Thanks for both of the replies. I'm sure the problem can be solved with a correctly placed Order() clause. But since I'm an MDX newbie, "correctly" is not going to be easy!
>>You'll want a 'sort' (which does the 'order by' in mdx).
Do you mean to just use the Order() function? I haven't been able to find anything on 'sort' as of yet...
>>Are the members in the correct order when browsing the dimension using Analysis Manager?
Yes, they are all in order in Analysis Manager
>>Could always be a bug within the RS matrix control??
Aaarghhhh (nuff said)
I have never used Order and have been reading up on it today. Would you have any pointers on how to use this in a Time dimension which has year,quarter,month,day member properties?
Thanks again!
Maria
-
sorry (duh) yep, it's the order() fn. Wrt the fn, you'll obviously not want to break the hierarchy (ie don't use Bdesc or Basc) and if you have a numeric key or a sort column (put in as member prop) this could make it easier to do the sort.
if the members are all sorted OK in another querying tool, I would start to be concerned that it could be the matrix control in RS. Does it have any specific sorting properties that could be defaulted and working against you?
Steve.
-
hi Steve
thanks for the tips on ordering. i will give them a try.
Yes, I was looking into doing some sorting at the Matrix level in RS. you can actually sort by the fields you include in the matrix report by right-clicking the appropriate field on the matrix report (which is the Month member of the Time Dimension in my case), select Group Edit, and in the Sorting tab you can select one of the fields in the matrix report by which to sort. I chose the month field but unfortunately it sorts in in alphabetical order!!! that's because the month property is returned in my MDX statement in names as "january, february, march,..." instead of in keys such as 1,2,3 - which is how they are actually mapped in my sql table.
if i return the month key instead of the month name then I will not get user friendly month names in my end-user reports, such as january, february, etc. i will get 1,2,3... even though they may be in the correct order. in sql server this would be solved by a join. but i can't figure out how to include the month key property in my MDX statement - so that it can be used as a sorting property in the matrix report - without having it disrupt the other working parts of the report. do i include it on the rows, or columns?
...it seems this person was having the same problem as me:
>> I had a similar problem and found that it was because the order of creation
>> of columns was based on the presence of data for the respective column in
>> the first matrix data row i.e. if there was no data for January then
>> February may be the first column with January appearing at the end
>> (rightmost) position. I partialy worked around this by sorting the data in
>> the original query but then could not obtain the row sort I really needed,
>> alternatively I would have had to create some dummy data in the query to
>> guarantee the month columns were created in the correct order.
>>
>> Julian Bowker
>> Marble Steps Systems
hmm..creating dummy data. i guess that's my next nut to crack
thanks!
Maria
-
You could try adding the key field as member property to th month level, the change your query to use the hierarchisize (spelling?) function, which will let you effectly nest the same dimension on itself on the one axis (ie your rows part of the query in english would look like BUID.Kids * hierarchisize(time.months, time.monthmemprop) 
. Hopefully this would then return to RS as a seperate column, and then you can add this as yet another group to the matrix, sort it appropriately and then set it's visibility to not vis.seems a v.complicated way of making it sort yeah? i tried it y just adding the member_ordinal intrinsic mem property to the rows section of the query, but in RS this then blew away all of my *real* row member fields, so basically useless, i didn't hve time to try the hier... fn but it might work.
interestingly, i tried a similar query with a cube i have here, basically modified your code to match our members, and the months sorted perfectly fine within the parent? Admitedly, I think our time dim was not created using the time wizard, it was created a sper a normal dim and then the time attributes were added post creation. Level keys are all integers 9eg year = 2004, month = 200401, week = 200527 etc etc
Steve.
-
Steve, thanks a lot for all your suggestions. I will give the hierarchy thing a try.
I have actually created my time dimension manually, without using the wizard as well. my table has a name and key column for the year, quarter, and month. in the Dimension editor for [Time] i am ordering the month by key, instead of name.
That's interesting that it works in your environment. Does the first user appearing in the Matrix row have data (or hits) for every single month along the Matrix colums?
Cheers, Maria
-
it's really reeking of a bug now isn't it? yeah, my first parent category(ie under which months are nested) does have a full complement of months even though subsequent ones are missing some in the sequence. Looks like the control could be doing the group/sort as it comes across the children, so if the first member has > full complement, then ones that come in later are added to the end?
Steve.
-
yes, it really does look like a bug. if I leave the NON EMPTY out of the query so as not to supress the empty rows, it works perfectly. so i do have a working version, it's just not perfect. I think I'm going to leave it to marinade for a few days and return to it then. I may even rebuild the cube. Maybe all my reprocessing of the cube and reloading of the database has had a detri-'mental' effect!
Thanks again!
Maria
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply