Report Parameters - Display Boolean Type As CheckBox

I have created a Report Parameter, and set the type of this to "Boolean".

This is displayed as a RadioButton with the options of True or False.

Is there anyway to change this to be displayed as a CheckBox?

Thanks,

Kate

See this thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=562091&SiteID=1

Report Parameters - Decimal vs. Float

My dataset returns data from a range of effective dates for a single
month. In the database, these dates are unfortunately not of type
datetime, but are decimal and are in the YYYYMMDD format.
I'm having trouble getting the report to execute because at the query
level, the datatype has to be decimal. I need to parameterize these
dates, but there is no decimal datatype for parameters.
I have a dataset that generates the necessary dates based on the type
of report the user wants (60/90/120 day) (if they want a 60-day report,
the effective date = 2 months from the first day of the current month,
etc).
SELECT DISTINCT
CONVERT(datetime,LEFT(CAST(effdte AS varchar), 4) + '/' +
SUBSTRING(CAST(effdte AS varchar), 5, 2) + '/' + '01') +
@.period_additive AS effdte_low,
CONVERT(datetime,LEFT(CAST(effdte AS varchar), 4) + '/' +
SUBSTRING(CAST(effdte AS varchar), 5, 2) + '/' + '31') +
@.period_additive AS effdte_high
FROM dbo.t_policy
WHERE YEAR(GETDATE()) - 1 = LEFT(CAST(effdte AS varchar), 4) AND
MONTH(GETDATE()) = SUBSTRING(CAST(effdte AS varchar), 5, 2) AND
LEFT(policy, 3) LIKE @.dept_cd AND LEFT(policy, 3) <> 'LPA'
What would the appropriate code be to do this within the Report
Parameters dialog instead? It seems like I'd have a better chance of
success if it were done that way.
Thanks!
MikeNo matter what, I get this error at runtime (Preview mode):
--
Processing Errors
--
An error has occurred during report processing.
Cannot read the next data row for the data set ds_main.
Arithmetic overflow error converting expression to data type datetime.
--
OK
--|||I think I fixed it...
Even though the database type is decimal, when searching by that range,
I have to put single quotes around it. Therefore, I converted the date
type back to a varchar and changed the parameter data types to string.
Now the report runs.
But there should be a more straightforward way to do this within the
Report Parameters dialog box, shouldn't there?sql

Report Parameters - can you export to Excel

When I export my report to Excel I would also like it to carry the
report parameters with it - put it up the top or on a separate sheet.
Is this possible?
PhilYou can in your report designer.
Add a Table header.
And then go to Expressions -> parameters => and place them|||Hi Phil,
what we do is put a table of the parameters at the top of a report and
hide it with a toggle button at the top...our standard report designs
include a set of toggle buttons at the top to toggle visible/invisible
sections of the reports...so we might have 2 or 4 charts and then 2 or
4 tables on the report which are all rendered but some are
hidden...then by pressing the tabs they appear/disappear...this closely
emulates the excel worksheets for reports and the business objects
worksheets......if we went further we could appear/disappear sets of
reports based on toggle buttons...
We wanted the parameters on the top of the report because when it is
printed you need the parameters to tell you what you are looking
at....and of course, when you export to excel all this stuff goes into
the workbook.
Though I have noticed the colours do not translate very well into
workbook..red turns into a horrible grey colour so far...though I am
sure htat will be fixed...
Peter|||thanks. Not ideal because then they effectively show twice in the main
report viewer but at least they go into the report.
thanks again|||good idea - i might try that
thanks|||sorry, not clear who i was replying to - first reply to first one,
second to second etc|||Hi Phil,
yes, we default them hidden on the report so the user does not see them
unless he/she specifically opens the parameters as he/she would do if
he/she wanted to print the report.
The interesting thing was that when sent to excel the hidden portions
of the report are always sent....I mean, that;s the most sensible
thing to do, but it is not always the case that programmers do the most
sensible thing..LOL!!
We quite like this style of solution or presenting the parameters...if
you come up with something better let us know!!!
Best Regards
Peter Nolan
www.peternolan.com

Report Parameters

Is there a way to force report designer overlook parameter dependencies, so that a report could be displayed by either of the two parameters although one parameter accepts but not requires the other?

For Example:

In my report I have two parameters, DeptID and ProjectID. The two drop downs display Departments and Projects respectively but only if the department is selected, the Projects list becomes active as its expecting a DepartmentID in the stored proc but accepts a null value as well. So, what I would like to achieve is display ALL projects in the Projects list, even if no department is selected. Any ideas how to do that?

Thanks.

Essentially what I need is to be able to filter the Projects List and not necessarily depend on the DepartmentID of the selected value in Department List. Kind of like filtering on Report Parameters without using Cascading Parameters?|||

Hello,

why can't you have all Departments selected as default. The the user does not have to select departments id actively in order to select projects.

Report Parameters

In the report parameters dialogue box I have set up a Non Queried list, when
someone selects Grand Rapids(label) I want to return multiple values, 21 OR
22 OR 23, these are the Grand Rapids Department ID's. Can this be done? If
so what is the correct syntax?
Label Value
Grand Rapids 21 OR 22 OR 23
WHERE (Acclaim.Staff.DepartmentID = @.DepartmentID)Why not make it a string parameter that returns "21,22,23"
and make the where clause like:
WHERE (Acclaim.Staff.DepartmentID in @.DepartmentID)
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sampson" <Sampson@.discussions.microsoft.com> wrote in message
news:F361919F-A515-4AE2-B7FA-0D68B6842542@.microsoft.com...
> In the report parameters dialogue box I have set up a Non Queried list,
when
> someone selects Grand Rapids(label) I want to return multiple values, 21
OR
> 22 OR 23, these are the Grand Rapids Department ID's. Can this be done?
If
> so what is the correct syntax?
> Label Value
> Grand Rapids 21 OR 22 OR 23
>
> WHERE (Acclaim.Staff.DepartmentID = @.DepartmentID)
>|||That will not work.You would think it would but it doesn't (most likely on
purpose to prevent injection attacks). You can do a dynamic query. Go into
generic query mode and do this:
= "Select blah from sometable where (Acclaim.Staff.DepartmentID in (" &
Parameters!DepartmentID.value & ")"
Of course if it is not a list of integers you then need to add parse this
(you could write code behind to do this) and put in the single quotes.
The other alternative is pass the parameter to a stored procedure and do it
from there.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uAOOxf2RFHA.2528@.TK2MSFTNGP10.phx.gbl...
> Why not make it a string parameter that returns "21,22,23"
> and make the where clause like:
> WHERE (Acclaim.Staff.DepartmentID in @.DepartmentID)
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Sampson" <Sampson@.discussions.microsoft.com> wrote in message
> news:F361919F-A515-4AE2-B7FA-0D68B6842542@.microsoft.com...
> > In the report parameters dialogue box I have set up a Non Queried list,
> when
> > someone selects Grand Rapids(label) I want to return multiple values, 21
> OR
> > 22 OR 23, these are the Grand Rapids Department ID's. Can this be done?
> If
> > so what is the correct syntax?
> >
> > Label Value
> > Grand Rapids 21 OR 22 OR 23
> >
> >
> > WHERE (Acclaim.Staff.DepartmentID = @.DepartmentID)
> >
>

Report Parameters

I am using report parameters that are non-queried. I want one parameter
label to be "All" and I want it to act as a wildcard. I tried giving it a
value of % and setting the Data Type to String instead of integer. It didn't
work though. What value will act as a wildcard and include all integers
pulled from my database.Chandler,
If it is only one parameter that you want to act as "all" only, then you can
just not use it at all... By default all records will be selected.
If it is a parameter of type integer that can have
- either integer values, which will filter by that specific value
- or something that will mean "all" - here, you could use blank or null
for that purpose.
And the where clause could look like that:
where [...] and YourIntField like (case when Isnull(@.YourIntParam,0) = 0
then '%' else @.YourIntParam end)
This will work, of course, if there are no values of zero in your field (I
mean if you don't pass on purpose the value zero for the parameter).
Otherwise, just choose a different value to compare to, instead of 0.
HTH,
Andrei.
"Chandler" <Chandler@.discussions.microsoft.com> wrote in message
news:137E11DC-3EE7-4DC8-B6D4-BF4607A7E585@.microsoft.com...
> I am using report parameters that are non-queried. I want one parameter
> label to be "All" and I want it to act as a wildcard. I tried giving it a
> value of % and setting the Data Type to String instead of integer. It
didn't
> work though. What value will act as a wildcard and include all integers
> pulled from my database.

Report Parameters

I have a report with a parameter set up for Account name. When I use
the drop down I do display the 5 accounts associated with the 5 rows
data. (One shows in the parameter drop down 3 times because it has
data for 3 different rows.)
Account 1
Account 1
Account 1
Account 2
Account 3
My question is, what else do I need to make the parameter at the top
work, currently my report is not filtering the data by Account. I
still see all 5 rows of data. Do I need a report filter as well?On Oct 1, 10:25 am, BLAW <brad...@.gmail.com> wrote:
> I have a report with a parameter set up for Account name. When I use
> the drop down I do display the 5 accounts associated with the 5 rows
> data. (One shows in the parameter drop down 3 times because it has
> data for 3 different rows.)
> Account 1
> Account 1
> Account 1
> Account 2
> Account 3
> My question is, what else do I need to make the parameter at the top
> work, currently my report is not filtering the data by Account. I
> still see all 5 rows of data. Do I need a report filter as well?
If I understand you correctly, you will want to set your dataset from
the Data tab to a variable. Something like:
select x, y, z, ... from table_x where AccountName = @.AccountName
Then in the Parameters tab (select via Edit Selected Dataset [...] ->
Parameters tab) set the variable @.AccountName (below Parameters:Name)
to Value: =Parameters!AccontName.Value
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultantsql

Report Parameters

I have a stored procedure which accepts a CustomerID parameter of type
char(3). If this parameter is null or blank, then data for all customers is
returned, otherwise just for the selected customer. This works as exepected
in query analyser.
pr_CustomerReport NULL OR
pr_CustomerReport ''
both return data for all customers
pr_CustomerReport 'FDR' returns data for selected customer.
I am trying to get a report to pass a parameter to this stored procedure
from a drop down list. If I select a parameter value in the drop down list I
get the data for the selected customer, but if I leave the parameter with no
selection I get nothing, but I KNOW the sp works correctly with blank or
null parameters.
In report parameters I have ticked both allow null value and allow blank
value. Why won't the report display anything? How can I find out if the
report is even attempting to run the stored procedure (I suspect it isn't)?
Is there anything else I need to do to get the report to run with an
unselected parameter?
Thanks for any help
KenYour drop down list is populated from the query. That query should return
null (dbnull) as one of the values so that you can select it later. You
can't just select nothing and assume it is null.
By selecting "allow null" and "allow blank" you say that your stored proc
(or query) can potentially accepts nulls (will not crash). By _not_
providing null as one of the valid values you say that null is _not_ valid
in the current situation and therefore can't be selected.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
"Ken Cooper" <_k.a.cooper_@._shu.ac.uk_> wrote in message
news:ulHvEjrYEHA.2812@.TK2MSFTNGP11.phx.gbl...
> I have a stored procedure which accepts a CustomerID parameter of type
> char(3). If this parameter is null or blank, then data for all customers
is
> returned, otherwise just for the selected customer. This works as
exepected
> in query analyser.
> pr_CustomerReport NULL OR
> pr_CustomerReport ''
> both return data for all customers
> pr_CustomerReport 'FDR' returns data for selected customer.
> I am trying to get a report to pass a parameter to this stored procedure
> from a drop down list. If I select a parameter value in the drop down list
I
> get the data for the selected customer, but if I leave the parameter with
no
> selection I get nothing, but I KNOW the sp works correctly with blank or
> null parameters.
> In report parameters I have ticked both allow null value and allow blank
> value. Why won't the report display anything? How can I find out if the
> report is even attempting to run the stored procedure (I suspect it
isn't)?
> Is there anything else I need to do to get the report to run with an
> unselected parameter?
> Thanks for any help
> Ken
>

Report Parameters

I am using stored procedures and I would like an input parameter to use a
different stored procedure from the report. Query access seems to be on the
report stored proc. I also don't want to run the report untill the user
selects parameters since it is long.
Thanks,
maaOn Nov 4, 7:30 am, maa <m...@.discussions.microsoft.com> wrote:
> I am using stored procedures and I would like an input parameter to use a
> different stored procedure from the report. Query access seems to be on the
> report stored proc. I also don't want to run the report untill the user
> selects parameters since it is long.
> Thanks,
> maa
In the Data tab of the BIDS environment, select the drop-down list box
to the right of 'Dataset:' and select '<New Dataset...>' change
'Command type:' to StoredProcedure and below 'Query string:' enter in
the new stored procedure that you want to use. Then select the
'Report' drop-down tab and select 'Report Parameters...' >> select the
'Add' button and below 'Available values:' select the dataset you just
created below 'Dataset:' The 'Value field:' will be what is used in
the report or elsewhere in another stored procedure (sometimes an
identity field in a table or primary key of sorts: but not always) and
the 'Label field:' is what is shown to the user to select from. To
delay the report running until after the parameter is selected, below
the 'Default values:' during the parameter creation process select
'Null.' Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Report Parameters

Hi,
Is it possible to have report parameter of string type displayed as a text
box instead of dropdown ? This will enable the user to type-in the parameter
value rather than choosing one. I have one more question. Is report
parameter of type String always displayed as a drop down list ? Can I have
checkboxes as report parameters ? These question are very basic. Iam new to
SSRS. Please provide help.
Thanks,
RKOn Oct 17, 5:48 am, "S V Ramakrishna"
<ramakrishna.seeth...@.translogicsys.com> wrote:
> Hi,
> Is it possible to have report parameter of string type displayed as a text
> box instead of dropdown ? This will enable the user to type-in the parameter
> value rather than choosing one. I have one more question. Is report
> parameter of type String always displayed as a drop down list ? Can I have
> checkboxes as report parameters ? These question are very basic. Iam new to
> SSRS. Please provide help.
> Thanks,
> RK
Regardless of datatype, anytime you provide a choice of available
values, whether non-queried or queried, you'll be presented with the
drop down. The drop down is the only option for displaying available
values. The only time you'll see checkboxes is if you select the
Multi-Value option and even then the checkboxes appear by way of the
drop down.
If you want your users to enter the parameter value in a text box, you
will not be able to offer available values. There is a slight caveat
to my last statement. You can use the prompt to suggest potential
values to be entered into the text box. For example, you could have
your prompt read, "Enter one of the following values: Yes; No; Maybe."
HTH

Report parameters

I'm using a query to populate a drop down list for a report parameter. E.g. a
number of stores. I'm using this to get sales figures per store.
Now I need a way to show the sales figures for all stores. A user should for
example be able to skip the stores selection list to select them all...
I haven't figured out yet how to skip this, or some other solution to get
every store. When I do not select a store, RS complains there's no store
selected. Even when I select "allow null value" in the parameter options...
Can someone help me?Hi Andreas,
look here in the Newsgroup :
Report Parameters with a choice of "All"
mfg Georg
"Andreas" <Andreas@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BC84B219-9A78-4E5F-8FC1-5CC1A4C175D1@.microsoft.com...
> I'm using a query to populate a drop down list for a report parameter.
E.g. a
> number of stores. I'm using this to get sales figures per store.
> Now I need a way to show the sales figures for all stores. A user should
for
> example be able to skip the stores selection list to select them all...
> I haven't figured out yet how to skip this, or some other solution to get
> every store. When I do not select a store, RS complains there's no store
> selected. Even when I select "allow null value" in the parameter
options...
> Can someone help me?
>|||thx georg
"Georg Schmelzer" wrote:
> Hi Andreas,
> look here in the Newsgroup :
> Report Parameters with a choice of "All"
>
> mfg Georg
>
>
> "Andreas" <Andreas@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:BC84B219-9A78-4E5F-8FC1-5CC1A4C175D1@.microsoft.com...
> > I'm using a query to populate a drop down list for a report parameter.
> E.g. a
> > number of stores. I'm using this to get sales figures per store.
> > Now I need a way to show the sales figures for all stores. A user should
> for
> > example be able to skip the stores selection list to select them all...
> > I haven't figured out yet how to skip this, or some other solution to get
> > every store. When I do not select a store, RS complains there's no store
> > selected. Even when I select "allow null value" in the parameter
> options...
> >
> > Can someone help me?
> >
>
>

Report parameters

I have a report that reads from a view and in the report parameters I am
using "like <param value>%". The problem is that some of the values in the
datbase are nulls and I need those returned as well as the non null values.
Any help on this would be greatly appreciated.
--
JerryJust change your SQL
select * from sometable where somefield like 'blah%' or somefield is null
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jerry" <Jerry@.jerry.com> wrote in message
news:AFF5F532-3E8A-4573-9B80-F329BD53B04A@.microsoft.com...
> I have a report that reads from a view and in the report parameters I am
> using "like <param value>%". The problem is that some of the values in
the
> datbase are nulls and I need those returned as well as the non null
values.
> Any help on this would be greatly appreciated.
> --
> Jerry|||I am using a view in Sql Server and adding a where clause in the view is no
problem and it works fine when you run the view. However I am using URL
Access in Sql Reporting Services and it is not working. IN the books online,
under "parameters, Reporting Services" is says the following:
If one of the values is null (that is, isnull), all other values specified
for that same parameter are ignored.
So if I enter &SomeField=%, it ignores the records with null values.
If use &SomeField=%&SomeField:isnull=true it returns nothing.
Is there a way to "OR" these together using URL Access?
Thanks for your help.
Jerry
"Bruce L-C [MVP]" wrote:
> Just change your SQL
> select * from sometable where somefield like 'blah%' or somefield is null
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jerry" <Jerry@.jerry.com> wrote in message
> news:AFF5F532-3E8A-4573-9B80-F329BD53B04A@.microsoft.com...
> > I have a report that reads from a view and in the report parameters I am
> > using "like <param value>%". The problem is that some of the values in
> the
> > datbase are nulls and I need those returned as well as the non null
> values.
> > Any help on this would be greatly appreciated.
> > --
> > Jerry
>
>|||You have two different things here. One is URL and passing the parameter. If
your parameter allows nulls then you should be able to do this. Next is
getting your report to work with a null value. My suggestion is to first get
this to work from Report Manager before you try with URL. You have to have
two things for this to work. First, the parameter needs to allow nulls.
Second, the query itself that needs to be modified to work with Null. What I
showed you below was how to modify the query to work with null values.
I'm not sure why you are trying to send two values with the URL. You should
only be sending one value. I am a little confused on what you are wanting. I
thought what you wanted was to provide a value and see all records that have
that value for the field AND show all records where the value for the field
is null (For instance, lets say I had a field call Color, I want to see all
values where the color is blue or the color is not specified (is null)).
Then the query would be:
select * from mytable where color = @.colorparam or color is null
Note that the field name and the parameter name can be the same but they do
not have to be. There is a mapping between query parameter and report
parameter, make sure you understand this point.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jerry" <Jerry@.jerry.com> wrote in message
news:B163FD9B-BFF9-46B7-A8F9-7A5863E43F88@.microsoft.com...
> I am using a view in Sql Server and adding a where clause in the view is
no
> problem and it works fine when you run the view. However I am using URL
> Access in Sql Reporting Services and it is not working. IN the books
online,
> under "parameters, Reporting Services" is says the following:
> If one of the values is null (that is, isnull), all other values specified
> for that same parameter are ignored.
> So if I enter &SomeField=%, it ignores the records with null values.
> If use &SomeField=%&SomeField:isnull=true it returns nothing.
> Is there a way to "OR" these together using URL Access?
> Thanks for your help.
> Jerry
>
> "Bruce L-C [MVP]" wrote:
> > Just change your SQL
> >
> > select * from sometable where somefield like 'blah%' or somefield is
null
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Jerry" <Jerry@.jerry.com> wrote in message
> > news:AFF5F532-3E8A-4573-9B80-F329BD53B04A@.microsoft.com...
> > > I have a report that reads from a view and in the report parameters I
am
> > > using "like <param value>%". The problem is that some of the values
in
> > the
> > > datbase are nulls and I need those returned as well as the non null
> > values.
> > > Any help on this would be greatly appreciated.
> > > --
> > > Jerry
> >
> >
> >sql

Report Parameters

hello
i am building a report which is going to show me list of candidates from
different groups for that i have created a qurey that display group list. and
a qurey display orginal data (candidate list) my problem is i can only select
one group at time for report parameter from group list can i do multi select
from that report pls any one help me out
Adnan AwanOn Oct 4, 10:30 am, Adnan Awan <AdnanA...@.discussions.microsoft.com>
wrote:
> hello
> i am building a report which is going to show me list of candidates from
> different groups for that i have created a qurey that display group list. and
> a qurey display orginal data (candidate list) my problem is i can only select
> one group at time for report parameter from group list can i do multi select
> from that report pls any one help me out
> Adnan Awan
If I'm understanding you correctly, you can create a multi-select
parameter via the Report tab -> Report Parameters... Then in the
stored procedure/query that is sourcing the report, you can create a
while loop to split the multiselect items in the input stored
procedure parameter (normally delimited by commas). You could load a
temp table w/these values and then base your query on ...'where xxxxx
not in (select xxxxx from #SomeTempTable)', etc. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

report parameters

Is it possible to set the properties of report parameters? I want to
show/hide parameters at run-time.
--
asharmaOn Apr 12, 4:10 am, asharma2004 <asha...@.harbingertechaxes.com> wrote:
> Is it possible to set the properties of report parameters? I want to
> show/hide parameters at run-time.
> --
> asharma
As far as I know, it is not. That said, you can allow for a default
blank value as part of your dataset that is sourcing your report
parameter. Also, if calling the report/RDL from w/i an ASP.NET or
WinForms application, you can have non-report drop-downs available to
the users and just pass hidden parameter values to the report based on
them. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Report parameters

Hi everybody.
I have one dataset with parameter 'UserID' and second Dataset which fill
UserID parameter. When i run my report i can choose value from second
Dataset, but i wont that in my Parameter will be present value as 'All' which
mean all records from query. It is possible? if yes how? any sample?
P.S.
I know how add this value but i don't know how to write my query.
Thank you.Hello Dmitri,
Here is how to write the query to have 'All' included in your list of
users:
SELECT UserNo, UserName FROM Users
UNION
SELECT -1, 'All'
Take care,
Michelle|||Hi,
SELECT col1,col2,...
FROM table1
where col1 = CASE WHEN @.Param = 'All' THEN col1
ELSE @.Param
END
Eric|||oops, I forgot the second half - to check for the 'All' in your second
query:
SELECT something1, something2
FROM someTable
WHERE (User = @.User OR @.User = 'All')
or
SELECT something1, something2
FROM someTable
WHERE (UserNo = @.UserNo OR @.UserNo = -1)
Michelle|||Hi,
select col1,col2,col3
from table1
where col1 = case when @.param = 'all' then col1
else @.param
end
hth,
Eric|||Thank you all.
"Aiwa" wrote:
> Hi,
> select col1,col2,col3
> from table1
> where col1 = case when @.param = 'all' then col1
> else @.param
> end
> hth,
> Eric
>

Report parameters

I have a datetime parameter. Also I have a listbox with available jobs to
report for. I want to filter the listbox on the selected date. How can I do
that? Thanksuse the date-time parmeter in the sql for the dropdownlist
"Markgoldin" wrote:
> I have a datetime parameter. Also I have a listbox with available jobs to
> report for. I want to filter the listbox on the selected date. How can I do
> that? Thanks|||I am trying that but when I switch to preview I am getitng an error:
The report parameter â'jobâ' has a DefaultValue or a ValidValue that depends
on the report parameter â'RunDateâ'. Forward dependencies are not valid.
And here is my SQL for Job parameter:
select * from udf_GetJobs(@.RunDate)
order by job
"Antoon" wrote:
> use the date-time parmeter in the sql for the dropdownlist
> "Markgoldin" wrote:
> > I have a datetime parameter. Also I have a listbox with available jobs to
> > report for. I want to filter the listbox on the selected date. How can I do
> > that? Thanks|||Switch the order of your parameters. You can do that where it lists the
parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Markgoldin" <Markgoldin@.discussions.microsoft.com> wrote in message
news:A2179882-FB0F-4293-98B7-3390E07299BD@.microsoft.com...
>I am trying that but when I switch to preview I am getitng an error:
> The report parameter 'job' has a DefaultValue or a ValidValue that depends
> on the report parameter "RunDate". Forward dependencies are not valid.
> And here is my SQL for Job parameter:
> select * from udf_GetJobs(@.RunDate)
> order by job
> "Antoon" wrote:
>> use the date-time parmeter in the sql for the dropdownlist
>> "Markgoldin" wrote:
>> > I have a datetime parameter. Also I have a listbox with available jobs
>> > to
>> > report for. I want to filter the listbox on the selected date. How can
>> > I do
>> > that? Thanks

Report Parameters

I have a report that takes one parameter called StartDate and I have given it
a default value and a prompt of 'Enter a Starting Date'. When I preview it in
the report writer or run it from the Report Manager after deployment, the
report runs using the default value, displays the correct data, and the
prompt is displayed with the default value in the input box. All is well and
I am a happy guy.
I have second report with a textbox that navigates to the first report using
the 'Jump to Report' method.
When jumping from the second report to the first from the DESIGNER, the
report runs using the default value, displays the correct data, and the
prompt is displayed with the default value in the input box. Again, all is
well.
When doing the same from the Report Manager I get the same results EXCEPT
the prompt is NOT displayed at all. How can I make the prompt allways show
when jumping from one report to another ?
I notice that when you hover over the textbox/link that the URL to the
target report is diplayed in the status bar of the browser with the value
rs:Parameters=False there. What needs to be done to make it "True"
Thanks in Advance
PeteYou need to use Jump to URL:
The below example specifies the directory starting from the root. I have
this because this particular report can reside in a different directory than
the report I am jumping to. If it is in the same directory you can just not
worry about specifying the directory. A couple of others things to note, I
use the globals variable so this will work regardless of where I deploy it.
Also, note that parameter values are case sensitive so you must match it
exactly or you will end up without values in your parameter fields. Also
note that you can add to this URL a command to tell it to hide the parameter
toolbar. Anyway, this should get you started.
=Globals!ReportServerUrl & "?/Inventory/Similar Loads&Manifest=" &
Fields!manifstdocno.Value & "&WasteIDNum=" & Fields!wasteidnum.Value
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
news:99C82369-630F-434E-A882-2D0D23A156A4@.microsoft.com...
>I have a report that takes one parameter called StartDate and I have given
>it
> a default value and a prompt of 'Enter a Starting Date'. When I preview it
> in
> the report writer or run it from the Report Manager after deployment, the
> report runs using the default value, displays the correct data, and the
> prompt is displayed with the default value in the input box. All is well
> and
> I am a happy guy.
> I have second report with a textbox that navigates to the first report
> using
> the 'Jump to Report' method.
> When jumping from the second report to the first from the DESIGNER, the
> report runs using the default value, displays the correct data, and the
> prompt is displayed with the default value in the input box. Again, all is
> well.
> When doing the same from the Report Manager I get the same results EXCEPT
> the prompt is NOT displayed at all. How can I make the prompt allways show
> when jumping from one report to another ?
> I notice that when you hover over the textbox/link that the URL to the
> target report is diplayed in the status bar of the browser with the value
> rs:Parameters=False there. What needs to be done to make it "True"
> Thanks in Advance
> Pete
>|||Thanks for your response.
I don't see how that will ensure that the parameters will be displayed with
their prompt on the target report.
I understand that you are manually building the URL to the target. Are you
doing that in the 'Jump to Report' drop-down of the Navigation tab in the
textbox's properties ?
Pete
"Bruce L-C [MVP]" wrote:
> You need to use Jump to URL:
> The below example specifies the directory starting from the root. I have
> this because this particular report can reside in a different directory than
> the report I am jumping to. If it is in the same directory you can just not
> worry about specifying the directory. A couple of others things to note, I
> use the globals variable so this will work regardless of where I deploy it.
> Also, note that parameter values are case sensitive so you must match it
> exactly or you will end up without values in your parameter fields. Also
> note that you can add to this URL a command to tell it to hide the parameter
> toolbar. Anyway, this should get you started.
> =Globals!ReportServerUrl & "?/Inventory/Similar Loads&Manifest=" &
> Fields!manifstdocno.Value & "&WasteIDNum=" & Fields!wasteidnum.Value
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
> news:99C82369-630F-434E-A882-2D0D23A156A4@.microsoft.com...
> >I have a report that takes one parameter called StartDate and I have given
> >it
> > a default value and a prompt of 'Enter a Starting Date'. When I preview it
> > in
> > the report writer or run it from the Report Manager after deployment, the
> > report runs using the default value, displays the correct data, and the
> > prompt is displayed with the default value in the input box. All is well
> > and
> > I am a happy guy.
> >
> > I have second report with a textbox that navigates to the first report
> > using
> > the 'Jump to Report' method.
> >
> > When jumping from the second report to the first from the DESIGNER, the
> > report runs using the default value, displays the correct data, and the
> > prompt is displayed with the default value in the input box. Again, all is
> > well.
> >
> > When doing the same from the Report Manager I get the same results EXCEPT
> > the prompt is NOT displayed at all. How can I make the prompt allways show
> > when jumping from one report to another ?
> >
> > I notice that when you hover over the textbox/link that the URL to the
> > target report is diplayed in the status bar of the browser with the value
> > rs:Parameters=False there. What needs to be done to make it "True"
> >
> > Thanks in Advance
> > Pete
> >
>
>|||No, in the Jump to URL (look below the Jump to Report). You can jump to any
URL but that URL can be a report which is how I use it. You have much more
control with the Jump to URL. You can show or not show the toolbar, you can
choose to render in a different format (like PDF or CSV). You can cause
another window to open up. For instance this one opens up a report in CSV
ASCII format (from report manager you get Unicode format which Excel doesn't
handle well).
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
If you don't want to have it appear in a new window then do this in jump to
URL:
=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
To open up in a new window you must have SP1 or SP2 installed.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
news:DA7C3AC8-B470-435F-A497-962ACB0AE880@.microsoft.com...
> Thanks for your response.
> I don't see how that will ensure that the parameters will be displayed
> with
> their prompt on the target report.
> I understand that you are manually building the URL to the target. Are you
> doing that in the 'Jump to Report' drop-down of the Navigation tab in the
> textbox's properties ?
> Pete
> "Bruce L-C [MVP]" wrote:
>> You need to use Jump to URL:
>> The below example specifies the directory starting from the root. I have
>> this because this particular report can reside in a different directory
>> than
>> the report I am jumping to. If it is in the same directory you can just
>> not
>> worry about specifying the directory. A couple of others things to note,
>> I
>> use the globals variable so this will work regardless of where I deploy
>> it.
>> Also, note that parameter values are case sensitive so you must match it
>> exactly or you will end up without values in your parameter fields. Also
>> note that you can add to this URL a command to tell it to hide the
>> parameter
>> toolbar. Anyway, this should get you started.
>> =Globals!ReportServerUrl & "?/Inventory/Similar Loads&Manifest=" &
>> Fields!manifstdocno.Value & "&WasteIDNum=" & Fields!wasteidnum.Value
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
>> news:99C82369-630F-434E-A882-2D0D23A156A4@.microsoft.com...
>> >I have a report that takes one parameter called StartDate and I have
>> >given
>> >it
>> > a default value and a prompt of 'Enter a Starting Date'. When I preview
>> > it
>> > in
>> > the report writer or run it from the Report Manager after deployment,
>> > the
>> > report runs using the default value, displays the correct data, and the
>> > prompt is displayed with the default value in the input box. All is
>> > well
>> > and
>> > I am a happy guy.
>> >
>> > I have second report with a textbox that navigates to the first report
>> > using
>> > the 'Jump to Report' method.
>> >
>> > When jumping from the second report to the first from the DESIGNER, the
>> > report runs using the default value, displays the correct data, and the
>> > prompt is displayed with the default value in the input box. Again, all
>> > is
>> > well.
>> >
>> > When doing the same from the Report Manager I get the same results
>> > EXCEPT
>> > the prompt is NOT displayed at all. How can I make the prompt allways
>> > show
>> > when jumping from one report to another ?
>> >
>> > I notice that when you hover over the textbox/link that the URL to the
>> > target report is diplayed in the status bar of the browser with the
>> > value
>> > rs:Parameters=False there. What needs to be done to make it "True"
>> >
>> > Thanks in Advance
>> > Pete
>> >
>>|||I guess I should brush up on my reading skills ! Haha (You did say Jump to
URL and not Jump to Report.) :)
That works like a charm. Thanks very much. You're right about URL being more
flexible. I've copied your samples into my evil book or reporting trickery.
Pete
"Bruce L-C [MVP]" wrote:
> No, in the Jump to URL (look below the Jump to Report). You can jump to any
> URL but that URL can be a report which is how I use it. You have much more
> control with the Jump to URL. You can show or not show the toolbar, you can
> choose to render in a different format (like PDF or CSV). You can cause
> another window to open up. For instance this one opens up a report in CSV
> ASCII format (from report manager you get Unicode format which Excel doesn't
> handle well).
> Here is an example of a Jump to URL link I use. This causes Excel to come up
> with the data in a separate window:
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> If you don't want to have it appear in a new window then do this in jump to
> URL:
> =Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
> Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
> To open up in a new window you must have SP1 or SP2 installed.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
> news:DA7C3AC8-B470-435F-A497-962ACB0AE880@.microsoft.com...
> > Thanks for your response.
> > I don't see how that will ensure that the parameters will be displayed
> > with
> > their prompt on the target report.
> >
> > I understand that you are manually building the URL to the target. Are you
> > doing that in the 'Jump to Report' drop-down of the Navigation tab in the
> > textbox's properties ?
> >
> > Pete
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> You need to use Jump to URL:
> >>
> >> The below example specifies the directory starting from the root. I have
> >> this because this particular report can reside in a different directory
> >> than
> >> the report I am jumping to. If it is in the same directory you can just
> >> not
> >> worry about specifying the directory. A couple of others things to note,
> >> I
> >> use the globals variable so this will work regardless of where I deploy
> >> it.
> >> Also, note that parameter values are case sensitive so you must match it
> >> exactly or you will end up without values in your parameter fields. Also
> >> note that you can add to this URL a command to tell it to hide the
> >> parameter
> >> toolbar. Anyway, this should get you started.
> >> =Globals!ReportServerUrl & "?/Inventory/Similar Loads&Manifest=" &
> >> Fields!manifstdocno.Value & "&WasteIDNum=" & Fields!wasteidnum.Value
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
> >> news:99C82369-630F-434E-A882-2D0D23A156A4@.microsoft.com...
> >> >I have a report that takes one parameter called StartDate and I have
> >> >given
> >> >it
> >> > a default value and a prompt of 'Enter a Starting Date'. When I preview
> >> > it
> >> > in
> >> > the report writer or run it from the Report Manager after deployment,
> >> > the
> >> > report runs using the default value, displays the correct data, and the
> >> > prompt is displayed with the default value in the input box. All is
> >> > well
> >> > and
> >> > I am a happy guy.
> >> >
> >> > I have second report with a textbox that navigates to the first report
> >> > using
> >> > the 'Jump to Report' method.
> >> >
> >> > When jumping from the second report to the first from the DESIGNER, the
> >> > report runs using the default value, displays the correct data, and the
> >> > prompt is displayed with the default value in the input box. Again, all
> >> > is
> >> > well.
> >> >
> >> > When doing the same from the Report Manager I get the same results
> >> > EXCEPT
> >> > the prompt is NOT displayed at all. How can I make the prompt allways
> >> > show
> >> > when jumping from one report to another ?
> >> >
> >> > I notice that when you hover over the textbox/link that the URL to the
> >> > target report is diplayed in the status bar of the browser with the
> >> > value
> >> > rs:Parameters=False there. What needs to be done to make it "True"
> >> >
> >> > Thanks in Advance
> >> > Pete
> >> >
> >>
> >>
> >>
>
>|||I set a link up using your advice. my link looks like this:
=Globals!ReportServerUrl & "?/sales reports/customer sales" &"& customer1="&
Fields!customer.Value & "& date1=" & Parameters!date1.Value & "& date2=" &
Parameters!date2.Value
When I use the report, I get a specified server url does not link to the
report server for this report or is not in the correct format. It looks
like the & symbols are not being displayed in the url so that if I look at
my browser address bar after following the link I see something like:
Sales%2520Reports%2fCustomer%2520Salescustomer1%3dVALENTINE%2520ENTERPRISES%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520date1%3d20050901date2%3d20050930%26
The parameters look like they follow immediately after the report name with
no ampersand between them.
Thanks in advance for you assistance.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eSthLfWtFHA.616@.TK2MSFTNGP11.phx.gbl...
> You need to use Jump to URL:
> The below example specifies the directory starting from the root. I have
> this because this particular report can reside in a different directory
> than the report I am jumping to. If it is in the same directory you can
> just not worry about specifying the directory. A couple of others things
> to note, I use the globals variable so this will work regardless of where
> I deploy it. Also, note that parameter values are case sensitive so you
> must match it exactly or you will end up without values in your parameter
> fields. Also note that you can add to this URL a command to tell it to
> hide the parameter toolbar. Anyway, this should get you started.
> =Globals!ReportServerUrl & "?/Inventory/Similar Loads&Manifest=" &
> Fields!manifstdocno.Value & "&WasteIDNum=" & Fields!wasteidnum.Value
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
> news:99C82369-630F-434E-A882-2D0D23A156A4@.microsoft.com...
>>I have a report that takes one parameter called StartDate and I have given
>>it
>> a default value and a prompt of 'Enter a Starting Date'. When I preview
>> it in
>> the report writer or run it from the Report Manager after deployment, the
>> report runs using the default value, displays the correct data, and the
>> prompt is displayed with the default value in the input box. All is well
>> and
>> I am a happy guy.
>> I have second report with a textbox that navigates to the first report
>> using
>> the 'Jump to Report' method.
>> When jumping from the second report to the first from the DESIGNER, the
>> report runs using the default value, displays the correct data, and the
>> prompt is displayed with the default value in the input box. Again, all
>> is
>> well.
>> When doing the same from the Report Manager I get the same results EXCEPT
>> the prompt is NOT displayed at all. How can I make the prompt allways
>> show
>> when jumping from one report to another ?
>> I notice that when you hover over the textbox/link that the URL to the
>> target report is diplayed in the status bar of the browser with the value
>> rs:Parameters=False there. What needs to be done to make it "True"
>> Thanks in Advance
>> Pete
>|||Try taking the space out between the ampersand and the parameter. Change
"& customer1=" to "&customer1="
I don't know if that will make a difference but I don't have the space in
there that you do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
news:uEafkkYtFHA.3000@.TK2MSFTNGP12.phx.gbl...
>I set a link up using your advice. my link looks like this:
> =Globals!ReportServerUrl & "?/sales reports/customer sales" &"&
> customer1="& Fields!customer.Value & "& date1=" & Parameters!date1.Value &
> "& date2=" & Parameters!date2.Value
> When I use the report, I get a specified server url does not link to the
> report server for this report or is not in the correct format. It looks
> like the & symbols are not being displayed in the url so that if I look at
> my browser address bar after following the link I see something like:
> Sales%2520Reports%2fCustomer%2520Salescustomer1%3dVALENTINE%2520ENTERPRISES%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520date1%3d20050901date2%3d20050930%26
> The parameters look like they follow immediately after the report name
> with no ampersand between them.
> Thanks in advance for you assistance.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:eSthLfWtFHA.616@.TK2MSFTNGP11.phx.gbl...
>> You need to use Jump to URL:
>> The below example specifies the directory starting from the root. I have
>> this because this particular report can reside in a different directory
>> than the report I am jumping to. If it is in the same directory you can
>> just not worry about specifying the directory. A couple of others things
>> to note, I use the globals variable so this will work regardless of where
>> I deploy it. Also, note that parameter values are case sensitive so you
>> must match it exactly or you will end up without values in your parameter
>> fields. Also note that you can add to this URL a command to tell it to
>> hide the parameter toolbar. Anyway, this should get you started.
>> =Globals!ReportServerUrl & "?/Inventory/Similar Loads&Manifest=" &
>> Fields!manifstdocno.Value & "&WasteIDNum=" & Fields!wasteidnum.Value
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
>> news:99C82369-630F-434E-A882-2D0D23A156A4@.microsoft.com...
>>I have a report that takes one parameter called StartDate and I have
>>given it
>> a default value and a prompt of 'Enter a Starting Date'. When I preview
>> it in
>> the report writer or run it from the Report Manager after deployment,
>> the
>> report runs using the default value, displays the correct data, and the
>> prompt is displayed with the default value in the input box. All is well
>> and
>> I am a happy guy.
>> I have second report with a textbox that navigates to the first report
>> using
>> the 'Jump to Report' method.
>> When jumping from the second report to the first from the DESIGNER, the
>> report runs using the default value, displays the correct data, and the
>> prompt is displayed with the default value in the input box. Again, all
>> is
>> well.
>> When doing the same from the Report Manager I get the same results
>> EXCEPT
>> the prompt is NOT displayed at all. How can I make the prompt allways
>> show
>> when jumping from one report to another ?
>> I notice that when you hover over the textbox/link that the URL to the
>> target report is diplayed in the status bar of the browser with the
>> value
>> rs:Parameters=False there. What needs to be done to make it "True"
>> Thanks in Advance
>> Pete
>>
>|||I found that this format works for me:
=Globals!ReportServerUrl + "?/Sales Reports/Customer Sales" + "&customer1="
+ Fields!customer.Value + "&date1=" + Parameters!date1.Value + "&date2=" +
Parameters!date2.Value
Thank you for your assistance.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23bp$2EctFHA.3452@.TK2MSFTNGP14.phx.gbl...
> Try taking the space out between the ampersand and the parameter. Change
> "& customer1=" to "&customer1="
> I don't know if that will make a difference but I don't have the space in
> there that you do.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Fredrick A. Zilz" <fzilz@.NOSPAM.interhealthusa.com> wrote in message
> news:uEafkkYtFHA.3000@.TK2MSFTNGP12.phx.gbl...
>>I set a link up using your advice. my link looks like this:
>> =Globals!ReportServerUrl & "?/sales reports/customer sales" &"&
>> customer1="& Fields!customer.Value & "& date1=" & Parameters!date1.Value
>> & "& date2=" & Parameters!date2.Value
>> When I use the report, I get a specified server url does not link to the
>> report server for this report or is not in the correct format. It looks
>> like the & symbols are not being displayed in the url so that if I look
>> at my browser address bar after following the link I see something like:
>> Sales%2520Reports%2fCustomer%2520Salescustomer1%3dVALENTINE%2520ENTERPRISES%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520%2520date1%3d20050901date2%3d20050930%26
>> The parameters look like they follow immediately after the report name
>> with no ampersand between them.
>> Thanks in advance for you assistance.
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:eSthLfWtFHA.616@.TK2MSFTNGP11.phx.gbl...
>> You need to use Jump to URL:
>> The below example specifies the directory starting from the root. I have
>> this because this particular report can reside in a different directory
>> than the report I am jumping to. If it is in the same directory you can
>> just not worry about specifying the directory. A couple of others things
>> to note, I use the globals variable so this will work regardless of
>> where I deploy it. Also, note that parameter values are case sensitive
>> so you must match it exactly or you will end up without values in your
>> parameter fields. Also note that you can add to this URL a command to
>> tell it to hide the parameter toolbar. Anyway, this should get you
>> started.
>> =Globals!ReportServerUrl & "?/Inventory/Similar Loads&Manifest=" &
>> Fields!manifstdocno.Value & "&WasteIDNum=" & Fields!wasteidnum.Value
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
>> news:99C82369-630F-434E-A882-2D0D23A156A4@.microsoft.com...
>>I have a report that takes one parameter called StartDate and I have
>>given it
>> a default value and a prompt of 'Enter a Starting Date'. When I preview
>> it in
>> the report writer or run it from the Report Manager after deployment,
>> the
>> report runs using the default value, displays the correct data, and the
>> prompt is displayed with the default value in the input box. All is
>> well and
>> I am a happy guy.
>> I have second report with a textbox that navigates to the first report
>> using
>> the 'Jump to Report' method.
>> When jumping from the second report to the first from the DESIGNER, the
>> report runs using the default value, displays the correct data, and the
>> prompt is displayed with the default value in the input box. Again, all
>> is
>> well.
>> When doing the same from the Report Manager I get the same results
>> EXCEPT
>> the prompt is NOT displayed at all. How can I make the prompt allways
>> show
>> when jumping from one report to another ?
>> I notice that when you hover over the textbox/link that the URL to the
>> target report is diplayed in the status bar of the browser with the
>> value
>> rs:Parameters=False there. What needs to be done to make it "True"
>> Thanks in Advance
>> Pete
>>
>>
>|||Hi,
I've the same issue with my reports when using "Jump to Report", even with
the new version of RS ... > Will this be fixed in the next version ?
How can we use Jump to URL with parameters set as "null" (i.e. for
picklists, to have "all values") ?
Thanks a lot for your answer !
Laetic
"Bruce L-C [MVP]" wrote:
> You need to use Jump to URL:
> The below example specifies the directory starting from the root. I have
> this because this particular report can reside in a different directory than
> the report I am jumping to. If it is in the same directory you can just not
> worry about specifying the directory. A couple of others things to note, I
> use the globals variable so this will work regardless of where I deploy it.
> Also, note that parameter values are case sensitive so you must match it
> exactly or you will end up without values in your parameter fields. Also
> note that you can add to this URL a command to tell it to hide the parameter
> toolbar. Anyway, this should get you started.
> =Globals!ReportServerUrl & "?/Inventory/Similar Loads&Manifest=" &
> Fields!manifstdocno.Value & "&WasteIDNum=" & Fields!wasteidnum.Value
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
> news:99C82369-630F-434E-A882-2D0D23A156A4@.microsoft.com...
> >I have a report that takes one parameter called StartDate and I have given
> >it
> > a default value and a prompt of 'Enter a Starting Date'. When I preview it
> > in
> > the report writer or run it from the Report Manager after deployment, the
> > report runs using the default value, displays the correct data, and the
> > prompt is displayed with the default value in the input box. All is well
> > and
> > I am a happy guy.
> >
> > I have second report with a textbox that navigates to the first report
> > using
> > the 'Jump to Report' method.
> >
> > When jumping from the second report to the first from the DESIGNER, the
> > report runs using the default value, displays the correct data, and the
> > prompt is displayed with the default value in the input box. Again, all is
> > well.
> >
> > When doing the same from the Report Manager I get the same results EXCEPT
> > the prompt is NOT displayed at all. How can I make the prompt allways show
> > when jumping from one report to another ?
> >
> > I notice that when you hover over the textbox/link that the URL to the
> > target report is diplayed in the status bar of the browser with the value
> > rs:Parameters=False there. What needs to be done to make it "True"
> >
> > Thanks in Advance
> > Pete
> >
>
>|||Sorry, I don't use Null. What I do for pick lists is add and All option and
then in the query I do this:
select * from sometable where (somefield = @.someparamter or @.someparameter ='All')
I use a union query to add All to my pick lists.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Laetic" <Laetic@.discussions.microsoft.com> wrote in message
news:8DF314B2-0F0C-4587-874B-FB4FF225B78E@.microsoft.com...
> Hi,
> I've the same issue with my reports when using "Jump to Report", even with
> the new version of RS ... > Will this be fixed in the next version ?
> How can we use Jump to URL with parameters set as "null" (i.e. for
> picklists, to have "all values") ?
> Thanks a lot for your answer !
> Laetic
> "Bruce L-C [MVP]" wrote:
>> You need to use Jump to URL:
>> The below example specifies the directory starting from the root. I have
>> this because this particular report can reside in a different directory
>> than
>> the report I am jumping to. If it is in the same directory you can just
>> not
>> worry about specifying the directory. A couple of others things to note,
>> I
>> use the globals variable so this will work regardless of where I deploy
>> it.
>> Also, note that parameter values are case sensitive so you must match it
>> exactly or you will end up without values in your parameter fields. Also
>> note that you can add to this URL a command to tell it to hide the
>> parameter
>> toolbar. Anyway, this should get you started.
>> =Globals!ReportServerUrl & "?/Inventory/Similar Loads&Manifest=" &
>> Fields!manifstdocno.Value & "&WasteIDNum=" & Fields!wasteidnum.Value
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "PeteMitchell" <PeteMitchell@.discussions.microsoft.com> wrote in message
>> news:99C82369-630F-434E-A882-2D0D23A156A4@.microsoft.com...
>> >I have a report that takes one parameter called StartDate and I have
>> >given
>> >it
>> > a default value and a prompt of 'Enter a Starting Date'. When I preview
>> > it
>> > in
>> > the report writer or run it from the Report Manager after deployment,
>> > the
>> > report runs using the default value, displays the correct data, and the
>> > prompt is displayed with the default value in the input box. All is
>> > well
>> > and
>> > I am a happy guy.
>> >
>> > I have second report with a textbox that navigates to the first report
>> > using
>> > the 'Jump to Report' method.
>> >
>> > When jumping from the second report to the first from the DESIGNER, the
>> > report runs using the default value, displays the correct data, and the
>> > prompt is displayed with the default value in the input box. Again, all
>> > is
>> > well.
>> >
>> > When doing the same from the Report Manager I get the same results
>> > EXCEPT
>> > the prompt is NOT displayed at all. How can I make the prompt allways
>> > show
>> > when jumping from one report to another ?
>> >
>> > I notice that when you hover over the textbox/link that the URL to the
>> > target report is diplayed in the status bar of the browser with the
>> > value
>> > rs:Parameters=False there. What needs to be done to make it "True"
>> >
>> > Thanks in Advance
>> > Pete
>> >
>>sql

Report Parameters

If I specifiy a parameter in my report dataset like @.status to filter the
report based on a status selected by the user - I can't figure out how to
give them the "ALL" option - so that the report ignores the status and
displays all the records.
--
ArkayTry this
SELECT NULL AS StatusValue, '<All>' AS Status
UNION
SELECT StatusValue, Status
FROM Status_mstr
"Arkay" <Arkay@.discussions.microsoft.com> wrote in message
news:7E0C2DD8-A1E9-407C-BDDD-AD0B110C5775@.microsoft.com...
> If I specifiy a parameter in my report dataset like @.status to filter the
> report based on a status selected by the user - I can't figure out how to
> give them the "ALL" option - so that the report ignores the status and
> displays all the records.
>
> --
> Arkay

report parameters

Hi
On a 2000 platform is it possible to make a parameter field searchable ?
So instead of just taking the first letter it should search using what the
user enters.
For instance when looking for washington
entering Was would place the marker on washingtonMichael, I'm pretty new to SRS, but I used this in the criteria next to
the column pcname to allow a filter for the pcname in the parameter:
LIKE '%' + @.pcname + '%'
I guess you could leave out the first % sign if you wanted each return
to begin with a particular character. Therefore you could enter Wa in
the field and might return WA9300, WASH93k9, etc. or the way it is
written sWAkjh93, redwakoe might also be returned.
Karen
michael v wrote:
> Hi
> On a 2000 platform is it possible to make a parameter field searchable ?
> So instead of just taking the first letter it should search using what the
> user enters.
> For instance when looking for washington
> entering Was would place the marker on washington

Report Parameter Problem

I have a report based on a stored procedure with three parameters: Start
Date, End Date, and Salesperson.
The first two are required, and the Salesperson is not. In the report, the
Salesperson is based on a query which provides a drop down for choosing a
name. It is set to allow nulls and blanks.
When I run the report in Visual Studio preview, if no Salesperson is chosen,
and both dates are entered, no data is returned. Doing the same thing via
debug in a browser, (the drop down is not blank, but says <Selelct a value>),
I get prompted to supply the Salesperson parameter.
Supplying a value for Salesperson in both cases works fine, but I want the
user to have the option to get all the data, not just for a particular
person. I've covered this case in the stored procedure, so I'm not sure what
is getting passed in the previewer when nothing is entered and no data is
returned.
If I run the query (sp) via the Data tab, and provide exactly the same
values for the parameters, I get the data I expect, and no prompt for the
Salesperson.
If I put a default value in for the Salesperson, (non-queried), e.g. "All",
the preview in VS goes crazy, and locks up.
I would have thought with allowing nulls and/or blanks, this should work. Am
I missing something?
Thanks for any help,
TomIf Salesperson parameter uses available values from query, then the query
must return null or blank as one of available values.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"TomT" <tomt@.newsgroup.nospam> wrote in message
news:87D78917-4603-4339-AD5B-C4C3377BA9AA@.microsoft.com...
>I have a report based on a stored procedure with three parameters: Start
> Date, End Date, and Salesperson.
> The first two are required, and the Salesperson is not. In the report, the
> Salesperson is based on a query which provides a drop down for choosing a
> name. It is set to allow nulls and blanks.
> When I run the report in Visual Studio preview, if no Salesperson is
> chosen,
> and both dates are entered, no data is returned. Doing the same thing via
> debug in a browser, (the drop down is not blank, but says <Selelct a
> value>),
> I get prompted to supply the Salesperson parameter.
> Supplying a value for Salesperson in both cases works fine, but I want the
> user to have the option to get all the data, not just for a particular
> person. I've covered this case in the stored procedure, so I'm not sure
> what
> is getting passed in the previewer when nothing is entered and no data is
> returned.
> If I run the query (sp) via the Data tab, and provide exactly the same
> values for the parameters, I get the data I expect, and no prompt for the
> Salesperson.
> If I put a default value in for the Salesperson, (non-queried), e.g.
> "All",
> the preview in VS goes crazy, and locks up.
> I would have thought with allowing nulls and/or blanks, this should work.
> Am
> I missing something?
> Thanks for any help,
> Tom|||Lev, thanks, I was assuming if no choice was made (drop down blank) a null
would be passed. I fixed it up to provide a value simulating a null
situation, and it now works fine.
Thanks for your quick response...
Tom
"Lev Semenets [MSFT]" wrote:
> If Salesperson parameter uses available values from query, then the query
> must return null or blank as one of available values.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "TomT" <tomt@.newsgroup.nospam> wrote in message
> news:87D78917-4603-4339-AD5B-C4C3377BA9AA@.microsoft.com...
> >I have a report based on a stored procedure with three parameters: Start
> > Date, End Date, and Salesperson.
> >
> > The first two are required, and the Salesperson is not. In the report, the
> > Salesperson is based on a query which provides a drop down for choosing a
> > name. It is set to allow nulls and blanks.
> >
> > When I run the report in Visual Studio preview, if no Salesperson is
> > chosen,
> > and both dates are entered, no data is returned. Doing the same thing via
> > debug in a browser, (the drop down is not blank, but says <Selelct a
> > value>),
> > I get prompted to supply the Salesperson parameter.
> >
> > Supplying a value for Salesperson in both cases works fine, but I want the
> > user to have the option to get all the data, not just for a particular
> > person. I've covered this case in the stored procedure, so I'm not sure
> > what
> > is getting passed in the previewer when nothing is entered and no data is
> > returned.
> >
> > If I run the query (sp) via the Data tab, and provide exactly the same
> > values for the parameters, I get the data I expect, and no prompt for the
> > Salesperson.
> >
> > If I put a default value in for the Salesperson, (non-queried), e.g.
> > "All",
> > the preview in VS goes crazy, and locks up.
> >
> > I would have thought with allowing nulls and/or blanks, this should work.
> > Am
> > I missing something?
> >
> > Thanks for any help,
> >
> > Tom
>
>|||TomT
Could you please tell me how did you simulate a value of null. I am running
into exactly same problem as yours where I cant make paramter optional as
Report Manager forces me to choose a value although I have defined null and
blank as options for the parameter.
TIA
"TomT" wrote:
> Lev, thanks, I was assuming if no choice was made (drop down blank) a null
> would be passed. I fixed it up to provide a value simulating a null
> situation, and it now works fine.
> Thanks for your quick response...
> Tom
> "Lev Semenets [MSFT]" wrote:
> > If Salesperson parameter uses available values from query, then the query
> > must return null or blank as one of available values.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "TomT" <tomt@.newsgroup.nospam> wrote in message
> > news:87D78917-4603-4339-AD5B-C4C3377BA9AA@.microsoft.com...
> > >I have a report based on a stored procedure with three parameters: Start
> > > Date, End Date, and Salesperson.
> > >
> > > The first two are required, and the Salesperson is not. In the report, the
> > > Salesperson is based on a query which provides a drop down for choosing a
> > > name. It is set to allow nulls and blanks.
> > >
> > > When I run the report in Visual Studio preview, if no Salesperson is
> > > chosen,
> > > and both dates are entered, no data is returned. Doing the same thing via
> > > debug in a browser, (the drop down is not blank, but says <Selelct a
> > > value>),
> > > I get prompted to supply the Salesperson parameter.
> > >
> > > Supplying a value for Salesperson in both cases works fine, but I want the
> > > user to have the option to get all the data, not just for a particular
> > > person. I've covered this case in the stored procedure, so I'm not sure
> > > what
> > > is getting passed in the previewer when nothing is entered and no data is
> > > returned.
> > >
> > > If I run the query (sp) via the Data tab, and provide exactly the same
> > > values for the parameters, I get the data I expect, and no prompt for the
> > > Salesperson.
> > >
> > > If I put a default value in for the Salesperson, (non-queried), e.g.
> > > "All",
> > > the preview in VS goes crazy, and locks up.
> > >
> > > I would have thought with allowing nulls and/or blanks, this should work.
> > > Am
> > > I missing something?
> > >
> > > Thanks for any help,
> > >
> > > Tom
> >
> >
> >|||TomT
Could you please tell me how did you simulate a value of null. I am running
into exactly same problem as yours where I cant make paramter optional as
Report Manager forces me to choose a value although I have defined null and
blank as options for the parameter.
TIA
"TomT" wrote:
> Lev, thanks, I was assuming if no choice was made (drop down blank) a null
> would be passed. I fixed it up to provide a value simulating a null
> situation, and it now works fine.
> Thanks for your quick response...
> Tom
> "Lev Semenets [MSFT]" wrote:
> > If Salesperson parameter uses available values from query, then the query
> > must return null or blank as one of available values.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "TomT" <tomt@.newsgroup.nospam> wrote in message
> > news:87D78917-4603-4339-AD5B-C4C3377BA9AA@.microsoft.com...
> > >I have a report based on a stored procedure with three parameters: Start
> > > Date, End Date, and Salesperson.
> > >
> > > The first two are required, and the Salesperson is not. In the report, the
> > > Salesperson is based on a query which provides a drop down for choosing a
> > > name. It is set to allow nulls and blanks.
> > >
> > > When I run the report in Visual Studio preview, if no Salesperson is
> > > chosen,
> > > and both dates are entered, no data is returned. Doing the same thing via
> > > debug in a browser, (the drop down is not blank, but says <Selelct a
> > > value>),
> > > I get prompted to supply the Salesperson parameter.
> > >
> > > Supplying a value for Salesperson in both cases works fine, but I want the
> > > user to have the option to get all the data, not just for a particular
> > > person. I've covered this case in the stored procedure, so I'm not sure
> > > what
> > > is getting passed in the previewer when nothing is entered and no data is
> > > returned.
> > >
> > > If I run the query (sp) via the Data tab, and provide exactly the same
> > > values for the parameters, I get the data I expect, and no prompt for the
> > > Salesperson.
> > >
> > > If I put a default value in for the Salesperson, (non-queried), e.g.
> > > "All",
> > > the preview in VS goes crazy, and locks up.
> > >
> > > I would have thought with allowing nulls and/or blanks, this should work.
> > > Am
> > > I missing something?
> > >
> > > Thanks for any help,
> > >
> > > Tom
> >
> >
> >|||TomT
Could you please tell me how did you simulate a value of null. I am running
into exactly same problem as yours where I cant make paramter optional as
Report Manager forces me to choose a value although I have defined null and
blank as options for the parameter.
TIA
"TomT" wrote:
> I have a report based on a stored procedure with three parameters: Start
> Date, End Date, and Salesperson.
> The first two are required, and the Salesperson is not. In the report, the
> Salesperson is based on a query which provides a drop down for choosing a
> name. It is set to allow nulls and blanks.
> When I run the report in Visual Studio preview, if no Salesperson is chosen,
> and both dates are entered, no data is returned. Doing the same thing via
> debug in a browser, (the drop down is not blank, but says <Selelct a value>),
> I get prompted to supply the Salesperson parameter.
> Supplying a value for Salesperson in both cases works fine, but I want the
> user to have the option to get all the data, not just for a particular
> person. I've covered this case in the stored procedure, so I'm not sure what
> is getting passed in the previewer when nothing is entered and no data is
> returned.
> If I run the query (sp) via the Data tab, and provide exactly the same
> values for the parameters, I get the data I expect, and no prompt for the
> Salesperson.
> If I put a default value in for the Salesperson, (non-queried), e.g. "All",
> the preview in VS goes crazy, and locks up.
> I would have thought with allowing nulls and/or blanks, this should work. Am
> I missing something?
> Thanks for any help,
> Tom|||Did you set default value for the parameter?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"tiwanap" <tiwanap@.discussions.microsoft.com> wrote in message
news:7C84F377-9ECA-4A87-AC8F-E936353B917A@.microsoft.com...
> TomT
> Could you please tell me how did you simulate a value of null. I am
> running
> into exactly same problem as yours where I cant make paramter optional as
> Report Manager forces me to choose a value although I have defined null
> and
> blank as options for the parameter.
> TIA
> "TomT" wrote:
>> I have a report based on a stored procedure with three parameters: Start
>> Date, End Date, and Salesperson.
>> The first two are required, and the Salesperson is not. In the report,
>> the
>> Salesperson is based on a query which provides a drop down for choosing a
>> name. It is set to allow nulls and blanks.
>> When I run the report in Visual Studio preview, if no Salesperson is
>> chosen,
>> and both dates are entered, no data is returned. Doing the same thing via
>> debug in a browser, (the drop down is not blank, but says <Selelct a
>> value>),
>> I get prompted to supply the Salesperson parameter.
>> Supplying a value for Salesperson in both cases works fine, but I want
>> the
>> user to have the option to get all the data, not just for a particular
>> person. I've covered this case in the stored procedure, so I'm not sure
>> what
>> is getting passed in the previewer when nothing is entered and no data is
>> returned.
>> If I run the query (sp) via the Data tab, and provide exactly the same
>> values for the parameters, I get the data I expect, and no prompt for the
>> Salesperson.
>> If I put a default value in for the Salesperson, (non-queried), e.g.
>> "All",
>> the preview in VS goes crazy, and locks up.
>> I would have thought with allowing nulls and/or blanks, this should work.
>> Am
>> I missing something?
>> Thanks for any help,
>> Tom