MDX Parameter Query

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 & ")"
--
Message posted via http://www.sqlmonster.comI 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!!!!
--
Message posted via http://www.sqlmonster.com|||Jason via SQLMonster.com wrote:
> I still receive the error: "[BC30648] String constants must end with
> a doube quote" though...
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
--
Message posted via http://www.sqlmonster.com|||Jason via SQLMonster.com wrote:
> I have changed the query statement to read:
> 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?
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!!
--
Message posted via http://www.sqlmonster.com|||Frank,
Problem solved!!
There were some simple formatting issues that I fixed.
Your help is greatly appreciated!!
--
Message posted via http://www.sqlmonster.com