ampersand in mdx

[Bateman@discussions.microsoft.com Mark Bateman]

I'm passing mdx statements to our Windows 2003 Analysis Server via xmla
(datsource is the url to the Msxisapi.dll on the AS machine).

Everything works fine until I pass an MDX statement containing an ampersand.
Doing so I get the following error: -

Formula error - cannot find dimension member
("[Time].[Fiscal].[Year].[2004].[Period 12].[Week 49].&[08/01/2005 ]") - in a
name-binding function

If I remove '.&[08/01/2005 ]' then everything works fine.

How I can I pass mdx queries containing ampersands?

Cheers,

Mark...

[Chris Webb]

Does replacing the ampersand with the escape sequence:
&
make it work? Does the MDX work when you run it in MDX Sample app?

Chris

[Mark Bateman]

Hi there Chris

I should have mentioned that in the original message. I have infact tried
both & and %26 (as you'd use it as part of the url) and I keep getting an AS
mdx error: -

Formula error - syntax error - token is not valid: "(
[Time].[Fiscal].[Year].[2004].[Period 12].[Week 50].&amp^;^[10/01/2005 ],
[TradeIndicator].[Trade Flag].[Trade])"

The MDX works when using in Proclarity and the MDX sample app. Any
statements I sue work when the ampersand isn't in the code but when one is
used I keep getting the 'cannot find dimension member' error.

Any more ideas?

Mark...

==================================

[Mark Bateman]

I should note that even though my reply reads 'both & and %26', I actually
had put the &amp[ersand]; escape code where the & is now.

Cheers...

"Mark Bateman" wrote:

> Hi there Chris
>
> I should have mentioned that in the original message. I have infact tried
> both & and %26 (as you'd use it as part of the url) and I keep getting an AS
> mdx error: -
>
> Formula error - syntax error - token is not valid: "(

> [Time].[Fiscal].[Year].[2004].[Period 12].[Week 50].&^;^[10/01/2005 ],

[Chris Webb]

Hi Mark,

I suspect the problem might be connected to the key values you're using for
your dimension. Can you create unique, integer keys for the members on all
levels of the dimension and then alter the dimension in Analysis Services so
it uses them rather than using the name as the key as you seem to be doing at
the moment?

Regards,

Chris

"Mark Bateman" wrote:

> I should note that even though my reply reads 'both & and %26', I actually

> had put the &[ersand]; escape code where the & is now.

[Mark Bateman]

Hi Chris,

To get around the problem I'm using dimensions in my mdx by calling the
member name instead of the member key. It just means I have to change all my
mdx statements which is a bit of a pain.

Seems worrying though that using this xmla connection, not all mdx
statements work since the ampersand symbol isn't allowed.

Thanks for your help,

Mark...

[Chris Webb]

I'm not sure it's that the ampersand isn't allowed, I think it is because AS
is having trouble locating members in some circumstances when you use the
ampersand syntax (ie when you're requesting a member by its key), because of
the keys you've chosen for these members.

[Mark Bateman]

Maybe so, but it can't be denied that the exact same pasted mdx code works
when pointing to the cube using ProClarity or the sample MDX app. Once the
ampersand is excluded, my test app works. Maybe I need to set up something at
the IIS side that caters for ampersands? Then again I'm only clutching at
straws.

Have you tried testign this yourself? Can you pass ampersands?

Cheers again,

Mark...

[Chris Webb]

Certainly the same MDX works when you run it using PTS on own machine, as
when you're using MDX Sample app, but when you run it through XMLA then PTS
is actually running on the server (and possibly using different connection
string properties, etc). The reason I'm suggesting you change the keys you're
using is that other people on this ng have had the same problem and not been
using XMLA, and it always seems to have been caused by their choice of key.
Unfortunately I can't test this myself because I don't have an install of
XMLA handy at the moment.

Apart from the fact that you're using the name of the member as its key, can
you tell me what the 'Member Keys Unique' property on the dimension and all
the levels is set to? Is it set to False? If your keys are in fact unique,
can you set it to true?

[Mark Bateman]

The cube I'm accessing the 'Member Keys Unique' property on the time.fiscal
dimension was set to false. This dimension contains 4 levels starting with
Year, Period, Week Number and Date. I've now set the 'Member Keys Unique'
property of the Date level to true. (Years property is disbled and the other
two gave an error after I processed the dimension and tried to re-process the
cube)

I can set the 'Member Keys Unique' property for the Date level to true.

[Chris Webb]

And what happens when you try to run your original query now?

[Mark Bateman]

Fantastic stuff Chris, no idea what the logic is behind it but it certainly
has worked and I can see lovely boring financial figures!

Thank you very much indeed. But can you tell me why you this this would have
had an impact?

[Chris Webb]

I'm not exactly sure, but as I said I've seen similar problems in the past
connected to non-unique keys.