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

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 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 validation from report manager in reporting services 2005

Hi,

Whenever user viewing a report using report manager,

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

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

Thanks in Advance.

Regards

Pintu

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

Thanks Brian for providing the useful informaion

|||

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

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

Thanks

Amy

Report Parameter Selection aid fir user.

Hi,
Am curious, have a report paramater the user selects from a pull down
list that is smart enough to go to the record containing the first letter the
user types in. Is there any more intelligence we can add to this ? For
example expanding the record selection to perhaps two or three characters the
user types in.
Thanks, Steve.You can write your own custom toolbar using ASP.NET. However, you would
lose some interactive functionality like zoom.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:BC425483-8FCB-4DD1-91FE-C1FF6535FC3F@.microsoft.com...
> Hi,
> Am curious, have a report paramater the user selects from a pull down
> list that is smart enough to go to the record containing the first letter
> the
> user types in. Is there any more intelligence we can add to this ? For
> example expanding the record selection to perhaps two or three characters
> the
> user types in.
> Thanks, Steve.|||Thanks for the reply Jeff ! Sounds like for this purpose it might be
overkill. Steve.
"Jeff A. Stucker" wrote:
> You can write your own custom toolbar using ASP.NET. However, you would
> lose some interactive functionality like zoom.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:BC425483-8FCB-4DD1-91FE-C1FF6535FC3F@.microsoft.com...
> > Hi,
> > Am curious, have a report paramater the user selects from a pull down
> > list that is smart enough to go to the record containing the first letter
> > the
> > user types in. Is there any more intelligence we can add to this ? For
> > example expanding the record selection to perhaps two or three characters
> > the
> > user types in.
> >
> > Thanks, Steve.
>
>

Report Parameter help

In am trying to create a report using a stored procedure having two
parameters. The Avalilable values for the parameters come from queries. The
user while running the report may not select one of the parameters i.e. leave
it blank. When I leave one of the parameters blank the report does not run, I
have to select some value for both parameters for report to run.
Can Someone let me know how to run a report without passing a value in
parameter.
Thanks
AnoopHi Anoop
You will always have to pass the parameter to the report because the
stored procedure is expecting it and won't run unless it has it.
The way round this is to pass a null value and allow for it in your
stored procedure.
Wherever you use the parameter in the stored procedure, you could use
the following syntax:
(@.QueueId IS NULL OR QueueId = @.QueueId)
eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
This would mean that if the @.QueueId parameter is null, it will ignore
it and return all the queues.
Hope this helps?
Regards
Dave|||Hi Dave,
Thanks for the response.
I am already handling NULLs in the Stored procedure. The problem that I am
having is that my report parameters are populated using queries which do not
have null, also user may not select all report parameters. When user does not
select a parameter the report does not pass NULL to sp infact it does not run
at all. Is there a way around this.
Thanks and Regards,
Anoop
"Bungle" wrote:
> Hi Anoop
> You will always have to pass the parameter to the report because the
> stored procedure is expecting it and won't run unless it has it.
> The way round this is to pass a null value and allow for it in your
> stored procedure.
> Wherever you use the parameter in the stored procedure, you could use
> the following syntax:
> (@.QueueId IS NULL OR QueueId = @.QueueId)
> eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
> This would mean that if the @.QueueId parameter is null, it will ignore
> it and return all the queues.
> Hope this helps?
> Regards
> Dave
>|||Unless you allow null or blank in the report, or have a default value , the
user MUST select a value for each parameter or the report will not run...
If your issue is that you wish to account for Null or blanks, then the prior
respondant's method is commonly used, although it may perform badly..
--
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
"Anoop" <Anoop@.discussions.microsoft.com> wrote in message
news:260B6167-9D77-40DC-83AE-1D172820E0E0@.microsoft.com...
> Hi Dave,
> Thanks for the response.
> I am already handling NULLs in the Stored procedure. The problem that I am
> having is that my report parameters are populated using queries which do
not
> have null, also user may not select all report parameters. When user does
not
> select a parameter the report does not pass NULL to sp infact it does not
run
> at all. Is there a way around this.
> Thanks and Regards,
> Anoop
>
> "Bungle" wrote:
> > Hi Anoop
> >
> > You will always have to pass the parameter to the report because the
> > stored procedure is expecting it and won't run unless it has it.
> >
> > The way round this is to pass a null value and allow for it in your
> > stored procedure.
> >
> > Wherever you use the parameter in the stored procedure, you could use
> > the following syntax:
> >
> > (@.QueueId IS NULL OR QueueId = @.QueueId)
> >
> > eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
> >
> > This would mean that if the @.QueueId parameter is null, it will ignore
> > it and return all the queues.
> >
> > Hope this helps?
> >
> > Regards
> >
> > Dave
> >
> >|||I think I cannot do what I am trying to do through reporting services as each
parameter must be supplied with a value for a report to run.
Thanks a lot for your help.
"Wayne Snyder" wrote:
> Unless you allow null or blank in the report, or have a default value , the
> user MUST select a value for each parameter or the report will not run...
> If your issue is that you wish to account for Null or blanks, then the prior
> respondant's method is commonly used, although it may perform badly..
> --
> 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
> "Anoop" <Anoop@.discussions.microsoft.com> wrote in message
> news:260B6167-9D77-40DC-83AE-1D172820E0E0@.microsoft.com...
> > Hi Dave,
> > Thanks for the response.
> >
> > I am already handling NULLs in the Stored procedure. The problem that I am
> > having is that my report parameters are populated using queries which do
> not
> > have null, also user may not select all report parameters. When user does
> not
> > select a parameter the report does not pass NULL to sp infact it does not
> run
> > at all. Is there a way around this.
> >
> > Thanks and Regards,
> >
> > Anoop
> >
> >
> > "Bungle" wrote:
> >
> > > Hi Anoop
> > >
> > > You will always have to pass the parameter to the report because the
> > > stored procedure is expecting it and won't run unless it has it.
> > >
> > > The way round this is to pass a null value and allow for it in your
> > > stored procedure.
> > >
> > > Wherever you use the parameter in the stored procedure, you could use
> > > the following syntax:
> > >
> > > (@.QueueId IS NULL OR QueueId = @.QueueId)
> > >
> > > eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
> > >
> > > This would mean that if the @.QueueId parameter is null, it will ignore
> > > it and return all the queues.
> > >
> > > Hope this helps?
> > >
> > > Regards
> > >
> > > Dave
> > >
> > >
>
>|||Can you modify the query that loads the parameters to add another line like
'None' that has a value of NULL? This would be similar to the method used to
add an 'All' selection.
Actually, I may need to try this myself for 1 of my reports.
Neil
"Anoop" wrote:
> I think I cannot do what I am trying to do through reporting services as each
> parameter must be supplied with a value for a report to run.
> Thanks a lot for your help.
> "Wayne Snyder" wrote:
> > Unless you allow null or blank in the report, or have a default value , the
> > user MUST select a value for each parameter or the report will not run...
> >
> > If your issue is that you wish to account for Null or blanks, then the prior
> > respondant's method is commonly used, although it may perform badly..
> >
> > --
> > 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
> >
> > "Anoop" <Anoop@.discussions.microsoft.com> wrote in message
> > news:260B6167-9D77-40DC-83AE-1D172820E0E0@.microsoft.com...
> > > Hi Dave,
> > > Thanks for the response.
> > >
> > > I am already handling NULLs in the Stored procedure. The problem that I am
> > > having is that my report parameters are populated using queries which do
> > not
> > > have null, also user may not select all report parameters. When user does
> > not
> > > select a parameter the report does not pass NULL to sp infact it does not
> > run
> > > at all. Is there a way around this.
> > >
> > > Thanks and Regards,
> > >
> > > Anoop
> > >
> > >
> > > "Bungle" wrote:
> > >
> > > > Hi Anoop
> > > >
> > > > You will always have to pass the parameter to the report because the
> > > > stored procedure is expecting it and won't run unless it has it.
> > > >
> > > > The way round this is to pass a null value and allow for it in your
> > > > stored procedure.
> > > >
> > > > Wherever you use the parameter in the stored procedure, you could use
> > > > the following syntax:
> > > >
> > > > (@.QueueId IS NULL OR QueueId = @.QueueId)
> > > >
> > > > eg. where (@.QueueId IS NULL OR QueueId = @.QueueId)
> > > >
> > > > This would mean that if the @.QueueId parameter is null, it will ignore
> > > > it and return all the queues.
> > > >
> > > > Hope this helps?
> > > >
> > > > Regards
> > > >
> > > > Dave
> > > >
> > > >
> >
> >
> >|||Hi,
I will try to be as clear as possible, excuse my english
What you will need to do is for the SP's that are used to generate the
parameter list add a value that will mean "All Possible value" like so:
create procedure <ParamList>
AS
declare @.paramtable TABLE (ordersequence TINYINT,
id INT,
VALUE VARCHAR(100))
insert into @.paramtable values (1,0,"All Possible values")
insert into @.paramtable
select 1,id,value
from table1
select id,value
from @.paramtable
order by ordersequence,value
Then you will need to setup your parameter in RS and define the default
value of this parameter to 0.
You will also need to modify your report SP to manage the case of the "All
Possible values" param value...
Something like this should work
Create Procedure <Report>
(
@.ParamValue INT
)
AS
select col1,col2,col3,col4
from table1
where col1 = case when @.ParamValue = 0 then col1
else @.ParamValue
end
HTH,
Aiwa
"Anoop" wrote:
> In am trying to create a report using a stored procedure having two
> parameters. The Avalilable values for the parameters come from queries. The
> user while running the report may not select one of the parameters i.e. leave
> it blank. When I leave one of the parameters blank the report does not run, I
> have to select some value for both parameters for report to run.
> Can Someone let me know how to run a report without passing a value in
> parameter.
> Thanks
> Anoop|||Hi,
Thanks for your help.
As I have 10 parameters for my report. I have now created tables which have
0 or blank as a value. These tables are used to populate the Lists for report
parameters. It is a long process but it is working.
Hope there was a simpler solution. Anyways..
Thanks
Anoop
"Aiwa" wrote:
> Hi,
> I will try to be as clear as possible, excuse my english
> What you will need to do is for the SP's that are used to generate the
> parameter list add a value that will mean "All Possible value" like so:
> create procedure <ParamList>
> AS
> declare @.paramtable TABLE (ordersequence TINYINT,
> id INT,
> VALUE VARCHAR(100))
> insert into @.paramtable values (1,0,"All Possible values")
> insert into @.paramtable
> select 1,id,value
> from table1
> select id,value
> from @.paramtable
> order by ordersequence,value
>
> Then you will need to setup your parameter in RS and define the default
> value of this parameter to 0.
> You will also need to modify your report SP to manage the case of the "All
> Possible values" param value...
> Something like this should work
> Create Procedure <Report>
> (
> @.ParamValue INT
> )
> AS
> select col1,col2,col3,col4
> from table1
> where col1 = case when @.ParamValue = 0 then col1
> else @.ParamValue
> end
> HTH,
> Aiwa
> "Anoop" wrote:
> > In am trying to create a report using a stored procedure having two
> > parameters. The Avalilable values for the parameters come from queries. The
> > user while running the report may not select one of the parameters i.e. leave
> > it blank. When I leave one of the parameters blank the report does not run, I
> > have to select some value for both parameters for report to run.
> >
> > Can Someone let me know how to run a report without passing a value in
> > parameter.
> >
> > Thanks
> > Anoopsql

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 out an html string

Is it possible to report out an html string into the rich format?
--> I use an html control (RichTextBox 2.5) that allows the user to create
the main body of a quote in a wysiwyg environment, and that formatting is
successfully saved as an html string to the SQL db and successfully displayed
when opening back up in the control.
--> Will Reporting Services allow me to take that html string and display in
the main body of a report, with the formatting applied (i.e. bold, bullets,
etc.)?
Any help would be greatly appreciated, from saving as a certain datatype in
SQL to rendering the code in the Reporting Services.
Many thanks,
AndyNo. This would most likely would be a security hole if reporting services
allowed that.
However, you could build your own ASP page around reporting services that
might give you a little of the functionality you want.
--
| Thread-Topic: Report out an html string
| thread-index: AcWie3YqffCUH69lRbybl0xkS1lurw==| X-WBNR-Posting-Host: 69.160.170.174
| From: =?Utf-8?B?cGFwYXJoaW5v?= <paparhino@.discussions.microsoft.com>
| Subject: Report out an html string
| Date: Tue, 16 Aug 2005 08:59:19 -0700
| Lines: 16
| Message-ID: <035749C9-7F6C-49E4-BFCD-2DA040AC5CAE@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:50470
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Is it possible to report out an html string into the rich format?
|
| --> I use an html control (RichTextBox 2.5) that allows the user to
create
| the main body of a quote in a wysiwyg environment, and that formatting is
| successfully saved as an html string to the SQL db and successfully
displayed
| when opening back up in the control.
|
| --> Will Reporting Services allow me to take that html string and display
in
| the main body of a report, with the formatting applied (i.e. bold,
bullets,
| etc.)?
|
| Any help would be greatly appreciated, from saving as a certain datatype
in
| SQL to rendering the code in the Reporting Services.
|
| Many thanks,
| Andy
|

report on two database

hi all

i m facing a problem in reporting services. i have a global database, in which i m mainting user inormation like database name, server name and all credentails about user database. i want that when my report is open a user parameter will display, on the basis of that users id report will display data from his database. server name can be different, database name can be different but schema of database will be same for all user. so pls help me

The DataSource string of the dataset is dynmaic which means that you are able to use Expressions within the Data Source, using the syntax ="DataSource=" & Parameter!SomeParameter.Value

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

thanks jens for response

but first time there should be fixed connection so that my user list will display. after selecting a particular customer my connection string will change. how this is possible because all dataset use same connection string. pls suggest me .

|||

Don't forget you can have and use multiple data sources. And these can either be shared or private to the report.

If your customer list and the actual data are coming from different data sources then you should make the dataset that returns the customer list for the parameter based on a static data source connection and the other dataset that brings back the actual report data based on a different data source connection.

If the the data source varies depending on which customer you choose then the second connection can be made expression based as Jens pointed out.

|||

ya adam

i was using same apprach, but when i m changing connection string of one dataset another dataset's connection string is automatically changes. can u show me that how can i change connection string of one dataset so that another dataset's connection string will not change.

|||

Don't use a single shared data source. If all datasets use the same shared data source then naturally having that expression based will affect all datasets that use it. Use multiple data sources, a different one for each dataset. Try not using a shared data source.

On the data tab, when you click the elipsis (...) button next to the datasets dropdown, you will see the properties of the dataset. In there you specify which data source it connects to. Don't choose the shared data source option. Instead define the data connection for that dataset.

If you're already doing this then maybe whats happening is that both data sources happen to have the same connection string and reporting services is trying to be clever and just treating it as one.

|||

thanku adam

actually i was sharing same data source. now i change it and its working properly

thank u again.

Report on successful sql logins

Looking for how to create a report for sql'05 to see who's logging in succes
fully and then failed user logins as well perhaps on a second report.
Is there a way to capture logins historically with just sql server? How do
you set this up? Could someone point me in the right direction or, possibly
an example?
ThanksGo to Server Properties, Security tab, Login auditing. Login information
will be written to SQL Server Log and Windows Event Viewer.
Ben Nevarez, MCDBA, OCP
Database Administrator
"James Wuerflein" wrote:

> Looking for how to create a report for sql'05 to see who's logging in succ
esfully and then failed user logins as well perhaps on a second report.
> Is there a way to capture logins historically with just sql server? How d
o you set this up? Could someone point me in the right direction or, possib
ly an example?
> Thanks
>|||What an easy way to take that an create a report forthose logins that I coul
d give to management?
Thanks

Go to Server Properties, Security tab, Login auditing. Login information
will be written to SQL Server Log and Windows Event Viewer.
Ben Nevarez, MCDBA, OCP
Database Administrator
"James Wuerflein" wrote:
[vbcol=seagreen]
> Looking for how to create a report for sql'05 to see who's logging in succ
esfully and then failed user logins as well perhaps on a second report.
>
> Is there a way to capture logins historically with just sql server? How d
o you set this up? Could someone point me in the right direction or, possib
ly an example?
>
> Thanks
>

Report of every user for every database on server

For auditing purposes, I create a list of all the users who have access to
my databases. I do this by issuing this:
exec sp_MSForEachDB sp_helpuser
However, this doesn't include the name of the database that the list of
users is for. How can I include this info?
Thanks."Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
news:%23vGdjyRmFHA.2080@.TK2MSFTNGP10.phx.gbl...
> For auditing purposes, I create a list of all the users who have access to
> my databases. I do this by issuing this:
> exec sp_MSForEachDB sp_helpuser
> However, this doesn't include the name of the database that the list of
> users is for. How can I include this info?
> Thanks.
>
I use some dynamic sql and a cursor for this. In Query Analyzer, set your
output to Text (as opposed to grid).
BEGIN
SET NOCOUNT ON
-- Create some variables
DECLARE @.currentdb sysname, -- Current database that we are working with
@.sql varchar(4000) -- Dynamic SQL statement
-- Load the cursor with the current list of database names. Exclude SQL
Server databases.
DECLARE dbnames CURSOR FOR
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'Northwind',
'pubs')
ORDER BY [name] ASC
-- For each database, perform your query
OPEN dbnames
FETCH NEXT
FROM dbnames
INTO @.currentdb
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- The @.sql statement should be in the form of:
SET @.sql = @.currentdb + '.dbo.sp_helpuser'
-- Print a header to the messages tab
PRINT '=======================================
=================='
PRINT @.currentdb
PRINT ''
-- Execute the sql statement
EXECUTE(@.sql)
PRINT ''
PRINT '=======================================
=================='
PRINT ''
PRINT ''
-- Get the next database name
FETCH NEXT
FROM dbnames
INTO @.currentdb
END
-- Cleanup
CLOSE dbnames
DEALLOCATE dbnames
END
Rick Sawtell|||Nice script! However, it seems to have an issue with databases that begin
with the number zero (0). I get the folowing error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '0'.
Any ideas?
Thanks.|||"Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
news:uykxweSmFHA.572@.TK2MSFTNGP15.phx.gbl...
> Nice script! However, it seems to have an issue with databases that begin
> with the number zero (0). I get the folowing error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '0'.
> Any ideas?
> Thanks.
>
Ummm.. Find a better naming convention.. <wink>
I was under the impression that all object qualifiers in SQL Server 2000
require a letter as the first character, followed by numbers and some
symbols...
Guess I was wrong.
You could modify the script to include the square brackets. That may fix
it.
So adjust as follows:
Old line
SET @.sql = @.currentdb + '.dbo.sp_helpuser'
New line
SET @.sql = '[' + @.currentdb + '].dbo.sp_helpuser'
I should have probably done that in the first place for those who insist on
putting spaces etc. in their naming conventions.
Rick Sawtell|||Use QUOTENAME(@.currentdb) instead of @.currentdb
Steve Kass
Drew University
Joshua Campbell wrote:

>Nice script! However, it seems to have an issue with databases that begin
>with the number zero (0). I get the folowing error:
>Server: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near '0'.
>Any ideas?
>Thanks.
>
>|||It's not sufficient. Because I had to something very similar but including
all the permissions per each user using sp_helpuser
"Steve Kass" wrote:

> Use QUOTENAME(@.currentdb) instead of @.currentdb
> Steve Kass
> Drew University
> Joshua Campbell wrote:
>
>|||I'm sorry, but I don't understand you. If there is problem with
QUOTENAME, could you show sample code where it doesn't
work as expected?
SK
Enric wrote:
>It's not sufficient. Because I had to something very similar but including
>all the permissions per each user using sp_helpuser
>"Steve Kass" wrote:
>
>

Report objects shift to right on very wide report - why?

Greetings,

I have a very wide report of more than 20 inches. I've placed several parameter values in the report header section so that the user can see what filters have been applied to the data. The testboxes shift their position several inches to the right when the report is run from the Report Manager.

Is there a way to make sure that a textbox is displayed at an absolute position? I thought maybe there would be a property on the report or body object that controls this but I don't see one.

Thanks for your help,

BCB

It may be because your left margin is high. Check how much left margin you have specified in Report (menu) -> Report Properties -> Layout (tab).

Shyam

|||Hi,

I've found similar (non-reproducible) behaviour sometimes, I've solved it putting all the "affected" items inside a rectangle control.

HTH

Jordi Rambla|||

Oh my, having the same problem and it drives me NUTS. Keeps kicking my textboxes to the right without any logical explanation I could think of. In layout tab, everything is fine. In preview tab, some textboxes that reside in Body, are shifted chaotically to the right, sometimes beyond the right border of the first page. When report is deployed and viewed, the position of the textboxes changes but they are still far on the right side. And finally in the printout, position may change once again and still not where I meant them to be. Nice WYSIWYG I should say.

My report is very simple, it contains a table and a matrix region. The textboxes in question hold the information about parameters and about printing time. The layout of the page is A4 landscape, with 15mm left margin and 10mm other margins. Does anybody have an idea what is going on? A bug? And by the way, enclosing textboxes into rectangle object doesn't work for me.

Denis

|||

Try increasing the Interactive Size or Report Size (properties found on the report object). In my case the browser seemed to want to treat the location of the text boxes I had in the page header as a percentage of page width rather than an obsolute location. The actual report was much wider than the property width (I forget which of the two properties I changed). When I increased page width the text boxes stopped shifting right or at least moved a very small amount that was tolerable. This all took place within the PageHeader section.

BCB

|||

Thank you very much for your reply.

I tried playing with "Interactive size" properties of the Report object, but that didn't change anything. BTW in my case, the interactive size values were wrong too, compared to the actual size of the reporting sections.

But then, I moved my textboxes from Body to the Page Header section, and that solved the problem. Now textboxes stay where they are in both Design, Preview and printed report.

Upon further investigation, the awful truth finally revealed itself. My report utilizes a matrix control to display variable amount of columns. It turns out that when matrix is expanding to the right at runtime, it will "push out" everything that is located on the right side of it in the design tab. Only controls located in the same section (usually Body) will be affected. What confuses me most, the vertical position of controls doesn't matter, as soon as .Left property of a control is greater than the right border of the matrix in the design tab, the control will be shifted to the right at runtime.

Since Report Designer does not support user-defined sections to split Body into individual parts, the only solution seems to be enclosing controls into Rectangle object to keep it on the left from the evil matrix control. Just as Jordi recommended.

Denis

Report objects shift to right on very wide report - why?

Greetings,

I have a very wide report of more than 20 inches. I've placed several parameter values in the report header section so that the user can see what filters have been applied to the data. The testboxes shift their position several inches to the right when the report is run from the Report Manager.

Is there a way to make sure that a textbox is displayed at an absolute position? I thought maybe there would be a property on the report or body object that controls this but I don't see one.

Thanks for your help,

BCB

It may be because your left margin is high. Check how much left margin you have specified in Report (menu) -> Report Properties -> Layout (tab).

Shyam

|||Hi,

I've found similar (non-reproducible) behaviour sometimes, I've solved it putting all the "affected" items inside a rectangle control.

HTH

Jordi Rambla|||

Oh my, having the same problem and it drives me NUTS. Keeps kicking my textboxes to the right without any logical explanation I could think of. In layout tab, everything is fine. In preview tab, some textboxes that reside in Body, are shifted chaotically to the right, sometimes beyond the right border of the first page. When report is deployed and viewed, the position of the textboxes changes but they are still far on the right side. And finally in the printout, position may change once again and still not where I meant them to be. Nice WYSIWYG I should say.

My report is very simple, it contains a table and a matrix region. The textboxes in question hold the information about parameters and about printing time. The layout of the page is A4 landscape, with 15mm left margin and 10mm other margins. Does anybody have an idea what is going on? A bug? And by the way, enclosing textboxes into rectangle object doesn't work for me.

Denis

|||

Try increasing the Interactive Size or Report Size (properties found on the report object). In my case the browser seemed to want to treat the location of the text boxes I had in the page header as a percentage of page width rather than an obsolute location. The actual report was much wider than the property width (I forget which of the two properties I changed). When I increased page width the text boxes stopped shifting right or at least moved a very small amount that was tolerable. This all took place within the PageHeader section.

BCB

|||

Thank you very much for your reply.

I tried playing with "Interactive size" properties of the Report object, but that didn't change anything. BTW in my case, the interactive size values were wrong too, compared to the actual size of the reporting sections.

But then, I moved my textboxes from Body to the Page Header section, and that solved the problem. Now textboxes stay where they are in both Design, Preview and printed report.

Upon further investigation, the awful truth finally revealed itself. My report utilizes a matrix control to display variable amount of columns. It turns out that when matrix is expanding to the right at runtime, it will "push out" everything that is located on the right side of it in the design tab. Only controls located in the same section (usually Body) will be affected. What confuses me most, the vertical position of controls doesn't matter, as soon as .Left property of a control is greater than the right border of the matrix in the design tab, the control will be shifted to the right at runtime.

Since Report Designer does not support user-defined sections to split Body into individual parts, the only solution seems to be enclosing controls into Rectangle object to keep it on the left from the evil matrix control. Just as Jordi recommended.

Denis

Report objects shift to right on very wide report - why?

Greetings,

I have a very wide report of more than 20 inches. I've placed several parameter values in the report header section so that the user can see what filters have been applied to the data. The testboxes shift their position several inches to the right when the report is run from the Report Manager.

Is there a way to make sure that a textbox is displayed at an absolute position? I thought maybe there would be a property on the report or body object that controls this but I don't see one.

Thanks for your help,

BCB

It may be because your left margin is high. Check how much left margin you have specified in Report (menu) -> Report Properties -> Layout (tab).

Shyam

|||Hi,

I've found similar (non-reproducible) behaviour sometimes, I've solved it putting all the "affected" items inside a rectangle control.

HTH

Jordi Rambla|||

Oh my, having the same problem and it drives me NUTS. Keeps kicking my textboxes to the right without any logical explanation I could think of. In layout tab, everything is fine. In preview tab, some textboxes that reside in Body, are shifted chaotically to the right, sometimes beyond the right border of the first page. When report is deployed and viewed, the position of the textboxes changes but they are still far on the right side. And finally in the printout, position may change once again and still not where I meant them to be. Nice WYSIWYG I should say.

My report is very simple, it contains a table and a matrix region. The textboxes in question hold the information about parameters and about printing time. The layout of the page is A4 landscape, with 15mm left margin and 10mm other margins. Does anybody have an idea what is going on? A bug? And by the way, enclosing textboxes into rectangle object doesn't work for me.

Denis

|||

Try increasing the Interactive Size or Report Size (properties found on the report object). In my case the browser seemed to want to treat the location of the text boxes I had in the page header as a percentage of page width rather than an obsolute location. The actual report was much wider than the property width (I forget which of the two properties I changed). When I increased page width the text boxes stopped shifting right or at least moved a very small amount that was tolerable. This all took place within the PageHeader section.

BCB

|||

Thank you very much for your reply.

I tried playing with "Interactive size" properties of the Report object, but that didn't change anything. BTW in my case, the interactive size values were wrong too, compared to the actual size of the reporting sections.

But then, I moved my textboxes from Body to the Page Header section, and that solved the problem. Now textboxes stay where they are in both Design, Preview and printed report.

Upon further investigation, the awful truth finally revealed itself. My report utilizes a matrix control to display variable amount of columns. It turns out that when matrix is expanding to the right at runtime, it will "push out" everything that is located on the right side of it in the design tab. Only controls located in the same section (usually Body) will be affected. What confuses me most, the vertical position of controls doesn't matter, as soon as .Left property of a control is greater than the right border of the matrix in the design tab, the control will be shifted to the right at runtime.

Since Report Designer does not support user-defined sections to split Body into individual parts, the only solution seems to be enclosing controls into Rectangle object to keep it on the left from the evil matrix control. Just as Jordi recommended.

Denis

Report Model Security

Hello
Is it possible to filter data within a report model (.smdl). We have a
generic report model that we wish to limit, based on user credentials?
Kind regards
Ricky
(SS2005)Ricky,
Yes that's possible. Have a look at the documentation under "Security
Filters".
Also a good summary can be found here:
http://blogs.msdn.com/bobmeyers/articles/Implementing_Data_Security_in_a_Report_Model.aspx
You'll use the GetUserId() function in the model to tie to your data.
-Krip|||Thanks Krip.
Incidentally, did you manage to get your issues resolved?
"Krip" <anonymous@.anonymous.com> wrote in message
news:C71CBA81-089F-4091-9C6D-21F71FAA7AAB@.microsoft.com...
> Ricky,
> Yes that's possible. Have a look at the documentation under "Security
> Filters".
> Also a good summary can be found here:
> http://blogs.msdn.com/bobmeyers/articles/Implementing_Data_Security_in_a_Report_Model.aspx
> You'll use the GetUserId() function in the model to tie to your data.
> -Krip
>

Report Model Filter

Hi,
When creating a report model, is it possible to configure a field so
that a user must apply a filter when they create reports in Report
Builder?
In my case, I have a simple report model that contains summarized data
grouped by date and then by hour. Due to the amount of data in the
underlying table, I would like for users to be forced to filter on
either a specific date or a range of dates if possible. Even better
would be for me to be able to set a default date range for the date in
the model.
Does anyone know if what I am attempting is possible or has anyone
done something similar?
Thanks in advance,
Shane.You can use couple of things, depending on your requirement.
1. "Prompt" where it will wait for user input. but there is no multi select.
2. "From...to" this will be suitable for your requirement where in the user
can give the from and to date so that it will filter the required.
Amarnath, MCTS
"shanejokeeffe" wrote:
> Hi,
> When creating a report model, is it possible to configure a field so
> that a user must apply a filter when they create reports in Report
> Builder?
> In my case, I have a simple report model that contains summarized data
> grouped by date and then by hour. Due to the amount of data in the
> underlying table, I would like for users to be forced to filter on
> either a specific date or a range of dates if possible. Even better
> would be for me to be able to set a default date range for the date in
> the model.
> Does anyone know if what I am attempting is possible or has anyone
> done something similar?
> Thanks in advance,
> Shane.
>

Report model

Hi,

In my database I have a table with user rights. For example:

User 1 may see region 1

User 2 may see region 2 and 3

Is it possible to use this in my report model. When user 1 makes a report with the report builder he must not be able to see region 2 and 3.

thanks in advance

You can use the SecurityFilters property on the corresponding model entity to grant permission to specific rows. In addition to the online documentation, look for a post on my blog in the near future for more details on the many options for securing the data exposed by a report model.

Report Manager slow to open

I have been trying various things I have read about in order to solve
the problem of Report Manager being very slow to open for the first
user. I have set the Idle Timeout section in IIS to 480 minutes. I have
created a scheduled report to run every 8 hours. (My client is a 24/7
organization.) I read somewhere that a fix was to refresh a report
every five minutes. I'm not sure how to refresh a report, but I did set
a snapshot to run every hour.
Setting the Idle Timeout to 480 minutes means that within an 8 hour
period the problem is solved. But there is still the problem of the
very slow open for the first user of the shift.
Creating the scheduled report and setting the snapshot have not solved
the problem. I am using Reporting Services 2000 and the scheduled
report and snapshot both contain no data because this client is
connecting to Access databases rather than SQL Server and the Access
database causes problems with both the schedule and the snapshot. Any
suggestions of what I can do to not cause the first user of the shift
to wait a minute or longer for Report Manager to open?
Thanks very much.
LisI would have thought that running a scheduled report every hour would work.
However, I guess because it returns nothing then it isn't doing what it
needs to do to wake up. Before I knew about the IIS configuration (which you
have done) I had a report open on my desktop and I set it to refresh. I did
it every 5 minutes because it was data I wanted to see. You obviously do not
have to have it do that. In layout, Report Menu-> Report Properties.
AutoRefresh
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<info@.techknowsolve.com> wrote in message
news:1168387368.490665.23570@.77g2000hsv.googlegroups.com...
>I have been trying various things I have read about in order to solve
> the problem of Report Manager being very slow to open for the first
> user. I have set the Idle Timeout section in IIS to 480 minutes. I have
> created a scheduled report to run every 8 hours. (My client is a 24/7
> organization.) I read somewhere that a fix was to refresh a report
> every five minutes. I'm not sure how to refresh a report, but I did set
> a snapshot to run every hour.
> Setting the Idle Timeout to 480 minutes means that within an 8 hour
> period the problem is solved. But there is still the problem of the
> very slow open for the first user of the shift.
> Creating the scheduled report and setting the snapshot have not solved
> the problem. I am using Reporting Services 2000 and the scheduled
> report and snapshot both contain no data because this client is
> connecting to Access databases rather than SQL Server and the Access
> database causes problems with both the schedule and the snapshot. Any
> suggestions of what I can do to not cause the first user of the shift
> to wait a minute or longer for Report Manager to open?
> Thanks very much.
> Lis
>|||Hi Bruce - Thanks for the quick reply. I tried scheduling a report
that actually pulls from a SQL Server database and though the
subscription ran just 30 minutes ago, Report Manager still took over a
minute to open... Thanks for the info on refreshing the report.
Unfortunately, that's not really a viable option because I'm trying to
get this to work for a remote client. I don't want anyone there to
have to have anything open on their desktop all day.
Any other suggestions?
Lis|||Other than to tell you that it shouldn't take so long to open Report
Manager. I assume that if you open report manager, close IE, open up IE and
Report Manager the second time it is fast?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<info@.techknowsolve.com> wrote in message
news:1168555000.446295.230260@.o58g2000hsb.googlegroups.com...
> Hi Bruce - Thanks for the quick reply. I tried scheduling a report
> that actually pulls from a SQL Server database and though the
> subscription ran just 30 minutes ago, Report Manager still took over a
> minute to open... Thanks for the info on refreshing the report.
> Unfortunately, that's not really a viable option because I'm trying to
> get this to work for a remote client. I don't want anyone there to
> have to have anything open on their desktop all day.
> Any other suggestions?
> Lis
>|||Yes, it is fast the second time. Here's another thing: yesterday I
read that I could change the idle timeout setting so that it never
times out by removing the check in the box that says "shutdown worker
processes after being idle for (time in minutes):" I did that, but
I'm still having the same problem.
:(
Lis

Report Manager default open to sub folder

Hello, when I log onto Report Manager web server using Windows Authentication, I need this user to be directed to a default sub folder.

For example: if I have folder A and sub folder 1 and 2. I need to be able to log in and be directed to sub folder 1. I was able to set up view role and access folder A. But then I need to click on folder 1 to view / create my reports.

You can specify the folder path on the URL, so if you want someone to start in Folder1, have them use this URL:

http://localhost/Reports/Pages/Folder.aspx?ItemPath=%2fFolder1

Regards.

REPORT MANAGER - Hiding Tabs/Links ***

Is there a way to hide Tabs and Links on the REPORT MANAGER interface to
limit user access? If you can provide and example that would be highly
appreciated.Yes, you want to limit access using security. On the properties tab of any
item there should be a security sub-tab which will allow you to set what
users can and can't do with that object. Depending on a users rights only
tabs that a user can use will show up.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:7F502733-69FE-4C4A-A16B-02E6A3192933@.microsoft.com...
> I'm not an expert on this, but you can limit user access through IIS. Make
> sure anonymous access is not checked in your security in IIS. Then through
> the Report Manager, you can add users (or groups) and give them certain
> access rights.
> "doug" wrote:
> > Is there a way to hide Tabs and Links on the REPORT MANAGER interface to
> > limit user access? If you can provide and example that would be highly
> > appreciated.