August 23, 2011 at 10:19 am
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.
August 24, 2011 at 6:39 am
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
August 24, 2011 at 7:15 am
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.
August 24, 2011 at 7:18 am
My Fault, it now shows only lines where count > 9
replace > 9 with < 10
regards Kees
August 24, 2011 at 8:00 am
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.
August 24, 2011 at 9:00 am
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
August 24, 2011 at 9:36 am
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply