Is this variable available in the WebI

[Former Member]

Hi There

I have a WebI report, now I need to get some of records(list) by some condition, for example

th raw data is

Tag SN Type

ABC 11111 Win2k

123 22222 Win2k

456 11111 Win7

789 44444 Win2003

now I need to get the data which "Type" is Win2k but the SN is not in the SN which "Type" is Win7, so I should only get one record which is

ABC 11111 Win2k

how can I get this data?

thanks

[Former Member]

My this WebI report has 10 tabs, 9 of them use one query filter, and this one is specially, If I add SN as filter in the query, I have to use second query for this tab only, which I dont want to

I am wondering in one query filter, can I add some variable to get this data

I tried to create a variable Win7SN = SN where (TYPE = 'Win7'), and If I put this variable in the report, the data looks like

Tag SN Type Win7SN

ABC 11111 Win2k

123 22222 Win2k

456 11111 Win7 11111

789 44444 Win2003

then I create another variable NotWin7SN = SN where (SN <> Win7SN), and If I put this in the report, the data looks like

Tag SN Type Win7SN NotWin&SN

ABC 11111 Win2k 11111

123 22222 Win2k 22222

456 11111 Win7 11111

789 44444 Win2003 44444

How Can I get the data "select records with Type = Win2k and SN not in the SN which Type is Win7" with variables?

thanks

[Former Member]

Hi,

Please try the following steps:

1. Create a variable as -

 v SN Win 7 = [SN] where ( [Type]= "WIin7") 

2. Create a flag variable which will return 1 for the required row and 0 for unrequired rows.

v Flag = If([Type]="Win2K" and SN Not equal to [v SN Win 7]) Then 1 Else 0 
OR 
v Flag = If([Type]="Win2K" and SN Not equal to Max([v SN Win 7])) Then 1 Else 0 

3. Create a Block Level Filter as v Flag = 1 to filter out the unrequired rows.

Regards,

Rohit

[Former Member]

I tried both, it does not work

if 1st variable = SN where Type = 'Wink7' in the report, for other Type, this value will be blank, thus, 2nd flag

= if(Type = 'Win2k' and SN <> 1st variable;1;0) will be always show 1, because for other Type, the 1st variable is blank

I think this is because 1st variable is actually an array including lots of different SN, the 2nd variable needs to use Not INLIST, but in WebI, INLIST's second parameter is a string, not a OBJECT

[Former Member]

Hi,

I tried with the data at my end and able to get the result using the below formula:

v Flag = If([Type]="Win2k" And [SN ] Not Equalto Max([v SN Win 7]) In Report) Then 1 Else 0 

The above formula will only work when you have only 1 SN for Type Win7. If you have more than 1 SN for Type = Win7 then this

must be done at the query level Eg with the help of sub-query.

Note: To apply the block level filter as I mentioned in my previous post you need to add* v Flag* in the block which you can hide

using border and font same as the report background.

Regards,

Rohit

Edited by: rohit12 on Mar 16, 2011 9:56 AM

[Former Member]

but how can I filter this SN which Type is Win7

the variable = SN where Type is Win7 is easy to create, but what i need is the record which type is win2k and SN not in SN which Type is Win7

it seems no such variable like SN inList Object?

[Former Member]

Tye this...

Create the variable Test with the formula u201CSN where (Type = "WIN7")u201D Test You will get

Tag SN Type

ABC 11111 Win2k

123 22222 Win2k

789 44444 Win2003

create another variable like u201CSN where ([SN] not equalto [TEST])u201D . You will get

Tag SN Type

123 22222 Win2k

789 44444 Win2003

Now put filter in u201CTypeu201D column for WIN2K you will get

Tag SN Type

123 22222 Win2k

I hope this will work out with you.

All the best..

[Former Member]

Hi cleo2010,

WEBi filter has this option for you to set a filter for a field to be 'In list' or 'not in list' from the value list of 'Result from another query'.

So for your case.

Create a WEBi query, set SN as filter field to be 'not in list', and setup the filter value list to be 'Result from another query which list all the serial number with where Type is Win7.

Hope this help

[Former Member]

my this Webi has 10 tabs, one of tab has this data, right now, I use two query, one is for other 9 tabs, 1 is for this tab which in the query condition, i use sub query

but I am wondering if I can get a list by variable, like 'select something where something'?

[Former Member]

You can try one more option use condition "TYPE = WIN2k" in Query.

If you do not want to change your query than filter the column value on report level.

Now you can create the variable like

= SN where (Type <> "WIN7")

User this variable in the place of SN objecs.

This can also resolve your issue without making any changes in you query.

Why to go to complicated sub query if we can solve this by simply applying filter than put variable for SN column.

Rachna

[Former Member]

You can solve this by simply put condition in the query Conditional pane.

Create one query in reult object right SN and in Condition

TYPE not EQL to Win7k

In second query your main query put the condition YPE inlist WIN2k

Inside this condition add one more condition like SN not equal to "Result from another query"

Condition structre will look like something like this.

YPE inlist WIN2k

AND

SN not equal to "Result from another query"

And

Year Equal To 2011

In this choose the query which you have created for WIN 7K

Hope this will resolve your issue.

[shanthi_bhaskar]

you can write simple formula in webi for this i dont think there is built in varaible exists

[Former Member]

Your data :

Tag      SN      Type
ABC    11111    Win2k
123    22222    Win2k
456    11111    Win7
789    44444    Win2003

This problem solves many ways: solve simple way on it using prompt function both objects SN, Type

Query filter: drag the SN object to query filter---->Equal to -

---

>prompt drag the Type object to query filter---->Equal to -

---

>prompt

OR

make Query filter on both SN, Type

Query filter:

SN--> Equal to----->11111

Type--> Equal to----->Win 2k

variable also possible but it shows remain cells empty in the column where Except your selection

IF([SN]=11111 and [Type]="Win2k") then [Type]

All the best,

Praveen