Skip to Main Content
Feature Request FR-3765
Product Area Page Components
Status CLOSED

1 Voters

A dynamic classic report that adjusts automatically to the DDLs applied on the backend table

ppramanik Public · Apr 24 2024

Idea Summary
I have a classic report that reports on a table. This table has columns named after dates for each Monday for the 90 days. Every Monday, the oldest date column would be deleted and a new date column will be introduced. However the report does not adjust automatically to it. I have to change the report manually every week to adjust to the change.

Use Case
This proposed feature would be very useful to the weekly reports that track the value of a certain attribute over a period of time.

Preferred Solution (Optional)
The idea is to refresh the classic or interactive reports during load so that a “select * from” statement will capture the latest from the table.

This is a great idea! You can already achieve this in APEX today with a slightly different approach.

Comments

Comments

  • greg OP 1.4 years ago

    I know I will get flack for suggesting the select * approach here but if your report source has "select *" or is based on a  table instead of SQL you can right-click the report columns node in the left panel (render tree) and choose Synchronize Columns.

    But this still is manual. SO…..

    A workaround could be to use the generic columns feature of the classic report with a source type of Function Body Returning SQL Query.

    You could return a ‘SELECT * ’  in your query and then set User Generic Column Names on and enter the total number of columns in your table. 

    At this point you lose your column settings and have to redo them. Since you know the order of the columns you can probably set the the properties here based on column order. 

    But maybe you need the column names in the report to adjust to the column being added to the table.

    If so then in the Attributes of the report Region you can set the Headings attributes to something like Column Names (InitCap) or even provide a function that returns the column names.

    You could go as far as to duplicate the logic used to create these columns and in your function that returns your SQL for the report source, write the code to give it the correct column names.

  • ppramanik OP 1.3 years ago

    Thanks Greg for your response. I followed your idea and was able to come up with correct column names and report data and was able to produce a fully dynamic report.

    However my classic report has pagination and the column headers only stay for the first page. For all others pages column headers are null.

    Any idea?

  • greg OP 1.3 years ago

    I don't have an answer from this limited information.

    I will say that if you rely on page item session state for any part of this then you need to check session state and make sure it is being stored per session. This is a top problem I run into when I assist others.

    If any part of the definition of the report (function, headings, columns, etc) relies on a page item, when the pagination asks for a refresh from the server, the page session state must be available.

    As with all debugging you must test assumptions. For example you may have assumptions about what page load looks like compared to a partial page refresh (which is how sorting and pagination work). You could test to see what sorting does as well to try to nail down what causes the issue, is it pagination alone or is it any kind of partial page refresh.

    Turn on debugging, run the page. Paginate or sort or do whatever breaks it. Then go look at the debug log and you will see what it is doing behind the scenes, what queries it actually runs.

    To further debug something like this you can use the web dev tools in the browser. See what it posts when you try to paginate. See what it returns. Check session state values using the developer toolbar in the running page.

    This is a core feature of the product and there is a fair amount written about it. If you cannot get it working I would post more details in the oracle forums. There is more activity there and that is a better place than the ideas app for solutions to get documented for the community.