Can someone suggest a good beginner's book on sql server 2005?
Use Access.
Explore how an Access Data Project works (it has a SQL Server backend).
Showing posts with label queries. Show all posts
Showing posts with label queries. Show all posts
Thursday, March 22, 2012
easy design tool
I have fairly simple table queries, and only a few dicey things, which I can handle in access. Is their a design tool for sql as easy and convenient as access?
Monday, March 19, 2012
Dynamically send query to report
hi all,
can we use our own SQL queries to display records in the report?
if report contains the Group field.
Can we do this in Crystal Report 8.5
and SQL server 2000
Thanks in Advance
Regards
Henry JonesYes u can
First of all tell me the Client side application techn... u r using
whether classic VB6 / VS2003
and u r requirement is bit unclear can u illustrate more on it
FaFa|||Make use of SQLQuery feauture
can we use our own SQL queries to display records in the report?
if report contains the Group field.
Can we do this in Crystal Report 8.5
and SQL server 2000
Thanks in Advance
Regards
Henry JonesYes u can
First of all tell me the Client side application techn... u r using
whether classic VB6 / VS2003
and u r requirement is bit unclear can u illustrate more on it
FaFa|||Make use of SQLQuery feauture
Friday, March 9, 2012
Dynamically change the DataFlow Queries
Hi Guys,
This is Ravi. I'm working on SSIS 2005 version. I have created the DTSX file from the SQL Server and executed it successfully from my .NET 2005 code.
Now I have a requirement that I need to dynamically change the Source database query. ie., based on the user selection I need to get the data from different tables of SQL and put it into an Excel file.
Can anyone help me in this..
Regards,
Ravi K. Kalyan
Mascon Global Limited.
You can only change the query if the metadata of the data-flow is unchanged thereafter.
If this is the case then read this: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx
It tells you how to dynamically alter your SQL queries.
-Jamie
|||Thanks for the reply.
I tried to modify my code using the dataflow, but cud't do it exactly the way I wanted.
I have a requirement of exporting the data into excel file using the SSIS. For that I have used Application & Package class of DTS Namespace.
I can able to load and execute the package. Now according to the user selection I need to export the data from different tables. Can I pass the source query to the package object from my .NET 2005 code? If Yes, can u please give me some sample code or any reference links.
--Kalyan
Sunday, February 26, 2012
Dynamic USE <YourDBNAME>
I know all about using dynamic queries and building queries on the fly, but I have a problem with trying to dynamically Use a database.
I have a script (or at least I'm trying to have a script) that will create (install) a database for me, then create the tables in the database.
I'm using a variable for the database name because the same database may be used for different projects on the same server. I only wanted to set the name of the database once.
I was hoping that after I created the database that I could somehow tell the script to start using that database.
Use @.mydbname is incorrect and if I use the stored proc to execute dynamically, it is not maintained after executing the proc. (Some silly thing about scope).
Does anyone have any ideas I could use?
ThanksWhere is your script stored? As a file, or as a stored procedure, or in a table?
If you are using an interface, like VB, then the simplest solution is to have the interface execute one script to create the database, switch to the new database, and then execute a second script to create the objects.
If you are running a stored procedure or an sql file, you could package the entire code as dynamic SQL and issue the USE statement in your code. sp_Execute can handle multiple statements, including USE. It is limited in size, I believe, so you would probably have to break your code up by object and inclued the USE statement in each section.
This question has come up before, so you could browse previous answers, but I have to say I have never seen a multi-database design like this that didn't result in an ongoing mess of version discrepancies, data duplication, and fragile patchworks of DTS packages to keep the whole thing moving. You'll probably tell me that this is not an option, but build scalability into your database design from day one and you will avoid a multitude of problems.|||Originally posted by rmillman
I know all about using dynamic queries and building queries on the fly, but I have a problem with trying to dynamically Use a database.
Thanks
I guess, the solution you are looking for is to use dynamic SQL. You will have to use the EXECUTE statement. Paste this code in the query analyzer, and run it:
USE master
GO
DECLARE @.DB AS VarChar(50)
SET @.DB = 'YourNewDB'
EXECUTE('CREATE DATABASE ' + @.DB)
EXECUTE('CREATE TABLE ' + @.DB + '.dbo.YourFirstNewTable(YourFirstCol int)')
-- add your other stuff
GO
Is this you are looking for?|||Originally posted by rmillman
I have a script [...] that will create (install) a database for me, then create the tables in the database.
Just checking: have you tried the -d <dbname> switch in isql ? Just like your sentence above, your batch file would have two parts. First create the database, then swith to it and use it. I would structure the BAT file as follows:
set DBNAME=mydb
isql -Q "create database .%DBNAME% ..." -S ... -U ...
isql -S ... -U ... -d %DBNAME% -i script.sql
The above executes the create database statement on the command line, so you caan substitute the variable. In the second invocation, the "script.sql" is where you create all your objects. This is pure command-line mode, so it is easily repeatable.
I have a script (or at least I'm trying to have a script) that will create (install) a database for me, then create the tables in the database.
I'm using a variable for the database name because the same database may be used for different projects on the same server. I only wanted to set the name of the database once.
I was hoping that after I created the database that I could somehow tell the script to start using that database.
Use @.mydbname is incorrect and if I use the stored proc to execute dynamically, it is not maintained after executing the proc. (Some silly thing about scope).
Does anyone have any ideas I could use?
ThanksWhere is your script stored? As a file, or as a stored procedure, or in a table?
If you are using an interface, like VB, then the simplest solution is to have the interface execute one script to create the database, switch to the new database, and then execute a second script to create the objects.
If you are running a stored procedure or an sql file, you could package the entire code as dynamic SQL and issue the USE statement in your code. sp_Execute can handle multiple statements, including USE. It is limited in size, I believe, so you would probably have to break your code up by object and inclued the USE statement in each section.
This question has come up before, so you could browse previous answers, but I have to say I have never seen a multi-database design like this that didn't result in an ongoing mess of version discrepancies, data duplication, and fragile patchworks of DTS packages to keep the whole thing moving. You'll probably tell me that this is not an option, but build scalability into your database design from day one and you will avoid a multitude of problems.|||Originally posted by rmillman
I know all about using dynamic queries and building queries on the fly, but I have a problem with trying to dynamically Use a database.
Thanks
I guess, the solution you are looking for is to use dynamic SQL. You will have to use the EXECUTE statement. Paste this code in the query analyzer, and run it:
USE master
GO
DECLARE @.DB AS VarChar(50)
SET @.DB = 'YourNewDB'
EXECUTE('CREATE DATABASE ' + @.DB)
EXECUTE('CREATE TABLE ' + @.DB + '.dbo.YourFirstNewTable(YourFirstCol int)')
-- add your other stuff
GO
Is this you are looking for?|||Originally posted by rmillman
I have a script [...] that will create (install) a database for me, then create the tables in the database.
Just checking: have you tried the -d <dbname> switch in isql ? Just like your sentence above, your batch file would have two parts. First create the database, then swith to it and use it. I would structure the BAT file as follows:
set DBNAME=mydb
isql -Q "create database .%DBNAME% ..." -S ... -U ...
isql -S ... -U ... -d %DBNAME% -i script.sql
The above executes the create database statement on the command line, so you caan substitute the variable. In the second invocation, the "script.sql" is where you create all your objects. This is pure command-line mode, so it is easily repeatable.
Friday, February 17, 2012
Dynamic SQL Queries!
An ASP application retrieves the DISTINCT records from all the columns
of a SQL Server DB table & populates them in drop-down lists. The no.
of drop-down lists on the web page depends upon the no. of columns in
the DB table. For e.g. if the DB table has, say, 5 columns, the web
page will show 5 drop-down lists.
Assume that the DB table stores information pertaining to books like
book name, category to which the book belongs to like sports, science,
music etc., author, publisher, publishing date etc.
Now suppose that a user selects an author named Author1 from the
drop-down list. When he does so, the page should get submitted & all
books that Author1 has penned should be displayed to the user. For e.g.
if Author1 has written 10 books, the user should be shown 10 records.
Now after Author1 has been selected & the appropriate records displayed
to the user, suppose the user selects an option from another drop-down
list like for e.g. the publisher drop-down list. An author can get his
books published by different publishers. When the user selects, say,
Publisher1, from the drop-down list, the user should now be displayed
all the records that Author1 has written BUT which have been published
by Publisher1 only. Now Author1 has written 10 books. Out of these 10
books, Publisher1 has published 4 books. So under such circumstances, 4
records should be retrieved & displayed to the user. The SQL query
would be
SELECT * FROM tblBooks WHERE Author='Author1' AND
Publisher='Publisher1'
The problem I am having is in adding the second WHERE clause i.e. 'AND
Publisher='Publisher1' in the SELECT query. Please note that all the
drop-down lists EXCEPT for the author drop-down list should change
again & contain only those records as options which are common to both
Author1 & Publisher1.
Arpan> The problem I am having is in adding the second WHERE clause i.e. 'AND
> Publisher='Publisher1' in the SELECT query.
So what is the problem? That query is syntactically valid so it isn't
obvious what your question is.
I would have expected separate tables for publisher and books joined by
a third table for the many-to-many relationship. Is it the join that
you have a problem with? For example:
SELECT ...
FROM tblBooks AS B
JOIN tblBookPublishers AS J
ON B.isbn = J.isbn
JOIN tblPublishers AS P
ON J.publisher_id = P.publisher_id
WHERE B.Author='Author1'
AND P.Publisher='Publisher1' ;
And by the way, books can have more than one author too, so author
probably shouldn't appear in the books table and you need at least two
more tables there.
If you need more help, please read this first:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||I do understand that your are right in saying that the records should
have been in seperate tables but the fact is the database that my
clients have wasn't created by a database expert; so they have all the
records in one table only & don't wish to seperate the records in
different tables because of time constraint.
Had related records been in different tables, there wouldn't have been
any problem but since that isn't the case, my problems have increased.
Any further suggestions?|||> Any further suggestions?
What is your question?
Please also include a CREATE TABLE statement if your question is about
a query.
David Portas
SQL Server MVP
--
of a SQL Server DB table & populates them in drop-down lists. The no.
of drop-down lists on the web page depends upon the no. of columns in
the DB table. For e.g. if the DB table has, say, 5 columns, the web
page will show 5 drop-down lists.
Assume that the DB table stores information pertaining to books like
book name, category to which the book belongs to like sports, science,
music etc., author, publisher, publishing date etc.
Now suppose that a user selects an author named Author1 from the
drop-down list. When he does so, the page should get submitted & all
books that Author1 has penned should be displayed to the user. For e.g.
if Author1 has written 10 books, the user should be shown 10 records.
Now after Author1 has been selected & the appropriate records displayed
to the user, suppose the user selects an option from another drop-down
list like for e.g. the publisher drop-down list. An author can get his
books published by different publishers. When the user selects, say,
Publisher1, from the drop-down list, the user should now be displayed
all the records that Author1 has written BUT which have been published
by Publisher1 only. Now Author1 has written 10 books. Out of these 10
books, Publisher1 has published 4 books. So under such circumstances, 4
records should be retrieved & displayed to the user. The SQL query
would be
SELECT * FROM tblBooks WHERE Author='Author1' AND
Publisher='Publisher1'
The problem I am having is in adding the second WHERE clause i.e. 'AND
Publisher='Publisher1' in the SELECT query. Please note that all the
drop-down lists EXCEPT for the author drop-down list should change
again & contain only those records as options which are common to both
Author1 & Publisher1.
Arpan> The problem I am having is in adding the second WHERE clause i.e. 'AND
> Publisher='Publisher1' in the SELECT query.
So what is the problem? That query is syntactically valid so it isn't
obvious what your question is.
I would have expected separate tables for publisher and books joined by
a third table for the many-to-many relationship. Is it the join that
you have a problem with? For example:
SELECT ...
FROM tblBooks AS B
JOIN tblBookPublishers AS J
ON B.isbn = J.isbn
JOIN tblPublishers AS P
ON J.publisher_id = P.publisher_id
WHERE B.Author='Author1'
AND P.Publisher='Publisher1' ;
And by the way, books can have more than one author too, so author
probably shouldn't appear in the books table and you need at least two
more tables there.
If you need more help, please read this first:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||I do understand that your are right in saying that the records should
have been in seperate tables but the fact is the database that my
clients have wasn't created by a database expert; so they have all the
records in one table only & don't wish to seperate the records in
different tables because of time constraint.
Had related records been in different tables, there wouldn't have been
any problem but since that isn't the case, my problems have increased.
Any further suggestions?|||> Any further suggestions?
What is your question?
Please also include a CREATE TABLE statement if your question is about
a query.
David Portas
SQL Server MVP
--
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
>> >>
>> >
>> >
>>
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
>> >>
>> >
>> >
>>
Subscribe to:
Posts (Atom)