6 Replies Latest reply on Jul 23, 2013 9:51 PM by Gor_Mahia

    dynamically change number of columns in report

    Gor_Mahia

      all,

      I am trying to change the number of columns in a report dynamically as in this sample(http://actionet.homelinux.net/htmldb/f?p=LSPDEMO:60:11041263091189::NO:::) , can somebody who has this code kindly send it to me or make reference...i'll appreciate

        • 1. Re: dynamically change number of columns in report
          Howard (... in Training)

          You don't indicate the type of report and it may not matter.  Every report's Report Attributes tab listing the display columns/items has a Conditional Display section for each column.  There are all kinds of Condition Types.  I like to use "PL/SQL Expression".  Then in the Expression 1 field that's displayed, put an expression that evaluates to TRUE (for display) and FALSE (for hide). So you could have the expression:

           

          :PX_DISPLAY_YN = "Y'

           

          I turn some columns on/off with checkboxes.  When the box was checked, PX_SELECTOR contained "JLN",  so I had for the Expression 1 condition

           

          INSTR(:PX_SELECTOR,'JLN') > 0

          When the checkbox was changed, I needed a (partial) page refresh so the display would change.

           

          Does that help?

           

          Howard

          • 2. Re: dynamically change number of columns in report
            Gor_Mahia

            Howard,

             

            There are all kinds of Condition Types.  I like to use "PL/SQL Expression".  Then in the Expression 1 field that's displayed, put an expression that evaluates to TRUE (for display) and FALSE (for hide). So you could have the expression:

             

            Thats true but its more efficient to use the sample reference i gave before . With conditions you are trying to conditionally hide 1/more columns collective in my case every column is indipendent pls check that sample to see what iam trying to do.

             

            regards,

            • 3. Re: dynamically change number of columns in report
              Howard (... in Training)

              I did look at the example and sorry, I have no idea where to get the code.  My approach makes every column independent as well.  In the example, they are making selections with a shuttle.  I think it all works about the same.  Selections are made in the shuttle and selector variable -- in this case the shuttle page item -- is referenced individually by each column's Conditional Display code.  The values in the shuttle are a colon (:) separated list of values.  Suppose the value for the State column is "ST".  (Of course the values have to be unique and not overlap.  You can't have ST and STA  since STA contains ST.)  So for the Expression1 of the State column one could have

              INSTR(:PX_SHUTTLE_SELECTOR,'ST')>0

               

              For the First Name Column, say:

              INSTR(:PX_SHUTTLE_SELECTOR,'FN')>0

              etc.

               

              Then you just need to fire a page refresh when the shuttle changes.

               

              Howard

              • 4. Re: dynamically change number of columns in report
                xarg-Xrc

                Code wasnt published, but my observations from toying around with firebug are:

                 

                1) Using dynamic action to "refresh" the RHS report when you make changes on the LHS selectors (using Jquery accordion).

                2) Its using Ajax calls to a page process

                 

                 

                 

                <script type="text/javascript"> 
                function updateColumns(){ 
                 var c=new Array(); 
                 $('#sortable2 li').each(function(i){ 
                  c.push($(this).text()); 
                 }); 
                 $.htmldbAjax({ 
                  data:{p_request:'APPLICATION_PROCESS=PAGE60_UPDATE_COLUMNS',f01:c}, 
                  success:function(){ 
                  $("#ORDERS").trigger("apexrefresh"); 
                  } 
                 }); 
                } 
                </script> 
                
                
                

                which (I think, not 100% certain) outputs out the table via htp.p with updated columns. Whoever put that up is a jquery whiz.

                 

                 

                Its very impressive.

                • 5. Re: dynamically change number of columns in report
                  xarg-Xrc

                  But Howards solution is easier to follow and works perfectly with a shuttle.

                   

                  Define a function like this:

                   

                  create or replace FUNCTION item_in_list(p_num IN NUMBER, p_shuttle_name in VARCHAR2)
                  
                  RETURN NUMBER
                  
                  IS
                      l_vc_arr2   APEX_APPLICATION_GLOBAL.VC_ARR2;
                      TYPE number_list is TABLE OF NUMBER;
                      num_list number_list := number_list();
                      x_ret NUMBER;
                  
                  BEGIN
                  
                      /* checks if p_num exists in the colon delimited list p_shuttle_name */
                  
                          l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE(p_shuttle_name);
                  
                          FOR i IN 1..l_vc_arr2.COUNT LOOP
                  
                              num_list.EXTEND;
                  
                              num_list(i):= CAST(l_vc_arr2(i) AS NUMBER);
                  
                          END LOOP;
                  
                          IF p_num MEMBER OF num_list then
                              return 1;
                          else
                              return 0;
                          end if;
                  END;
                  
                  
                  

                  And then for every column put a condition (Function returning boolean) like this:

                   

                  begin
                  if item_in_list(1, : p10_shuttle) = 1 then
                  return true;
                  else
                  return false;
                  end if;
                  end;
                  
                  
                  

                  where 1 in my case is the return lov value.

                   

                  This is if your shuttle's return value is numeric and display value is varchar2. If both are varchar2 use instr as Howard suggested.

                   

                  Its probably not as fancy as the Jquery one but gets the job done.

                  • 6. Re: dynamically change number of columns in report
                    Gor_Mahia

                    i'll give it a shot and let you know...regards