RE: STRTOMEMBER PROBLEM

#1

Hi "Fernando,

You could potentially try some string manipulation to overcome the issue
with the embedded space in the string. Try enclosing the string in '[ ]'
characters eg., '[New York]'. You may also need to build a string including
the parent members of the passed member for the StrToMember function to work.

Best of luck.

Graeme.

"Fernando MarÃ§al" wrote:

Quote:

Hello,

Im currently developing a report in SSRS2005 and I am using MDX with
parameters.

The problem is in the generated query i get STRTOMEMBER(@varname), and if
varname includes spaces STRTOMEMBER doesnt work.

Example: STRTOMEMBER("New York") fails with "The syntax for 'YORK' is
incorrect.". STRTOMEMBER("Washington") succeeds. Is this a BUG?

#2

Graeme is right - this is not a bug - STRTOMEMBER function accepts MDX
expression which it reparses - and "NEW YORK" looks like two identifiers.
However, while enclosing it in [ ]'s helps - it is still pretty bad, because
you are passing completely unqualified member name to AS. You should try to
build qualified member name both for correctness and performance.

--
==============================*=================== =
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL*og/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==============================*=================== =
"Graeme Scott [MSFT]" <Graeme Scott [MSFT]@discussions.microsoft.com> wrote
in message news:EC82E32B-3E2F-4B50-80DA-A78C3E087F16 (AT) microsoft (DOT) com...

Quote:

Hi "Fernando,

You could potentially try some string manipulation to overcome the issue
with the embedded space in the string. Try enclosing the string in '[ ]'
characters eg., '[New York]'. You may also need to build a string
including
the parent members of the passed member for the StrToMember function to
work.

Best of luck.

Graeme.

"Fernando Marçal" wrote:

Hello,

Im currently developing a report in SSRS2005 and I am using MDX with
parameters.

The problem is in the generated query i get STRTOMEMBER(@varname), and if
varname includes spaces STRTOMEMBER doesnt work.

Example: STRTOMEMBER("New York") fails with "The syntax for 'YORK' is
incorrect.". STRTOMEMBER("Washington") succeeds. Is this a BUG?

#3

Fernando - you misinterpreted my comment about performance. I am not worried
about performance of string concatenation. I am worried about negative
impact on AS performance if you feed to it unqualified member names. When AS
sees unqualified member name, it needs to scan all attributes in all
dimensions, which can be very expensive, especially if they are large
dimensions or even worse ROLAP dimensions or linked dimensions or MOLAP
unmaterialized dimensions etc. Therefore I strongly irge you to qualify
member name by its dimension, hierarchy and level if possible.

--
==============================*=================== =
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL*og/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==============================*=================== =
"Fernando Marçal" <FernandoMaral (AT) discussions (DOT) microsoft.com> wrote

Quote:

Hi,

Thanks for all the help. I've managed to concatenate the '[]' in the
report
parameters since I am having a hard time editing the query, but that's
another story.

Performance is not the issue here since I am only doing the concatenation
for some parameters (2 or 3).

Thanks for the help.

"Mosha Pasumansky [MS]" wrote:

Graeme is right - this is not a bug - STRTOMEMBER function accepts MDX
expression which it reparses - and "NEW YORK" looks like two identifiers.
However, while enclosing it in [ ]'s helps - it is still pretty bad,
because
you are passing completely unqualified member name to AS. You should try
to
build qualified member name both for correctness and performance.

--
==============================*=================== =
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL*og/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==============================*=================== =
"Graeme Scott [MSFT]" <Graeme Scott [MSFT]@discussions.microsoft.com
wrote
in message news:EC82E32B-3E2F-4B50-80DA-A78C3E087F16 (AT) microsoft (DOT) com...
Hi "Fernando,

You could potentially try some string manipulation to overcome the
issue
with the embedded space in the string. Try enclosing the string in
'[ ]'
characters eg., '[New York]'. You may also need to build a string
including
the parent members of the passed member for the StrToMember function to
work.

Best of luck.

Graeme.

"Fernando Marçal" wrote:

Hello,

Im currently developing a report in SSRS2005 and I am using MDX with
parameters.

The problem is in the generated query i get STRTOMEMBER(@varname), and
if
varname includes spaces STRTOMEMBER doesnt work.

Example: STRTOMEMBER("New York") fails with "The syntax for 'YORK' is
incorrect.". STRTOMEMBER("Washington") succeeds. Is this a BUG?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

RE: STRTOMEMBER PROBLEM

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

RE: STRTOMEMBER PROBLEM - 10-20-2005 , 07:30 AM

Re: STRTOMEMBER PROBLEM - 10-20-2005 , 12:52 PM

Re: STRTOMEMBER PROBLEM - 10-21-2005 , 12:03 PM