Alternating BackgroundColor for rows in a table

I would like to alternate the background color for rows in a table. Is there
a simple way to do this?
Basically I got it working by returning a rank column that identified the
row number. I then used the following code in the TableRow BackgroundColor
property:
IIF(Fields!rank.Value mod 2 = 0,"#f7f7f7","#e7e7ff")
Seems like there must be an easier way then having to return a rank column
in the dataset associated with the table.Greg,
That's pretty much the only way I know to do it. Although, I used a
different formula then you did. Here's what I stuck in my Background Color
property box:
=iif(RowNumber(Nothing) mod 2=1, "OldLace", Nothing)
OldLace was the color I chose because I didn't want something hard on the
eyes. The Nothing leaves it as Transparent, which isn't really a color, but
doesn't force it to be another color. You could just as easily enter another
one of the available colors in the False part.
Does this help any?
Catadmin
"Greg Larsen" wrote:
> I would like to alternate the background color for rows in a table. Is there
> a simple way to do this?
> Basically I got it working by returning a rank column that identified the
> row number. I then used the following code in the TableRow BackgroundColor
> property:
> IIF(Fields!rank.Value mod 2 = 0,"#f7f7f7","#e7e7ff")
> Seems like there must be an easier way then having to return a rank column
> in the dataset associated with the table.|||Yes this does help. By using RowNumber function you don't need to generate a
row number(or rank as I called it) as part of the dataset being returned.
Thank you that is exactly want I needed.
"Catadmin" wrote:
> Greg,
> That's pretty much the only way I know to do it. Although, I used a
> different formula then you did. Here's what I stuck in my Background Color
> property box:
> =iif(RowNumber(Nothing) mod 2=1, "OldLace", Nothing)
> OldLace was the color I chose because I didn't want something hard on the
> eyes. The Nothing leaves it as Transparent, which isn't really a color, but
> doesn't force it to be another color. You could just as easily enter another
> one of the available colors in the False part.
> Does this help any?
> Catadmin
> "Greg Larsen" wrote:
> > I would like to alternate the background color for rows in a table. Is there
> > a simple way to do this?
> >
> > Basically I got it working by returning a rank column that identified the
> > row number. I then used the following code in the TableRow BackgroundColor
> > property:
> >
> > IIF(Fields!rank.Value mod 2 = 0,"#f7f7f7","#e7e7ff")
> >
> > Seems like there must be an easier way then having to return a rank column
> > in the dataset associated with the table.

Alternate the background color of rows

Hi!

I 'm working whit a matrix.

I want alternate the background color of rows. I did with table, but I don't Know how do I do with matrix. I desire something like this:

column1

column2

row1

data1,1

data1,2

row2

data2,1

data2,2

row3

data3,1

data3,2

Please, if somebody can help me

Thanks

hey there

there is something on this page that may help you

http://blogs.msdn.com/chrishays/

|||

Specifically you want to read this blog article: http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx

-- Robert

|||

Robert, I've used the suggestions in Chris Hay's example. However, it only gets me 90% to where I need.

I cannot get the subtotal background color to alternate. Do you know a way to get this functionality. Kind of pointless and ugly to have everything alternate except the last column or row.

Alternate the background color of rows

Hi!

I 'm working whit a matrix.

I want alternate the background color of rows. I did with table, but I don't Know how do I do with matrix. I desire something like this:

column1

column2

row1

data1,1

data1,2

row2

data2,1

data2,2

row3

data3,1

data3,2

Please, if somebody can help me

Thanks

Please read this blog article: http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx

-- Robert

Alternate the background color of rows

Hi!

I 'm working whit a matrix.

I want alternate the background color of rows. I did with table, but I don't Know how do I do with matrix. I desire something like this:

column1 column2 row1 data1,1 data1,2 row2 data2,1 data2,2 row3 data3,1 data3,2

Please, if somebody can help me

Thanks

hey there

there is something on this page that may help you

http://blogs.msdn.com/chrishays/

|||

Specifically you want to read this blog article: http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx

-- Robert

|||

Robert, I've used the suggestions in Chris Hay's example. However, it only gets me 90% to where I need.

I cannot get the subtotal background color to alternate. Do you know a way to get this functionality. Kind of pointless and ugly to have everything alternate except the last column or row.

Alternate Synchronization Partner Error

Greetings:
I am working on a setting up an alternate synchronization partner, and
getting an error. Bear with me as the explanation of what I have done so far
is long.
We have about 75 customers with about 100 pull subscriptions using about 20
publications in merge replication with Sql Server 2000. Our customers are
located where many have dial up connections and they need to set own
synchronization schedules. All are synchronizing with programs we wrote in
VB 6.0 using SQLDMO and the ActiveX merge control. All our customers have
been able to successfully synchronize now for many months, so we know our
programs are working as they should. They all synchronize over the internet
to a single publisher/distributor.
We would like to build some redundancy into our topology so that we have an
alternate synchronization server, or partner, as the BOL calls it, in case
the primary publisher fails. However, we want to locate the alternate synch
partner at a site other than where the primary publisher is located.
I have printed the MS KB article # 321176 on how to set up an alternate
synch partner. I also listened to the web cast from MS on this subject.
Following the KB #321176, I have set up two test databases, and a
subscriber. There is publisher A, and publisher B, with subscriber A.
Publisher A is the primary publisher and publisher B is to become our
alternate synch server. As the KB article and web cast said to do, I have
completed the following:
1.Created a global pull subscription on publisher B to publisher A.
2.Created a publication on publisher B which is identical to the database
on publisher A. The database names on both publisher A and publisher B are
the same name as are the publication names.
3.Generated a snapshot of the database on publisher B.
4.Enabled subscriber A on publisher B.
5.Set the alternate synch partners on both publisher A and publisher B so
each can use the other as a synch partner.
6.Created a pull subscription to publisher A from subscriber A which
synchronizes fine.
I then have tried to synchronize to the alternate publisher B from
subscriber A by changing the job commands as described in the KB article, but
get an error. In fact, the merge agent is able to connect to publisher B,
and initializes the process. In other words, I believe I have everything
set up correctly since the subscriber A connects and initializes to publisher
B. When it starts the actual synchronization, I get the following message:
“The process could not drop one or more tables because the tables are being
used by other publications.“ This is shown as error # -2147200976.
Apparently, subscriber A is trying to use the snapshot from publisher B,
which has scripts that tell it to drop tables. However, the tables trying to
be dropped are the subscription database as it was originally set up from the
pull subscription to publisher A. So, while the error make sense in a way
because the tables are replicated, subscriber A cannot synchronize to
publisher B because it is stopped by the error.
What am I doing wrong?
Do the database names and/or the publication names on the two publishers
need to be different? I had read one reference in BOL that said they should
be the same, but the KB article seems to imply otherwise.
I have found very little on any internet web site about Sql Server that
describes setting up and running alternate synch partners. Microsoft has the
most I have found on the subject, and I think I am following the MS set-up
correctly.
Any help would be gratefully appreciated.
Thanks!
Bill
Hi Bill,
From your descriptions, I understood that when applying alternate
synchronization partner, you encouter the error message "The process could
not drop one or more tables because the tables are being used by other
publications." Have I understood you? Correct me if I was wrong.
Based on my knowledge, this is because some entries does not match between
table sysmergepublications and sysmergesubscriptions on the publisher and
subscriber. A quick resolution is drop the publication completely and then
create it again.
How to manually remove a replication in SQL Server 2000
http://support.microsoft.com/kb/324401
I understood drop the publication may have huge business impact to your
business, as an option, please send the the result of following T-SQL
statement. I would like to check to see whether I could help further
select * from sysmerge_subscriptions
go
sp_helpserver
However, please understand that this replication issues tend to be very
complex and hard to troubleshoot in newsgroups. If you need further
assistance, I recommend that you open a Support incident with Microsoft
Customer Service and Support (CSS) so that a dedicated Support Professional
can work with you in a more timely and efficient manner. If you need any
help in this regard, please let me know.
For a complete list of Microsoft Customer Service and Support phone
numbers, please go to the following address on the World Wide Web:
<http://support.microsoft.com/directory/overview.asp>
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael:
On a hunch before I sent you the requested the information, I changed the
database name on the alternate synch server (Publisher B) to a name that was
different from the original on Publisher A. Then the subscriber A was able
to sychronize with Publisher B, so I made it over that hurdle.
However, now, Subscriber A will not syncrhonize with Publisher A, but only
with Publsiher B. I get the following error:
"The Publisher has been restored from a backup whose schema change version
is different from the Subscriber. Rerun the Snapshot Agent and reinitialize"
The Publisher has NOT been restored from a backup. However, I went ahead as
the message said and reran the snapshot and reinitialized the subscription
from Subscriber A. Now the subscriber will not synch with Publisher B, and I
get the same error as above. So, on Publisher B, I do the snapshap again,
and reinitialize the subscription and then synch and it works. Then I try to
synch to Publisher A again, and get the error.
In other words, after I get a successful synch to either of the publishers,
I get the error on the other one the next time I try to synch to the other.
It is like an infinite loop. One works, the other doesn't. I run the
snapshot on the other and reinitialize, and it synchs, and then first one
doesn't, etc, etc. etc.
What is wrong now?
Thanks in advance for any help you can give!
Bill
"Michael Cheng [MSFT]" wrote:

> Hi Bill,
> From your descriptions, I understood that when applying alternate
> synchronization partner, you encouter the error message "The process could
> not drop one or more tables because the tables are being used by other
> publications." Have I understood you? Correct me if I was wrong.
> Based on my knowledge, this is because some entries does not match between
> table sysmergepublications and sysmergesubscriptions on the publisher and
> subscriber. A quick resolution is drop the publication completely and then
> create it again.
> How to manually remove a replication in SQL Server 2000
> http://support.microsoft.com/kb/324401
> I understood drop the publication may have huge business impact to your
> business, as an option, please send the the result of following T-SQL
> statement. I would like to check to see whether I could help further
> --
> select * from sysmerge_subscriptions
> go
> sp_helpserver
> --
> However, please understand that this replication issues tend to be very
> complex and hard to troubleshoot in newsgroups. If you need further
> assistance, I recommend that you open a Support incident with Microsoft
> Customer Service and Support (CSS) so that a dedicated Support Professional
> can work with you in a more timely and efficient manner. If you need any
> help in this regard, please let me know.
> For a complete list of Microsoft Customer Service and Support phone
> numbers, please go to the following address on the World Wide Web:
> <http://support.microsoft.com/directory/overview.asp>
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/tec...rview/40010469
> Others: https://partner.microsoft.com/US/tec...pportoverview/
> If you are outside the United States, please visit our International
> Support page: http://support.microsoft.com/common/international.aspx
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi,
I found you may encounter the problem described by KB article 814460
FIX: Merge Replication with Alternate Synchronization Partners May Not
Succeed After You Change the Retention Period
http://support.microsoft.com/kb/814460
Please feel free to ask this hotfix by contacting CSS. If you are simply
requesting a hotfix be sent to you and no other support then charges are
usually refunded or waived.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default...S;PHONENUMBERS
NOTE that the hotfix is only intended to correct the problem that is
described in this article. Only apply it to systems that are experiencing
this specific problem. This hotfix may receive additional testing.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thank you, Michael. This sounds like the issue I am having.
I looked at the "http://support.microsoft.com/kb/814460" as you suggested.
At the bottom of the page is says that the fix is also included in "MS03-031:
Security patch for SQL Server 2000 Service Pack 3" and gives a link to
download it, so I would prefer to do that rather than place a phone call as I
think it will save time.
However, in the middle of the KB # 814460, it says:
"The problem exists in SQL Server 2000 Service Pack 3, version 8.00.0760. To
fix this version, you must apply SQL Server 2000 Service Pack 3 rollup,
version 8.00.0765."
The versions we are running are 8.00.0760 on both of the affected servers,
so I tried to find the "SQL Server 2000 Service Pack 3 rollup, version
8.00.0765" on the Microsoft web site and cannot. When I search both
downloads and the entire site, I get no hits except back to the KB # 814460.
Can you tell me where I can get "SQL Server 2000 Service Pack 3 rollup,
version 8.00.0765"?
Thank you.
Bill
"Michael Cheng [MSFT]" wrote:

> Hi,
> I found you may encounter the problem described by KB article 814460
> FIX: Merge Replication with Alternate Synchronization Partners May Not
> Succeed After You Change the Retention Period
> http://support.microsoft.com/kb/814460
> Please feel free to ask this hotfix by contacting CSS. If you are simply
> requesting a hotfix be sent to you and no other support then charges are
> usually refunded or waived.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/default...S;PHONENUMBERS
> NOTE that the hotfix is only intended to correct the problem that is
> described in this article. Only apply it to systems that are experiencing
> this specific problem. This hotfix may receive additional testing.
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/tec...rview/40010469
> Others: https://partner.microsoft.com/US/tec...pportoverview/
> If you are outside the United States, please visit our International
> Support page: http://support.microsoft.com/common/international.aspx
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Bill,
Sorry that I should have clarified it more clearly.
Yes, You could download the patch available in the KB article 821277
MS03-031: Security patch for SQL Server 2000 Service Pack 3
http://support.microsoft.com/kb/821277
Let me know whether this security patch resolves your issue.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael:
Thanks for this reference. I have installed the hot fix on the alternate
synchronization server, but have not yet installed it on the primary server.
That will have to wait a couple of days as that server is very busy right now
with our customer's activity, so I won't be able to check the results on the
error I was getting for at least a couple of days. The hot fix requires Sql
Server to be stopped to install it, and we need to wait until a more
opportune time that has minimal impact on our customers.
I will be sure, however, to let you know the results when I have a chance to
test it. I just wanted you to know I won't be able to do my next testing on
this issue for a little while yet.
Thanks for your continuing help and I'll be sure to let you know what happens.
Bill
"Michael Cheng [MSFT]" wrote:

> Hi Bill,
> Sorry that I should have clarified it more clearly.
> Yes, You could download the patch available in the KB article 821277
> MS03-031: Security patch for SQL Server 2000 Service Pack 3
> http://support.microsoft.com/kb/821277
> Let me know whether this security patch resolves your issue.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi Bill,
I haven't heard back from you yet and I'm just writing in to see if you
have had an opportunity to perform the hotfix. If you could get back to me
at your earliest convenience, we will be able to go ahead
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Michael:
Sorry it took me so long to respond back to you. I have been extremely busy
with customer issues and a family crisis of sorts, so I didn't get time unitl
yesterday to do some more testing.
I applied the hotfix to the three servers involved and it eliminated the
error! I was able to synchronize successfully numerous times between all the
machines. I was very pleased with the result.
However, I had my business partner add a another subscriber late yesterday
afternoon. He was able to create a suscription fine to the primary synch
server, but when trying the alternate, he started getting some errors. But
we both had other matters to attend to at that time and did not have a chance
yet to analyze the errors and if it was the way Sql Server is setup on his
machine or some other issue. We will work on that issue today, and may have
some additional questions for you. We did apply the hotfix to the computer
he is using for this test.
Thanks for you continuing help.
Bill
"Michael Cheng [MSFT]" wrote:

> Hi Bill,
> I haven't heard back from you yet and I'm just writing in to see if you
> have had an opportunity to perform the hotfix. If you could get back to me
> at your earliest convenience, we will be able to go ahead
> If you have any questions or concerns, don't hesitate to let me know. We
> are always here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Michael:
We resolved all the initial errors and have the test alternate synch partner
running well with two subscribers. We have been able to run many, many
successful test syncrhonizations to both servers now without difficulty.
Part of testing plan, however, is to make some schema changes, and I have
run into another problem there. I will start a new thread for this issue.
Thanks for your help.
Bill
"Michael Cheng [MSFT]" wrote:

> Hi Bill,
> I haven't heard back from you yet and I'm just writing in to see if you
> have had an opportunity to perform the hotfix. If you could get back to me
> at your earliest convenience, we will be able to go ahead
> If you have any questions or concerns, don't hesitate to let me know. We
> are always here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>

Alternate names

I need to implement support for alternate names on the database Im working on.
When a user looks up: Antony Bigglesworth the query would return both: Antony Bigglesworth as well as Tony Bigglesworth, would both exist.
Anyone has a best practice on this or has done this before?You mean hat it returns every first name that is a substring of the name you look for?
Something like:

Where SearchString Like '%' + FirstNameField + '%'

I believe that would return, Antony, Tony, Ton, A, but not Abby and George.|||I presume iot is more complex that that yes? "Dick" for "Richard" and "Teddy" for "Edward"?|||I presume iot is more complex that that yes? "Dick" for "Richard" and "Teddy" for "Edward"?

Yes, Antony clearly wasnt the best choice.

I need Richard Bigglesworth to be returned when I look for Dick Bigglesworth!

Thanks for getting back on this.|||Use my fuzzy-search algorithm. It was created to do exactly what you describe.
http://sqlblindman.googlepages.com/fuzzysearchalgorithm

Example:
set nocount on
select dbo.comparetext('Antony Bigglesworth', 'Tony Bigglesworth') as CompareValue1
select dbo.comparetext('Richard Bigglesworth', 'Dick Bigglesworth') as CompareValue2

Results:
CompareValue1
----
94

CompareValue2
----
74|||Thanks Blindman,

That looks great.

I also have to define a group of names which would be specificly checked and collected then returned from the database. Thus, the user would look for "Tony" as the first name, and so all the people with first name "Tony" and all the people with the first name "Anthony" would be returned.

I kind of did it now, created a table where you can add in these name groups, and a small sql script then checks if the sought after first name is in the table, if it is it gets the group id, then cycles through the group collecting all the relevant records then returns them.

Thanks for all the help on this|||To be thorough, you should accept the names "Buddy" and "Pal" and perhaps "Dude" as wild-cards that match any name.|||don't forget "Todd" and "Biff"|||In SoCal, we also have to expand a bit on Blindman's suggestion, since we also have "Duuuuuude!" as well as any name with the suffix "meister" appended (as in "Paulmeister" or "Blindmanmeister").sql

Alternate Background Color in Row

Hi,
I am working on a report in the Reporting Services. I was wondering if we
can set alternate background color for a group of rows on the report. Let's
see the example below.
ID Name Month Field4 Field5
1 A Jan 8 9 -- Background color: red
1 A Jan 10 2 -- Background color: red
1 A May 3 3 -- Background color:
transparent
2 B Feb 2 4 -- Background color: red
3 C Apr 5 6 -- Background color:
transparent
3 C Apr 7 5 -- Background color:
transparent
4 D Mar 2 2 -- Background color: red
4 D Jun 3 1 -- Background color:
transparent
The background color will be set based on the group of ID, Name and Month
fields. Does anyone know if this is doable? If so, how to do it?
Any help would be greatly appreciated!
Thank you in advance.
Jeannetteto alternate colors in a table, place this in BackgroundColor expression:
=iif(RowNumber(Nothing) Mod 2, "AliceBlue", "White")
feel free to place whatever colors you want in there
"Jeannette" wrote:
> Hi,
> I am working on a report in the Reporting Services. I was wondering if we
> can set alternate background color for a group of rows on the report. Let's
> see the example below.
> ID Name Month Field4 Field5
> 1 A Jan 8 9 -- Background color: red
> 1 A Jan 10 2 -- Background color: red
> 1 A May 3 3 -- Background color:
> transparent
> 2 B Feb 2 4 -- Background color: red
> 3 C Apr 5 6 -- Background color:
> transparent
> 3 C Apr 7 5 -- Background color:
> transparent
> 4 D Mar 2 2 -- Background color: red
> 4 D Jun 3 1 -- Background color:
> transparent
> The background color will be set based on the group of ID, Name and Month
> fields. Does anyone know if this is doable? If so, how to do it?
> Any help would be greatly appreciated!
> Thank you in advance.
> Jeannette|||Thank you very much for your response! Carl. Although your answer is not
exactly what I am looking for, it really helps me to get to what I need for
my report. As I explained earlier in my previous message, the alternate
background color will be set based on the GROUP of ID, Name, and Color
fields. Below is what I found for my question. It really works!
In BackgroundColor expression:
=iif(RunningValue(Cstr(Fields!ID.Value) & CStr(Fields!Name.Value) &
CStr(Fields!Month.Value),CountDistinct,Nothing) Mod 2, "AliceBlue", "White")
Jeannette
"Carl Henthorn" wrote:
> to alternate colors in a table, place this in BackgroundColor expression:
> =iif(RowNumber(Nothing) Mod 2, "AliceBlue", "White")
> feel free to place whatever colors you want in there
> "Jeannette" wrote:
> > Hi,
> >
> > I am working on a report in the Reporting Services. I was wondering if we
> > can set alternate background color for a group of rows on the report. Let's
> > see the example below.
> >
> > ID Name Month Field4 Field5
> > 1 A Jan 8 9 -- Background color: red
> > 1 A Jan 10 2 -- Background color: red
> > 1 A May 3 3 -- Background color:
> > transparent
> > 2 B Feb 2 4 -- Background color: red
> > 3 C Apr 5 6 -- Background color:
> > transparent
> > 3 C Apr 7 5 -- Background color:
> > transparent
> > 4 D Mar 2 2 -- Background color: red
> > 4 D Jun 3 1 -- Background color:
> > transparent
> >
> > The background color will be set based on the group of ID, Name and Month
> > fields. Does anyone know if this is doable? If so, how to do it?
> >
> > Any help would be greatly appreciated!
> >
> > Thank you in advance.
> >
> > Jeannette

altering table with default value

Hi, How to alter a table with default value?
I am using the below statement, But, it is not working..Any pointers?
Thx..
----------
alter table action_item ALTER COLUMN STATUS default 0ALTER TABLE ACTION_ITEM ADD CONSTRAINT
DF_ACTION_ITEM_STATUS DEFAULT 0 FOR STATUS
GO
UPDATE ACTION_ITEM SET STATUS =0 where STATUS IS NULL
GO

Alter table that is masked for replication

Hi,
I have a snapshot replication between two SQL server 2000 (Main Svr & Backup
Svr). At present the replication is working well but now due enhancement i
need to alter soem of the table. When I perform the alteration to the 'Main
Svr' I get a error message 'Cannot delete and create table and it is used by
replication'.
How can I alter the table?
After alteration will this be reflected back to 'Backup Svr'?
The 'Backup Svr' is hosted as another SQL instance on a Win2k server. How
can I connect to this second instance using ADODB (V2.8) from visual basic.
Thanks
Hari
Hi Paul,
Thanks for that let me try it.
Hope you could also help me on this.
How can i connect to secodn sql instance using ADODB in VB.
Thanks,
Hari
"Paul Ibison" wrote:

> To add a column, use sp_repladdcolumn. To drop one use
> sp_repldropcolumn. To change an existing column, you
> could add a new column with the new datatype
> (sp_repladdcolumn), do an update on the table to populate
> the column, then drop the column (sp_repldropcolumn). Do
> this again to create the column having the same original
> name. Alternatively you could use:
> sp_dropsubscription @.publication = 'northwindxxx'
> , @.article = 'region'
> , @.subscriber = 'pll-lt-16'
> sp_droparticle @.publication = 'northwindxxx'
> , @.article = 'region'
> sp_refreshsubscriptions @.publication ='northwindxxx'
> And do the opposite to add back in once the change has
> been made.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

ALTER AND UPDATE together not working....

Hi ,
I have a Stored Procedure like this...
| CREATE PROCEDURE V_test AS
| Select MS.memno, MS.ymdeff, MS.ymdend,MS.Aidcode INTO
STAGE_memspan
| FROM membspan MS INNER JOIN STAGE_members MM
I | on SM.memno = MM.memno
|
| ALTER TABLE STAGE_membspan ADD Aidcode_Description char (72)
NULL
| UPDATE STAGE_membspan
| SET Aidcode_Description = CL.[desc]
II | from SATGE_membspan SM, CODE_LOOKUP CL
| where SM.Aidcode = CL.code and CL.id = 'rp'
After executing this I am getting following error :
Invalid Column name 'Aidcode_Description'
If I execute I part seperately and II part seperately it work fine...
Thanks...!!!!.When the parser is examiningthe statement and validating all the =columnames etc tye ALTER has not yet been done, therefore when it =examines the update the column being referenced does not exist. Run =these as two separate batches and all will be fine. Not really sure why =you want an alter in a stored proc anyway since by definition it can =only be done once, and the major benefit of stored procs comes when they =are run many times.
Mike John
"veena" <vgs@.yahoo.com> wrote in message =news:uMfXdWITDHA.1576@.TK2MSFTNGP12.phx.gbl...
> Hi ,
> > I have a Stored Procedure like this...
> > | CREATE PROCEDURE V_test AS
> | Select MS.memno, MS.ymdeff, MS.ymdend,MS.Aidcode INTO
> STAGE_memspan
> | FROM membspan MS INNER JOIN STAGE_members MM
> I | on SM.memno =3D MM.memno
> |
> | ALTER TABLE STAGE_membspan ADD Aidcode_Description char =(72)
> NULL
> > | UPDATE STAGE_membspan
> | SET Aidcode_Description =3D CL.[desc]
> II | from SATGE_membspan SM, CODE_LOOKUP CL
> | where SM.Aidcode =3D CL.code and CL.id =3D 'rp'
> > > After executing this I am getting following error :
> Invalid Column name 'Aidcode_Description'
> > If I execute I part seperately and II part seperately it work fine...
> > Thanks...!!!!.
> > > >=20|||Thanks Mike,
I came to know the reason and also I am not including ALTER in a stored
procedure...since its a one time execution process... Thanks again...
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:#2zPClITDHA.1912@.tk2msftngp13.phx.gbl...
When the parser is examiningthe statement and validating all the columnames
etc tye ALTER has not yet been done, therefore when it examines the update
the column being referenced does not exist. Run these as two separate
batches and all will be fine. Not really sure why you want an alter in a
stored proc anyway since by definition it can only be done once, and the
major benefit of stored procs comes when they are run many times.
Mike John
"veena" <vgs@.yahoo.com> wrote in message
news:uMfXdWITDHA.1576@.TK2MSFTNGP12.phx.gbl...
> Hi ,
> I have a Stored Procedure like this...
> | CREATE PROCEDURE V_test AS
> | Select MS.memno, MS.ymdeff, MS.ymdend,MS.Aidcode INTO
> STAGE_memspan
> | FROM membspan MS INNER JOIN STAGE_members MM
> I | on SM.memno = MM.memno
> |
> | ALTER TABLE STAGE_membspan ADD Aidcode_Description char (72)
> NULL
> | UPDATE STAGE_membspan
> | SET Aidcode_Description = CL.[desc]
> II | from SATGE_membspan SM, CODE_LOOKUP CL
> | where SM.Aidcode = CL.code and CL.id = 'rp'
>
> After executing this I am getting following error :
> Invalid Column name 'Aidcode_Description'
> If I execute I part seperately and II part seperately it work fine...
> Thanks...!!!!.
>
>

Alter a column to allow null or not null values if meets criteria

I'm very new working with SQL server and I'm trying to create a check
constraint that will allow the an specific field to be null only if the
result on a second field is zero and not null if this result is greater
than zero.
My boss is pushing me to implement this criteria on my SQL database
right away please Help...For example:
ALTER TABLE your_table
ADD CONSTRAINT ck_constraint_name
CHECK ((col1 IS NULL AND col2 = 0)
OR (col1 IS NOT NULL AND col2 > 0)) ;
I hope your boss intends that you apply this to a TEST system and TEST
the impact rather than go right away into production...
David Portas
SQL Server MVP
--|||Thanks David, It works perfect you are the best...|||May be easier to write a trigger that examines the inserted table to check
for the values.
"imagabo" <imagabo@.hotmail.com> wrote in message
news:1130424448.351441.168020@.g47g2000cwa.googlegroups.com...
> I'm very new working with SQL server and I'm trying to create a check
> constraint that will allow the an specific field to be null only if the
> result on a second field is zero and not null if this result is greater
> than zero.
> My boss is pushing me to implement this criteria on my SQL database
> right away please Help...
>

Alpha Prefix - Can it be done in SQL?

Good Morning Db forum.
I am working on a query that I need to pull all fields that contain 3 alpha characters. for example BCB001, MCR001, CHP001 and so forth.
Is there a SQL alpha wildcard that I could use to pull all records that have the three alpha chars?where patindex('%[a-z][a-z][a-z]%',daColumn) > 0

:)|||Awesome thank you. Now would that be the same for say numbers/digits?

where patindex like [0-9][0-9][a-z]% if i wanted to return 145A or something along those lines?|||something along those lines -- you should test it yourself :)

Allowing access to Enterprise Manager without giving admin rights.

I have a user that will be doing specific updates to a specific table
in SQL. Rather than have them working at the server when these needed
to be done, I thought I would install SQL Admin Tools at their
workstation. Does anyone know if I can do this, and allow him use of
Enterprise Manager to access this table, without giving him admin
rights. He will need to import an excel file into this table
periodically.
Thanks.There's no need to give him Admin rights in order to update a table on
occaison.
Simply grant him insert/update/delete permissions to the specific table.
Then write some vb code to insert the data from Excel to SQL.
321686 HOW TO: Import Data into SQL Server from Excel
http://support.microsoft.com/?id=321686
Or
Create a DTS Package on the server. Have the user put his Excel file on a
server share, and then
periodically have the DTS package scheduled to run and process the data.
319951 HOW TO: Transfer Data to Excel by Using SQL Server Data
Transformation
http://support.microsoft.com/?id=319951
Or
You could simply give him db_datareader, db_datawriter in the database.
See Fixed Database Roles in SQL Books Online
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Allowing a conenction to a SQL Server 2005 database from another computer on a LAN

I am working with one other person on a VS 2005 vb.net web project that accesses SQL Server 2005. Both the computers are connected and my partner can run the application on his computer from his VS 2005 but we are getting an error on our first databind to a gridview on the page we are trying to run the error is below

A connection was successfully established with the
server, but then an error occurred during the
pre-login handshake. When connecting to SQL Server
2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow
remote connections. (provider: Named Pipes Provider,
error: 0 - No process is on the other end of the pipe.)

I check the properties of the SQL Server and the check box is checked that says allow remote access. I am not sure what to do.

Hi,

First, please try to allow remote connections for TCP/IP and Named Pipe according to the following KB article.

http://support.microsoft.com/kb/914277/en-us

If that still doesn't work, you can check the following link for troubleshooting.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=275050&SiteID=1

Allow user to choose grouping order?

I have a report which is working off a dataset with 5 columns. Date, Type, Color, Value1, Value2
We would like to set up a report such the user viewing the report could drag and drop column headers and set up grouping in whatever order they want. What would be the best way to do this? Is it even possible, or do we have to create a separate report for each combination?

For example, One report just groups by Date and Type. They might also want to group by Type then Date. Other times we want Date, Type, then color.

I used to run the reports in a third party grid which supported this, but since it was all client side, it was very slow for large datasets.

You can change the grouping through report parameters. Chris has an example of this in his blog:

http://blogs.msdn.com/chrishays/archive/2004/07/15/DynamicGrouping.aspx

Allow Nested Lists to Grow

Hello. I have nested lists that are working the way I want it to, but the
parent list does not grow vertically to accomodate the child list. If it
gets too long it just displays right over top of the child list rather than
push the child down.

BJ

Hi,

From your description, it seems that you want to show the whole child list which contained in a parent list even if the child list get longer and longer, right?

If so, I think you should set the content's property which existed inside the parent list to AutoSize. Here is a sample from MSDN document for you.

My report design uses two nested lists. The inner list contains a text box for the ATM name and address and an image for the map. The image in my report is set to AutoSize, so that it will grow depending on the size of the image coming from the Web service. The outer list contains text boxes for the store name and location. The outer list is grouped by store name. An image of my report in Layout mode is shown in Figure 3.

Figure 3. Image of report in Layout mode (Click on the picture for a larger image)

Figure 4 shows the rendered report with the maps to the ATM locations.

Quoted from:
http://msdn2.microsoft.com/en-us/library/bb293147.aspx

Thanks.

|||

I really appreciate you responding to me.

This sounds like what I am looking for, but the property "AutoSize" is only available for Images. I am dealing with child lists that share a parent. The parent's information is bleeding over top of the child information.

Allow Multiple Parameters form Windows Application

I have been working through a solution to allow multiple parameters to be
passed into a SQL RS report. I have created a windows application that calls
sql reporting services. The problem that I am having now is passing the
multiple values the user selects from the dropdown to sql reporting services.
I am setting: returnValues.Value
When I try to set this parameters to 1;2;3, report fails. However, setting
that value to 1 works.
Is this possbile?RS 2000 does not support multiple selections. For instance,
select * from blah where somefield in (@.Param)
will not work. What you can do is use either an expression or call a stored
procedure that takes the parameter and handles appropriately).
This will work:
= "select * from blah where somefield in (" & Parameters!Paramname.value &
")"
Note that this assume you have dealt with putting in all the proper syntax
like single quotes around charater type parameters, etc and that this will
be a valid query when done.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
>I have been working through a solution to allow multiple parameters to be
> passed into a SQL RS report. I have created a windows application that
> calls
> sql reporting services. The problem that I am having now is passing the
> multiple values the user selects from the dropdown to sql reporting
> services.
>
> I am setting: returnValues.Value
> When I try to set this parameters to 1;2;3, report fails. However,
> setting
> that value to 1 works.
> Is this possbile?|||I have been able to get the mulitple selection parameters to work if I call
the SQL RS report from a web page. I basically created dropdown listboxes
and have the form post to the url of the page I want to run. The multiple
selection values are sent to the report and the report works fine. However,
I am trying to call SQL RS report from a Windows Application. I have my
report created in a way that it uses a stored procedure to parse the multiple
values passed to in and joins to those values from a temp table.
"Bruce L-C [MVP]" wrote:
> RS 2000 does not support multiple selections. For instance,
> select * from blah where somefield in (@.Param)
> will not work. What you can do is use either an expression or call a stored
> procedure that takes the parameter and handles appropriately).
> This will work:
> = "select * from blah where somefield in (" & Parameters!Paramname.value &
> ")"
> Note that this assume you have dealt with putting in all the proper syntax
> like single quotes around charater type parameters, etc and that this will
> be a valid query when done.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
> >I have been working through a solution to allow multiple parameters to be
> > passed into a SQL RS report. I have created a windows application that
> > calls
> > sql reporting services. The problem that I am having now is passing the
> > multiple values the user selects from the dropdown to sql reporting
> > services.
> >
> >
> > I am setting: returnValues.Value
> >
> > When I try to set this parameters to 1;2;3, report fails. However,
> > setting
> > that value to 1 works.
> >
> > Is this possbile?
>
>|||OK, so you are doing the stored procedure method. That a good way to do it.
This whole thing work if from a web page passing in the multiple selections,
the only difference is that you are doing this from a windows app?
How are you integrating your windows app? URL integration or web services?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
news:89FA2276-2654-40C1-B03C-EFE7F1B5D0F9@.microsoft.com...
>I have been able to get the mulitple selection parameters to work if I call
> the SQL RS report from a web page. I basically created dropdown listboxes
> and have the form post to the url of the page I want to run. The multiple
> selection values are sent to the report and the report works fine.
> However,
> I am trying to call SQL RS report from a Windows Application. I have my
> report created in a way that it uses a stored procedure to parse the
> multiple
> values passed to in and joins to those values from a temp table.
> "Bruce L-C [MVP]" wrote:
>> RS 2000 does not support multiple selections. For instance,
>> select * from blah where somefield in (@.Param)
>> will not work. What you can do is use either an expression or call a
>> stored
>> procedure that takes the parameter and handles appropriately).
>> This will work:
>> = "select * from blah where somefield in (" & Parameters!Paramname.value
>> &
>> ")"
>> Note that this assume you have dealt with putting in all the proper
>> syntax
>> like single quotes around charater type parameters, etc and that this
>> will
>> be a valid query when done.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
>> message
>> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
>> >I have been working through a solution to allow multiple parameters to
>> >be
>> > passed into a SQL RS report. I have created a windows application that
>> > calls
>> > sql reporting services. The problem that I am having now is passing
>> > the
>> > multiple values the user selects from the dropdown to sql reporting
>> > services.
>> >
>> >
>> > I am setting: returnValues.Value
>> >
>> > When I try to set this parameters to 1;2;3, report fails. However,
>> > setting
>> > that value to 1 works.
>> >
>> > Is this possbile?
>>|||I actually need the ability to write the report to a file or display the
report in a web browser on the screen. In both cases I have to set the
Parameter values using an array.
"Bruce L-C [MVP]" wrote:
> OK, so you are doing the stored procedure method. That a good way to do it.
> This whole thing work if from a web page passing in the multiple selections,
> the only difference is that you are doing this from a windows app?
> How are you integrating your windows app? URL integration or web services?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
> news:89FA2276-2654-40C1-B03C-EFE7F1B5D0F9@.microsoft.com...
> >I have been able to get the mulitple selection parameters to work if I call
> > the SQL RS report from a web page. I basically created dropdown listboxes
> > and have the form post to the url of the page I want to run. The multiple
> > selection values are sent to the report and the report works fine.
> > However,
> > I am trying to call SQL RS report from a Windows Application. I have my
> > report created in a way that it uses a stored procedure to parse the
> > multiple
> > values passed to in and joins to those values from a temp table.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> RS 2000 does not support multiple selections. For instance,
> >> select * from blah where somefield in (@.Param)
> >>
> >> will not work. What you can do is use either an expression or call a
> >> stored
> >> procedure that takes the parameter and handles appropriately).
> >>
> >> This will work:
> >>
> >> = "select * from blah where somefield in (" & Parameters!Paramname.value
> >> &
> >> ")"
> >>
> >> Note that this assume you have dealt with putting in all the proper
> >> syntax
> >> like single quotes around charater type parameters, etc and that this
> >> will
> >> be a valid query when done.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >>
> >> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
> >> message
> >> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
> >> >I have been working through a solution to allow multiple parameters to
> >> >be
> >> > passed into a SQL RS report. I have created a windows application that
> >> > calls
> >> > sql reporting services. The problem that I am having now is passing
> >> > the
> >> > multiple values the user selects from the dropdown to sql reporting
> >> > services.
> >> >
> >> >
> >> > I am setting: returnValues.Value
> >> >
> >> > When I try to set this parameters to 1;2;3, report fails. However,
> >> > setting
> >> > that value to 1 works.
> >> >
> >> > Is this possbile?
> >>
> >>
> >>
>
>|||What I was trying to clarify is what does and does not work.
It sounds like calling the report from a web page using URL integration does
work. But, you are trying to use web services from your windows app (as an
alternative you can embed an IE control and use URL integration). Since you
say that a single value selected work, I wonder if some seperator character
is causing a problem. In your windows app try having a textbox that you key
in the correct value and see if that works.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
news:A07E6166-50E6-44D8-8ED4-3E3C279EAB1E@.microsoft.com...
>I actually need the ability to write the report to a file or display the
> report in a web browser on the screen. In both cases I have to set the
> Parameter values using an array.
> "Bruce L-C [MVP]" wrote:
>> OK, so you are doing the stored procedure method. That a good way to do
>> it.
>> This whole thing work if from a web page passing in the multiple
>> selections,
>> the only difference is that you are doing this from a windows app?
>> How are you integrating your windows app? URL integration or web
>> services?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
>> message
>> news:89FA2276-2654-40C1-B03C-EFE7F1B5D0F9@.microsoft.com...
>> >I have been able to get the mulitple selection parameters to work if I
>> >call
>> > the SQL RS report from a web page. I basically created dropdown
>> > listboxes
>> > and have the form post to the url of the page I want to run. The
>> > multiple
>> > selection values are sent to the report and the report works fine.
>> > However,
>> > I am trying to call SQL RS report from a Windows Application. I have
>> > my
>> > report created in a way that it uses a stored procedure to parse the
>> > multiple
>> > values passed to in and joins to those values from a temp table.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> RS 2000 does not support multiple selections. For instance,
>> >> select * from blah where somefield in (@.Param)
>> >>
>> >> will not work. What you can do is use either an expression or call a
>> >> stored
>> >> procedure that takes the parameter and handles appropriately).
>> >>
>> >> This will work:
>> >>
>> >> = "select * from blah where somefield in (" &
>> >> Parameters!Paramname.value
>> >> &
>> >> ")"
>> >>
>> >> Note that this assume you have dealt with putting in all the proper
>> >> syntax
>> >> like single quotes around charater type parameters, etc and that this
>> >> will
>> >> be a valid query when done.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >>
>> >>
>> >> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
>> >> >I have been working through a solution to allow multiple parameters
>> >> >to
>> >> >be
>> >> > passed into a SQL RS report. I have created a windows application
>> >> > that
>> >> > calls
>> >> > sql reporting services. The problem that I am having now is passing
>> >> > the
>> >> > multiple values the user selects from the dropdown to sql reporting
>> >> > services.
>> >> >
>> >> >
>> >> > I am setting: returnValues.Value
>> >> >
>> >> > When I try to set this parameters to 1;2;3, report fails. However,
>> >> > setting
>> >> > that value to 1 works.
>> >> >
>> >> > Is this possbile?
>> >>
>> >>
>> >>
>>

Allow Blank in parm not working

I am using RS2005. I have a data driven parm that I have checked "allow blank" as a value. I cant allow null so that isnt checked. I have even entered a space in the area where you can provide a default value. When I preview the report ... this parameter has <Select a Value> in the combo box. All other parms are filled in with defaults so the report should run. I hit "View Report" and get an error message "Please select a value for the parameter 'ParameterX' " which is the parameter I have said could be blank. Of course if I drop down the list there isnt a blank value to select - and I dont see why I would want to add one and force a selection of spaces ... seems like something is wrong here. Please help if you can. I am hoping it is just something simple.

Hello,

So let me get this right you have a drop down in the report that you want to be able to display a blank value you and contain a black value at the same time ?

what you can do is add a few lines of code to the sproc or sql statement that produces the resaults for the drop down parameter .

~~~~~~~~~~~ sample code

select EmployeeName, 2 as sort_col

from empdb

union all

select ' ' as EmployeeName, 1 as sort_col

Order by sort_col

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This will work in TSQL and there is a work around for oracle and teradata

|||

Thanks Charles,

I am using RS2005. The parm is a drop down list box. My question is why does my user have to select *anything* for that parameter if they want to leave it blank? Why do I have to insert a join to my query to add in a blank value which appears as a line of spaces in the drop down list. If the list is left untouched, and I have provided a default value of spaces (when I set up the parameter I gave it a default of spaces) - then why would it not work? It shows <Select a Value> text in the window of the list and forces you to make a selection ... if you want to leave it blank ... that is not an option. What is the purpose of having a check box "allow blanks" when you set up the parameter if there isnt any way to exercise the option of leaving it blank? I dont want to make my user *select* a blank line ... they should just be able to bypass that parameter if they dont want to make a selection.

|||

The allow blank works for input parameters. in order for blanks to be aloud in a drop down the sproc behind it must supplied the blank value

|||it seems very strange to me that I have to make them *choose* a blank ... since the idea is to minimize keystrokes by supplying a default value. Thanks for the reply :-)

Allow authenticated access without a log on

I have reporting services set up and working, using Windows Authentication
and a web page with hyperlinks to the reports
(i.e. /Reports/Pages/Report.aspx?ItemPath=%2fReports%2MyReport).
This is on an Intranet and users who have not authenticated against the
domain get a username and password prompt (as we would expect).
I turns out that none of the users will authenticated and so they will all
get the log on prompt.
Is there any way to avoid the log in prompt ? (a simple way - I know I can
Implement my own Security Extension - but this is overkill for my needs).
I can't use anonymous access as different groups of people need to see
different reports (and be denied access to the others).
There are 4 groups and I already know which group each user is in. ( they do
a forms based sign in to a gateway application first)
Is it possible for instance to pass a username and password to a report and
have it authenticate that way ?
Someone suggested using the API:
dim lgn as new
Microsoft.SqlServer.ReportingServices2005.ReportingService2005()
lgn.LogonUser("Myusername" , "myPassword" , "" )
Is this possible ? if so how would the report then be launched ?
I don't mind hard coding a generic username and password in the asp.net code
behind for the page. ( I can set up 4 generic accounts, one for each group).
--
MartyYou should be able to do this. If the user is logged in to your PC in the
same domain as the RS server then they should not be prompted.
Anything special about the server? I.e. is it a domain controller. Is it
running sharepoint? Does it have a fixed IP address?
I ask about the fixed IP address because I saw this problem once when I had
a test server that was using DHCP.
Also, try adding your domain users and groups to a local group and then use
that local group when setting up your roles.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Marty" <smarty@.community.nospam> wrote in message
news:9B1A741B-88BB-494A-AA71-67C058D8C7D7@.microsoft.com...
>I have reporting services set up and working, using Windows Authentication
> and a web page with hyperlinks to the reports
> (i.e. /Reports/Pages/Report.aspx?ItemPath=%2fReports%2MyReport).
> This is on an Intranet and users who have not authenticated against the
> domain get a username and password prompt (as we would expect).
> I turns out that none of the users will authenticated and so they will all
> get the log on prompt.
> Is there any way to avoid the log in prompt ? (a simple way - I know I can
> Implement my own Security Extension - but this is overkill for my needs).
> I can't use anonymous access as different groups of people need to see
> different reports (and be denied access to the others).
> There are 4 groups and I already know which group each user is in. ( they
> do
> a forms based sign in to a gateway application first)
> Is it possible for instance to pass a username and password to a report
> and
> have it authenticate that way ?
> Someone suggested using the API:
> dim lgn as new
> Microsoft.SqlServer.ReportingServices2005.ReportingService2005()
> lgn.LogonUser("Myusername" , "myPassword" , "" )
> Is this possible ? if so how would the report then be launched ?
> I don't mind hard coding a generic username and password in the asp.net
> code
> behind for the page. ( I can set up 4 generic accounts, one for each
> group).
> --
> Marty|||"Also, try adding your domain users and groups to a local group and then use
that local group when setting up your roles."
Ah but my issue is that the users have not logged in to the domain when they
use my application. If I get them to log in to the domain first (as a test)
it all works seamlessley, with no prompts. However this is not how they
normally work (don't ask !).|||On Jan 8, 3:13=A0am, Marty <sma...@.community.nospam> wrote:
> "Also, try adding your domain users and groups to a local group and then u=se
> that local group when setting up your roles."
> Ah but my issue is that the users have not logged in to the domain when th=ey
> use my application. If I get them to log in to the domain first (as a test=)
> it all works seamlessley, with no prompts. However this is not how they
> normally work (don't ask !).
Along the same line, is there a way to use Windows Auth to not require
the log on screen every time we open
the Reporting Services web page rpt-serv/reports? The report server
is in domain I created a local group and added
mydomain\domain users to it? Hope this did not hijack your thread
Marty :)
Bob|||Hello Smart,
Since the reporting services is designed for the windows auth, you may need
to specify the domain account and password to log on to the domain.
So it will prompt the dialog.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||This makes absolutely no sense. You want them to be authenticated in RS
without logging in BUT they haven't logged in period. Then how would any
system in the world know who they are?
Sorry, this is just plain silly. If you aren't logged into a domain then you
aren't logged in and you are not authenticated and there is no way to be
authenticated without logging in.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Marty" <smarty@.community.nospam> wrote in message
news:ABBE4EB2-43DE-424F-9655-32D3FF57559D@.microsoft.com...
> "Also, try adding your domain users and groups to a local group and then
> use
> that local group when setting up your roles."
> Ah but my issue is that the users have not logged in to the domain when
> they
> use my application. If I get them to log in to the domain first (as a
> test)
> it all works seamlessley, with no prompts. However this is not how they
> normally work (don't ask !).
>

All the people who said javascript: was not working

Hi,
I figured out why the javascript wasnt working in my browser.
If you just say localhost/reportserver - it doesnt work
try typing your <servername>/reportserver - and the javascript functions
will work.
Hope this helps
Thanks
RPAhh. OK. Thanks for getting back on the solution.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RP" <RP@.discussions.microsoft.com> wrote in message
news:F8322A36-8298-48CF-97E4-AFDC111BADC5@.microsoft.com...
> Hi,
> I figured out why the javascript wasnt working in my browser.
> If you just say localhost/reportserver - it doesnt work
> try typing your <servername>/reportserver - and the javascript functions
> will work.
> Hope this helps
> Thanks
> RP