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

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

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

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/filter

I have a drop down on my report but I want the user to be able to select more
than one option from the drop down list...is that possible?No. I'm afraid out of the box Reporting Services does not support selection
of multiple values in the dropdowns. Its something that I'm looking for a
solution to myself.
"vbaker" wrote:
> I have a drop down on my report but I want the user to be able to select more
> than one option from the drop down list...is that possible?|||Multi select is going to be part of SQL 2005 RS. you can either
1. provide extra parameter fields, they can select one from each field
2. allow them to enter a string which contains multiple values, but you
can't do a good job of prompting here, and you must parse the string or
3. Put an html or asp.net page in front of the report, and expose date
pickers, multi-select combo boxes etc to gather parameters then call the
report via web service URL.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"vbaker" <vbaker@.discussions.microsoft.com> wrote in message
news:A2544701-17A3-4B14-8042-F4F33B4A12F4@.microsoft.com...
>I have a drop down on my report but I want the user to be able to select
>more
> than one option from the drop down list...is that possible?

Report parameter 'x' was not found.

I created a simple report to display the default value of a report parameter.
But when I run in VS it works, but when I run it under the "Custom Report"
in SQL Server Managment Studio the report parameter is not found. Below is
the code what am I doing wrong. Thanks in advance for your help.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource1">
<DataSourceReference>DataSource1</DataSourceReference>
<rd:DataSourceID>b64e9e97-e752-4d30-8e40-ad77c2582190</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="x">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>5</Value>
</Values>
</DefaultValue>
<Prompt>x</Prompt>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Left>2in</Left>
<Top>0.375in</Top>
<rd:DefaultName>textbox1</rd:DefaultName>
<Width>1.125in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.375in</Height>
<Value>=First(Fields!x.Value, "DataSet1")</Value>
</Textbox>
</ReportItems>
<Height>2in</Height>
</Body>
<rd:ReportID>397ab84d-5c20-454f-9ec1-a1548611ba69</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select @.x x</CommandText>
<QueryParameters>
<QueryParameter Name="@.x">
<Value>=Parameters!x.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>DataSource1</DataSourceName>
</Query>
<Fields>
<Field Name="x">
<rd:TypeName>System.Object</rd:TypeName>
<DataField>x</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>Did you find a solution to this?
--
-sam
"Greg Larsen" wrote:
> I created a simple report to display the default value of a report parameter.
> But when I run in VS it works, but when I run it under the "Custom Report"
> in SQL Server Managment Studio the report parameter is not found. Below is
> the code what am I doing wrong. Thanks in advance for your help.
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <DataSources>
> <DataSource Name="DataSource1">
> <DataSourceReference>DataSource1</DataSourceReference>
> <rd:DataSourceID>b64e9e97-e752-4d30-8e40-ad77c2582190</rd:DataSourceID>
> </DataSource>
> </DataSources>
> <BottomMargin>1in</BottomMargin>
> <RightMargin>1in</RightMargin>
> <ReportParameters>
> <ReportParameter Name="x">
> <DataType>String</DataType>
> <DefaultValue>
> <Values>
> <Value>5</Value>
> </Values>
> </DefaultValue>
> <Prompt>x</Prompt>
> </ReportParameter>
> </ReportParameters>
> <rd:DrawGrid>true</rd:DrawGrid>
> <InteractiveWidth>8.5in</InteractiveWidth>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <Body>
> <ReportItems>
> <Textbox Name="textbox1">
> <Left>2in</Left>
> <Top>0.375in</Top>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <Width>1.125in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.375in</Height>
> <Value>=First(Fields!x.Value, "DataSet1")</Value>
> </Textbox>
> </ReportItems>
> <Height>2in</Height>
> </Body>
> <rd:ReportID>397ab84d-5c20-454f-9ec1-a1548611ba69</rd:ReportID>
> <LeftMargin>1in</LeftMargin>
> <DataSets>
> <DataSet Name="DataSet1">
> <Query>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <CommandText>select @.x x</CommandText>
> <QueryParameters>
> <QueryParameter Name="@.x">
> <Value>=Parameters!x.Value</Value>
> </QueryParameter>
> </QueryParameters>
> <DataSourceName>DataSource1</DataSourceName>
> </Query>
> <Fields>
> <Field Name="x">
> <rd:TypeName>System.Object</rd:TypeName>
> <DataField>x</DataField>
> </Field>
> </Fields>
> </DataSet>
> </DataSets>
> <Width>6.5in</Width>
> <InteractiveHeight>11in</InteractiveHeight>
> <Language>en-US</Language>
> <TopMargin>1in</TopMargin>
> </Report>

Report parameter won't show values with stored procedure results

Hi,
I wrote a stored procedure in C# to return results that I could use to
populate a report parameter list. The problem occurs when I set up the
parameter. Under "Report Parameters -> Available Values -> From Query", I
can set the dataset fine but no entries appear under the Value field or the
Label field. When I run the query (whether it's thru the data tab or in SQL
server management Studio or the SQL Server project), I get the correct
results. If I try to type in the name of the field, I get an
rsInvalidDataSetReferenceField error when I preview the report.
Below is the steps I took to get to where I'm at:
1.
The stored procedure was supposed to extract certain areas from the area
path, with the TFSWarehouse as my data source. The areas of interest were
top-level areas, i.e. \\TeamProject\TopLevelArea\SubAreas... The
CommandText attribute of my SqlCommand instance is as follows:
@."SELECT DISTINCT Area.[Area Path]
FROM Area INNER JOIN
[Current Work Item] ON Area.__ID = [Current Work
Item].Area";
2.
The results are returned in an SqlDataReader. I read thru each record of
the reader and manipulate some of the data and send it back using
SqlContext.Pipe.SendResultsStart/Row/End when appropriate. The structure of
the record consists of one column, called "TopLevelArea".
3.
I compiled it into an assembly using the Visual Studio command prompt, ran a
T-SQL query to create the procedure, so that it appears in the Stored
Procedures list. And then I created a dataset whose command type is
StoredProcedure, and it contains the name of the procedure. Then I set up
the parameter and that's where everything went all wrong.
Please help.Hi Winkles,
Would you please try to check the DataSet Properties?
Make sure the Field have set a proper name in the data tab of the Report
Project.
Also, please make sure you have set the correct dataset name when you
config the report parameter.
If this does not help, would you please send the sample stored procedure to
me for troubleshooting? Thanks!
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hello Winkles,
I have tested on my side with your code.
You need to set the Field tab in the DataSet properties.
In the Field Name, type TopLevelArea, and type TopLevelArea in the Value.
(Remove the equal mark). And then, you could get the Field name in the
Parameter.
Please do the above and let me know the result.
Sincerely,
Wei Lu
Microsoft Online Community Support|||Hi Wei,
It worked!!! Thank you very much! I had been including the equal sign
before.
The 'TopLevelArea' in Field Name...does that correspond to the metadata
column in the stored procedure? What does the TopLevelArea in the 'Value'
column match up with? Or do they both refer to the same thing?
Thanks again.
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:1BHmMqBwGHA.5976@.TK2MSFTNGXA01.phx.gbl...
> Hello Winkles,
> I have tested on my side with your code.
> You need to set the Field tab in the DataSet properties.
> In the Field Name, type TopLevelArea, and type TopLevelArea in the Value.
> (Remove the equal mark). And then, you could get the Field name in the
> Parameter.
> Please do the above and let me know the result.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
>|||Hi Winkles,
Yes, it correspond to the metadata column in the stored procedure.
The TopLevelArea in the Value column is the Friendly Name you could use in
the Report. You could modify it with other name.
Here is the article for your reference:
Fields
Each dataset in a report contains a list of fields. Typically, the fields
refer to columns or fields returned by the query in the dataset. Fields
that refer to database fields contain a pointer to the database field and a
name property. You can use the name property to provide a friendly name in
place of the name of the database field. In addition to database fields,
the fields list can contain calculated fields. Calculated fields contain a
name and an expression. The expression can be as simple as a concatenation
of two database fields (for example, first name and last name), or it can
be used to perform complex calculations.
Some query languages are flexible enough so that a query can be written to
return friendly field names and perform calculations, making changes to the
fields list unnecessary. The fields list is especially useful when using a
database or query language that does not provide this flexibility.
http://msdn2.microsoft.com/en-us/library/ms160324.aspx
If you have any questions or concerns, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Supportsql

report parameter with mdx

Hi,
I am running analysis services with SQL Server 2000. There I created a
cube which I am handling with reporting services. So far, no problem.
But now I also added some report parameter, which I would like to use
to define the dataset. But every time when I try to include a parameter
into the mdx-query I get an error-message, that named parameter are not
supported by OLE DB. I try to call a parameter by @.param_name
Is it possible to include reportparameter in mdx-queries?
If yes, how?
Thanksyes it is, show me your mdx query and i'll tell you how to do it.
Greg
"mickmack" <access.20.mickmack@.spamgourmet.com> wrote in message
news:1134637862.017069.135030@.o13g2000cwo.googlegroups.com...
> Hi,
> I am running analysis services with SQL Server 2000. There I created a
> cube which I am handling with reporting services. So far, no problem.
> But now I also added some report parameter, which I would like to use
> to define the dataset. But every time when I try to include a parameter
> into the mdx-query I get an error-message, that named parameter are not
> supported by OLE DB. I try to call a parameter by @.param_name
> Is it possible to include reportparameter in mdx-queries?
> If yes, how?
> Thanks
>|||Hi Greg,
thanks for your response.
Thats my current query:
WITH
MEMBER [Measures].[percentage] AS '[Measures].[Analysis_Count] /
([Measures].[Analysis_Count], [analysis].[Alle Analysis])',
FORMAT_STRING = 'Percent'
SELECT
{[Measures].[Analysis_Count], [Measures].[percentage]} ON AXIS(0),
{client.members} ON AXIS(1),
{area.members} ON AXIS(2),
{date_month.members} ON AXIS(3),
{analysis.members} ON AXIS(4)
FROM ISDBIv2
something like that I would like to add:
... WHERE ([client].[@.paramname])
Thanks|||Hi Greg,
thanks for your response.
Thats my current query:
WITH
MEMBER [Measures].[percentage] AS '[Measures].[Analysis_Count] /
([Measures].[Analysis_Count], [analysis].[Alle Analysis])',
FORMAT_STRING = 'Percent'
SELECT
{[Measures].[Analysis_Count], [Measures].[percentage]} ON AXIS(0),
{client.members} ON AXIS(1),
{area.members} ON AXIS(2),
{date_month.members} ON AXIS(3),
{analysis.members} ON AXIS(4)
FROM ISDBIv2
something like that I would like to add:
... WHERE ([client].[@.paramname])
Thanks|||ok, no problem. i assume client is a dimension so,
you do this
1. run the query without the parameter in so you get the fields
2. create a second dataset which just gets out the clients
select {[Measures].[Analysis_count]} on AXIS(0),
{[client].Members} on AXIS(1)
from FROM ISDBIv2
3. set up the parameter as you would normally, i.e. click Report -> Report
Parameters etc etc, give it a name 'paramname' and get it to get the clients
from that dataset
4. change the main query to
= "WITH " &
"MEMBER [Measures].[percentage]" & "AS " &
"'[Measures].[Analysis_Count] / ([Measures].[Analysis_Count],
[analysis].[Alle Analysis])', " &
"FORMAT_STRING = 'Percent' " &
"SELECT " &
"{[Measures].[Analysis_Count], [Measures].[percentage]} ON AXIS(0), "&
" {client.members} ON AXIS(1), " &
"{area.members} ON AXIS(2), " &
"{date_month.members} ON AXIS(3), " &
"{analysis.members} ON AXIS(4) " &
"FROM ISDBIv2 " &
"where ([client].[ " & Parameters!paramname.Value & "])"
that should do it, very long winded explanation, but thought i'd cover all
bases just incase. You will notice that you cannot run the query anymore
this is because of the = sign, but do a preview and it should work. Any
problems let me know, cos i've come accross quite a few random ones.
good luck Greg
"mickmack" <access.20.mickmack@.spamgourmet.com> wrote in message
news:1134651740.902628.68600@.o13g2000cwo.googlegroups.com...
> Hi Greg,
> thanks for your response.
> Thats my current query:
> WITH
> MEMBER [Measures].[percentage] AS '[Measures].[Analysis_Count] /
> ([Measures].[Analysis_Count], [analysis].[Alle Analysis])',
> FORMAT_STRING = 'Percent'
> SELECT
> {[Measures].[Analysis_Count], [Measures].[percentage]} ON AXIS(0),
> {client.members} ON AXIS(1),
> {area.members} ON AXIS(2),
> {date_month.members} ON AXIS(3),
> {analysis.members} ON AXIS(4)
> FROM ISDBIv2
> something like that I would like to add:
> ... WHERE ([client].[@.paramname])
> Thanks
>|||Hi Greg,
thanks. that is the solution I were looking for. The first three steps
I had allready done. But the way to build the query by concatenating
the substrings was very useful.
But I got one big problem. Every time I try to do the preview I got an
error message:
"... double dimensions about (independent) axis - during calculating an
axis" (translated to english)
When I delete the " {client.members} ON AXIS(1), " &" - part the
preview is running. Unfortunatly I need this dimension in my matrix.
Do you have some ideas?
thanks|||I guess, I found the orginal english error message. It is:
"... duplicate dimensions accross independent axis..."|||sorry my fault, should've actually read the script.
Take the where clause out completely and put this in
"{[client].[ " & Parameters!paramname.Value & "]} ON AXIS(1), " &
instead of
" {client.members} ON AXIS(1), " &
The problem is that the original script was referring to the same dimension
twice. Sorry bout that,should work fine now. Let me know how it goes.
Greg
"mickmack" <access.20.mickmack@.spamgourmet.com> wrote in message
news:1134725728.305647.143860@.z14g2000cwz.googlegroups.com...
> Hi Greg,
> thanks. that is the solution I were looking for. The first three steps
> I had allready done. But the way to build the query by concatenating
> the substrings was very useful.
> But I got one big problem. Every time I try to do the preview I got an
> error message:
> "... double dimensions about (independent) axis - during calculating an
> axis" (translated to english)
> When I delete the " {client.members} ON AXIS(1), " &" - part the
> preview is running. Unfortunatly I need this dimension in my matrix.
> Do you have some ideas?
> thanks
>|||Yes, indeed it is working pretty fine now :-) But one last question.
What do I have do to filter data where "date_month.members" are older
than "startdate" and younger than "enddate". I know that is normal mdx.
but i havent done this before.
thanks|||From what you've said i assume you have parameters 'startdate' and
'enddate'. So you can do this by applying a filter to the main dataset.
click the '...' button next to the dataset title and click the filters tab.
In the expressions put date field in the expressions box should look
something like Fields!datename.value then put >= in operator box and put the
startdate parameter in the value box, you'll find it by clicking on the
expression option on the drop down from value. Then repeat for the end date
but put <= in the operator box.
That should do it. I can't off the top of my head think how to do it mdx
without referring to the date_month.members twice and we know that will
cause problems.
cheers
Greg
"mickmack" <access.20.mickmack@.spamgourmet.com> wrote in message
news:1134728307.957458.193420@.g44g2000cwa.googlegroups.com...
> Yes, indeed it is working pretty fine now :-) But one last question.
> What do I have do to filter data where "date_month.members" are older
> than "startdate" and younger than "enddate". I know that is normal mdx.
> but i havent done this before.
> thanks
>|||Hi Greg,
maybe you are interested in a solution with mdx. It is possible to
declare a MEMBER-Field defining a startdate and enddate.
SET [filter] AS '[date].[2005].[12].[1] : [date].[2005].[12].[19] '
Unfortunatly this method creates some other problems. First the named
dates "2005/12/01" and "2005/12/19" have to exist in your database.
Besides, if your time dimension contains weeks instead of month this
way is not possible, unless you are programming a function which is
calculating a week number of a date. So this way is not the best one.
I tried to solve my problem by using filters. But that creates some
problems too. First of all, my dimension is a time-dimension. So I dont
know how to call the entire date, because the dimension is divided in
day, month, year. In addition I set the type of my parameter (startdate
and enddate) to DateTime. But everytime I when I am calling the
preview, I get the errormessage, that the type of the parameter is not
correct. Switching the type to string, the filter does not work
correctly.
Do you have some ideas?
Thanks|||sorry could you just clarify how your dates are stored in your database? Is
it three different columns, one for day, month and year?
If that is the case you could concatenate them into a string of one date in
reporting services, or you could add a new column to your database with the
full date in...
Sorry if i've misinterpretted the issue here.
Greg
"mickmack" <access.20.mickmack@.spamgourmet.com> wrote in message
news:1134977793.241846.273900@.g44g2000cwa.googlegroups.com...
> Hi Greg,
> maybe you are interested in a solution with mdx. It is possible to
> declare a MEMBER-Field defining a startdate and enddate.
> SET [filter] AS '[date].[2005].[12].[1] : [date].[2005].[12].[19] '
> Unfortunatly this method creates some other problems. First the named
> dates "2005/12/01" and "2005/12/19" have to exist in your database.
> Besides, if your time dimension contains weeks instead of month this
> way is not possible, unless you are programming a function which is
> calculating a week number of a date. So this way is not the best one.
> I tried to solve my problem by using filters. But that creates some
> problems too. First of all, my dimension is a time-dimension. So I dont
> know how to call the entire date, because the dimension is divided in
> day, month, year. In addition I set the type of my parameter (startdate
> and enddate) to DateTime. But everytime I when I am calling the
> preview, I get the errormessage, that the type of the parameter is not
> correct. Switching the type to string, the filter does not work
> correctly.
> Do you have some ideas?
> Thanks
>

Report parameter with datetime

Hi,

Anyone can help me?

I created a store proc with @.startdate and @.enddate

in query analyser - I run it as Exec SP_Admission '2006/01/01','2006/01/25' and it is ok.

Then I created a report in VS2005 and set the parameter as datetime in report parameter.

if I manually force to fill startdate and enddate with ‘yyyy/mm/dd’ format in preview, it works.

However if I choose the date from the given calendar(Date Time Picker), it doesn’t work

it is said that "The value provided the report parameter 'enddate' is not valid for its type.

Of course it not valid because it always comes with ‘dd/mm/yyyy’ format

I didn't setup the date time picker, it will automatically when I choose datetime as datatype.

any idea what should I do?

thanks,

Susan

Hi Susan,

not exactly brilliant, but

Exec SP_Admission convert(datetime,'12/10/2006',103),convert(datetime,'12/11/2006',103)
should work in your case, so put your parameter instead of '12/10/2006'
|||

Thanks...

someone suggested that in my SP -

I declare as varchar then in where statement I convert it as you mention.

however in report para - I can't set it as datetime but set to string.

it works ok but I can't use the date time picker .

well at least works that way...

Thanks so much

Report parameter validation from report manager in reporting services 2005

Hi,

Whenever user viewing a report using report manager,

how can we validate the input values from user for a datatime type report parameter.

i.e. validating whether the date is correct and in required format.

Thanks in Advance.

Regards

Pintu

The built in parameter UI performs only simple validation (making sure a float parameter has numeric characters, for example). The server will perform a full validation on the parameter value for formatting, culture, data type, etc. If you want more advanced client side validation, you will need to provide custom parameter UI in your own portal. Report Manager is not extensible in this regard.|||

Thanks Brian for providing the useful informaion

|||

Isn't there any built in functionality to prevent SQL injection? Or any build in functionality to prevent other scripts from being inserted i.e. scripts to prevent < > characters etc?

Can you provide a sample of a simple custom UI and tell us where/how to call it?

Thanks

Amy

Report parameter using Member_Key

I am trying to create a parameter driven SSRS report agains my cube but I want the parameter to contain only the Key value of the member I am trying to select. When I use the query designer, I get something like this:

SELECT ( STRTOSET(@.PlantStructureDepartment, CONSTRAINED)

Where @.PlantStructureDepartment is my paramter and contains the Unique Name of the member like this "[Plant Structure].[Department].&[39952]" where "39952" is the Key value.

What I want is to be able to pass the paramter in as "39952" and build the rest of the string. I tried this:

SELECT ( STRTOSET('[Plant Structure].[Department].&[' + @.PlantStructureDepartment + ']', CONSTRAINED)

but that didn't work.

The reason I'm trying to do this is that we are using SharePoint integrated filtering and we have a number of basic relational reports using the same filters as the reports against the cubes. We would like to keep them all using the integer values.

For sake of completeness, here is the full query as it sits right now:

SELECT NON EMPTY { [Measures].[FTT Percent] } ON COLUMNS, NON EMPTY { ([Plant Structure].[Prod Line].[Prod Line].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.PlantStructureDepartment, CONSTRAINED) ) ON COLUMNS FROM [FTT]) WHERE ( IIF( STRTOSET(@.PlantStructureDepartment, CONSTRAINED).Count = 1, STRTOSET(@.PlantStructureDepartment, CONSTRAINED), [Plant Structure].[Department].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I am fairly new to MDX and would appreciate any help or suggestions.

Here is a copy of the query modified to use Adventure Works but illustrating what you requested. Where I've used '1', you would use your report parameter value.

Hope that helps,
Bryan

Code Snippet

SELECT

NON EMPTY { [Measures].[Reseller Sales Amount] } ON COLUMNS,

NON EMPTY { ([Product].[SubCategory].[SubCategory].ALLMEMBERS ) }

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM (

SELECT ( STRTOSET('[Product].[Category].&['+'1'+']', CONSTRAINED) ) ON COLUMNS

FROM [Adventure Works])

WHERE (

IIF( STRTOSET('[Product].[Category].&['+'1'+']', CONSTRAINED).Count = 1,

STRTOSET('[Product].[Category].&['+'1'+']', CONSTRAINED),

[Product].[Category].currentmember

)

)

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

|||

Bryan,

Thanks for the reply. I think you are stating the same thing that I tried already. When I hardcode the value as you suggest, the query works. However, when I set up the parameter, I get this message:

Parser: The query contains the Department parameter, which is not declared. (msmgdsrv)

This is because the paramter isn't declared in the data tab of the report. When I declare the parameter, I get this error:

Query (6, 23) The restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated. (Microsoft SQL Server 2005 Analysis Services)

The parameters page of the Data tab only allows you to tie a parameter to a dimension of the cube. I think I need a way around that.

Thanks again for the idea. If you have another, I'd appreciate it.

Chris

|||

The CONSTRAINED keyword is there to prevent an injection of a function call into the statement. While I think this is a good idea, I get the same error as you do when no injection is occuring. While I would recommend digging into the MDX statement a little deeper to understand the issue, I have in the past simply removed the CONSTRAINED keyword.

Not sure this is the best of ideas, but .....

Bryan

|||

That's what it was.

Thanks!

|||

You have CONSTRAINED in several areas, do you need to remove them all. Can you post your modified code?

Thanks,

Shari

|||I don't have access to my query right at the moment (I'm at a different location) but suffice it to say, I removed all of the CONSTRAINED flags and it worked. To the point made earlier, I'm not sure if it was a good idea, but it got me around the issue I was having.|||

I tried that, removing all the CONSTRAINED but got another error about STRTOSET function expects a tuple set, string was supplied .... . I am anxious to see your query if you could share (when you have access).

Thanks,

Shari