Hi. I have been working on this one for the past two days and have been coming up short. I am trying to create the dataset 'Measures', discussed on pages 153-154. I have created and tested my datasource(analytical services dsSSAS). I a trying to create the dataset now via the FX mode in the query box. This is what I have entered.
="SELECT *FROM $System.MDSCHEMA_MEASURES" + "WHERE CUBE_NAME ='" & Parameters!pCube.Value & "'" +"AND (MEASUREGROUP_NAME ='" & Parameters!pMeasureGroup.Value & "'" +"OR MEASURE_DISPLAY_FOLDER ='" & Parameters!pMeasureGroup.Value & "' )"
I have created the necessay report parameters (with the required defaults as discussed in the book).
THE PROBLEM: When I click the "refresh fields" button, nothing happens.The fields are NOT populated within the dataset.
I have removed all white spaces etc.
Any ideas would be greatfully appreciated.
regards Steve

Darren Gosbell was right on the nose in his comment

http://www.sqldev.org/sql-server-analysis-services/analysis-services-reporting-service-error-with-mdx-query-9750.shtml


"I'm assuming from hints in your question that you are probably using SSRS 2005and SSAS 2005.

What sort of connection are you using?

If under "Type" it says "Microsoft SQL Server Analysis Services" then this will be your issue as I do not believe that this provider supports expressions in the query. If this is the case, change the provider to "OLE DB" and select the "Microsoft OLE DB Provider for Analysis Services 9.0" driver and this should fix your issue."

In a later response, he says the following

"Sorry, I missed the mention of 2008 - so the 10.0 provider is correct.

I just checked and I get the same error, but if you read the start of the error it says "Could not update a list of fields for the query..."

What I normally do with expressions in SSRS MDX queries is to run the query once with the expression usinghard coded parameters, this lets SSRS populate the field list.

Then I put in the quotes and string concatenation to build the expression, then you can ignore the warning about not being able to update the field list."


From yours truly...
Thanks a million Darren you saved my sanity.
regards Steve

Hello Steve,

> What I normally do with expressions in SSRS MDX queries is to run the query once with the expression usinghard coded parameters, this lets SSRS populate the field list.
> Then I put in the quotes and string concatenation to build the expression, then you can ignore the warning about not being able to update the field list."

This piece of advice is correct. Fields can only be automatically detected in the report design tools if the query commandtext is *not* expression-based. Therefore, it is a good idea to first get the fields list populated using a non-expression based query that close to the final query you want to run. Put the expression you plan to use for the query in a top-level textbox in the report to verify the expression produces the expected query commandtext when running the report. Only as a final step replace the original query with the expression-based query.

HTH,
Robert