Order by of a table passed as parameters?

Is it possible to parametrise the 'order by' options of a table/group so
that a single report could be ordered by users on run time depending of
their needs?
The alternative is to write/copy the same report once and again and have it
repeated (one instance) for each orderable column.
Regards.Sorry for this post. I just found it on RS' BOL.
Regards.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> escribió en el mensaje
news:eXv4RWQXFHA.3572@.TK2MSFTNGP12.phx.gbl...
> Is it possible to parametrise the 'order by' options of a table/group so
> that a single report could be ordered by users on run time depending of
> their needs?
> The alternative is to write/copy the same report once and again and have
> it repeated (one instance) for each orderable column.
> Regards.
>

ORDER BY - parameter

In order to allow our users to create custom report queries, we've created a report with two parameters: "Where" and "OrderBy". In the Where parameter we pass the columns to be checked (Division = 'south' AND price > 100). This works great. The "OrderBy" parameter also works, if and only if, one column name is passed. If we attempt to pass a parameter (both in the designer and URL string) formatted as "col1, col2", the preview fails when the comma is encountered. Is it not possible to create a parameter that contains more than one column for the ORDER BY clause (parameter)?
ThanksThis works, I used this type of parameter all the time. Is you query an
expression? Or are you using an @. parameter. It will only work as an
expression. To pass the parameter on the ULR it must be encoded.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
news:C26612BE-A503-44F8-9575-1B7C09332181@.microsoft.com...
> In order to allow our users to create custom report queries, we've created
a report with two parameters: "Where" and "OrderBy". In the Where parameter
we pass the columns to be checked (Division = 'south' AND price > 100). This
works great. The "OrderBy" parameter also works, if and only if, one column
name is passed. If we attempt to pass a parameter (both in the designer and
URL string) formatted as "col1, col2", the preview fails when the comma is
encountered. Is it not possible to create a parameter that contains more
than one column for the ORDER BY clause (parameter)?
> Thanks|||John.,
I changed your Where parameter name to WherePrm and added OrderBy1, OrderBy2
parameters. If needed assign default values.
SELECT TR_COID, TR_TranDate, TR_TranAmt, TR_Merchant, TR_MerchState,
TR_MerchCity, TR_MerchZip, TR_AcctCode, TR_MCC, TR_AcctNbr FROM TranDet
WHERE @.WherePrm ORDER BY @.OrderBy1, @.OrderBy2
Cem
"John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
news:7A469A37-A660-4626-A73A-09BCC1C28F46@.microsoft.com...
> Jason,
> Here is the actual statement: (again, the "where" works fine with multiple
entries)
> ="SELECT TR_COID, TR_TranDate, TR_TranAmt, TR_Merchant, TR_MerchState,
TR_MerchCity, TR_MerchZip, TR_AcctCode, TR_MCC, TR_AcctNbr FROM TranDet
WHERE (" & Parameters!Where.Value & ") ORDER BY (" &
Parameters!OrderBy.Value & ")"
> Do I need to further define whatever in the designer?
> Thanks,
> "Jason Carlson [MSFT]" wrote:
> > This works, I used this type of parameter all the time. Is you query an
> > expression? Or are you using an @. parameter. It will only work as an
> > expression. To pass the parameter on the ULR it must be encoded.
> >
> > --
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
> > news:C26612BE-A503-44F8-9575-1B7C09332181@.microsoft.com...
> > > In order to allow our users to create custom report queries, we've
created
> > a report with two parameters: "Where" and "OrderBy". In the Where
parameter
> > we pass the columns to be checked (Division = 'south' AND price > 100).
This
> > works great. The "OrderBy" parameter also works, if and only if, one
column
> > name is passed. If we attempt to pass a parameter (both in the designer
and
> > URL string) formatted as "col1, col2", the preview fails when the comma
is
> > encountered. Is it not possible to create a parameter that contains more
> > than one column for the ORDER BY clause (parameter)?
> > >
> > > Thanks
> >
> >
> >|||Remove the parens in the order by. It is not valid SQL.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
news:7A469A37-A660-4626-A73A-09BCC1C28F46@.microsoft.com...
> Jason,
> Here is the actual statement: (again, the "where" works fine with multiple
entries)
> ="SELECT TR_COID, TR_TranDate, TR_TranAmt, TR_Merchant, TR_MerchState,
TR_MerchCity, TR_MerchZip, TR_AcctCode, TR_MCC, TR_AcctNbr FROM TranDet
WHERE (" & Parameters!Where.Value & ") ORDER BY (" &
Parameters!OrderBy.Value & ")"
> Do I need to further define whatever in the designer?
> Thanks,
> "Jason Carlson [MSFT]" wrote:
> > This works, I used this type of parameter all the time. Is you query an
> > expression? Or are you using an @. parameter. It will only work as an
> > expression. To pass the parameter on the ULR it must be encoded.
> >
> > --
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "John Joslin" <JohnJoslin@.discussions.microsoft.com> wrote in message
> > news:C26612BE-A503-44F8-9575-1B7C09332181@.microsoft.com...
> > > In order to allow our users to create custom report queries, we've
created
> > a report with two parameters: "Where" and "OrderBy". In the Where
parameter
> > we pass the columns to be checked (Division = 'south' AND price > 100).
This
> > works great. The "OrderBy" parameter also works, if and only if, one
column
> > name is passed. If we attempt to pass a parameter (both in the designer
and
> > URL string) formatted as "col1, col2", the preview fails when the comma
is
> > encountered. Is it not possible to create a parameter that contains more
> > than one column for the ORDER BY clause (parameter)?
> > >
> > > Thanks
> >
> >
> >

Oracle SQL vs MSSQL SQL

How can I list out all table name of within a schema in MSSQL2005?

In Oracle, I can run this SQL to get all table_name or object_name of a users.

SQL:

SELECT TABLE_NAME FROM USER_TABLES;

SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER='<DB_USERNAME>';

SELECT OBJECT_NAME FROM USER_OBJECTS;

SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER='<DB_USERNAME>';

How to do it in MSSQL2005? any SQL cmd to execute to get the table / object list?

How to do it in MS SQL Server management studio?

I wanna have a list of table / objects under a schema by one action/script, but I DO NOT wanna to use MS SQL server management studio, check table / object name one by one.

Thanks.

Yes .. It is possible...

Select * from INFORMATION_SCHEMA.TABLES where table_schema='dbo'