Monday, June 14, 2010

Interactive Reports with over 100 Columns

I ran into an issue last week where I had an Interactive Report (IR) with over 100 columns. All 100 plus columns displayed for end users, however I was only able to modify the first 100 columns. For example, the 101st column did not display in the report attributes screen (see screen shot below)



After some digging around I figured out how to modify columns that weren't displayed on the Report Attributes screen. This demo requires Firefox and Firebug. You should also be able to do this in Google Chrome. This demo works for APEX 3.x and APEX 4.0.

Build Large IR:


SELECT LEVEL c001, LEVEL c002, LEVEL c003, LEVEL c004, LEVEL c005, LEVEL c006, LEVEL c007,
LEVEL c008, LEVEL c009, LEVEL c010, LEVEL c011, LEVEL c012, LEVEL c013, LEVEL c014,
LEVEL c015, LEVEL c016, LEVEL c017, LEVEL c018, LEVEL c019, LEVEL c020, LEVEL c021,
LEVEL c022, LEVEL c023, LEVEL c024, LEVEL c025, LEVEL c026, LEVEL c027, LEVEL c028,
LEVEL c029, LEVEL c030, LEVEL c031, LEVEL c032, LEVEL c033, LEVEL c034, LEVEL c035,
LEVEL c036, LEVEL c037, LEVEL c038, LEVEL c039, LEVEL c040, LEVEL c041, LEVEL c042,
LEVEL c043, LEVEL c044, LEVEL c045, LEVEL c046, LEVEL c047, LEVEL c048, LEVEL c049,
LEVEL c050, LEVEL c051, LEVEL c052, LEVEL c053, LEVEL c054, LEVEL c055, LEVEL c056,
LEVEL c057, LEVEL c058, LEVEL c059, LEVEL c060, LEVEL c061, LEVEL c062, LEVEL c063,
LEVEL c064, LEVEL c065, LEVEL c066, LEVEL c067, LEVEL c068, LEVEL c069, LEVEL c070,
LEVEL c071, LEVEL c072, LEVEL c073, LEVEL c074, LEVEL c075, LEVEL c076, LEVEL c077,
LEVEL c078, LEVEL c079, LEVEL c080, LEVEL c081, LEVEL c082, LEVEL c083, LEVEL c084,
LEVEL c085, LEVEL c086, LEVEL c087, LEVEL c088, LEVEL c089, LEVEL c090, LEVEL c091,
LEVEL c092, LEVEL c093, LEVEL c094, LEVEL c095, LEVEL c096, LEVEL c097, LEVEL c098,
LEVEL c099, LEVEL c100, LEVEL c101, LEVEL c102, LEVEL c103, LEVEL c104, LEVEL c105,
LEVEL c106, LEVEL c107, LEVEL c108
FROM DUAL
CONNECT BY LEVEL <= 10


Get the COLUMN_ID


This is the column that you want to modify that isn't currently displayed on the Reports Attributes page

SELECT column_id,
column_alias,
display_order,
report_label
FROM APEX_APPLICATION_PAGE_IR_COL
WHERE application_id = :app_id
AND page_id = :page_id

Run


Go to the Reports Attributes screen (the screen that lists all the IR columns)

Open Firebug and go to the Console Window. Enter the following:

apex_p601_setColumnIDandSubmit('311507131164520006'); // Enter your column ID here

7 comments:

  1. Martin,

    I ran into this about 18 months ago and couldn't find a solution. I finally told the end-users they could only have 100 columns! They realized that made sense anyway, so it wasn't a problem.

    Could you tell us what the command does here? Does it just force the needed column to immediately show up this one time, or will it continue displaying the next time you hit this Development page?

    When I hit this problem, I was trying to change the column orders to match the users' requested order, so I guess I'd have to run that Console command for all of the columns that didn't show, so I could sort them in the desired order?

    Thanks for providing this! It's a shame they didn't fix it for Apex 4.0.

    ReplyDelete
  2. Hi Stew,

    If you hover your mouse over the "Edit Column" icon beside each IR column you'll see this JS command being called.

    I didn't look at the uncompressed code to see exactly what it does, however at first glance it allows you to go to the Column Attributes page and modify the column. It won't change the top 100 display order (i.e. the 101st entry still won't appear on the main list).

    Since all the columns are displayed to the end users, you could have set the display order and then saved the IR as the default report.

    As for APEX 4.0 fixing this, I don't think it's a critical issue. I had a very special case with this problem.

    ReplyDelete
  3. Thanks for the quick reply. Now I get it, this hack simply allows you to edit that column. As an alternative, what about clicking the Edit link to edit the 100th (last) column, then on that page, click the Next column button?

    As for my issue with the display order, I was talking about the order of the columns when the user selects Gear->Columns, in the Unselected column of this multi-select. I needed to display the columns in a specific order, for example, alphabetically. The idea is to make it easier for the user to find the column they want to add to their report.

    Thanks again,

    Stew

    ReplyDelete
  4. Hi Stew,

    Yes you're right you can hit the "next" button. I also noticed in APEX 4.0 in the tree view you can see all the columns there so you wouldn't need to do this fix.

    Martin

    ReplyDelete
  5. Hey thanks for the update that Apex 4 works around this somewhat. That said, having more than 100 columns is probably a bad thing, eh? For performance if just not bad design! :-)

    I still appreciate your tip, if for no reason than our DBAs won't upgrade to Apex 4.0 for many months after it's even released.

    They're so picky about the stability of the production systems! ;-)

    ReplyDelete
  6. Hi Martin,
    I visited your blog today and read many of your posts and found very useful to a beginner like me.
    I have one issue, hope you give a solution.
    In my application IRs are dynamically generated with the help procedures such as wwv_flow_api.create_page,wwv_flow_api.create_page_plug,wwv_flow_api.create_worksheet etc. This enables users to browse reports for database tables. Tables having more no.of columns can not be browsed through IR and get error as ORA-06502: PL/SQL: numeric or value error: character string buffer too small. modplsql's 32K limit may be the practical reason for this issue.
    I use 3.2.1.00.12 version of APEX. How can I display all the columns or atleast some columns with user understandable dispaly message to use "select columns" facility to browse limited columns at a time?

    Regards,
    Trapz

    ReplyDelete
  7. Hi Trapz,

    You could modify the default report settings to only include a predefined amount of columns. This would require that you modify some the APEX tables and put your APEX instance in an unsupported state so please proceed with caution.

    Thanks for the feedback.

    Martin

    ReplyDelete