sql server reporting services: I am attempting to add a date parameter to a RS MDX query. Below I have included my query. I have been following a olapasandrs.asp white paper on microsoft.com, but I keep receiving an error message - "The expression for the query 'query name' contains an error: [BC30648] String constants must end with a doube quote"

If someone would please be kind enough to take a look at my query and point out any problems I would appreciate it!!

="SELECT{[Measures].[Gm] ,[Measures].[Sales],[Measures].[COS],[Measures].[Rebate],[Measures].[Adjustment]} ON AXIS(0) , {nonemptycrossJoin([products].[Glcls].Members,[segdiv].[divname].Members)} ON AXIS(1) ,{[Time].Members} ON AXIS(2) FROM vc_rebates Where (" & Parameters!pTime.Value & ")"

--

I have an update:

I have changed my query to read-

="SELECT{[Measures].[Gm] ,[Measures].[Sales],[Measures].[COS],[Measures].[Rebate],[Measures].[Adjustment]} ON AXIS(0),
{nonemptycrossJoin([products].[Glcls].Members,[segdiv].[divname].Members)} ON AXIS(1),
{Descendants([Time].[All Time],[Time].[Month])} ON AXIS(2)
FROM vc_rebates Where(" & Parameters!pTime.Value & ")"

I still receive the error: "[BC30648] String constants must end with a doube quote" though...

Any help would be appreciated!!!!

--

[quoted text, click to view]

you have to write a double quote at the beginning and end of each line.

="SELECT{[Measures].[Gm]
,[Measures].[Sales],[Measures].[COS],[Measures].[Rebate],[Measures].[Adjustm
ent]} ON AXIS(0), " +
"{nonemptycrossJoin([products].[Glcls].Members,[segdiv].[divname].Members)}
ON AXIS(1), " +
"{Descendants([Time].[All Time],[Time].[Month])} ON AXIS(2) " +
"FROM vc_rebates Where(" & Parameters!pTime.Value & ")"

regards

Frank
www.xax.de

Thank you Frank!

I have changed the query statement to read:

="SELECT{[Measures].[Gm] ,[Measures].[Sales],[Measures].[COS],[Measures].[Rebate],[Measures].[Adjustment]} ON Columns, " +
"Non Empty { [products].[Glcls].Members } ON Rows, " +
"Non Empty { Descendants([segdiv].[All segDiv],[segDiv].[Divname],LEAVES) } ON Pages, " +
"Non Empty { Descendants([Time].[All Time],[Time].[Month],LEAVES) } ON Sections " +
"FROM vc_rebates " +
"Where (" & Parameters!pDate.Value & ")"

Now I am receving a new error message, "The expression for the query 'Query Name' contains an error: [BC30451] Name 'Non' is not declared".

Do you have any additional suggestions?

Thanks

--

[quoted text, click to view]

OK...now your MDX-Statement seems to be wrong.
The "Name 'Non' is not declared seems to result of the "Non Empty"
Did you test your MDX-Query in mdx-query-application
(Start-Progs-SQLServer-Analysis Services -MDX-Application)? Usually without
the "-Quotes!! and filled up with the parameter-value.
For Test:
Erase the Non Empty-Clause

regards

Frank
www.xax.de

Thank you for your response Frank,

I have one last post for you...

I have been able to deploy the report and have not encoutnered any errors. Upon selecting the parameter though I am reeiving an error message from reporting services:

Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. (rrRenderingError) Get Online Help
Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown.
Operation is not valid due to the current state of the object.

If I remove the parameter the report runs fine. Do you have any suggestions? I have posted the parameter query statement below.

with Member [Measures].[TimeMemberUniqueName] as '[Time].currentmember.UniqueName' member [Measures].[TimeDisplayName] as 'space([Time].Currentmember.Level.Ordinal *4) + Time.Currentmember.Name'
Select {[Measures].[TimeMemberUniqueName],[Measures].[TimeDisplayName]} on Columns,
{[Time].members} on rows
from vc_rebates

Thank you in advance!!

--

Frank,

Problem solved!!

There were some simple formatting issues that I fixed.

Your help is greatly appreciated!!

--