12 Replies Latest reply: Mar 16, 2011 9:58 AM by Rohit Gupta RSS

Is this variable available in the WebI

cleo tao
Currently Being Moderated

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

  • Re: Is this variable available in the WebI
    Praveen Vodela
    Currently Being Moderated

    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

  • Re: Is this variable available in the WebI
    Shanthi Bhaskar
    Currently Being Moderated

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

  • Re: Is this variable available in the WebI
    Rachna Jain
    Currently Being Moderated

    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.

  • Re: Is this variable available in the WebI
    cleo tao
    Currently Being Moderated

    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'?

    • Re: Is this variable available in the WebI
      Rachna Jain
      Currently Being Moderated

      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

  • Re: Is this variable available in the WebI
    cleo tao
    Currently Being Moderated

    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?

    • Re: Is this variable available in the WebI
      Rachna Jain
      Currently Being Moderated

      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..

    • Re: Is this variable available in the WebI
      Bonar Asido Sihombing
      Currently Being Moderated

      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

  • Re: Is this variable available in the WebI
    cleo tao
    Currently Being Moderated

    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

    • Re: Is this variable available in the WebI
      Rohit Gupta
      Currently Being Moderated

      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

      • Re: Is this variable available in the WebI
        cleo tao
        Currently Being Moderated

        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

        • Re: Is this variable available in the WebI
          Rohit Gupta
          Currently Being Moderated

          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

Actions