SQL Server Performance Forum – Threads Archive

Urgent:can’t Order cube after NON EMPTY is used

Hello I’m having quite a frustrating problem. My MDX statement
returns data via a Matrix report (reporting services) that
shows me monthly hits to a website on a user-by-user
basis. It is displayed in Alphabetical order based on the
users name. Sometimes hundreds of users can be returned (a 12 page
report). It’s the case that many of these users haven’t
made any hits to the site so they just have empty rows
against them. I have used NON EMPTY to get rid of these
empty rows like so: SELECT { Measures.members } on Columns ,
NON EMPTY { Crossjoin( [Time].[Calendar].[2004].[May]:
[Time].[Calendar].[2004].[July], [Business].[User].
[Business Entity Id].[1583].Children ) } on Rows
FROM UsageStats_Phase1 This suppresses the empty rows, but messes up the
ordering. People who have hits in May are ordered
alphabetically, and people who don’t are left out. then
people who have hits in June are ordered alphabetically
and everyone else is left out, etc. so the entire report isn’t listed alphabetically. Only on
a month by month basis, so unless you know that, it looks
like people are missing from the report. Until you scroll
down to the correct month for which they have hits. The other problem is that the months (columns) aren’t
showing up in order either. So instead of May, June,
July… it’s now something like June, May, July, etc… Does anyone know how I can reorder my results after
suppressing the empty rows? any help would be very much
appreciated!! Thanks in advance
Maria

Hi Maria, While you have users nested within the months, i don’t think you’ll be able to get the ordering the way you want it. I’m not sure how you would envisage this working, because if only users with a name starting with ‘B’ were active in May and then the ‘A’ users were active in June, how can the ‘A’ users get ordered before the ‘B’ users, as this would then break them out of the month in which they were active. An alternative is to nest the months within the users, so the query becomes SELECT { Measures.members } on Columns ,
NON EMPTY {{[Business].[User].
[Business Entity Id].[1583].Children} * {[Time].[Calendar].[2004].[May]:
[Time].[Calendar].[2004].[July]}} on Rows
FROM UsageStats_Phase1 Another alternative is to not have the month on an axis at all (obviously this depends on the reports’ user requirements), but you could create a tuple from the months and then use this as a filter. This would make the query something like WITH MEMBER [Time].[MyFilter] AS ‘[Time].[Calendar].[2004].[May] + Time].[Calendar].[2004].[June] +
[Time].[Calendar].[2004].[July]’ SELECT { Measures.members } on Columns ,
NON EMPTY {[Business].[User].[Business Entity Id].[1583].Children} on Rows
FROM UsageStats_Phase1
WHERE
[Time[.[MyFilter] The ordering of your months (of every level within every dimension really) is determined by the ‘Order By’ column in the properties page for the level (when editing cubes in Analysis Manager). You could use the ORDER function in your query, but this will probably require you to specify a measure to order the results by. Hope this helps. Steve HTH, Steve

Hi Steve, Thanks so much for your reply and help. You solved my problem in one fell swoop!!! [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] The first suggestion you came up with worked! i.e. SELECT { Measures.members } on Columns , NON EMPTY {{[Business].[User]. [Business Entity Id].[1583].Children} * {[Time].[Calendar].[2004].[May]: [Time].[Calendar].[2004].[July]}} on Rows FROM UsageStats_Phase1 I Just copied and pasted from your mail and voila!! Thanks again! Maria (saved bacon) Bermudes [8D]

]]>