August 11, 2004 at 5:08 am
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
August 11, 2004 at 12:08 pm
Hi Maria,
To sort in a specific way, you can use the ORDER function. It will return a sorted set based on a Subquery. The syntax is something like: Order(<<set>>, OrderByExpression, ASC | DESC | BASC | BDESC). You can view the specific details in Books On Line.
Where possible, I prefer to use the NONEMPTYCROSSJOIN instead of a NON EMPTY { CROSSJOIN(...) }. If I remember properly, the CROSSJOIN will return all values first and then the NON EMPTY will filter out the empty values. The NONEMPTYCROSSJOIN will only return the non empty values.
I hope that helps.
Sami
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply