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

I don't know much about the mapping of MDX queries to matrix reports,
but it seems to me that the CrossJoin could be causing some of your
problems, because the resulting set will have user rows for each month.
Could the query be:

[Time].[Calendar].[2004].[July]} on Columns,
NON EMPTY {[Business].[User].
[Business Entity Id].[1583].Children )} on Rows
FROM UsageStats_Phase1
Where ([Measures].[Hits])

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

"Jano" <Janobermudes (AT) microsoft (DOT) com> wrote


Hi Maria,
You can use ORDER function to arrange the resultset returned in a
order u want

Order(«Set», {«String Expression» | «Numeric Expression»}

u can specify ASC | DESC | BASC | BDESC] to get the resultset in
Ascending or Desc or Asc/Desc with breaking the hierarchy.

BTW, instead of using Non Empty keyword before CrossJoin, use
NonEmptyCrossJoin function which is much faster.

Vinay Dharmik