Have you ever tried to create a dataset in SSRS 2008 (based upon an OLAP cube) and ran into that notorious “Query (1, 1) Parser: The syntax for '=' is incorrect.” error message and a plethora of other really informative error messages.
Well I did, when trying to create my query (using the expression editor) and trying to populate the dataset fields. Please read on… the plot thickens.
I have been working my way through the examples in Paul Turley’s book “Microsoft SQL Server Reporting Services Recipes: For Designing Expert Reports” and really hit the wall on this one, thus I am posting here for others that may have encountered the same problem.
First off, you should know that (as any good boy would) I had set my data source to “Microsoft SQL Server Analysis Services”, with Windows authentication. Not really rocket science when you come to think of it.
Like the myriads of others before me, I have spent the past few days trying to figure out why… no matter what I did, I could not get my dataset to populate within the “Report Data” view.
Checked on Google… Hey I am no fool.. trust me I have spent hours UNTIL I stumbled upon a posting that Darren Gosbell replied to.
Keeping the drama to a minimum, it would seem that
Using …”"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 10.0" driver and this should fix your issue.””
What Darren does is
“What I normally do with expressions in SSRS MDX queries is to run the query once with the expression using hard 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.”
Thanks a million Darren for the posting. I thought that the problem lay between the brain and the keyboard.
Happy programming to all.
Regards Steve
