mdx query

I write the following MDX query that functions correctly:

WITH MEMBER [PERIOD CONTAB].[FY MONTH].[Totale] as

'AGGREGATE ( { [PERIOD CONTAB].[FY MONTH].&[2007-01-01T00:00:00] : [PERIOD CONTAB].[FY MONTH].&[2007-09-01T00:00:00] } )'

SELECT NON EMPTY { [Measures].[EUR] } ON COLUMNS ,

{

[Operating Cost]

,[COST ELEMENT].[MACRO COST].[Total Operating Cost]

,[COST ELEMENT].[MACRO COST].&[Capital Charge]

,[COST ELEMENT].[MACRO COST].[Total Contract Cost]

}

*

UNION (

{[PERIOD CONTAB].[FY MONTH].&[2007-01-01T00:00:00] :[PERIOD CONTAB].[FY MONTH].&[2007-09-01T00:00:00] }

, [PERIOD CONTAB].[FY MONTH].[Totale])

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

from [PMO_MDB]

If I try to insert the query inside Reporting Services substituting some data with query parameter in the following way I get an error:

WITH MEMBER [PERIOD CONTAB].[FY MONTH].[Totale] as

'AGGREGATE ( { STRTOMEMBER(@.FY_MONTH_START) : STRTOMEMBER(@.FY_MONTH_END) } )'

SELECT NON EMPTY { [Measures].[EUR] } ON COLUMNS ,

{

[Operating Cost]

,[COST ELEMENT].[MACRO COST].[Total Operating Cost]

,[COST ELEMENT].[MACRO COST].&[Capital Charge]

,[COST ELEMENT].[MACRO COST].[Total Contract Cost]

}

*

UNION (

{ STRTOMEMBER(@.FY_MONTH_START) : STRTOMEMBER(@.FY_MONTH_END) }

, [PERIOD CONTAB].[FY MONTH].[Totale])

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM ( SELECT ( STRTOSET(@.PROJECTSTREAM, CONSTRAINED) ) ON COLUMNS FROM [PMO_MDB])

WHERE ( IIF( STRTOSET(@.PROJECTSTREAM, CONSTRAINED).Count = 1, STRTOSET(@.PROJECTSTREAM, CONSTRAINED), [PROJECT].[STREAM].currentmember ) )

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The query seems sintaticcally correct !

So what happened !

Here is the error:

TITLE: Microsoft Visual Studio

Query preparation failed.

ADDITIONAL INFORMATION:

Parser: The FY_MONTH_START parameter could not be resolved because it was referenced in an inner subexpression. (msmgdsrv)

BUTTONS:

OK

Having the same problem in reporting services. Have you found anything?|||

Sounds like it's having a problem with the STRTOMEMBER function inside of the custom member definition. Since you know that your member will only be a single value, you could always write the query in dynamic mdx and pass the parameter values in. The top would look something like this:

Code Snippet

=" WITH MEMBER [PERIOD CONTAB].[FY MONTH].[Totale] as

'AGGREGATE ( { " + Parameters!FY_MONTH_START.Value + " : " + Parameters!FY_MONTH_END.Value + ") } )'

SELECT NON EMPTY { [Measures].[EUR] } ON COLUMNS , " +

It's a little tricky to get the designer to accept dynamic mdx. You have to write the original mdx query first. Then you have to click on the elipses for the dataset to bring up the dataset dialog. Then in the dialog, click the "f(x)" button to fill in the text. The dialog box that comes up will allow you to enter in the "=" sign and do the rest of your dynamic mdx.

|||

Thanks for the reply

I solved simply removing the character ' in the member definition !

Don't know why .... but now it functions -)

WITH MEMBER [PERIOD CONTAB].[FY MONTH].[Totale] as AGGREGATE ( { " + Parameters!FY_MONTH_START.Value + " : " + Parameters!FY_MONTH_END.Value + ") } )

.....

Cosimo