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 returning too much data

I am running the below query and getting back results that have the word
"mode" in it. Isn't the keyword CONTAINS supposed to treat my search
expression as one word? Can someone show me what is wrong with this query so
that it returns only records that have the exact search expression "mode-4"
in it? Thank you.
SELECT MyFields
FROM MyTable M
LEFT JOIN Table1 T1 ON T1.Field1 = M.Field1
LEFT JOIN Table2 T2 ON T2.Field1 = M.Field2
LEFT JOIN Table3 T3 ON T3.Field1 = M.Field3
LEFT JOIN Table4 T4 ON T4.Field1 = M.Field4
WHERE CONTAINS( M.* , '"mode-4"' ) ORDER BY M.Field1
are the fields in Table1, Table2, Table3, Table4 and MyFields fulltext
indexed or are they integer values?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:82826E0A-C56B-4B47-B811-95F8F7075530@.microsoft.com...
>I am running the below query and getting back results that have the word
> "mode" in it. Isn't the keyword CONTAINS supposed to treat my search
> expression as one word? Can someone show me what is wrong with this query
> so
> that it returns only records that have the exact search expression
> "mode-4"
> in it? Thank you.
> SELECT MyFields
> FROM MyTable M
> LEFT JOIN Table1 T1 ON T1.Field1 = M.Field1
> LEFT JOIN Table2 T2 ON T2.Field1 = M.Field2
> LEFT JOIN Table3 T3 ON T3.Field1 = M.Field3
> LEFT JOIN Table4 T4 ON T4.Field1 = M.Field4
> WHERE CONTAINS( M.* , '"mode-4"' ) ORDER BY M.Field1
|||Not sure what you mean by "are they integer values", but the table that
contains MyFields is full-text indexed. Do tables 1,2,3, and 4 need to be
full-text indexed? Course, I'm thinking yes since you asked the question
"Hilary Cotter" wrote:

> are the fields in Table1, Table2, Table3, Table4 and MyFields fulltext
> indexed or are they integer values?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:82826E0A-C56B-4B47-B811-95F8F7075530@.microsoft.com...
>
>
|||Perhaps if you could post the schema. For the record mode-4 is indexed and
queried two separate words. If 4 is not in your noise word list this should
work.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:E3738AD4-7A01-40C2-95DB-A968D423B247@.microsoft.com...[vbcol=seagreen]
> Not sure what you mean by "are they integer values", but the table that
> contains MyFields is full-text indexed. Do tables 1,2,3, and 4 need to be
> full-text indexed? Course, I'm thinking yes since you asked the question
>
> "Hilary Cotter" wrote:
|||Sorry, but our company specifically prohibits posting any schema details in
newsgroups, but should the order be:
1. Remove words from the noise list.
2. Create the full-text index.
I'm wondering that since I created the index before removing the 4 from the
noise list, that it may be the reason my search is not working.
"Hilary Cotter" wrote:

> Perhaps if you could post the schema. For the record mode-4 is indexed and
> queried two separate words. If 4 is not in your noise word list this should
> work.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:E3738AD4-7A01-40C2-95DB-A968D423B247@.microsoft.com...
>
>
|||OK, let me guess your schema then from what you have posted.
Create MyFields(PK int not null identity primary key, Fields1 char(20),
Field2 char(20), Field3 char(20), Field4 char(20))
Create T1 (pk int not null references MyFields(PK), Field1 char(20))
Create T2 (pk int not null references MyFields(PK), Field2 char(20))
Create T3 (pk int not null references MyFields(PK), Field3 char(20))
Create T4 (pk int not null references MyFields(PK), Field4 char(20))
This is kind of critical as I think your join condition is all wrong.
But you are correct with a search on mode-4 and you have removed 4 from your
noise word list after building your index you will not get correct results.
In fact you should get fewer results which makes me wonder about your join
condition.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:15E5FCB3-0A36-4B0E-97D0-C0E62376B759@.microsoft.com...[vbcol=seagreen]
> Sorry, but our company specifically prohibits posting any schema details
> in
> newsgroups, but should the order be:
> 1. Remove words from the noise list.
> 2. Create the full-text index.
> I'm wondering that since I created the index before removing the 4 from
> the
> noise list, that it may be the reason my search is not working.
> "Hilary Cotter" wrote:
|||The join condition is there because there are many other items that we are
building a where clause on. I simplified the query to what I thought was most
necessary and only included the joins to be true to my actual query, but with
what you have said about creating the full-text after removing the number 4
from the noise file, now I do not think they matter. If I could show you the
actual schema, I think you would agree with me.
Thank you very much for your time. I'll recreate the full-text index, after
removing any noise words, and see how it works for me then.
"Hilary Cotter" wrote:

> OK, let me guess your schema then from what you have posted.
> Create MyFields(PK int not null identity primary key, Fields1 char(20),
> Field2 char(20), Field3 char(20), Field4 char(20))
> Create T1 (pk int not null references MyFields(PK), Field1 char(20))
> Create T2 (pk int not null references MyFields(PK), Field2 char(20))
> Create T3 (pk int not null references MyFields(PK), Field3 char(20))
> Create T4 (pk int not null references MyFields(PK), Field4 char(20))
> This is kind of critical as I think your join condition is all wrong.
> But you are correct with a search on mode-4 and you have removed 4 from your
> noise word list after building your index you will not get correct results.
> In fact you should get fewer results which makes me wonder about your join
> condition.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:15E5FCB3-0A36-4B0E-97D0-C0E62376B759@.microsoft.com...
>
>
|||MS changed the worbreaker in windows 2003, to contain what I consider
is now a bug.
The hyphen in 'mode-4' is actually now used to split the phrase into 2
words, therefore doing an OR search, hence returning every record with
'mode' OR '4' in it, which I expect will be quite a few.
On windows 2000, this worked properly to join the word, as a hyphen is
actually supposed to in text.
Eventually I got around it by replacing all hyphens in the indexed
text with HYP ie 'modeHYP4'. You need to replace any hyphens that
users enter in the search to the same.
Lots of our product skus had hyphens in so it was causing all sorts of
problems, 21-500 was returning thousands of results instead of 1.
Hope this helps...
On 14 Feb, 19:35, Mike Collins <MikeColl...@.discussions.microsoft.com>
wrote:
> The join condition is there because there are many other items that we are
> building a where clause on. I simplified the query to what I thought was most
> necessary and only included the joins to be true to my actual query, but with
> what you have said about creating the full-text after removing the number 4
> from the noise file, now I do not think they matter. If I could show you the
> actual schema, I think you would agree with me.

Help with query

I have 3 tables:
Words
WordLists
WordsInLists
I need to make a query to select other words (distinct) that appear on the same list of a given word, so what I did was something like
Select the top 50 lists where word XXX appears, put into a cursor and loop the cursor to list all the words that belong to the same list where word XXX is, this works fine, but the problem is that I obviously get different sets of results (one for each list in the cursor), how can I make to put all the results in the same resultset?, is this possible without creating a temp table?
Thanks
I'm not sure that I completely understand the relationship between tables or what you're looking for ... but here is the SQL based on my interpretation of your question:

SELECT DISTINCT
wil2.list
, wil2.word
FROM wordsinlists AS wil2
, (SELECT wl.list
FROM words AS w
, wordlists AS wl
, wordsinlists AS wil
WHERE w.word = wil.word
AND wl.list = wil.list
AND w.word = 'XXX') AS subquery
WHERE subquery.list = wil2.list
AND wil2.word != 'XXX';

This will give you all lists and associated words in the lists containing 'XXX'. If you just want the words, delete wil2.list.

Hope this helps,
Josh|||This can be done in a single query. It would help if you post some sample schema and data using CREATE TABLE & INSERT statements. And the expected results.|||I ended up doing nested selects using the IN directive and everything works fine.
Thanks!