want to limit MDX output

Question

Post reply

want to limit MDX output

Tobar

SSCarpal Tunnel

Points: 4876
More actions
August 23, 2011 at 10:19 am

#256124

I want to limit the following code so that only "counts" > 9 are returned and I can not figure it out.
SELECT NON EMPTY {
[Measures].[AverageAchievementLevel],
[Measures].[AverageScore],
[Measures].[Fact Count],
[Measures].[Score]} on columns,
NON EMPTY ([Com Student Demographics].[Race Ethnicity].Members)
on rows
from [ReportCard2007NoStudents]
WHERE(
[Com Organization].[Organization Number].[0001],
[Com Organization].[Organization Type Code].[03])
<><
Livin' down on the cube farm. Left, left, then a right.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Kees Beimans SSC Eights! Points: 978 More actions · Answer 1

Try this

SELECT NON EMPTY {

[Measures].[AverageAchievementLevel],

[Measures].[AverageScore],

[Measures].[Fact Count],

[Measures].[Score]}

HAVING [Measures].[Fact Count] > 9

on columns,

NON EMPTY ([Com Student Demographics].[Race Ethnicity].Members)

on rows

from [ReportCard2007NoStudents]

WHERE(

[Com Organization].[Organization Number].[0001],

[Com Organization].[Organization Type Code].[03])

Regards Kees

Tobar SSCarpal Tunnel Points: 4876 More actions · Answer 2

I don't get an error, but it also doesn't limit the output to lines where the count is > 9.

<><
Livin' down on the cube farm. Left, left, then a right.

Kees Beimans SSC Eights! Points: 978 More actions · Answer 3

My Fault, it now shows only lines where count > 9

replace > 9 with < 10

regards Kees

Tobar SSCarpal Tunnel Points: 4876 More actions · Answer 4

There is some other cubefoolary going on. I think the having is looking at the whole count not just the count of the slice. Therefore when I say < 10 I get no rows. Same when I say < 100 even thought I have counts of 9 and 26. Oh well.

I went off looking for a description of the Having command in MDX but could not find it. I found another option for my problem with the IIF command. I will try that. If it works I will post here.

<><
Livin' down on the cube farm. Left, left, then a right.

Kees Beimans SSC Eights! Points: 978 More actions · Answer 5

I was working on more than one thing at the same time.

I placed the having clause in the column axis instead of the row axis

:doze::Whistling:

That does not work. Having works on the axis and limits tuples of the axis. You can even limit the tuples with non presented measures.

Regards Kees

Tobar SSCarpal Tunnel Points: 4876 More actions · Answer 6

Dude!! Thanks for staying with it. I was off in lala land trying to figure out some other arcane way to get this result. It works great!:-)

<><
Livin' down on the cube farm. Left, left, then a right.