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