Dynamic SQL queries

Hi,
I am trying to use dynamic queries in RDL from the data tab in the report
designer. The columns to be selected and the name of the table from which to
select are supplied as report parameters. Can someone give me an example of
how to do this without using stored procedures or UDFs? Is it possible at
all? I am using the reporting services from MS SQL 2000.
Thanks in advance,
PavanI've done this a few times, but then I've used stored procedures or UDF. But
you should be able to do something similar without stored procedures. I've
done something like this with MDX, should work out with SQL queries as well.
I'd try something like this:
4 Parameters - param1, param2, param3 and param4, paramTable
Query:
="Select " & Parameters!param1.Value & " as P1, " & Parameters!param2.Value
& " as P2, " & Parameters!param3.Value & " as P3, " &
Parameters!param4.Value & " as P4 from " & Parameters!paramTable.Value& "
where 1=1"
(Everything needs to be on the "same" line, no line breaks. Test in NotePad
without wordwrap to see that everything is on the same line, even if the
lines break in the editor.)
This should give you a dataset with fields P1, P2, P3 and P4, which you now
can use in your report.
You will need to be sure that all the options are available in all tables.
Or you could do cascading parameters where you choose the table first, and
then the drop down list of all parameters gets filled with valid choices.
Kaisa M. Lindahl Lervik
"Pavan Edara" <epavan_vrce@.hotmail.com> wrote in message
news:%23ZbqDyD6GHA.4064@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I am trying to use dynamic queries in RDL from the data tab in the report
> designer. The columns to be selected and the name of the table from which
> to select are supplied as report parameters. Can someone give me an
> example of how to do this without using stored procedures or UDFs? Is it
> possible at all? I am using the reporting services from MS SQL 2000.
> Thanks in advance,
> Pavan
>|||One additional point. First have regular SQL so you get your field list.
Then change it as below. Once it is an expression the field list will not
update.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
news:%23LaW$xE6GHA.2120@.TK2MSFTNGP03.phx.gbl...
> I've done this a few times, but then I've used stored procedures or UDF.
> But you should be able to do something similar without stored procedures.
> I've done something like this with MDX, should work out with SQL queries
> as well.
> I'd try something like this:
> 4 Parameters - param1, param2, param3 and param4, paramTable
> Query:
> ="Select " & Parameters!param1.Value & " as P1, " &
> Parameters!param2.Value & " as P2, " & Parameters!param3.Value & " as P3,
> " & Parameters!param4.Value & " as P4 from " &
> Parameters!paramTable.Value& " where 1=1"
> (Everything needs to be on the "same" line, no line breaks. Test in
> NotePad without wordwrap to see that everything is on the same line, even
> if the lines break in the editor.)
> This should give you a dataset with fields P1, P2, P3 and P4, which you
> now can use in your report.
> You will need to be sure that all the options are available in all tables.
> Or you could do cascading parameters where you choose the table first, and
> then the drop down list of all parameters gets filled with valid choices.
> Kaisa M. Lindahl Lervik
> "Pavan Edara" <epavan_vrce@.hotmail.com> wrote in message
> news:%23ZbqDyD6GHA.4064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I am trying to use dynamic queries in RDL from the data tab in the report
>> designer. The columns to be selected and the name of the table from which
>> to select are supplied as report parameters. Can someone give me an
>> example of how to do this without using stored procedures or UDFs? Is it
>> possible at all? I am using the reporting services from MS SQL 2000.
>> Thanks in advance,
>> Pavan
>|||Bruce,
I am trying to follow the BOL tutorial on dynamic queries. The expression
that they have will not fit as a one-liner in the generic query designer? Do
you have a work-around so that i can preview the result? Expression below:
="SELECT c.firstname, c.lastname, e.title, d.departmentID " &"From
HumanResources.EmployeeDepartmentHistory D " &"INNER JOIN
HumanResources.Employee E " & "ON D.EmployeeID = E.EmployeeID " & "INNER JOIN
Person.Contact C " &"ON E.ContactID = C.ContactID " &
Iif(Parameters!Department.Value = 0, "", "WHERE D.DepartmentID = " &
Parameters!Department.Value) & "ORDER BY C.LastName"
Thanks!
"Bruce L-C [MVP]" wrote:
> One additional point. First have regular SQL so you get your field list.
> Then change it as below. Once it is an expression the field list will not
> update.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
> news:%23LaW$xE6GHA.2120@.TK2MSFTNGP03.phx.gbl...
> > I've done this a few times, but then I've used stored procedures or UDF.
> > But you should be able to do something similar without stored procedures.
> > I've done something like this with MDX, should work out with SQL queries
> > as well.
> >
> > I'd try something like this:
> >
> > 4 Parameters - param1, param2, param3 and param4, paramTable
> >
> > Query:
> > ="Select " & Parameters!param1.Value & " as P1, " &
> > Parameters!param2.Value & " as P2, " & Parameters!param3.Value & " as P3,
> > " & Parameters!param4.Value & " as P4 from " &
> > Parameters!paramTable.Value& " where 1=1"
> >
> > (Everything needs to be on the "same" line, no line breaks. Test in
> > NotePad without wordwrap to see that everything is on the same line, even
> > if the lines break in the editor.)
> > This should give you a dataset with fields P1, P2, P3 and P4, which you
> > now can use in your report.
> > You will need to be sure that all the options are available in all tables.
> > Or you could do cascading parameters where you choose the table first, and
> > then the drop down list of all parameters gets filled with valid choices.
> >
> > Kaisa M. Lindahl Lervik
> >
> > "Pavan Edara" <epavan_vrce@.hotmail.com> wrote in message
> > news:%23ZbqDyD6GHA.4064@.TK2MSFTNGP03.phx.gbl...
> >> Hi,
> >>
> >> I am trying to use dynamic queries in RDL from the data tab in the report
> >> designer. The columns to be selected and the name of the table from which
> >> to select are supplied as report parameters. Can someone give me an
> >> example of how to do this without using stored procedures or UDFs? Is it
> >> possible at all? I am using the reporting services from MS SQL 2000.
> >>
> >> Thanks in advance,
> >> Pavan
> >>
> >
> >
>
>|||Have a report with nothing but your parameters and a textbox. Set your
textbox to the expression so you can see the results.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:42F27824-479E-4B10-A232-083F860313EA@.microsoft.com...
> Bruce,
> I am trying to follow the BOL tutorial on dynamic queries. The expression
> that they have will not fit as a one-liner in the generic query designer?
> Do
> you have a work-around so that i can preview the result? Expression below:
> ="SELECT c.firstname, c.lastname, e.title, d.departmentID " &"From
> HumanResources.EmployeeDepartmentHistory D " &"INNER JOIN
> HumanResources.Employee E " & "ON D.EmployeeID = E.EmployeeID " & "INNER
> JOIN
> Person.Contact C " &"ON E.ContactID = C.ContactID " &
> Iif(Parameters!Department.Value = 0, "", "WHERE D.DepartmentID = " &
> Parameters!Department.Value) & "ORDER BY C.LastName"
> Thanks!
>
> "Bruce L-C [MVP]" wrote:
>> One additional point. First have regular SQL so you get your field list.
>> Then change it as below. Once it is an expression the field list will not
>> update.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
>> news:%23LaW$xE6GHA.2120@.TK2MSFTNGP03.phx.gbl...
>> > I've done this a few times, but then I've used stored procedures or
>> > UDF.
>> > But you should be able to do something similar without stored
>> > procedures.
>> > I've done something like this with MDX, should work out with SQL
>> > queries
>> > as well.
>> >
>> > I'd try something like this:
>> >
>> > 4 Parameters - param1, param2, param3 and param4, paramTable
>> >
>> > Query:
>> > ="Select " & Parameters!param1.Value & " as P1, " &
>> > Parameters!param2.Value & " as P2, " & Parameters!param3.Value & " as
>> > P3,
>> > " & Parameters!param4.Value & " as P4 from " &
>> > Parameters!paramTable.Value& " where 1=1"
>> >
>> > (Everything needs to be on the "same" line, no line breaks. Test in
>> > NotePad without wordwrap to see that everything is on the same line,
>> > even
>> > if the lines break in the editor.)
>> > This should give you a dataset with fields P1, P2, P3 and P4, which you
>> > now can use in your report.
>> > You will need to be sure that all the options are available in all
>> > tables.
>> > Or you could do cascading parameters where you choose the table first,
>> > and
>> > then the drop down list of all parameters gets filled with valid
>> > choices.
>> >
>> > Kaisa M. Lindahl Lervik
>> >
>> > "Pavan Edara" <epavan_vrce@.hotmail.com> wrote in message
>> > news:%23ZbqDyD6GHA.4064@.TK2MSFTNGP03.phx.gbl...
>> >> Hi,
>> >>
>> >> I am trying to use dynamic queries in RDL from the data tab in the
>> >> report
>> >> designer. The columns to be selected and the name of the table from
>> >> which
>> >> to select are supplied as report parameters. Can someone give me an
>> >> example of how to do this without using stored procedures or UDFs? Is
>> >> it
>> >> possible at all? I am using the reporting services from MS SQL 2000.
>> >>
>> >> Thanks in advance,
>> >> Pavan
>> >>
>> >
>> >
>>