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

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 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 not printing

I'm having a problem with one machine especifically. On my machine if I hit
the print buttom on Report Manager, I get a printing dialog box for a long
time but nothing is printed (no error message neither). If I try the same
thing in another machine, it prints fine. I though about uninstalling and
reinstalling the client ActiveX, but don't know how to do it. Any suggestions,
Thanks,
Carmen.To uninstall the activex control, go to the tools menu in IE. Then go to
Internet Options->Settings->View Objects. Then delete the RSClientPrint
control.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Carmen" <Carmen@.discussions.microsoft.com> wrote in message
news:210BFC5F-91E8-4AE4-BBBD-AC1F7817FD94@.microsoft.com...
> I'm having a problem with one machine especifically. On my machine if I
> hit
> the print buttom on Report Manager, I get a printing dialog box for a long
> time but nothing is printed (no error message neither). If I try the same
> thing in another machine, it prints fine. I though about uninstalling and
> reinstalling the client ActiveX, but don't know how to do it. Any
> suggestions,
> Thanks,
> Carmen.|||Thanks, Daniel. It worked!
Carmen
"Daniel Reib [MSFT]" wrote:
> To uninstall the activex control, go to the tools menu in IE. Then go to
> Internet Options->Settings->View Objects. Then delete the RSClientPrint
> control.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Carmen" <Carmen@.discussions.microsoft.com> wrote in message
> news:210BFC5F-91E8-4AE4-BBBD-AC1F7817FD94@.microsoft.com...
> > I'm having a problem with one machine especifically. On my machine if I
> > hit
> > the print buttom on Report Manager, I get a printing dialog box for a long
> > time but nothing is printed (no error message neither). If I try the same
> > thing in another machine, it prints fine. I though about uninstalling and
> > reinstalling the client ActiveX, but don't know how to do it. Any
> > suggestions,
> >
> > Thanks,
> >
> > Carmen.
>
>

Report Manager/Viewer Help!!

The parameters box in the report view displays in tabular format if you have
at least two parameters. Can I change this in anyway so it displays in
columan format.
Current display
1. parameter box 2. parameter box
3. parameter box 4. parameter box
Can I change it to display like:
1. parameter box
2. parameter box
3. parameter box
4. parameter boxThe answer is no you can't! Sorry.
You could generate you're parameter asp page and run the report with
url access from a button on the asp page. That way you can be much more
fancy with the parameters.
Chris
OriginalStealth wrote:
> The parameters box in the report view displays in tabular format if
> you have at least two parameters. Can I change this in anyway so it
> displays in columan format.
> Current display
> 1. parameter box 2. parameter box
> 3. parameter box 4. parameter box
> Can I change it to display like:
> 1. parameter box
> 2. parameter box
> 3. parameter box
> 4. parameter box

Report Manager on 64 bit machine does not recognize 32 bit data sources

I'm a report developer who has created some reportsd against sybase data on another server. I am developing on a 64 bit Server 2003 box using SQL 2005 Reporting services. I hace created 32 bit ODBC data dsns using the supplied utitlity (odbcad32.exe). As I am creating the reports I can pull data using the data sources without a problem, however when I deploy my datasource and report to Report manager and run I get the following:

An error has occurred during report processing.

Cannot create a connection to data source 'Chicago_Reporting'.

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

This has been driving me crazy for 2 days now. Any help would be very much appreciated.

Thanks

Hi Rick!

I use a 64-bit Windows Server 2003 Enterprise server also and connect to about seven different database types, although Sybase is not one of them. Have you installed SQL Server 2005 Service Pack 2?

|||

HI Chuck,

Thanks for the response. I have limited experience with server admin stuff. I was brought for my reporting experience more then anything else. As far as I can tell our build number is 2005.090.3042.00 which equates to the original version of SP2 on the build list.

Rick

Report Manager error with OK button

I'm just installed SQL Reporting Services 2000 with SP2 on a Windows Server
2003 box that is fully patched.
I have the .net framework v1.1 installed and running. My issue is that when
I try to either add roles or modify anything in the site settings the OK
button does nothing. The cancel button seems to work find. However
anywhere in any screen that you see the Ok button is there..just not
clickable.
Please help.
--
Report DeveloperThis sort of thing is usually because anonymous security has been assigned
for IIS. If that is the case then everyone is the same identity when logged
in, i.e. there is no administrator. Have you done anything like that with
your site?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:FC320F8E-DAA6-4B85-93A3-93DC3F9B6DA0@.microsoft.com...
> I'm just installed SQL Reporting Services 2000 with SP2 on a Windows
> Server
> 2003 box that is fully patched.
> I have the .net framework v1.1 installed and running. My issue is that
> when
> I try to either add roles or modify anything in the site settings the OK
> button does nothing. The cancel button seems to work find. However
> anywhere in any screen that you see the Ok button is there..just not
> clickable.
> Please help.
> --
> Report Developer|||You nailed that one. At first I did have anoynmous access to the site
enabled..However I have since disabled that...
People currently in the local administrators group have the access to create
folders etc..However people who are not only see the home folder and the My
Subscriptions..
--
Report Developer
"Bruce L-C [MVP]" wrote:
> This sort of thing is usually because anonymous security has been assigned
> for IIS. If that is the case then everyone is the same identity when logged
> in, i.e. there is no administrator. Have you done anything like that with
> your site?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:FC320F8E-DAA6-4B85-93A3-93DC3F9B6DA0@.microsoft.com...
> > I'm just installed SQL Reporting Services 2000 with SP2 on a Windows
> > Server
> > 2003 box that is fully patched.
> >
> > I have the .net framework v1.1 installed and running. My issue is that
> > when
> > I try to either add roles or modify anything in the site settings the OK
> > button does nothing. The cancel button seems to work find. However
> > anywhere in any screen that you see the Ok button is there..just not
> > clickable.
> >
> > Please help.
> > --
> > Report Developer
>
>|||It sounds like you do not have any security setup. RS uses roll based
security. This is what I do. Create a local group on the RS machine, I call
it Reports. To this group add any individual users or domain groups etc that
you want. This group is for browse only. You can have other groups for other
rights but to start off with you want to have a group that the users can
view and run reports. Then you go to the home of report managers,
properties, click on New Role assignment, group or username put in Reports
(or whatever you called the group when you created it). Check the box that
says browse.
When you do this from home all folders underneath will inherit this role. If
you have a particular folder that you don't want to have that group to have
access then you can go to that folder and modify the permission.
Hopefully that will get you in the right direction.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:F4C40FA5-D505-4906-A519-85834D382ABF@.microsoft.com...
> You nailed that one. At first I did have anoynmous access to the site
> enabled..However I have since disabled that...
> People currently in the local administrators group have the access to
create
> folders etc..However people who are not only see the home folder and the
My
> Subscriptions..
> --
> Report Developer
>
> "Bruce L-C [MVP]" wrote:
> > This sort of thing is usually because anonymous security has been
assigned
> > for IIS. If that is the case then everyone is the same identity when
logged
> > in, i.e. there is no administrator. Have you done anything like that
with
> > your site?
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Matt" <Matt@.discussions.microsoft.com> wrote in message
> > news:FC320F8E-DAA6-4B85-93A3-93DC3F9B6DA0@.microsoft.com...
> > > I'm just installed SQL Reporting Services 2000 with SP2 on a Windows
> > > Server
> > > 2003 box that is fully patched.
> > >
> > > I have the .net framework v1.1 installed and running. My issue is
that
> > > when
> > > I try to either add roles or modify anything in the site settings the
OK
> > > button does nothing. The cancel button seems to work find. However
> > > anywhere in any screen that you see the Ok button is there..just not
> > > clickable.
> > >
> > > Please help.
> > > --
> > > Report Developer
> >
> >
> >

report manager

i have a sql server 2005 standard box with a \Dev engine and a \Tst engine on it

for dev,

i cannnot see the site settings from within Report Manager

for tst,

i cannnot see the new folder, new data source, upload file, site settings from within Report Manager

my windows domain account is in the Local Admin for that box.

what's the issues here?

anyone?

|||You may no longer have RS permissions for creating items and modifying site settings. Although there are default role assignments that give local Administrators Content Manager and System Administrator rights, it is possible to delete these role assignments. If that happens, local Administrators end up with only Manage catalog item permissions and Manage site-level permissions, and you will have to recreate role assignments giving local Administrators Content Manager (or whatever you want) permissions.|||

Mike, could you or anyone points me to where I should go to recreate the role assingments for local administrators. I have full control permissions on the ReportManager folder but I don't get all the admin features when I launch the http://localhost/reports page. Thanks for your help.

|||

I found the solution here: http://msdn2.microsoft.com/en-us/library/bb283249.aspx

report manager

i have a sql server 2005 standard box with a \Dev engine and a \Tst engine on it

for dev,

i cannnot see the site settings from within Report Manager

for tst,

i cannnot see the new folder, new data source, upload file, site settings from within Report Manager

my windows domain account is in the Local Admin for that box.

what's the issues here?

anyone?

|||You may no longer have RS permissions for creating items and modifying site settings. Although there are default role assignments that give local Administrators Content Manager and System Administrator rights, it is possible to delete these role assignments. If that happens, local Administrators end up with only Manage catalog item permissions and Manage site-level permissions, and you will have to recreate role assignments giving local Administrators Content Manager (or whatever you want) permissions.|||

Mike, could you or anyone points me to where I should go to recreate the role assingments for local administrators. I have full control permissions on the ReportManager folder but I don't get all the admin features when I launch the http://localhost/reports page. Thanks for your help.

|||

I found the solution here: http://msdn2.microsoft.com/en-us/library/bb283249.aspx

Report item not linked to a dataset

I have a need to add a text box to a report. The text box will have an
expression that I need to have that captues the value of a field from a
second data set associated with the report. I add a text box but in the edit
expresssion process I can't access any fields (from any data set) and i get
the message Report item not linked to a dataset. I'm not trying to add a new
table or anything like that. I just want to be able to grab that field value
and disply.
--
Thanks, JimOn Sep 21, 11:38 am, Jim B <J...@.lightning.com> wrote:
> I have a need to add a text box to a report. The text box will have an
> expression that I need to have that captues the value of a field from a
> second data set associated with the report. I add a text box but in the edit
> expresssion process I can't access any fields (from any data set) and i get
> the message Report item not linked to a dataset. I'm not trying to add a new
> table or anything like that. I just want to be able to grab that field value
> and disply.
> --
> Thanks, Jim
If I'm understanding you correctly, you should be able to reference a
dataset via an aggregate expression. Something like this should work.
=Max(Fields!SomeFieldName.Value, "SomeDatasetName")
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||That's exactly what I needed to know. Thanks for your response to my question.
--
Thanks, Jim
"EMartinez" wrote:
> On Sep 21, 11:38 am, Jim B <J...@.lightning.com> wrote:
> > I have a need to add a text box to a report. The text box will have an
> > expression that I need to have that captues the value of a field from a
> > second data set associated with the report. I add a text box but in the edit
> > expresssion process I can't access any fields (from any data set) and i get
> > the message Report item not linked to a dataset. I'm not trying to add a new
> > table or anything like that. I just want to be able to grab that field value
> > and disply.
> > --
> > Thanks, Jim
>
> If I'm understanding you correctly, you should be able to reference a
> dataset via an aggregate expression. Something like this should work.
> =Max(Fields!SomeFieldName.Value, "SomeDatasetName")
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Sep 23, 9:20 am, Jim B <J...@.lightning.com> wrote:
> That's exactly what I needed to know. Thanks for your response to my question.
> --
> Thanks, Jim
> "EMartinez" wrote:
> > On Sep 21, 11:38 am, Jim B <J...@.lightning.com> wrote:
> > > I have a need to add a text box to a report. The text box will have an
> > > expression that I need to have that captues the value of a field from a
> > > second data set associated with the report. I add a text box but in the edit
> > > expresssion process I can't access any fields (from any data set) and i get
> > > the message Report item not linked to a dataset. I'm not trying to add a new
> > > table or anything like that. I just want to be able to grab that field value
> > > and disply.
> > > --
> > > Thanks, Jim
> > If I'm understanding you correctly, you should be able to reference a
> > dataset via an aggregate expression. Something like this should work.
> > =Max(Fields!SomeFieldName.Value, "SomeDatasetName")
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Glad I could help.
Regards,
Enrique Martinez
Sr. Software Consultant

Report header when using columns

Hi,
I am designing a report with two columns, and I'm trying to add a header
only on the first page of the report. I've added a text box in the body of
the report, but my problem is that this textbox can't be larger than the
column size.
Is there any way to put this header on the whole width of the report ?
Thanks in advance.
--
Florent MERYHi,
No need to add text boxes. Just follow the below steps
Right click on the table-> select properties->General Tab-> Uncheck the
"Repeat header row on each page"..
Regards,
Sriman.
"Florent" wrote:
> Hi,
> I am designing a report with two columns, and I'm trying to add a header
> only on the first page of the report. I've added a text box in the body of
> the report, but my problem is that this textbox can't be larger than the
> column size.
> Is there any way to put this header on the whole width of the report ?
> Thanks in advance.
> --
> Florent MERY|||Thank you for your answer, but it's not exactly what I'm trying to do. As I'm
using 2 columns in my report, the table headers will only appear on the top
of the first column.
What I would like to do is to have in fact a kind of title zone for my
report, only for the first page.
If I use the Page header, with a conditional visibility, I always get a
blanck zone at the top of every page, even when the header is not visible.
And if I use the table header rows, it only appear on the first column of
the page, and it does not use the whole width of the page.
Any workaround for my problem ?
Thanks in advance...
--
Florent MERY
"Sriman" wrote:
> Hi,
> No need to add text boxes. Just follow the below steps
> Right click on the table-> select properties->General Tab-> Uncheck the
> "Repeat header row on each page"..
> Regards,
> Sriman.
> "Florent" wrote:
> > Hi,
> >
> > I am designing a report with two columns, and I'm trying to add a header
> > only on the first page of the report. I've added a text box in the body of
> > the report, but my problem is that this textbox can't be larger than the
> > column size.
> >
> > Is there any way to put this header on the whole width of the report ?
> >
> > Thanks in advance.
> >
> > --
> > Florent MERY