August 26, 2010 at 7:51 am
Hi guys
I have been struggeling for a little while with MDX Queries and I'm sure that I'm missing something fundamental but cannot put my finger on it.
Anyway here is a summary of my problem:
I have a cube called [Cube1] which has just a number of records inside it.
I have a Time and Product Dimensions which are linked to this Cube.
If I perform the following MDX Query:
SELECT
{ [Measures].[Number of Records] } ON COLUMNS,
{ [Product].[Type].[Type] } ON ROWS
FROM
[Cube1]
WHERE
([Time].[Year].&[2010-01-01T00:00:00])
I see all my products for 2010 ... so far so good.
If I had a MEMBER suddenly the WHERE Clause is ignored and it displays all the records.
WITH MEMBER [Measures].[Test] AS '''TOTO'''
SELECT
{ [Measures].[Tets] } ON COLUMNS,
{ [Product].[Type].[Type] } ON ROWS
FROM
[Cube1]
WHERE
([Time].[Year].&[2010-01-01T00:00:00])
I have looked at SCOPE, FILTER, ... but to no avail.
Can someone help please?
Cheers
Ludo
August 26, 2010 at 8:13 am
Basically it's working 'as intended' 🙂 but that doesn't overly help you.
Assuming what you want is to still only display the members that have a count value in your Number of Records measure, you could try adding the NON_EMPTY_BEHAVIOR property to the definition of the calc member and use the Number of Records measure as it's value.
Alternatively, you can filter the set of members to be displayed on rows, and this will again make use of your existing measure, maybe something like filter({set}, [Number of Records] > 0)
Steve.
August 26, 2010 at 8:33 am
Thanks for the info.
I will investigate and will hopefully find an solution.
Cheers
August 27, 2010 at 2:13 am
Still no joy I'm affraid.
Following more testing it down on me that if you create a Member which uses only Measures from the Cube the Where Clause is used. If you use any other value like a constant the Calculated Member is out of Scope of the Where Clause.
How can I do the following in a Calculated Member and still use a Where Clause?
WITH
MEMBER [Measure].[Test] AS
'100 - [Measure].[Cube Measure]'
SELECT
{[Measure].[Test]} ON 0,
{<Dimension>} ON 1
FROM
[Cube]
WHERE
(<dimension1>,<Dimension2>, ...)
Basically how can I specify that I want to use the Constant "100" within the Scope of the Where Clause.
Cheers
Ludo
August 31, 2010 at 2:36 am
For anybody who's interested here is the solution.
Let's say that you have a meaure that tells you how many records you have in your set. That's an existing measure presented to you when you build your cude ([<cube> Count]).
When creating your Calculated Measure you rely on this measure to identify whether or not this calculation is within the scope of your Where Clause or the Scope of your query in genral.
WITH
MEMBER measures.[calculated measure] AS
IIF (IsEmpty([Measures].[<cube> Count]),
null,
<do whatever you like>),
NON_EMPTY_BEHAVIOR = [Measures].[<cuve> Count]
SELECT
NON EMPTY {Measures.[calculated measure], ...} ON 0,
NON EMPTY {<Dimension>} ON 1
FROM
<Cube>
WHERE
(<Where Clause>)
Now the records retrieved will be only for the records matching the Where Clause.
I hope this helps someone.
Cheers
Ludo
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply