Hi y'all,

I've tried created a MDX query, that combines my Advertiser and Products on
the rows, and my year (2005) in the column. I also have a dimension "Months".
When i try to show the data for June and July, it gives no result. When I
only choose 1 month, it works perfect. Anyone an idea ?

Here is the MDX


select
{[Broadcast Date].[Year].[2005]} ON COLUMNS,
Non Empty Filter(NonEmptyCrossJoin([Advertiser
Responsible].[All Advertiser Responsible].CHILDREN,[Product].[All
Product].CHILDREN),([Month_Name].[All Month_Name] = ([June] OR [July]))) ON
AXIS(1)

from [Spot Sales Test]

where ([Broadcasting Company].[All Broadcasting Company].[TV].[MyCompany])


Even without the brackets around June or July, it didn't work

Kind regards

How about filtering differently for June or July:

{[Broadcast Date].[Year].[2005]} ON COLUMNS,
NonEmptyCrossJoin(
[Advertiser Responsible].[All Advertiser Responsible].CHILDREN,
[Product].[All Product].CHILDREN,
{[Month_Name].[All Month_Name].[June],
[Month_Name].[All Month_Name].[July]}) ON AXIS(1)

from [Spot Sales Test]

where ([Broadcasting Company].[All Broadcasting
Company].[TV].[MyCompany])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Koen,

Deepak's suggestions of including the June and June members in the
crossjoin statement is the way I would have done it too and is probably
the best way to structure your query.

But I thought you may be interested in a couple of the reasons why your
original query returned no result.

1. The set you are filtering over does not include any month members. If
you don't explicitly specify any members on a particular dimension, the
cube will return the default member for that dimension. This is usually
the "All" member (which will never be equal to June or July)

2. The equals operator will resolve both operands to their values, and
by placing [June] and [July] in brackets, you have specified them as
tuples which will return the numeric values based on the current context
of the other dimensions.

If you really wanted to filter on member name you would have to do
something like:

Date].Currentmember.Name = "July" OR [Broadcast Date].Currentmember.Name
= "June")
Or if you ever need to test for a specific member you can use the IS
operator

IS [Broadcast Date].[Month].[July])
HTH

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell



In article <OKucNnOKGHA.916 (AT) TK2MSFTNGP10 (DOT) phx.gbl>,
deepak_puri (AT) progressive (DOT) com says...

Thanks for the tips! I will try them soon.
Now, i have another problem. When i try to execute the following statement
in query analyzer it gives an Access Violation Error.

select * from
openquery(MP_VMMA_OLAP,
'
with
SET Groep1_selectie AS ''[Advertiser Responsible].[All Advertiser
Responsible].CHILDREN''
select
[Broadcast Date].[Year].[2001] ON COLUMNS,
Non Empty CrossJoin (Groep1_selectie,[Product].[All
Product].CHILDREN) ON ROWS,

from [Spot Sales test]

where ([Measures].[PSD Price])
'
)

Apparantly it can't stand the Non Empty CrossJoin (this runs in mdx sample
appl.)(I use Non Empty en not NonEmptyCrossJoin becaus the last one takes way
too much time)

Anyone an idea? Thx!

"Darren Gosbell" wrote:

It only occurs when i try to crossjoin my advertiser and my product
dimension. But when i want to join with eg "Month"-dimension, it gives no
error...

"Koen" wrote:

Are you sure the identical query works in mdx sample? You have only
specified a member on the column axis, where you should have a set (even
if it is just a set with one member) .

A single member or list of members can be specified as part of a set by
enclosing them in "curly" brackets {}/

eg

openquery(MP_VMMA_OLAP,
'
with
SET Groep1_selectie AS ''[Advertiser Responsible].[All
Advertiser
Responsible].CHILDREN''
select
{[Broadcast Date].[Year].[2001]} ON COLUMNS,
Non Empty CrossJoin (Groep1_selectie,[Product].[All
Product].CHILDREN) ON ROWS,

from [Spot Sales test]

where ([Measures].[PSD Price])
'
)
A couple of other observations:

1. Do you need to use the "WITH SET..." construct? If you are not re-
using the set anywhere else in the query it does not help much.

2. Have you looked into the NonEmptyCrossJoin() function (as opposed to
using the NON EMPTY clause in front of a standard Crossjoin)? It is much
faster than a NON EMPTY Crossjoin, although it does have limitations
with things like calculated measures.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <57FBB0D0-4E7A-4757-943F-20EF5BA20DD0 (AT) microsoft (DOT) com>,
Koen (AT) discussions (DOT) microsoft.com says...

yes it works in the mdx sample. Maybe the cross join was too large ? Anyway,
thanks everyone for the answers. Helped a lot, and probably will help mor
people in the future!


"Darren Gosbell" wrote: