If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Using Like and Not Like Condition in my report

Started by cbyrne, 21 Sep 2011 03:21:51 AM

Previous topic - Next topic

cbyrne

Hello All, Need your help again!, I have the below string variable and it doesn't seem to do anything, i have an actual quantity and a booked in quantity and i want ti highlight in Red when the booked in quantity is different.

CASE WHEN '[Query1].[Total(Actual PC Base Units)]' LIKE '[Query1].[Total(Booked In PC Base Units)]' THEN 'GREEN' WHEN '[Query1].[Total(Actual PC Base Units)]' NOT LIKE '[Query1].[Total(Booked In PC Base Units)]' THEN 'RED' ELSE 'WHITE ' END


Many Thanks

Chris

PRIT AMRIT

If I understood, you are trying to find all the values from [Query1].[Total(Booked In PC Base Units)], which starts with the strings from [Query1].[Total(Actual PC Base Units)]? If yes, then the LIKE function would not work.

What is your database? If it is SQL server, you may use the below expression.

Create a Data Item and try with the below expression and see if it gives you the correct result.

charindex([Query1].[Total(Actual PC Base Units)]','[Query1].[Total(Booked In PC Base Units)]) >0

Quotecharindex ( string_exp1, string_exp2 [ , start_location ]  )
Searches string_exp2 for the first occurrence of string_exp1 and returns an integer. start_location is the character position to start searching for string_exp1 in string_exp2. If start_location is not given, is a negative number, or is zero, the search starts at the beginning of string_exp2.

Correct me if I am wrong  ;)


blom0344

Wouldn't it be easier to subtract the 2 results and validate whether the difference = 0 or <> 0? The LIKE operator would be used for matching partial values between strings

cbyrne

Guys i think i got myself all confused which isn't difficult for me... :(

Anyway i tried your way PRIT and this wouldn't work with Oracle DB.  I tried creating several data items as per below.

[Actual PC Base Units]>0
[Booked In PC Base Units]>0

then
[Actual PC Base Units]and[Booked In PC Base Units]

then i created the below string variable
CASE WHEN [Query1].[Actual PC Base Units And Booked in PC Bases Greater Than] THEN 'GREEN' WHEN [Query1].[Actual PC Bases and Booked in Less Than] THEN 'RED' ELSE 'WHITE ' END

Like i said all confused and not sure what i am doing.  I basically have two columns in a list view which give a total so when one of them doesn't match i want to highlight this in Red.

Thanks for your help guys.


Chris

blom0344

1. Create a calculation like  total([value1]-[value2]) with aggregate setting calculated
2. Call this dataitem 'diff'
3. Create boolean variable like [diff] = 0
4. Associate diff with the list  (through list properties)
5. Add style variable to the column you want to highlight. Choose background color for 'yes' setting.

It should really be that simple!!