Change SQL (order by) at runtime?

I'm creating a fairly simple report using the ReportViewer control in
a Windows app. I have credentials and everything working, but I want
to change the SQL at run time. The dataset in the designer has this
SQL:
Select * from CPS.Main Order By Docket
Easy enough, but how can I replace that Docket with something at
runtime. I tried
Select * from CPS.Main Order By ?
and passed in a parameter. It runs, but ignores the Order By.
Am I going about this the wrong way? It's an ODBC database, not
SQLServer if that matters.First, one option for you is to allow the user to sort as they want. User
sortable columns is availabe starting in RS 2005.
Otherwise, what you want to do is to set the data source as an expression.
You are creating a string that equates to the SQL you want.
= "Select * from CPS.Main Order By " & Parameters.MyParam.Value
Be sure you have the field list you want because once you switch to this you
will not get a field list refreshed.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dgk" <dgk@.somewhere.com> wrote in message
news:rcggs31e1ni0a29noueeetlud5qugmguko@.4ax.com...
> I'm creating a fairly simple report using the ReportViewer control in
> a Windows app. I have credentials and everything working, but I want
> to change the SQL at run time. The dataset in the designer has this
> SQL:
> Select * from CPS.Main Order By Docket
> Easy enough, but how can I replace that Docket with something at
> runtime. I tried
> Select * from CPS.Main Order By ?
> and passed in a parameter. It runs, but ignores the Order By.
>
> Am I going about this the wrong way? It's an ODBC database, not
> SQLServer if that matters.|||On Fri, 29 Feb 2008 12:14:12 -0600, "Bruce L-C [MVP]"
<bruce_lcNOSPAM@.hotmail.com> wrote:
>First, one option for you is to allow the user to sort as they want. User
>sortable columns is availabe starting in RS 2005.
>Otherwise, what you want to do is to set the data source as an expression.
>You are creating a string that equates to the SQL you want.
>= "Select * from CPS.Main Order By " & Parameters.MyParam.Value
>Be sure you have the field list you want because once you switch to this you
>will not get a field list refreshed.
Not option one, each page is a separate law case, the Order By will be
putting those pages in some sort of order.
I was about to tell you that your second approach doesn't work,
because the red ! in the data tab of the designer (VS2005) is greyed
out, but it does work when switching to the Preview tab.
Thanks, problem solved.|||I should have said you don't get a field list AND you can't execute from the
dataset tab.
But, you figured that out yourself.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dgk" <dgk@.somewhere.com> wrote in message
news:53lgs3l06d1gkp5k2cgrul7c16dq7kam80@.4ax.com...
> On Fri, 29 Feb 2008 12:14:12 -0600, "Bruce L-C [MVP]"
> <bruce_lcNOSPAM@.hotmail.com> wrote:
>>First, one option for you is to allow the user to sort as they want. User
>>sortable columns is availabe starting in RS 2005.
>>Otherwise, what you want to do is to set the data source as an expression.
>>You are creating a string that equates to the SQL you want.
>>= "Select * from CPS.Main Order By " & Parameters.MyParam.Value
>>Be sure you have the field list you want because once you switch to this
>>you
>>will not get a field list refreshed.
>
> Not option one, each page is a separate law case, the Order By will be
> putting those pages in some sort of order.
> I was about to tell you that your second approach doesn't work,
> because the red ! in the data tab of the designer (VS2005) is greyed
> out, but it does work when switching to the Preview tab.
> Thanks, problem solved.
>

Change query of report in reportviewer

Hi,

I have a reporting services report that i show in an asp.net page by a reportviewer control.

Is there a property that i can set in my asp.net page, to change the query for changing the data in my report.

The number and names of the columns will be the same.

I should work with parameters, but the query changes more than just a few where-clauses. So i think working with parameters looks impossible.

Thanks,

Dennis

Hi,

using parameters is the only way to accomplish it. You can use a stored procedure in conjunction with a parameter which decicde within your stored procedue code which query to execute.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Excuse me

How Can I do a clr routine with a call to crystal report?

Thanks

Change query of report in reportviewer

Hi,

I have a reporting services report that i show in an asp.net page by a reportviewer control.

Is there a property that i can set in my asp.net page, to change the query for changing the data in my report.

The number and names of the columns will be the same.

I should work with parameters, but the query changes more than just a few where-clauses. So i think working with parameters looks impossible.

Thanks,

Dennis

Hi,

using parameters is the only way to accomplish it. You can use a stored procedure in conjunction with a parameter which decicde within your stored procedue code which query to execute.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Excuse me

How Can I do a clr routine with a call to crystal report?

Thanks