Help with setting row background color

I am trying to do something where I say look through the row. When you come across the word "Start" color that box green and color all other boxes to the right in that row green as well until you come upon the word "stop". Is this possible? If I am not making sense just let me know and I will try to explain better. Thanks in advance for any help that I get.

I believe I understand what you are looking for. I'm guessing the report is time based going across? Sort of like a Gantt chart?

If this is something like your scenario, here's a solution that might work...

One row per item, instead of text 'start' and 'stop', have a field that has a start time and length of time.

Then add a cell for each time period. The background color should be:

=iif(Fields!RPlusInHours.Value <= 3 and Fields!LengthInHours.Value+Fields!RPlusInHours.Value >= 3.5, "Green", "Transparent")

This needs to be cut/paste into each cell, and then times in the IIF adjusted accordingly. That part was a painful process, but once complete, the report looks very nice. All you have to to is enter the action, the start time and how long, and a nice bar goes across. (You can also use some code to have it choose colors for you, but I skipped that here since you specified green for all)

If this doesn't match your scenario, please describe what your columns are used for more and we can try again.

|||

I am not sure how your data is structured. Also i assume you are using a table as the display tool in report manager.

What i did was create a data set that looks similar to this

select 'start' as col1, '2' as col2, '3' as col3 , '4' as col4, '5' as col5 ,'stop' as col6

result

col1 col2 col3 col4 col5 col6

start 2 3 4 5 stop

--

i then created a table in the report body. Each column in the table had a column from the data set. Starting with col1 ending with col6.

In each expression box for backgroundcolor i put the following expression.

=IIF( Fields!col1.Value ="start","green","white")

for col6 i put the following

=IIF( Fields!col6.Value ="stop","red","white")

this will make the row green until the last column which will be red.

If your data set is structured any other way post an example here and we may be able to help you find the answer .

Thank you

|||

Hey Charles, thanks. That is kind of what I am going for. However I dont want the entire column to be green just the row since each row contains different information.

Ok I thought I would add this and try to explain (Note: that I there is datae in these fields but I removed it). What is in Black is what I want to be green in my report and what is in green and Red will be transparent. What is in black varies in each row. So I need something that works per row and not just field specific. Does that make sense?

avail a

sql

Help with security model for RS implementation needed

We're running Reporting Services (wSP1) on a Win 2003 server box. Presently
(for development), SQL Server, the web application and RS all run on the
same box. I've configured an app pool in IIS under which Reports,
ReportServer and the web application run. I'm also collecting credentials
via forms auth which I pass as the credentials to RS during web service
calls. We are using URL access to access rendered reports.
RS Windows Service is configured to run as NT AUTH\Network Service.
All datasources are set up using trusted security.
What I'd like to be able to do to ensure that we use connection pooling is
not impersonate the credentials passed in but instead connect to the OLAP
database as a single domain account.
Is this possible and if so, what security configuration changes should I
make to make this happen?
Thanks in advance.
-TimPlease disregard my original post. The absurd amounts of caffeine I've been
consuming lately have caused temporary memory loss. :)
-Tim
"Tim Ellison" <TimEllison@.direcway.com> wrote in message
news:Oajl$kstEHA.1400@.TK2MSFTNGP11.phx.gbl...
> We're running Reporting Services (wSP1) on a Win 2003 server box.
Presently
> (for development), SQL Server, the web application and RS all run on the
> same box. I've configured an app pool in IIS under which Reports,
> ReportServer and the web application run. I'm also collecting credentials
> via forms auth which I pass as the credentials to RS during web service
> calls. We are using URL access to access rendered reports.
> RS Windows Service is configured to run as NT AUTH\Network Service.
> All datasources are set up using trusted security.
> What I'd like to be able to do to ensure that we use connection pooling is
> not impersonate the credentials passed in but instead connect to the OLAP
> database as a single domain account.
> Is this possible and if so, what security configuration changes should I
> make to make this happen?
> Thanks in advance.
> -Tim
>

Help with Security Model

Hi all,

I need some sugestions from all of you about setting up security model in
our SQL2000 box.

The server was setup using Mixed mode. However, all the applications
(web and MS access) access the server using "sa" userid.

There are several databases in our server. Ex: (DB1,DB2,DB3,DB4 and DB5)

Application 1: need read/write access to DB1,DB2 and DB3
Application 2: need read/write access to DB5
Application 3: need read/write access to DB4 and DB3

Should I set up three userids and give them the dbo access to those
database that they need to use?

Does that make any sense to you?

Thank you for all your suggestionThe 'sa' account should NEVER be used by ANY application or user (other than the DBA, and then carefully.)

Use three different UID/Pwd for the applications and GRANT permissions that way.|||In addition, unless they really NEED dbo, just GRANT the minimum they need.|||I'm a firm believer in the "principle of least privlege", meaning you create as many accounts as you need and only give each one the privleges that it needs to do its job. Check out the predefined database roles (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_help_5omd.asp) to see if your accounts even need dbo, or if a combination of db_datareader and db_datawriter would do.

-PatP|||I take it a bit further than data_reader/writer and grant Select/Insert/Update/Delete permissions as required. For instance, a financial application allows Inserts but NO updates. Once a record has been inserted, that's the way it stays. Corrections are made by inserting another record to adjust the transaction (along with an explanation.) Auditors seem to prefer this for following the money trail. Good thing I'm sa... :)|||Thank you for all your suggestions.

help with pull subscription

Hello,
I am trying to connect a remote sql box to another sql box using pull
subscription. I have the publisher registered in EM. When I try and create a
pull subscription it cannot find the publication unless anonymous
subscribers is on. Did I miss setting a password somewhere? Thanks in
advance.
Jake
Paul,
Sorry I read the instructions wrong. I changed to the SQL server
authentication putting in the replicationserver user and it's password. I
still however do not see the publication on the subscriber via the GUI
unless I mark the publication to allow anonymous. Any ideas?
1. In SQL Enterprise Manager from the Publisher, on the Tools menu, point to
Replication, and then click Configure Publishing, Subscribers, and
Distribution.
Note Because the Subscriber is not configured for the distributor, the
Configure Publishing, Subscribers, and Distribution options are not
available for the Subscriber.
2. Click the Publishers tab.
3. Click the Publisher, and then click the Properties (...) button.
4. Click the General tab, click Using SQL server authentication of this
account to specify that the replication agents use SQL Server
Authentication, and then specify an account that has sufficient permissions
in the publishing database.
"Jake" <rondican@.hotmail.com> wrote in message
news:eNy7p2RtEHA.3200@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I am trying to connect a remote sql box to another sql box using pull
> subscription. I have the publisher registered in EM. When I try and create
> a pull subscription it cannot find the publication unless anonymous
> subscribers is on. Did I miss setting a password somewhere? Thanks in
> advance.
> Jake
>

Help with multiple IIFs, or need suggestion of better solution.

I am trying to check multiple fields from a db to see if they have either a 1 or 0 value, and if there is a 1, then write a value into a text box. I need to check multiple fields, and if all of them are checked then I have to insert the value for each into the text box. If it was just checking one condition it woudl be easy, because I could just nest IIF's until it was true.

So I can't do because once the truth clause is satisfied it will exit the loop: IIF(Fields!Fielda.Value = 1,"Fielda",IIF(Fields!Fieldb.Value=1,"Fieldb"....)

I also cannot do:
=IIfFields!Fielda.Value=1,"Fielda,"")
=IifFields!Fieldb.Value=1,"Fieldb,"")

Is there a way to have a whole bunch of IIF's, or can anyone think of another way to do this?

Much appreciated.

Use the "And" operator. It would look like this:

iif (Fields!Fielda.Value = 1 and Fields!Fieldb.Value=1 and Fields!Fieldc.Value=1, "Fielda", "")

|||Ryan, I appreciate the answer, but I think you misunderstood. I want it to say if Fielda = 1 then insert text, and if Fieldb = 1 then insert text, not if all of them = 1.

This would be the ideal situation:

=IIF(Fields!Fielda.Value=1,"Fielda","")
IIF(Fields!Fieldb.Value=1,"Fieldb","")
IIF(Fields!Fieldc.Value=1,"Fieldc","")
And so on for all the fields for this particular text box.

Or another example (that I've tried that did not work)
=IIF(Fields!Fielda.Value=1,"Fielda","") &
IIF(Fields!Fieldb.Value=1,"Fieldb","") &
IIF(Fields!Fieldc.Value=1,"Fieldc","") &

I can't use what you said because that would only evaluate one statement, and I need to evaluate 8 different statements. That's the problem. Is there a way to have mutliple seperate IIF's in an expression like I have above? If not, is there another solution?
|||

One question is what is the datatype on the database field? If it is boolean then you should be able to do:

=IIF(Fields!Fielda.Value,"Fielda","") + IIF(Fields!Fieldb.Value,"Fieldb","")...

The + should work for concatenation since all of the fields area string. Another thing that I have seen is that you may have to do CDec on the database fields to force a datatype match.

=IIF(CDec(Fields!Fielda.Value)=1,"Fielda","") + IIF(CDec(Fields!Fieldb.Value)=1,"Fieldb","") ...

|||

Use the Report Properties.Code.Custom Code feature.

1.Create a function in the CODE section

2. Pass all your field values to the function

3. The return value is used in the textbox.

You have a lot more coding power in the CODE section than you do with expressions.

Hope this helps.

|||

Can you do it in SQL using case statement ?

|||

=switch(Fields!FieldA.Value = 1, "A", Fields!FieldB.Value = 1, "B", true, "")

Thanks, Donovan.

Help with mail that send mail when database bakcup fails

Hello

I have got a script which gives the mail to the dba mail box when database backup fails.

In the script I want to make a change so that I get the particular database name , on what ever database i implement.

Can you tell me some suggestions.

The script I am using is :

use master
go
alter PROCEDURE dbo.SendMail
@.to VARCHAR(255),
@.subject VARCHAR(255),
@.message VARCHAR(8000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@.rv INT,
@.from VARCHAR(64),
@.server VARCHAR(255);
SELECT
@.from = 'testsql2000@.is.depaul.edu',
@.server = 'smtp.depaul.edu';

select @.message = @.message + char(13) + Char(13) + @.@.servername + '-'+ db_name()+ '-' + 'Backup Status Failed' + Char(13)

EXEC @.rv = dbo.xp_smtp_sendmail
@.to = @.to,
@.from = @.from,
@.message = @.message,
@.subject = @.subject,
@.server = @.server;
END
GO

After the above script is run the following should be given in the 2nd step when
the backup jobs are scheduled

exec master.dbo.sendmail
@.to = 'dvaddi@.depaul.edu',
@.subject =' Test sqlserver 2000',
@.message = '' ;

Thanks

Sorry I don′t understand you issue, what do you want to achieve ?

HTH, Jens Suessmeyer.

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

I want to get the database name also in the mail I receive along with the server name.

For example if I execute it on Test database in the mail I should get : Test server (servername) - Test (database name ) - Backup status.

and if I execute on Test2000 database in the mail I should get : Testserver(servername ) - Test2000(database name)- bakcupstatus.

I am able to get the servername and the backup status. But for the database name I am getting just 'master'

Thanks

|||

When I have to that, I always script up the job which I prepared for a particular database and prepares creation statements for them. So the script which is produced if you script the job from SQL Agent can be tweaked to take a variable which is substituted during the creation of the job, something like:

DECLARE DatabaseName VARCHAR(200)

sp_add_jobstep param1, @.Command = Here goes your command to execute with the appropiate statements including the param to be substitued with the databasename

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

There are three different commands in execute sql mail:

exec sys.xp_startmail
exec sys.xp_sendmail {[@.recipients =] 'recipients [;...n]'}
[,[@.message =] 'message']
[,[@.query =] 'query']
[,[@.attachments =] 'attachments [;...n]']
[,[@.copy_recipients =] 'copy_recipients [;...n]'
[,[@.blind_copy_recipients =] 'blind_copy_recipients [;...n]'
[,[@.subject =] 'subject']
[,[@.type =] 'type']
[,[@.attach_results =] 'attach_value']
[,[@.no_output =] 'output_value']
[,[@.no_header =] 'header_value']
[,[@.width =] width]
[,[@.separator =] 'separator']
[,[@.echo_error =] 'echo_value']
[,[@.set_user =] 'user']
[,[@.dbuse =] 'database']

exec sys.xp_stopmail

Help with mail that send mail when database bakcup fails

Hello

I have got a script which gives the mail to the dba mail box when database backup fails.

In the script I want to make a change so that I get the particular database name , on what ever database i implement.

Can you tell me some suggestions.

The script I am using is :

use master
go
alter PROCEDURE dbo.SendMail
@.to VARCHAR(255),
@.subject VARCHAR(255),
@.message VARCHAR(8000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@.rv INT,
@.from VARCHAR(64),
@.server VARCHAR(255);
SELECT
@.from = 'testsql2000@.is.depaul.edu',
@.server = 'smtp.depaul.edu';

select @.message = @.message + char(13) + Char(13) + @.@.servername + '-'+ db_name()+ '-' + 'Backup Status Failed' + Char(13)

EXEC @.rv = dbo.xp_smtp_sendmail
@.to = @.to,
@.from = @.from,
@.message = @.message,
@.subject = @.subject,
@.server = @.server;
END
GO

After the above script is run the following should be given in the 2nd step when
the backup jobs are scheduled

exec master.dbo.sendmail
@.to = 'dvaddi@.depaul.edu',
@.subject =' Test sqlserver 2000',
@.message = '' ;

Thanks

Sorry I don′t understand you issue, what do you want to achieve ?

HTH, Jens Suessmeyer.

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

I want to get the database name also in the mail I receive along with the server name.

For example if I execute it on Test database in the mail I should get : Test server (servername) - Test (database name ) - Backup status.

and if I execute on Test2000 database in the mail I should get : Testserver(servername ) - Test2000(database name)- bakcupstatus.

I am able to get the servername and the backup status. But for the database name I am getting just 'master'

Thanks

|||

When I have to that, I always script up the job which I prepared for a particular database and prepares creation statements for them. So the script which is produced if you script the job from SQL Agent can be tweaked to take a variable which is substituted during the creation of the job, something like:

DECLARE DatabaseName VARCHAR(200)

sp_add_jobstep param1, @.Command = Here goes your command to execute with the appropiate statements including the param to be substitued with the databasename

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

There are three different commands in execute sql mail:

exec sys.xp_startmail
exec sys.xp_sendmail {[@.recipients =] 'recipients [;...n]'}
[,[@.message =] 'message']
[,[@.query =] 'query']
[,[@.attachments =] 'attachments [;...n]']
[,[@.copy_recipients =] 'copy_recipients [;...n]'
[,[@.blind_copy_recipients =] 'blind_copy_recipients [;...n]'
[,[@.subject =] 'subject']
[,[@.type =] 'type']
[,[@.attach_results =] 'attach_value']
[,[@.no_output =] 'output_value']
[,[@.no_header =] 'header_value']
[,[@.width =] width]
[,[@.separator =] 'separator']
[,[@.echo_error =] 'echo_value']
[,[@.set_user =] 'user']
[,[@.dbuse =] 'database']

exec sys.xp_stopmail

Help with mail that send mail when database bakcup fails

Hello

I have got a script which gives the mail to the dba mail box when database backup fails.

In the script I want to make a change so that I get the particular database name , on what ever database i implement.

Can you tell me some suggestions.

The script I am using is :

use master
go
alter PROCEDURE dbo.SendMail
@.to VARCHAR(255),
@.subject VARCHAR(255),
@.message VARCHAR(8000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@.rv INT,
@.from VARCHAR(64),
@.server VARCHAR(255);
SELECT
@.from = 'testsql2000@.is.depaul.edu',
@.server = 'smtp.depaul.edu';

select @.message = @.message + char(13) + Char(13) + @.@.servername + '-'+ db_name()+ '-' + 'Backup Status Failed' + Char(13)

EXEC @.rv = dbo.xp_smtp_sendmail
@.to = @.to,
@.from = @.from,
@.message = @.message,
@.subject = @.subject,
@.server = @.server;
END
GO

After the above script is run the following should be given in the 2nd step when
the backup jobs are scheduled

exec master.dbo.sendmail
@.to = 'dvaddi@.depaul.edu',
@.subject =' Test sqlserver 2000',
@.message = '' ;

Thanks

Sorry I don′t understand you issue, what do you want to achieve ?

HTH, Jens Suessmeyer.

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

I want to get the database name also in the mail I receive along with the server name.

For example if I execute it on Test database in the mail I should get : Test server (servername) - Test (database name ) - Backup status.

and if I execute on Test2000 database in the mail I should get : Testserver(servername ) - Test2000(database name)- bakcupstatus.

I am able to get the servername and the backup status. But for the database name I am getting just 'master'

Thanks

|||

When I have to that, I always script up the job which I prepared for a particular database and prepares creation statements for them. So the script which is produced if you script the job from SQL Agent can be tweaked to take a variable which is substituted during the creation of the job, something like:

DECLARE DatabaseName VARCHAR(200)

sp_add_jobstep param1, @.Command = Here goes your command to execute with the appropiate statements including the param to be substitued with the databasename

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

There are three different commands in execute sql mail:

exec sys.xp_startmail
exec sys.xp_sendmail {[@.recipients =] 'recipients [;...n]'}
[,[@.message =] 'message']
[,[@.query =] 'query']
[,[@.attachments =] 'attachments [;...n]']
[,[@.copy_recipients =] 'copy_recipients [;...n]'
[,[@.blind_copy_recipients =] 'blind_copy_recipients [;...n]'
[,[@.subject =] 'subject']
[,[@.type =] 'type']
[,[@.attach_results =] 'attach_value']
[,[@.no_output =] 'output_value']
[,[@.no_header =] 'header_value']
[,[@.width =] width]
[,[@.separator =] 'separator']
[,[@.echo_error =] 'echo_value']
[,[@.set_user =] 'user']
[,[@.dbuse =] 'database']

exec sys.xp_stopmail