8 Replies Latest reply: Apr 25, 2013 10:14 AM by Anushree Gore RSS

    Enable non-associated values in list box for selection.

      Hi,

       

      I have a table T1 which has the following fields:

       

      ID, Date, Column1, Column2..

      1,   21,     A,          1

      1,   22,     B,          2

      2,   23,     C,          1

      2,   24,     D,          2

       

      and so on...

       

      I also have another Table T2 which has field Column that stores name of all columns starting from Column1, Column2 etc. T1 and T2 are related with ID field. Each ID can be related to some columns (depends on which ID it is).

       

      Now in my sheet i have a search box which searches ID field. I also have a list box with field Column (from T2). Both are having same alternate state.

      But now whenever user selects ID 1 in search-box, only the columns that are linked to ID1 are enabled in listbox. I want that irrespective of ID selected, all columns should be enabled for selection in list box.

       

      These selected columns will be used as dimensions in a Pivot table (dynamic pivot table) i have created.

       

      I tried using only({1}Column) as expression in list box but it shows null value.

       

      Please help !!

        • Re: Enable non-associated values in list box for selection.
          Stefan Wühl

          Try as expression in your list box

           

          =aggr( only({1} Column), Column)

           

          or

           

          =only( {1} aggr( only({1} Column, Column))

           

          This should show always all columns. But I believe that if you select a column that's incompatible with your ID selection, this will invalidate your ID selection.

            • Re: Enable non-associated values in list box for selection.

              I tried the expression, but they are not working.

               

              By the way, if i can use or mention the Alternate State name within GetSelectedFields() function probably my problem will get solved.

              Cause I'm using the following expression in enable condition of each of the dimensions in Pivot Chart:

              e.g.

              in the dimension Column1, this is the enable condition:

              SubStringCount(Concat(GetFieldSelections({alternate State} Column),'|'),'Column1')

               

              But it seems the alternate state is not being accepted by the function.

              • Re: Enable non-associated values in list box for selection.

                I tried the expression, but they are not working.

                 

                By the way, if i can use or mention the Alternate State name within GetSelectedFields() function probably my problem will get solved.

                Cause I'm using the following expression in enable condition of each of the dimensions in Pivot Chart:

                e.g.

                in the dimension Column1, this is the enable condition:

                SubStringCount(Concat(GetFieldSelections({alternate State} Column),'|'),'Column1')

                 

                But it seems the alternate state is not being accepted by the function.

                • Re: Enable non-associated values in list box for selection.

                  I tried the expression, but they are not working.

                   

                  By the way, if i can use or mention the Alternate State name within GetSelectedFields() function probably my problem will get solved.

                  Cause I'm using the following expression in enable condition of each of the dimensions in Pivot Chart:

                  e.g.

                  in the dimension Column1, this is the enable condition:

                  SubStringCount(Concat(GetFieldSelections({alternate State} Column),'|'),'Column1')

                   

                  But it seems the alternate state is not being accepted by the function.

                  • Re: Enable non-associated values in list box for selection.

                    I tried the expression, but they are not working.

                     

                    By the way, if i can use or mention the Alternate State name within GetSelectedFields() function probably my problem will get solved.

                    Cause I'm using the following expression in enable condition of each of the dimensions in Pivot Chart:

                    e.g.

                    in the dimension Column1, this is the enable condition:

                    SubStringCount(Concat(GetFieldSelections({alternate State} Column),'|'),'Column1')

                     

                    But it seems the alternate state is not being accepted by the function.

                    • Re: Enable non-associated values in list box for selection.

                      I tried the expression, but they are not working.

                       

                      By the way, if i can use or mention the Alternate State name within GetSelectedFields() function probably my problem will get solved.

                      Cause I'm using the following expression in enable condition of each of the dimensions in Pivot Chart:

                      e.g.

                      in the dimension Column1, this is the enable condition:

                      SubStringCount(Concat(GetFieldSelections({alternate State} Column),'|'),'Column1')

                       

                      But it seems the alternate state is not being accepted by the function.

                      • Re: Enable non-associated values in list box for selection.

                        I tried the expression, but they are not working.

                         

                        By the way, if i can use or mention the Alternate State name within GetSelectedFields() function probably my problem will get solved.

                        Cause I'm using the following expression in enable condition of each of the dimensions in Pivot Chart:

                        e.g.

                        in the dimension Column1, this is the enable condition:

                        SubStringCount(Concat(GetFieldSelections({alternate State} Column),'|'),'Column1')

                         

                        But it seems the alternate state is not being accepted by the function.

                        • Re: Enable non-associated values in list box for selection.

                          I tried the expressions but they  are not working.