Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports
This is the forum to discuss the Wrox book Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports by Paul Turley, Robert M. Bruckner; ISBN: 9780470563113
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old August 4th, 2010, 01:44 PM
Registered User
 
Join Date: Aug 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Cube Metadata Recipe

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
Reply With Quote
  #2 (permalink)  
Old August 5th, 2010, 08:14 AM
Registered User
 
Join Date: Aug 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default OK folks this is the reason for the problem..my hair is grey

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
Reply With Quote
  #3 (permalink)  
Old August 26th, 2010, 07:16 PM
Registered User
Points: 18, Level: 1
Points: 18, Level: 1 Points: 18, Level: 1 Points: 18, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Cube Dynamic Rows Recipe rparge BOOK: Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports 9 March 15th, 2011 08:34 AM
Recipe to change CR query at run time. dbron Crystal Reports 4 June 24th, 2009 03:07 AM
Metadata sabucalex General .NET 2 November 29th, 2006 02:02 AM



All times are GMT -4. The time now is 03:56 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.