• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 405
  • Last Modified:

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

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
bruskhickory
Asked:
bruskhickory
2 Solutions
 
Mez4343Commented:
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
 
Ioannis ParaskevopoulosCommented:
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
 
bruskhickoryAuthor Commented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now