Parameter Entry on a report

I'm trying to graduate from Access to SQL and am finding that Access is still meeting my needs better than SQL.

So, I figure I'm doing something wrong.

In Access, I have queries that contain parameters. So far, I've gotten SQL to accept parameters, using =@.prompt. But, this seems to be a limited parameter capability. Number one, I can't use a phrase for a prompt. Number 2, I can't do something like Access' Between [upper] And [lower]; I've not found away to use parameters as bounds in SQL.

Any help?

In your SQL query for

Between [upper] And [lower]

You need to do something like

SELECT MYPARAMETER FROM MYTABLE WHERE STARTDATE BETWEEN @.StartDate AND @.EndDate

What do you mean by

Number one, I can't use a phrase for a prompt

If you mean that you can't enter text into a parameter, you need to declare the parameter to be varchar.

|||

Hmm.

I have to enter it as SQL code, not in the builder like I used to for Access?

I am a major novice at SQL, having used Access' query builder forever.

On the "phrase for a prompt" part, I meant something like, =@.Enter Start Date:.

|||

I think you can use query builder, but I've never used it. I just write the SQL query.

|||Perhaps you've already looked at these but here is a good basic good tutorial about parameters:

http://msdn2.microsoft.com/en-us/library/aa337432.aspx

|||Thanx, mrtrombone! I think that will help a lot.

parameter entry / view report

I'm fairly new to RS, but have created some reports and deployed them.
I have parameter value list that is generate from a SELECT statement.
To be a bit more user-friendly, I'd like the user to be able to click on an
entry in the parameter list and have the sytem run the report without having
to click the View Report .
Is there a way to avoid having the user click the View Report ? It would
save a step and is something that can become annoying as the user moves from
one report to another based on the parameter chosen.
TIA,
DougIf you use the enter key the View Report button is the default. I do this
instead of clicking on it sometime.
RS does not know when you are on the last parameter, you need to either
click on view report or use the enter key.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:5F6FC4F0-F033-4B3D-9565-DE61F0FD0E34@.microsoft.com...
> I'm fairly new to RS, but have created some reports and deployed them.
> I have parameter value list that is generate from a SELECT statement.
> To be a bit more user-friendly, I'd like the user to be able to click on
> an
> entry in the parameter list and have the sytem run the report without
> having
> to click the View Report .
> Is there a way to avoid having the user click the View Report ? It would
> save a step and is something that can become annoying as the user moves
> from
> one report to another based on the parameter chosen.
> TIA,
> Doug

Page Refresh After Parameter Entry

I've been seeing an issue with several of my reports where it will
refresh the page after any parameter is entered or changed. Has anyone
else witnessed this or found a way to keep it from refreshing until the
'view report' button is clicked?
Cheers,
BenIf there are parameter dependencies, then the parameter valid values will
update. For example if parameter a is a catalog, and parameter b is a list
of products. parameter b uses 'a' as input for the query, then the page
requires a postback to run the parameter query. Is this what you are
seeing?
Thanks,
Steve MunLeeuw
"sullins602" <ben.sullins@.gmail.com> wrote in message
news:1154541094.061852.59400@.p79g2000cwp.googlegroups.com...
> I've been seeing an issue with several of my reports where it will
> refresh the page after any parameter is entered or changed. Has anyone
> else witnessed this or found a way to keep it from refreshing until the
> 'view report' button is clicked?
> Cheers,
> Ben
>|||Thanks for the reply Steve...
Yes that is how it appears, the issue here is that there are no
parameter dependencies...any idea's?
Thanks,
Ben
Steve MunLeeuw wrote:
> If there are parameter dependencies, then the parameter valid values will
> update. For example if parameter a is a catalog, and parameter b is a list
> of products. parameter b uses 'a' as input for the query, then the page
> requires a postback to run the parameter query. Is this what you are
> seeing?
> Thanks,
> Steve MunLeeuw
>
> "sullins602" <ben.sullins@.gmail.com> wrote in message
> news:1154541094.061852.59400@.p79g2000cwp.googlegroups.com...
> > I've been seeing an issue with several of my reports where it will
> > refresh the page after any parameter is entered or changed. Has anyone
> > else witnessed this or found a way to keep it from refreshing until the
> > 'view report' button is clicked?
> >
> > Cheers,
> > Ben
> >|||Hmmm, if there are no parameter dependencies, then you are correct the page
shouldn't reload. I've had trouble of parameter definitions not getting
updated when overwriting during the publish. I'm just grasping at straws
here, but I'd rename the report and publish it and test. If that doesn't
work I'd test with just a single parameter and start building them up from
there.
Steve MunLeeuw
"sullins602" <ben.sullins@.gmail.com> wrote in message
news:1155951876.839117.289800@.i3g2000cwc.googlegroups.com...
> Thanks for the reply Steve...
> Yes that is how it appears, the issue here is that there are no
> parameter dependencies...any idea's?
> Thanks,
> Ben
> Steve MunLeeuw wrote:
>> If there are parameter dependencies, then the parameter valid values will
>> update. For example if parameter a is a catalog, and parameter b is a
>> list
>> of products. parameter b uses 'a' as input for the query, then the page
>> requires a postback to run the parameter query. Is this what you are
>> seeing?
>> Thanks,
>> Steve MunLeeuw
>>
>> "sullins602" <ben.sullins@.gmail.com> wrote in message
>> news:1154541094.061852.59400@.p79g2000cwp.googlegroups.com...
>> > I've been seeing an issue with several of my reports where it will
>> > refresh the page after any parameter is entered or changed. Has anyone
>> > else witnessed this or found a way to keep it from refreshing until the
>> > 'view report' button is clicked?
>> >
>> > Cheers,
>> > Ben
>> >
>|||Do any of the parameters have their default values set to an expression? If
a parameter has an expression of any type as its default, reporting services
assumes that it could potentialy rely on other parameters, so every time a
parameter before it in the parameter list is changed, the page refreshes.
I ran into this when trying to set the default for date parameters to the
beginning and end of the current month. I found that if I created a new
dataset and calculated the default values in sql and returned each of them as
a separate column, I could set the parameter defaults to the dataset and
prevent the page from refreshing.
Hope this helps.
David
"sullins602" wrote:
> Thanks for the reply Steve...
> Yes that is how it appears, the issue here is that there are no
> parameter dependencies...any idea's?
> Thanks,
> Ben
> Steve MunLeeuw wrote:
> > If there are parameter dependencies, then the parameter valid values will
> > update. For example if parameter a is a catalog, and parameter b is a list
> > of products. parameter b uses 'a' as input for the query, then the page
> > requires a postback to run the parameter query. Is this what you are
> > seeing?
> >
> > Thanks,
> > Steve MunLeeuw
> >
> >
> > "sullins602" <ben.sullins@.gmail.com> wrote in message
> > news:1154541094.061852.59400@.p79g2000cwp.googlegroups.com...
> > > I've been seeing an issue with several of my reports where it will
> > > refresh the page after any parameter is entered or changed. Has anyone
> > > else witnessed this or found a way to keep it from refreshing until the
> > > 'view report' button is clicked?
> > >
> > > Cheers,
> > > Ben
> > >
>|||ok so the problem turned out to be that the parameters were out of
order. In the data set definition they went in A,B,C,D and in the
report definition they went D,C,B,A. Now that the two are in order it
runs w/o doing any page refreshes between paramater entry.
Thanks!
David Siebert wrote:
> Do any of the parameters have their default values set to an expression? If
> a parameter has an expression of any type as its default, reporting services
> assumes that it could potentialy rely on other parameters, so every time a
> parameter before it in the parameter list is changed, the page refreshes.
> I ran into this when trying to set the default for date parameters to the
> beginning and end of the current month. I found that if I created a new
> dataset and calculated the default values in sql and returned each of them as
> a separate column, I could set the parameter defaults to the dataset and
> prevent the page from refreshing.
> Hope this helps.
> David
> "sullins602" wrote:
> > Thanks for the reply Steve...
> >
> > Yes that is how it appears, the issue here is that there are no
> > parameter dependencies...any idea's?
> >
> > Thanks,
> > Ben
> > Steve MunLeeuw wrote:
> > > If there are parameter dependencies, then the parameter valid values will
> > > update. For example if parameter a is a catalog, and parameter b is a list
> > > of products. parameter b uses 'a' as input for the query, then the page
> > > requires a postback to run the parameter query. Is this what you are
> > > seeing?
> > >
> > > Thanks,
> > > Steve MunLeeuw
> > >
> > >
> > > "sullins602" <ben.sullins@.gmail.com> wrote in message
> > > news:1154541094.061852.59400@.p79g2000cwp.googlegroups.com...
> > > > I've been seeing an issue with several of my reports where it will
> > > > refresh the page after any parameter is entered or changed. Has anyone
> > > > else witnessed this or found a way to keep it from refreshing until the
> > > > 'view report' button is clicked?
> > > >
> > > > Cheers,
> > > > Ben
> > > >
> >
> >