Problem w/ Dynamic Query

I'm having problems constructing a dynamic query on the Data tab in SQL Reporting Services. When I try to use one of the reporting parameters it fails with "The expression for the query â'Dataâ' contains an error: [BC30648] String constants must end with a double quote." I want to be able to append the value of the chain the user selected to the end of the query. Anyone have any ideas.
My query is:
="SELECT {{[Product].[All Product]}*{Descendants([Market].[All Market].[Bob Jones])}} on rows,
{{[Time].[All Time].[2003-01-01 00:00:00].[2003-05-16 00:00:00],
[Time].[All Time].[2004-01-01 00:00:00].[2004-05-16 00:00:00],
[Time].[All Time].[2003-01-01 00:00:00].[2003-06-16 00:00:00],
[Time].[All Time].[2004-01-01 00:00:00].[2004-06-16 00:00:00]}
*{[Dollar Volume]}} on columns FROM " & Parameters!Chain.Value
If I get rid of the parameter and just end the query with a hard coded chain it works.Did you try appending a CStr(Parameters!Chain.Value) instead?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Karch" <Karch@.discussions.microsoft.com> wrote in message
news:A1A5AB61-FFAC-49C3-A300-290A7C85906C@.microsoft.com...
> I'm having problems constructing a dynamic query on the Data tab in SQL
Reporting Services. When I try to use one of the reporting parameters it
fails with "The expression for the query â'Dataâ' contains an error:
[BC30648] String constants must end with a double quote." I want to be able
to append the value of the chain the user selected to the end of the query.
Anyone have any ideas.
> My query is:
> ="SELECT {{[Product].[All Product]}*{Descendants([Market].[All
Market].[Bob Jones])}} on rows,
> {{[Time].[All Time].[2003-01-01 00:00:00].[2003-05-16 00:00:00],
> [Time].[All Time].[2004-01-01 00:00:00].[2004-05-16 00:00:00],
> [Time].[All Time].[2003-01-01 00:00:00].[2003-06-16 00:00:00],
> [Time].[All Time].[2004-01-01 00:00:00].[2004-06-16 00:00:00]}
> *{[Dollar Volume]}} on columns FROM " & Parameters!Chain.Value
> If I get rid of the parameter and just end the query with a hard coded
chain it works.|||Parameterized MDX queries are not supported by the OleDB provider for OLAP
8.0. Therefore you have to use an expression-based query (in the text-based
generic query designer).
Since you cannot execute expression-based queries directly in the query
designer, you should first design your report based on a MDX query _without_
parameters, detect the fields and design the report.
When you are done, you would convert the MDX query into an expression-based
MDX query (as you tried initially). Before doing that, you might want to add
a textbox somewhere in your report and experiment with the expression till
it evaluates to a valid MDX query and then copy the expression into the
query designer.
You might also want to check out a sample available for download:
http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-9c83-c6801f0576ff&DisplayLang=en
Additional information on the integration of RS 2000 and AS 2000 is provided
at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Karch" <Karch@.discussions.microsoft.com> wrote in message
news:AC5B0D16-5C07-4258-AF6C-EC6925180DE0@.microsoft.com...
> I looked at it again. I think the problem is that parameters are not
supported with the OLE DB provider. The report I'm generating is hitting a
cube.
> Query:
> SELECT {{[Product].[All Product]}*{Descendants([Market].[All
Market].[East - Bob Jones])}} on rows, {{[Time].[All Time].[2003-01-01
00:00:00].[2003-05-18 00:00:00], [Time].[All Time].[2004-01-01
00:00:00].[2004-05-16 00:00:00], [Time].[All Time].[2003-01-01
00:00:00].[2003-06-15 00:00:00], [Time].[All Time].[2004-01-01
00:00:00].[2004-06-13 00:00:00]}*{[Dollar Volume]}} on columns FROM @.Chain
> @.Chain is mapped to "=Parameters!Chain.Value" in the DataSet.
> Error:
> Could not generate a list of fields for the query. Check the query syntax
or click Refresh Fields on the query toolbar. The ICommandWithParameters
interface is not supported by the 'MSOLAP.2' provider. Command parameters
are unsupported with the current provider.
>
> "Ravi Mumulla (Microsoft)" wrote:
> > Did you try appending a CStr(Parameters!Chain.Value) instead?
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > "Karch" <Karch@.discussions.microsoft.com> wrote in message
> > news:A1A5AB61-FFAC-49C3-A300-290A7C85906C@.microsoft.com...
> > > I'm having problems constructing a dynamic query on the Data tab in
SQL
> > Reporting Services. When I try to use one of the reporting parameters
it
> > fails with "The expression for the query â?~Dataâ?T contains an error:
> > [BC30648] String constants must end with a double quote." I want to be
able
> > to append the value of the chain the user selected to the end of the
query.
> > Anyone have any ideas.
> > >
> > > My query is:
> > > ="SELECT {{[Product].[All Product]}*{Descendants([Market].[All
> > Market].[Bob Jones])}} on rows,
> > > {{[Time].[All Time].[2003-01-01 00:00:00].[2003-05-16 00:00:00],
> > > [Time].[All Time].[2004-01-01 00:00:00].[2004-05-16 00:00:00],
> > > [Time].[All Time].[2003-01-01 00:00:00].[2003-06-16 00:00:00],
> > > [Time].[All Time].[2004-01-01 00:00:00].[2004-06-16 00:00:00]}
> > > *{[Dollar Volume]}} on columns FROM " & Parameters!Chain.Value
> > >
> > > If I get rid of the parameter and just end the query with a hard coded
> > chain it works.
> >
> >
> >|||First Prize.
Thanks for the assistance. Sure helped me alot
"Robert Bruckner [MSFT]" wrote:
> Parameterized MDX queries are not supported by the OleDB provider for OLAP
> 8.0. Therefore you have to use an expression-based query (in the text-based
> generic query designer).
> Since you cannot execute expression-based queries directly in the query
> designer, you should first design your report based on a MDX query _without_
> parameters, detect the fields and design the report.
> When you are done, you would convert the MDX query into an expression-based
> MDX query (as you tried initially). Before doing that, you might want to add
> a textbox somewhere in your report and experiment with the expression till
> it evaluates to a valid MDX query and then copy the expression into the
> query designer.
> You might also want to check out a sample available for download:
> http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-9c83-c6801f0576ff&DisplayLang=en
> Additional information on the integration of RS 2000 and AS 2000 is provided
> at:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Karch" <Karch@.discussions.microsoft.com> wrote in message
> news:AC5B0D16-5C07-4258-AF6C-EC6925180DE0@.microsoft.com...
> > I looked at it again. I think the problem is that parameters are not
> supported with the OLE DB provider. The report I'm generating is hitting a
> cube.
> >
> > Query:
> > SELECT {{[Product].[All Product]}*{Descendants([Market].[All
> Market].[East - Bob Jones])}} on rows, {{[Time].[All Time].[2003-01-01
> 00:00:00].[2003-05-18 00:00:00], [Time].[All Time].[2004-01-01
> 00:00:00].[2004-05-16 00:00:00], [Time].[All Time].[2003-01-01
> 00:00:00].[2003-06-15 00:00:00], [Time].[All Time].[2004-01-01
> 00:00:00].[2004-06-13 00:00:00]}*{[Dollar Volume]}} on columns FROM @.Chain
> >
> > @.Chain is mapped to "=Parameters!Chain.Value" in the DataSet.
> >
> > Error:
> > Could not generate a list of fields for the query. Check the query syntax
> or click Refresh Fields on the query toolbar. The ICommandWithParameters
> interface is not supported by the 'MSOLAP.2' provider. Command parameters
> are unsupported with the current provider.
> >
> >
> >
> > "Ravi Mumulla (Microsoft)" wrote:
> >
> > > Did you try appending a CStr(Parameters!Chain.Value) instead?
> > >
> > > --
> > > Ravi Mumulla (Microsoft)
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > "Karch" <Karch@.discussions.microsoft.com> wrote in message
> > > news:A1A5AB61-FFAC-49C3-A300-290A7C85906C@.microsoft.com...
> > > > I'm having problems constructing a dynamic query on the Data tab in
> SQL
> > > Reporting Services. When I try to use one of the reporting parameters
> it
> > > fails with "The expression for the query â?~Dataâ?T contains an error:
> > > [BC30648] String constants must end with a double quote." I want to be
> able
> > > to append the value of the chain the user selected to the end of the
> query.
> > > Anyone have any ideas.
> > > >
> > > > My query is:
> > > > ="SELECT {{[Product].[All Product]}*{Descendants([Market].[All
> > > Market].[Bob Jones])}} on rows,
> > > > {{[Time].[All Time].[2003-01-01 00:00:00].[2003-05-16 00:00:00],
> > > > [Time].[All Time].[2004-01-01 00:00:00].[2004-05-16 00:00:00],
> > > > [Time].[All Time].[2003-01-01 00:00:00].[2003-06-16 00:00:00],
> > > > [Time].[All Time].[2004-01-01 00:00:00].[2004-06-16 00:00:00]}
> > > > *{[Dollar Volume]}} on columns FROM " & Parameters!Chain.Value
> > > >
> > > > If I get rid of the parameter and just end the query with a hard coded
> > > chain it works.
> > >
> > >
> > >
>
>