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 in SELECT

I have a report parameter (called Column) that I would like
incorporated in the SELECT Statement. For example, the select is :
SELECT @.Column AS Expr1 FROM Table
The report runs after I select the appropriate value for the parameter,
but I do not get the correct data. If I select Test for the column
parameter, then Test appears for the value in the report--instead of
the correct data.
Does anyone know if building a dynamic column select statement is
possible like this?You have to create an expression for the SQL.
= "Select " & Parameters!Paramname.Value & " as Expr1 from Table"
Be sure you have your field list already because RS will not be able to
detect the fields for you once you use an expression.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Aaron" <aaron.heller@.gmail.com> wrote in message
news:1139495719.116692.50590@.g47g2000cwa.googlegroups.com...
>I have a report parameter (called Column) that I would like
> incorporated in the SELECT Statement. For example, the select is :
> SELECT @.Column AS Expr1 FROM Table
> The report runs after I select the appropriate value for the parameter,
> but I do not get the correct data. If I select Test for the column
> parameter, then Test appears for the value in the report--instead of
> the correct data.
> Does anyone know if building a dynamic column select statement is
> possible like this?
>

Report Parameter Default Value from Column

DataSet2:
select MyCol1 from MySecondTable
In the Report Parameters dialog box, I add MyCol1 as a multi-value
parameter. When I run the report, I can select one or more values from the
MyCol1 drop-down. This works great.
The problem is I want the first value in MyCol1 to be the default value in
the drop-down. In the Default Values box, I select Non-queried, and use the
expression =Parameters!MyCol1.Value(0) This seems exactly right to me.
With the default value set as above, the report fails to run with the
following error: Error during processing of 'MyCol1' report parameter.
Any suggestions on how to fix this?
Thanks,
--
RandyYou can create a hidden parameter but not multi-valued. Set its default
value as coming from a query and select MyCol1 in DataSet2, it should pull
the first value from the dataset. Then you can set the default value of the
multi-valued parameter to the value of the hidden parameter.
"randy1200" wrote:
> DataSet2:
> select MyCol1 from MySecondTable
> In the Report Parameters dialog box, I add MyCol1 as a multi-value
> parameter. When I run the report, I can select one or more values from the
> MyCol1 drop-down. This works great.
> The problem is I want the first value in MyCol1 to be the default value in
> the drop-down. In the Default Values box, I select Non-queried, and use the
> expression =Parameters!MyCol1.Value(0) This seems exactly right to me.
> With the default value set as above, the report fails to run with the
> following error: Error during processing of 'MyCol1' report parameter.
> Any suggestions on how to fix this?
> Thanks,
> --
> Randy|||I have done this by creating another dataset called someting like
MyCol1Default defined as:
SELECT TOP1 MyCol1 FROM MySecondTable
or
SELECT MIN(MyCol1) FROM MySecondTable
Then in the report parameter definition specify that the default comes from
dataset MyCol1Default and that the value is MyCol1.
HTH,
Magendo_man
"randy1200" wrote:
> DataSet2:
> select MyCol1 from MySecondTable
> In the Report Parameters dialog box, I add MyCol1 as a multi-value
> parameter. When I run the report, I can select one or more values from the
> MyCol1 drop-down. This works great.
> The problem is I want the first value in MyCol1 to be the default value in
> the drop-down. In the Default Values box, I select Non-queried, and use the
> expression =Parameters!MyCol1.Value(0) This seems exactly right to me.
> With the default value set as above, the report fails to run with the
> following error: Error during processing of 'MyCol1' report parameter.
> Any suggestions on how to fix this?
> Thanks,
> --
> Randy

Report Parameter- 'All'

I am trying to make a report parameter that allows the user to select one of three values. Two of the values are what;s stored in the view's field right now: (as char 1) 'O' (open) or 'C' (closed). I need to add the choice 'All' that will show BOTH open and closed.

I made a dataset for the parameters as

select distinct valve_position

from view monitoring

where (not(valve_position is null))

Of course, that only yields choices of O or C, so I changed it to

select distinct valve_position , ''

from view monitoring

where (not(valve_position is null))

union

select 'All', -1

Then I changed the base query dataset to include

where valve_position = @.valve or valve_position = -1

When I run the report, O and C work, but All doesn't retrieve any records.

How do I something so simple? Of course, I see MSRS 2005 has a multi value check box...

Thanks for any help.

The comparisong valve_position = -1 should be @.valve = -1

For details about multivalue parameters please look at http://msdn2.microsoft.com/en-US/library/ms155917(SQL.90).aspx

|||Thank you!!! That worked!

Report parameter - > Select All Option is not coming

Hi,
I have created a report parameter with multi-value option.
In preview mode i can see "Select All option" but once deployed i can
not see that option over web. any idea?
Regards,
SriOn May 11, 9:34 am, Sriman <Sri...@.discussions.microsoft.com> wrote:
> Hi,
> I have created a report parameter with multi-value option.
> In preview mode i can see "Select All option" but once deployed i can
> not see that option over web. any idea?
> Regards,
> Sri
Without knowing more details, it sounds like you have SQL Server 2005
SP1 installed on the production box. If so, you will want to install
SP2. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Report Paramert problem

I am trying to put a parameter on my report to select a field from my query but keep getting this error. What am I doing wrong?

[rsInvalidReportParameterDependency] The report parameter ‘Program’ has a DefaultValue or a ValidValue that depends on the report parameter “Program”. Forward dependencies are not valid.

Build complete -- 1 errors, 0 warnings

Hi,

For the report parameter 'Program',On the Data tab Select on the Dataset name through which you are sending this 'Program' parameter to the database and click on this symbol "..." adjacent to the dataSet selection drop down list.Now a dataset window opens,From that window click on the Parameters tab.Parameters:

Name Value

If the parameter Name:@.Program

Value:Parameters!Program.Value

then you get this error.

Hope this helps.

|||

Mr4100

[rsInvalidReportParameterDependency] The report parameter ‘Program’ has a DefaultValue or a ValidValue that depends on the report parameter “Program”. Forward dependencies are not valid.

This problem comes from when you are using a parameter that has reference to another parameter. It appears that "Program" is depended on another parameter in your report. The solution is to move the "Program" parameter in the parameterlist ahead or the other parameter.

For Example: if City is depend on State

@.Date

@.City

@.State this will product the above error

Change to this:

@.Date

@.State

@.City

This will fix the problem

Ham

|||

I can't move this ahead or anything this is the only parameter I have. I am trying to assign a value from my dataset back to the parameter using a prompt. I want the user to be able to select their program which would then compare the parameter value to the dataset value and return the correct data in the report atleast this how I think it should work. I've tried using a filter in the dataset properties but it won't work either.

|||I don't understand Mahima, this is what I have done and the reason I'm getting the error?|||

mr4100.

Parameters are evaluated and executed before your data is return from the dataset, so I would suggest on your parameter "default values" - do a returnset of all avaliable problems. Also, you should be able to pass this parameter value into your stored procedure to filter only for the "programs" selected. Hint, go to data tab, select "...", select the parameter tab and make sure that the parameter your passing to your Stored Procedure to the same name as your report parameter.

Ham

|||Sorry should read "available programs"|||

I took a different route and figured it out.

Thanks.

Report only showing a single record

I have a DataSet which is defined as "SELECT * FROM Table". There are MANY
records in Table. However, the report only shows a single record/page. I
placed the fields on the report in the "Body". What am I missing here?Nevermind... Got it. It's the List item.
"Dan" wrote:
> I have a DataSet which is defined as "SELECT * FROM Table". There are MANY
> records in Table. However, the report only shows a single record/page. I
> placed the fields on the report in the "Body". What am I missing here?|||When you place the fields on the blank layout surface you are not using any
of the controls that know about multiple rows. You need to place either a
table control or a list control and then drag and drop onto them.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:7F61EA6A-DB25-43FC-9117-84A2E7E037AA@.microsoft.com...
>I have a DataSet which is defined as "SELECT * FROM Table". There are MANY
> records in Table. However, the report only shows a single record/page. I
> placed the fields on the report in the "Body". What am I missing here?sql

Report Model is generating without data

I have been generating report models for users to use with Report Builder and there is no data when they select the model. I noticed that the tables I chose did not have a primary key and when I chose a different table, with a primary key, and generated a model from it, then there was data for the user to use in Report Builder.

Is there a documented work around or will I need to set a primary key on each table?

You don't need a physical PK on each of your tables. Instead, open up the DSV in your Report Model project, right click each table, and choose "Set Logical Primary Key" and the appropriate field. You'll then need to rebuild the model. See if this works for you!|||Thanks Russell! That worked just fine after I rebuilt the model.

Report is not printing selected parameters

I have a report that a user can select one of five parameters. Lets just say
those parameters are:
ALL
North
South
East
West
The default value is set to ALL and multi-values are not allowed. If the
end-user selects North, the report displays just fine in Preview mode. When
the user prints the report, the entire report is printed instead of just
printing the report for North.
Any suggestions would be appreciated. Thanks!
AmedeoPlease disregard this question. I deployed the reports to the report manager
and everything is working fine from the report manager. If I select East and
then click print, it only prints the report for East.
I'm not sure why is wasn't working from the preview mode tab but I've got
other things to spend my time trying to figure out.
Thanks!
"Amedeo Feroce" <aferoce@.akdhc.com> wrote in message
news:%23ytf7bKwGHA.4968@.TK2MSFTNGP03.phx.gbl...
>I have a report that a user can select one of five parameters. Lets just
>say those parameters are:
> ALL
> North
> South
> East
> West
> The default value is set to ALL and multi-values are not allowed. If the
> end-user selects North, the report displays just fine in Preview mode.
> When the user prints the report, the entire report is printed instead of
> just printing the report for North.
> Any suggestions would be appreciated. Thanks!
> Amedeo
>

Report Help..!

Hi All,

I have a set of "VB reports" binded to a wizard, where you can select fields which will appear on the report. Also you can change Group By and Order By Fields.

Initiallly this was designed and used for "A4" size papers. But now I need dynamically adjust the report for selected paper size. Anyone can guide me on this...?

I am hoping to use papers larger than "A4" only....

- Help meHow about designing seperate files for different sizes and display them accordingly?|||Dim Report As New dsrVoucher

Report.SetUserPaperSize 1400, 2200
Report.PaperSize = crPaperUser

then so on...

but.. size u 'll given is in pixel..
not in inches..cms..
well.. as i also suggest.. the better way to do so is to design different report..with different page setup...
crystal report have some very unfortunate and teasing issues.. while printing .. bcoz of printer based reporting system..

but very some time teasing....and always comfortable at any type of reporting...
neways
best of luck

Report header and footer disappear when exporting

When I export my report to a PDF, everything is fine. However, if I select a group to drill down and then export that drilled down report to a pdf, the reports header and footer disappear. How can I fix this? My report has a few subreports, so that might have something to do with it.
ThanksSee if you find solution here
http://support.businessobjects.com/