MDX Query - Simple Count(*)

Question

MDX Query - Simple Count(*)

Ludo-ZX7R

SSCrazy

Points: 2882
More actions
August 31, 2010 at 7:49 am

#227057

Hi
I have a Cube with a "Count" Measure. This works well and tell me how many rows are retrieved when I perform Queries, ...
I also have a few dimensions and I can perform the following Query:
WITH MEMBER Measures.Toto AS (<dimension0>.&[VALID], Measures.Count)
SELECT {Measures.Toto} ON 0,
{<dimension1>} ON 1
WHERE
(<dimension2>..., <dimension3>...)
This query gives me the number of records which have a [VALID] value as dimension0 within the Where Clause specified.
If I want a count of the records which have a [VALID] OR [INVALID] dimension0 how do I do it?
I have tried the following:
(<dimension0>.[VALID]+<dimension0>.[INVALID], Measures.Count)
({<dimension0>.[VALID],<dimension0>.[INVALID]}, Measures.Count)
(UNION(<dimension0>.[VALID],<dimension0>.[INVALID]), Measures.Count)
... plus a few other stuff but I always get errors ...
Hiw can I achieve this result?
Cheers
Ludo

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

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Ludo-1135998 (8/31/2010)
Hi
I have a Cube with a "Count" Measure. This works well and tell me how many rows are retrieved when I perform Queries, ...
I also have a few dimensions and I can perform the following Query:
WITH MEMBER Measures.Toto AS (<dimension0>.&[VALID], Measures.Count)
SELECT {Measures.Toto} ON 0,
{<dimension1>} ON 1
WHERE
(<dimension2>..., <dimension3>...)
This query gives me the number of records which have a [VALID] value as dimension0 within the Where Clause specified.
If I want a count of the records which have a [VALID] OR [INVALID] dimension0 how do I do it?
I have tried the following:
(<dimension0>.[VALID]+<dimension0>.[INVALID], Measures.Count)
({<dimension0>.[VALID],<dimension0>.[INVALID]}, Measures.Count)
(UNION(<dimension0>.[VALID],<dimension0>.[INVALID]), Measures.Count)
... plus a few other stuff but I always get errors ...
Hiw can I achieve this result?
Cheers
Ludo

I think it's something like this: -

WITH MEMBER Measures.Toto AS ((<dimension0>.&[VALID],<dimension0>.&[INVALID]), Measures.Count)

SELECT {Measures.Toto} ON 0,

{<dimension1>} ON 1

FROM {<from clause>}

WHERE

(<dimension2>..., <dimension3>...)

Not at my desk, so can't check the syntax.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 2

Are Valid and Invalid the only two options? If so, then don't filter this dimension at all. This assumes that you haven't set either of these values to be the default for the dimension.

By not filtering, you're effectively filtering on the 'All' member for any dimension not listed in the WHERE clause.

Steve.

Ludo-ZX7R SSCrazy Points: 2882 More actions · Answer 3

Hi

Unfortunately there are more options that just VALID and INVALID so I need something to filter the result.

Cheers

Ludo-ZX7R SSCrazy Points: 2882 More actions · Answer 4

Hi

Thanks for the answer.

Unfortunately when I do that I get the following error:

"The <dimension0> hierarchy appears more than once in the tuple.

Cheers

Ludo

Ludo-ZX7R SSCrazy Points: 2882 More actions · Answer 5

Here is my solution which may not be the best but here we go:

(<dimension0>.&[VALID], measures.Count) + (<dimension0>.&[INVALID], measures.Count)

Voila sorted!

That gives me the number of records which are either VALID or INVALID.

However if you want to count the number of records which are INVALID but are also LOW in <dimension1> (for argument's sake) you can do the following:

((<dimension0>.&[INVALID], <dimension1>.&[LOW]), Measures.Count)

In the tule as we use 2 different dimensions it works.

I hope this helps someone.

Cheers 🙂