Solved

How to highlight data only if it meets criteria comparing against the rest of the data.

Posted on 2013-02-06
3
380 Views
Last Modified: 2013-02-21
Hi Experts, I have a complicated issue.
I need to highlight rows of data if it meets a set of criteria. It is simple to highlight the row if it meets 3 sets criteria E.g. If column1=”Dog”, Column2=”Claws” and Column3=”Hungry” then highlight row. That is easy to do, however what I want to do is look at the rest of the rows of data for that day and see if Column2 and Column3 also appear but with a different column1.  This is very confusing to explain. I will use false data but hopefully you will see what I am trying to do. Say I have a pet cage and I don't mind all (column1) dogs, cats, bear’s etc mixing in that same cage. However if there are more than two types of column1 plus Column2=”Claws” and column3=”Hungry” then I want those rows highlighted so I don’t put them in the same cage. That is only if the column1 types are different. If for example there are 3 rows with dogs and they all have “Claws” and are “Hungry” they don’t need to be highlighted.  Not a very good example but best I could come up with to explain my problem. Is there a way to do this using custom code? I have studied programming but not for 10 years, so I know the concepts but I am very rusty.  I guess I just need a little guidance I how I might go about doing this. If It can't be done then I would like to know this as well. Many thanks for your help.   One more thing, I can only do the programming in the Report. I cannot setup procedures or anything directly to the Database. This needs to be done via the custom code in the report as we use a ODBC connection.
0
Comment
Question by:bruskhickory
3 Comments
 
LVL 10

Assisted Solution

by:Mez4343
Mez4343 earned 250 total points
ID: 38862142
First thought is to create a new DataSet (SQL Query) that will return row(s) based on the conditional criteria you need. This may involve creating a somewhat complex SQL query to do everything you detailed so you you need some decent sql skills. If you can create SQL result then you can check the result in your BackgroundColor property with a simple =IIF statement to do the highlighting. If you want to try SQL and need some help, post a sample db with requirements.

Custom code is an option but I wouldnt presonally put in ODBC db access calls there because you would need to handle errors and the like. If you need a starter reference for doing Custom Code check here. http://msdn.microsoft.com/en-us/library/ms156028.aspx
0
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 250 total points
ID: 38862623
Select a.column1, a.column2, a. column3, b.species from table1 a join
(Select column2, column3, case when max( column1)<>column2 then 'Different' else 'same' end as Species from table1 group by column2, column3) b
On a.column2 = b.column2
and a.column3 = b.column3


Try the above and see if it gets you the data you want.

Giannis
0
 

Author Closing Comment

by:bruskhickory
ID: 38916378
Sorry for late feed back I had to put this issue down the list. I still have yet to try it out.
Thanks for your help.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question