Escape special characters in query?

How can I escape the character " in the query designer?
This is my MDX query:
="select {[Measures].[Fakturerte timer HT],[Measures].[Fakturert beløp
HT],[Measures].[Gjsnitt fakturert timepris HT],[Measures].[Gjsnitt fakturert
timepris HT Veil pris],[Measures].[Gjsnitt fakturert timepris HT Salgspris]}
on 0,
non empty filter([Konsern].members,
len([Konsern].currentmember.Properties("Caption") > 0)) on 1
from [Prosjekt Komplett]
where ([Transaksjonstype].[Alle transaksjonstyper].[" &
Parameters!Trans.Value & "],[Dato].[" & Parameters!Periode.Value & "])"
My problem is that the work Caption needs a " on each side. My query works
in the MDX Sample Application, but it fails if I use the " in the Report
Designer - and it fails if I don't use them...
Without the "'s:
An error has occured during report processing.
Query execution failed for data set PB_AxpCmpWMB_Project1'
Formula error - syntax error - token is not valid:
"filter([Konsern].members,
len([Konsern].currentmember.Properties(Caption)^)^ > 0)"
With the "'s in Report Designer Data tab:
x:\wiersholm\Fakturarapport konsern.rdl The expression for the query
'PB_AxpCmpWMB_Project1' contains an error: [BC30004] Character constant must
contain exactly one character.
How can I use my query with "s?
All help appreciated!
Kaisa M. LindahlI found out. You escape " with an additional ".
And then I had to change my query again, as the Report Designer didn't like
me using (len).
="select {[Measures].[Fakturerte timer HT],[Measures].[Fakturert beløp
HT],[Measures].[Gjsnitt fakturert timepris HT],[Measures].[Gjsnitt fakturert
timepris HT Veil pris],[Measures].[Gjsnitt fakturert timepris HT Salgspris]}
on 0,
non empty filter([Konsern].members,
([Konsern].currentmember.Properties(""Caption"") > """")) on 1
from [Prosjekt Komplett]
where ([Transaksjonstype].[Alle transaksjonstyper].[" &
Parameters!Trans.Value & "],[Dato].[" & Parameters!Periode.Value & "])"
Kaisa
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:eylH2R6$EHA.4072@.TK2MSFTNGP10.phx.gbl...
> How can I escape the character " in the query designer?
> This is my MDX query:
> ="select {[Measures].[Fakturerte timer HT],[Measures].[Fakturert beløp
> HT],[Measures].[Gjsnitt fakturert timepris HT],[Measures].[Gjsnitt
fakturert
> timepris HT Veil pris],[Measures].[Gjsnitt fakturert timepris HT
Salgspris]}
> on 0,
> non empty filter([Konsern].members,
> len([Konsern].currentmember.Properties("Caption") > 0)) on 1
> from [Prosjekt Komplett]
> where ([Transaksjonstype].[Alle transaksjonstyper].[" &
> Parameters!Trans.Value & "],[Dato].[" & Parameters!Periode.Value & "])"
> My problem is that the work Caption needs a " on each side. My query works
> in the MDX Sample Application, but it fails if I use the " in the Report
> Designer - and it fails if I don't use them...
> Without the "'s:
> An error has occured during report processing.
> Query execution failed for data set PB_AxpCmpWMB_Project1'
> Formula error - syntax error - token is not valid:
> "filter([Konsern].members,
> len([Konsern].currentmember.Properties(Caption)^)^ > 0)"
> With the "'s in Report Designer Data tab:
> x:\wiersholm\Fakturarapport konsern.rdl The expression for the query
> 'PB_AxpCmpWMB_Project1' contains an error: [BC30004] Character constant
must
> contain exactly one character.
> How can I use my query with "s?
> All help appreciated!
> Kaisa M. Lindahl
>

Error: The MDX function ROOT failed because...

I'm having problem calculating sales for all customer minus certain occupations.

This Adventureworks query is something like what Excel 2003 produces.

Code Snippet

WITH MEMBER [Customer].[Occupation].[Exclude Occupations] AS

'AGGREGATE({

[Customer].[Occupation].&[Clerical],

[Customer].[Occupation].&[Management]

})'

MEMBER Measures.[Other Occupation Sales] as (ROOT(Customer), [Measures].[Internet Sales Amount]) - [Measures].[Internet Sales Amount]

SELECT

{Measures.[Other Occupation Sales]} ON COLUMNS,

[Product].[Category].[Category].MEMBERS ON ROWS

FROM [Adventure Works]

WHERE

([Customer].[Occupation].[Exclude Occupations])

Error: The MDX function ROOT failed because the coordinate for the 'Occupation' attribute contains a set.

If you delete this line, the query works!

[Customer].[Occupation].&[Clerical],

Does anyone know what this means? Is there another way to get Excel to formulate an equivalent query?

The problem is because there is a set in the current coordinate, and Root has problem dealing with it (although it shouldn't!). The best solution is to replace Root(Customer) with [All Customers] - which will have exactly same functionality, better performance, and won't generate the error that started this thread.|||

Yes, I replaced Root(Customer) with [Customer].[Occupation].[All Customers] and it works. The problem is that I wanted to create a general purpose calculated measure that would override every attribute in the customer dimension with "All". I thought Root did precisely that. If I can't use Root() then I will need to remember to update this calculation every time an attribute is added or removed from the customer dimension.

Thanks for the input.

|||

> The problem is that I wanted to create a general purpose calculated measure that would override every attribute in the customer dimension with "All"

[All Customers] does exactly that ! There is no need to update your calculation every time new attribute is added, [All Customers] (in any hierarchy of customers dimension) will automatically move coordinate to All member in every attribute.

|||

Try my AdventureWorks repro with [All Customers] instead of Root(). It doesn't work. It doesn't replace the aggregate set of occupations with the All tuple. I end up with zeros in the results because [All Customers] is doing absolutely nothing for me.

The only way I got it to work was with [Customer].[Occupation].[All Customers]. This member does, in fact, replace my aggregate set of occupations.

Put that in your pipe and smoke it!

I'm curious how [All Customers] is defined to work (vs. Root()).

|||Of course - you are right. I don't know what I was thinking. What I said is only true when dimension has single hierarchy which includes all attributes, which is obviously not the case for most of the dimensions, especially Customer.|||Thanks for the help. At least I have a work-around for now. It would be nice if Root(dimension) did the same thing as change coordinates to [All Customers] on every attribute. Maybe in the next version...