Including Report Parameters in SQL Query

I have created a DAtaset for using in my report. Everything works. My SQL
contains Top 4 ... which I want to pass as a parameter.
I created a report parameter called "Months". How can I replace top 4 by top
(parameters!months.value) '
However I try to include the parameter in my SQL query it fails to work.I think in this case you need to use an expression for the source. Use the
generic query designer and do this:
= "some sql statements blah blah Top " & parameters!Months.value & " rest of
SQL Statement"
Remember that when refering to a parameter it is case sensitive so it much
match exactly.
When I do this I will sometimes first work out the expression using a report
with a single textbox and my parameters and set the textbox to the
expression so I can see it prior to using it for the source of the dataset.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in message
news:0A77BFEC-384D-4808-AAFC-153E3BA6D487@.microsoft.com...
>I have created a DAtaset for using in my report. Everything works. My SQL
> contains Top 4 ... which I want to pass as a parameter.
> I created a report parameter called "Months". How can I replace top 4 by
> top
> (parameters!months.value) '
> However I try to include the parameter in my SQL query it fails to work.|||Thanks fror your reply.
I have tried that. What I don't understand is where should I stop the SQL
String with " ? I have created the query in VS 2003 on the data tab with the
grafical query creator (looking like SQL Enterprise manager).
There is no SQL String inside quotation marks I could interrupt, enter the
parameter and continue the sql string with quotation marks. Do you get my
problem?
markus
"Bruce L-C [MVP]" wrote:
> I think in this case you need to use an expression for the source. Use the
> generic query designer and do this:
> = "some sql statements blah blah Top " & parameters!Months.value & " rest of
> SQL Statement"
> Remember that when refering to a parameter it is case sensitive so it much
> match exactly.
> When I do this I will sometimes first work out the expression using a report
> with a single textbox and my parameters and set the textbox to the
> expression so I can see it prior to using it for the source of the dataset.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in message
> news:0A77BFEC-384D-4808-AAFC-153E3BA6D487@.microsoft.com...
> >I have created a DAtaset for using in my report. Everything works. My SQL
> > contains Top 4 ... which I want to pass as a parameter.
> >
> > I created a report parameter called "Months". How can I replace top 4 by
> > top
> > (parameters!months.value) '
> >
> > However I try to include the parameter in my SQL query it fails to work.
>
>|||The issue is that you are in the graphical query instead of the generic
query designer. There is a button (hover over them) to switch to the other
designer. Then put an = sign in from of it.
For instance, let's say that your query looks like this:
select * from sometable
Click on the button (to the right of the ...). Now do this:
= "select * from sometable"
It should work for you. Now modify it to include your parameter as I had it
below.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in message
news:E1EEB69E-9C07-471F-8A0D-D05765EEE453@.microsoft.com...
> Thanks fror your reply.
> I have tried that. What I don't understand is where should I stop the SQL
> String with " ? I have created the query in VS 2003 on the data tab with
> the
> grafical query creator (looking like SQL Enterprise manager).
> There is no SQL String inside quotation marks I could interrupt, enter the
> parameter and continue the sql string with quotation marks. Do you get my
> problem?
> markus
> "Bruce L-C [MVP]" wrote:
>> I think in this case you need to use an expression for the source. Use
>> the
>> generic query designer and do this:
>> = "some sql statements blah blah Top " & parameters!Months.value & " rest
>> of
>> SQL Statement"
>> Remember that when refering to a parameter it is case sensitive so it
>> much
>> match exactly.
>> When I do this I will sometimes first work out the expression using a
>> report
>> with a single textbox and my parameters and set the textbox to the
>> expression so I can see it prior to using it for the source of the
>> dataset.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in
>> message
>> news:0A77BFEC-384D-4808-AAFC-153E3BA6D487@.microsoft.com...
>> >I have created a DAtaset for using in my report. Everything works. My
>> >SQL
>> > contains Top 4 ... which I want to pass as a parameter.
>> >
>> > I created a report parameter called "Months". How can I replace top 4
>> > by
>> > top
>> > (parameters!months.value) '
>> >
>> > However I try to include the parameter in my SQL query it fails to
>> > work.
>>|||Ok, I found it. I changed my SQL to:
= "SELECT TOP " & parameters!anzMonate.Value & "
dbo.v_Calls_Statistik_Sum.*, Jahr, Monat FROM dbo.v_Calls_Statistik_Sum
ORDER BY Jahr DESC, Monat DESC"
But after editing my SQL I get an error:
"Der Ausdruck für das query-Objekt ´Calls´ enthält einen Fehler: [BC30648]
Zeichenfolgenliterale müssen mit einem doppelten Anführungszeichen enden."
~
"The expression for Object 'DATASETNAME' contains error BC30648. Literals
have to end with double quotation marks."
My Parameter is an integer. Why or where do I have to use quotation marks?
Thank you so much,
markus
"Bruce L-C [MVP]" wrote:
> The issue is that you are in the graphical query instead of the generic
> query designer. There is a button (hover over them) to switch to the other
> designer. Then put an = sign in from of it.
> For instance, let's say that your query looks like this:
> select * from sometable
> Click on the button (to the right of the ...). Now do this:
> = "select * from sometable"
> It should work for you. Now modify it to include your parameter as I had it
> below.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in message
> news:E1EEB69E-9C07-471F-8A0D-D05765EEE453@.microsoft.com...
> > Thanks fror your reply.
> >
> > I have tried that. What I don't understand is where should I stop the SQL
> > String with " ? I have created the query in VS 2003 on the data tab with
> > the
> > grafical query creator (looking like SQL Enterprise manager).
> > There is no SQL String inside quotation marks I could interrupt, enter the
> > parameter and continue the sql string with quotation marks. Do you get my
> > problem?
> > markus
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> I think in this case you need to use an expression for the source. Use
> >> the
> >> generic query designer and do this:
> >> = "some sql statements blah blah Top " & parameters!Months.value & " rest
> >> of
> >> SQL Statement"
> >>
> >> Remember that when refering to a parameter it is case sensitive so it
> >> much
> >> match exactly.
> >>
> >> When I do this I will sometimes first work out the expression using a
> >> report
> >> with a single textbox and my parameters and set the textbox to the
> >> expression so I can see it prior to using it for the source of the
> >> dataset.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "MarkusPoehler" <MarkusPoehler@.discussions.microsoft.com> wrote in
> >> message
> >> news:0A77BFEC-384D-4808-AAFC-153E3BA6D487@.microsoft.com...
> >> >I have created a DAtaset for using in my report. Everything works. My
> >> >SQL
> >> > contains Top 4 ... which I want to pass as a parameter.
> >> >
> >> > I created a report parameter called "Months". How can I replace top 4
> >> > by
> >> > top
> >> > (parameters!months.value) '
> >> >
> >> > However I try to include the parameter in my SQL query it fails to
> >> > work.
> >>
> >>
> >>
>
>|||MarkusPoehler wrote:
> = "SELECT TOP " & parameters!anzMonate.Value & "
> dbo.v_Calls_Statistik_Sum.*, Jahr, Monat FROM
> dbo.v_Calls_Statistik_Sum ORDER BY Jahr DESC, Monat DESC"
> But after editing my SQL I get an error:
> "Der Ausdruck für das query-Objekt ´Calls´ enthält einen Fehler:
> [BC30648] Zeichenfolgenliterale müssen mit einem doppelten
> Anführungszeichen enden." ~
Markus,
schreib das mal so:
write it in that way:
= "SELECT TOP " & parameters!anzMonate.Value & "
dbo.v_Calls_Statistik_Sum.*, " +
" Jahr, Monat FROM dbo.v_Calls_Statistik_Sum ORDER BY Jahr DESC, Monat DESC"
Du muss den ganzen String quoten
You have to quote the whole string with "
best regards
Frank
--
www.xax.de|||Frank Matthiesen wrote:
nochmal wg. Umbruch
= "SELECT TOP " +
" & parameters!anzMonate.Value & " +
" dbo.v_Calls_Statistik_Sum.*, " +
" Jahr, Monat FROM dbo.v_Calls_Statistik_Sum " +
" ORDER BY Jahr DESC, Monat DESC"
gruss
frank|||Hi Frank,
Hi Fränk, :)
vielen Dank, das war ein entscheidender Hinweis. Darauf wäre ich nie
gekommen, dass dieser Text-SQL Editor plötzlich Zeilen unterscheidet.
Thank you! This was the crux.
Markus
"Frank Matthiesen" wrote:
> MarkusPoehler wrote:
> > = "SELECT TOP " & parameters!anzMonate.Value & "
> > dbo.v_Calls_Statistik_Sum.*, Jahr, Monat FROM
> > dbo.v_Calls_Statistik_Sum ORDER BY Jahr DESC, Monat DESC"
> >
> > But after editing my SQL I get an error:
> >
> > "Der Ausdruck für das query-Objekt ´Calls´ enthält einen Fehler:
> > [BC30648] Zeichenfolgenliterale müssen mit einem doppelten
> > Anführungszeichen enden." ~
>
> Markus,
> schreib das mal so:
> write it in that way:
> = "SELECT TOP " & parameters!anzMonate.Value & "
> dbo.v_Calls_Statistik_Sum.*, " +
> " Jahr, Monat FROM dbo.v_Calls_Statistik_Sum ORDER BY Jahr DESC, Monat DESC"
> Du muss den ganzen String quoten
> You have to quote the whole string with "
> best regards
> Frank
> --
> www.xax.de
>
>
>