Hi all,
Here's the plot:
I have MDX queries stored in a DB.
I run those queries and display them in a web page, but I would like to
limit rows returned to, let's say, 100.
So, before running a query, I parse the query and insert Head function for
the ROWS set, e.g.
I turn:
SELECT NON EMPTY {Measures.Members} ON COLUMNS
, NON EMPTY {Dim1.Level.Members} ON ROWS
FROM Cube
into:
SELECT NON EMPTY {Measures.Members} ON COLUMNS
, NON EMPTY Head({Dim1.Level.Members}, 100) ON ROWS
FROM Cube

The problem is that Head filters out first 100 rows whether they are empty
or not, and then NON EMPTY takes only the non-empty ones!
This results in rowcount often less than 100, and if first 100 rows are
empty then it results in an error.

Is there any way to do that?
I mean for the general query, not for a particular one (I know I could use
Filter(Dim1.Level.Members, Not IsEmpty(<SOME MEASURE> ))).

If I could something like: ... Head( NON EMPTY {set}, 100 ) , i.e. apply non
empty filter BEFORE Head function?
Is there a way to achieve this?

Thanks,

Igor

Hi Igor,

You might be able to use the NONEMPTYCROSSJOIN function instead. On the plus
side it will give you what you want with faster performance than NON EMPTY;
on the minus side there are some quirks that you have to watch out for - the
fact that it always removes calculated members (unless you have a calculated
measure with the NON_EMPTY_BEHAVIOR set properly), that it doesn't behave
itself with virtual cubes, that the way it works on the Measures dimension
isn't properly documented etc. If you can live with these quirks, then
something like:

SELECT NON EMPTY {Measures.Members} ON COLUMNS
, Head(NONEMPTYCROSSJOIN({Dim1.Level.Members}), 100) ON ROWS
FROM Cube

....should do the trick.

HTH,

Chris



"Igor Mekterovic" wrote:

Thank you Chris for a quick reply!
Unfortunately I have a lot of calculated members, and I'm always quering
against virtual cubes beacuse I'm using dynamic security.
Thanks anyway,
Igor


"Chris Webb" <OnlyForPostingToNewsgroups (AT) hotmail (DOT) com> wrote

One last thought - if you are using dynamic security, could you do the non
empty filtering inside your security expression? That's to say that as well
as only showing the members on a dimension that a user has permission to see,
out of these members they only see the non-empty ones. NECJ isn't
unpredictable when it comes to virtual cubes, just a little hard to
understand - with a little experimentation you can probably make it work. See
the following thread for more information
http://groups.google.co.uk/groups?hl...com%26rnum%3D2

Obviously taking this approach has a number of significant drawbacks, but I
could see it working if you were only allowing access to your cube through
the interface where you want to do the non-empty filtering.

Regards,

Chris

"Igor Mekterovic" wrote: