7 Replies Latest reply: Jan 24, 2014 3:49 PM by SKarroum RSS

    How to create a report  based on selected item from Select list?

    SKarroum

      Hi,

      I have created a tables_LOV based on:

      select table_name d, table_name r from user_tab_cols

      where column_name like '%_type%'

       

      Then I created a page item ListOfTables,  Display as select list and pointing to tables_LOV.

      I run the page, and i can select the table i want from the drop down list.

       

      How to create a report  based on the selected item? (ex: select * from selected_table)

       

      many thanks in advance

      Salah

        • 1. Re: How to create a report  based on selected item from Select list?
          fac586

          23f29dfb-f3d1-42bf-b347-de28f528a12a wrote:

          Please update your forum profile with a real handle instead of "23f29dfb-f3d1-42bf-b347-de28f528a12a".

          I have created a tables_LOV based on:

          select table_name d, table_name r from user_tab_cols

          where column_name like '%_type%'

           

          Then I created a page item ListOfTables,  Display as select list and pointing to tables_LOV.

          I run the page, and i can select the table i want from the drop down list.

           

          How to create a report  based on the selected item? (ex: select * from selected_table)

          Could you explain why you want to do this? It seems like a strange basis for an application.

           

          When you post to this forum you'll get a faster, more effective response by including as much relevant information as possible upfront. Please tell us:


          • Full APEX version
          • Full DB/version/edition/host OS
          • Web server architecture (EPG, OHS or APEX listener/host OS)Browser(s) and version(s) used
          • Theme
          • Template(s)
          • Region/item type(s) (making particular distinction as to whether a "report" is a standard report, an interactive report, or in fact an "updateable report" (i.e. a tabular form)


          With APEX we're also fortunate to have a great resource in apex.oracle.com where we can reproduce and share problems. Reproducing things there is the best way to troubleshoot most issues, especially those relating to layout and visual formatting. If you expect a detailed answer then it's appropriate for you to take on a significant part of the effort by getting as far as possible with an example of the problem on apex.oracle.com before asking for assistance with specific issues, which we can then see at first hand.

          • 2. Re: How to create a report  based on selected item from Select list?
            SKarroum

            Hi,

            I have updated my display name thx

             

            I am using APEX 4.2

            the reason i am doing that is:

             

            In may application i have more than 60 tables, having the same data columns,  like "TableName Type Identifier"  "Name Englist"  "Name French"

            so, instead of creating a page for each table, i want to use something more efficient.

            i.e, when select a table from drop down list, i want to query this table.(select * from selectedtable)

            this query represent a report on this table.

             

            preferance to have interactive report.

             

            many thanks

            Salah

            • 3. Re: How to create a report  based on selected item from Select list?
              vincent_deelen

              You can create a proces that creates an apex collection based on a query string that has your LOV as table_name. Then you can create an interactive report based on that collection.

              Use a page load proces to create the collection and then force a page submit when you change the LOV. Or you can create a page load dynamic action that creates the collection, and subsequently refreshes the report region.

              APEX_COLLECTION

              Implementing Dynamic Actions

               

              Or yet another solution would be to write a function that returns a table set. Use table_name as input parameter. you can do a query on that function with

              select * from table( my_function( p_table_name => :P_LOV_ITEM ) );

               

              Regards,

              Vincent

              http://vincentdeelen.blogspot.com

              • 4. Re: How to create a report  based on selected item from Select list?
                fac586

                SKarroum wrote:

                 

                I am using APEX 4.2

                the reason i am doing that is:

                 

                In may application i have more than 60 tables, having the same data columns,  like "TableName Type Identifier"  "Name Englist"  "Name French"

                Wouldn't it be much more efficient to just have one table?

                • 5. Re: How to create a report  based on selected item from Select list?
                  SKarroum

                  Thanks alot Vincent, I really appreciate your help.

                  you provided 3 options, which i am not sure how to do any

                  Kindly can you please provide a sample of 1 option.

                   

                  in my 1st note: i had

                  myTables_LOV as: SELECT table_name FROM user_tab_Cols WHERE column_name like '%TP_ID%'

                  Then i created a page item: P_myTables, where is select list and uses myTables ans the LOV.

                  With your options, do i need what i had, and how can I reuse it if needed, or just start clean?

                  The only missing link is: when i select an item from drop down list i want to display a report for selected table.

                   

                  from your Note:

                  i think myTables_LOV could be the function which returns a table set.

                  select * from(my_function(p_table_name => p_myTables));

                  please note: couldn't put (:) in front of p_mytables, if shows happy face.

                  where do i get p_table_name? is it right the use of p_myTables in the function?

                  where or when i can use this function?

                   

                  regards

                  Salah

                  • 6. Re: How to create a report  based on selected item from Select list?
                    vincent_deelen

                    Hi Salah,

                     

                    Allright, have a look at this page: http://apex.oracle.com/pls/apex/f?p=vincentdeelen:collection_report

                     

                    I think that simulates what you're trying to accomplish. I've set up the simplest method I could think of.

                    The report is based on an apex collection. If you are not familiar with that, you should study the documentation: APEX_COLLECTION

                    To recreate my example you should:

                    1) create an (interactive) report on your collection

                    SELECT *
                       FROM APEX_collections
                     WHERE collection_name = 'MY_COLLECTION'
                    

                    2) create a page_item select list for the tables you want to display (in my case this is called "P38_TABLES" )

                    3) create a dynamic action that triggers on change of your select list page_item. The dynamic action must be a PL/SQL procedure perfoming the following code:

                    declare
                      l_query varchar2(4000);
                    begin
                      l_query := 'select * from '||:P38_TABLES;
                      if apex_collection.collection_exists
                            ( p_collection_name => 'MY_COLLECTION' )
                      then
                        apex_collection.delete_collection
                          ( p_collection_name => 'MY_COLLECTION' );
                      end if;
                      
                      apex_collection.create_collection_from_query
                        ( p_collection_name => 'MY_COLLECTION'
                        , p_query           => l_query
                        );
                    end;
                    

                    Make sure you add your page_item to the "Page Items to Submit" section.

                    4) Add an extra true action that does a refresh of the report region.

                     

                    Here are two pictures describing the da:

                    http://www.vincentdeelen.com/images/otn/OTN_COLLECTION_REPORT_DA1.png

                    http://www.vincentdeelen.com/images/otn/OTN_COLLECTION_REPORT_DA2.png

                     

                    Good luck and regards,

                    Vincent

                    http://vincentdeelen.blogspot.com

                    • 7. Re: How to create a report  based on selected item from Select list?
                      SKarroum

                      GREAT, it works

                      I really appreciate your HELP

                      Million thanks