4 Replies Latest reply: Jan 28, 2013 1:04 AM by drama9346 RSS

    help with  showing tabular form

    drama9346
      Hi everyone!

      I use Apex 4.1 and 10g database.

      I create report with code below
      select APEX_ITEM.RADIOGROUP(1,rownum) " ", 
               APEX_ITEM.DISPLAY_AND_SAVE(2,op.id_pd) id, 
               APEX_ITEM.DISPLAY_AND_SAVE(3, oi.naziv) name, 
               APEX_ITEM.DISPLAY_AND_SAVE(4,op.vpd) vpd, 
               APEX_ITEM.DISPLAY_AND_SAVE(5,op.change) change,   
               APEX_ITEM.DISPLAY_AND_SAVE(6,op.id_vk) id_vk, 
               APEX_ITEM.DISPLAY_AND_SAVE(7,op.zodp_dat) zdat, 
               APEX_ITEM.DISPLAY_AND_SAVE(8,op.zodp_kol) zkol
      from okpl_p op, okpl_pd oi
      where op.id_p = :P4_ID_POM
      and oi.id_pd = op.id_pd
      and oi.oe in ((select oe from okpl_users where lower(username) = lower(v('app_user'))))
      and now I need to show tabular form, based on the record from selected row (I select row for showing tabular form with clicking on radio).

      For each op.id_p from table okpl_p I can have many records (op.id_pd is key), and for those records I have nex table where I write some data for each week in year.

      So, now I need some clue how can I achieve this.

      I hope we understand each other!

      Regards,
      drama9346
        • 1. Re: help with  showing tabular form
          riedelme
          drama9346 wrote:
          I create report with code below
          select APEX_ITEM.RADIOGROUP(1,rownum) " ", 
          APEX_ITEM.DISPLAY_AND_SAVE(2,op.id_pd) id, 
          APEX_ITEM.DISPLAY_AND_SAVE(3, oi.naziv) name, 
          APEX_ITEM.DISPLAY_AND_SAVE(4,op.vpd) vpd, 
          APEX_ITEM.DISPLAY_AND_SAVE(5,op.change) change,   
          APEX_ITEM.DISPLAY_AND_SAVE(6,op.id_vk) id_vk, 
          APEX_ITEM.DISPLAY_AND_SAVE(7,op.zodp_dat) zdat, 
          APEX_ITEM.DISPLAY_AND_SAVE(8,op.zodp_kol) zkol
          from okpl_p op, okpl_pd oi
          where op.id_p = :P4_ID_POM
          and oi.id_pd = op.id_pd
          and oi.oe in ((select oe from okpl_users where lower(username) = lower(v('app_user'))))
          and now I need to show tabular form, based on the record from selected row (I select row for showing tabular form with clicking on radio).

          For each op.id_p from table okpl_p I can have many records (op.id_pd is key), and for those records I have nex table where I write some data for each week in year.

          So, now I need some clue how can I achieve this.
          What is wrong? Have you put the query in a reports region? Is the output not the way you expect or are you getting no rows back?
          • 2. Re: help with  showing tabular form
            drama9346
            Hi!

            I don't know how can I achieve that after I select one radio buton from report, I see right tabular form below report. Code in my previous post is for gettng report and it is ok (report shows all data I need).

            Regards,
            drama9346
            • 3. Re: help with  showing tabular form
              Andreas Wismann
              Here's how you can achieve your goal:

              Create a Hidden Item to control the primary key of your details table.
              Name it, say, P4_HIDDEN_ID_PD (or give it any name that you like).

              In the report, your statement should begin like this:
              select 
               APEX_ITEM.RADIOGROUP(
                 p_idx            => 1
               , p_value          => op.id_pd
               , p_selected_value => :P4_HIDDEN_ID_PD
               , p_attributes     => 'onclick="apex.submit({
                   request:''ROW_SELECTED''
                      ,set:{''P4_HIDDEN_ID_PD'':''' || op.id_pd || '''}
                   });"'
                 ) as Row_Selector,
               , -- the other columns ...
               
              (I use indention to keep it easy to read, you can reduce all the unnecessary whitespaces to save http traffic).

              Keep away from using ROWNUM in this example. You should use op.id_pd for the p_value parameter of your radiogroup, because only then will your radiogroup indicate the row that the user has clicked (p_selected_value => :P4_HIDDEN_ID_PD).

              That said, it's always a very good idea to only have INTEGERS in a Primary Key column like op.id_pd, because if it contained weird characters you could not use it safely in JavaScript.
              You have to set the Row_selector's "Display as" to "Standard report column", which I think you know already.

              Then create the details report, where your select statement goes like this:
              select * -- place your favourite details columns here
                from my_details_table -- what's its name?
               where my_details_table.id_pd = :P4_HIDDEN_ID_PD
              And nice to have, you'd give the details report a display condition of
              "Value of Item/Column in Expression 1 is not NULL"
              and in Expression 1, you enter
              P4_HIDDEN_ID_PD
              (take care you do NOT prefix its name with a : in this case!)

              You can also steer your processes depending on the :REQUEST value of "ROW_SELECTED", as you may see in the upper SELECT statement.

              Hope this helps.
              • 4. Re: help with  showing tabular form
                drama9346
                Hi Andreas!

                Sorry for my late response, but I was out for 2 days. Thanks for your solution, but I have problem with it. If I use your SQL code:
                Code from Andreas:
                select 
                APEX_ITEM.RADIOGROUP(
                p_idx => 1
                , p_value => op.id_pd
                , p_selected_value => :P4_HIDDEN_ID_PD
                , p_attributes => 'onclick="apex.submit({
                request:''ROW_SELECTED''
                ,set:{''P4_HIDDEN_ID_PD'':''' || op.id_pd || '''}
                });"'
                ) as Row_Selector
                , -- the other columns ...
                {quote} 
                
                I get: 
                Query cannot be parsed within the Builder. If you believe your query is syntactically correct,
                check the ''generic columns'' checkbox below the region source to proceed without parsing. ORA-00907: missing right parenthesis
                I can solve this problem by removing  
                p_idx => , p_value => , p_selected_value =>, p_attributes =>
                 but then I get another problem. In my :P4_HIDDEN_ID_PD I don't get op.id_pd, instead of that I have under radiougroup button in my report text showing 
                onclick="apex.submit({request:'ROW_SELECTED',set:{'P4_HIDDEN_ID_PD':'302571'}});"
                Any idea for solutions?
                
                Regards,
                drama9346
                
                Add:
                I solve those problems with looking in apex.radiogroup help on  [url http://docs.oracle.com/cd/E10513_01/doc/apirefs.310/e12855/apex_item.htm#CHDGCJCG]page[/url]. I add '' into my select code, so now it looks like: 
                SELECT APEX_ITEM.RADIOGROUP(
                1,
                op.id_pd,
                :P4_HIDDEN_ID_PD,
                '',
                'onclick="apex.submit({request:''ROW_SELECTED'',set:{''P4_HIDDEN_ID_PD'':''' || op.id_pd || '''}});"') Row_Selector,
                -- the other columns ...
                Edited by: drama9346 on 27.1.2013 22:58