tag:blogger.com,1999:blog-1561253375800464394.post3082337441807093501..comments2024-03-13T11:59:19.468+01:00Comments on Denes Kubicek ApEx BLOG: Getting Interactive Report QueryDenes Kubicekhttp://www.blogger.com/profile/01962373591521960882noreply@blogger.comBlogger39125tag:blogger.com,1999:blog-1561253375800464394.post-53339700986445171592021-11-05T02:19:21.088+01:002021-11-05T02:19:21.088+01:00Hi.
Does this still work in apex 21?
I'm usi...Hi.<br /><br />Does this still work in apex 21?<br /><br />I'm using simillar function, but in a package. When i call it in apex, in IR as "Function Body returnin SQL Query" (return package_name.get_report_sql(p_page_id => 1000)), apex developer breaks, with JSON.WRITER.NOT_OPEN error and Error: SyntaxError: Unexpected token { in JSON at position 232.<br /><br />If i show query with sys.htp.p(v_query) and then use it in apex or sql developer, it works.<br /><br />As soon as comment out<br />apex_ir.get_last_viewed_report_id ...<br />apex_ir.get_report ...<br />page in apex developer works, but without query colums (expected).<br /><br />What could be the problem?<br /><br />Thanks.<br /><br />BR,<br />Dip<br /> Diplinghttps://www.blogger.com/profile/08507121781551682703noreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-87235011951029479502016-03-30T01:15:04.794+02:002016-03-30T01:15:04.794+02:00Hi Tony,
There are certainly infinite solutions, ...Hi Tony, <br />There are certainly infinite solutions, but the only one I heard about is based on the IR Query Plugin authored by Denes. <br />I couldn't get it because the downoload link was broken.<br />I am working on a printing project where I must customize my PDF Outputs according to my interactive reports sorting an filtering. <br />To make simple, I have an APEX 5 interactive report built on the following query --> SELECT EMPNO, ENAME, JOB, HIREDATE, COMM, SAL FROM EMP. <br />Assume that this is the page 12 of my application.<br />I created a report Query and an XSL-FO custom layout associated with this query. I am using a bipublisher server. <br />Finally, I created a button on page 12 to download the PDF report associated with my report query. And it works but does not meet my needs. Now, the Goal is <br />to produce the PDF Outputs based on the sorting and filtering of the end users. Any suggestion about this?<br /><br />Best Regards<br /><br />Kassoum Kone<br />Abj, CIVAnonymoushttps://www.blogger.com/profile/00489358956402142459noreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-75090057748003032152016-03-30T01:12:52.632+02:002016-03-30T01:12:52.632+02:00BI Publisher uses the RTF layout to product custom...BI Publisher uses the RTF layout to product custom layouts, FOP uses the xls style sheets...<br /><br />Bi Publisher expects the query to be executed on the APEX end to build an XML data feed to go with the layout to produce PDF output. Thus the columns required are not really flexible in this sense of what you CAN do with an interactive report adding and subtracting columns from a report.<br /><br />I remember the topic you are talking about from the Experts Guide to APEX for APEX 5.. Just don't remember how author handled this with interactive reports. Since they can't really have the ability to modify source too much..<br />APEX Developer In the USAhttps://www.blogger.com/profile/06746962787261032815noreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-39294120374479145942016-03-30T01:06:34.351+02:002016-03-30T01:06:34.351+02:00This comment has been removed by the author.Anonymoushttps://www.blogger.com/profile/00489358956402142459noreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-22613562092845899752016-03-29T17:36:45.022+02:002016-03-29T17:36:45.022+02:00Creating a custom layout for an interactive report...Creating a custom layout for an interactive report will entail using either an XLS style sheet or a bi publisher report. Dene's plugin will get the query but again, to use a custom layout will take more work.<br /><br />What exactly is the business requirement you are trying to resolve?<br /><br />Maybe post to the APEX support forum and others can help??<br /><br />Thank you,<br /><br />Tony Miller<br />Los Alamos, NMAPEX Developer In the USAhttps://www.blogger.com/profile/06746962787261032815noreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-25926865522942198322016-03-29T07:25:56.412+02:002016-03-29T07:25:56.412+02:00Someone deleted all of my applications in the work...Someone deleted all of my applications in the workspace I share including the one with the plugin. Send me your email contact to deneskubicek@yahoo.de and I will attach the plugin to it until I make it available again.<br /><br />Regards,<br /><br />Denes KubicekDenes Kubicekhttps://www.blogger.com/profile/01962373591521960882noreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-53709250787991366862016-03-29T01:56:06.276+02:002016-03-29T01:56:06.276+02:00Hi Tony,
Thanks for your quick reply! But I don...Hi Tony,<br />Thanks for your quick reply! But I don't have any idea of how to use the code exposed there to customize the PDF printing based on a custom layout and an interactive report output driven by the end user. I was following a solution related to the dynamic action plugin suggested by Denes. But was more confused when I met the download problem since there is only one place where we can get it. <br /><br />RegardsAnonymoushttps://www.blogger.com/profile/00489358956402142459noreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-46640982518268907452016-03-28T19:25:12.114+02:002016-03-28T19:25:12.114+02:00Here is a link to the blog article on this: http:/...Here is a link to the blog article on this: http://deneskubicek.blogspot.com/search?q=IR+report+query<br /><br />Thank you,<br /><br />Tony Miller<br />Los Alamos, NMAPEX Developer In the USAhttps://www.blogger.com/profile/06746962787261032815noreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-12453660360821930912016-03-28T19:02:18.937+02:002016-03-28T19:02:18.937+02:00Hi all,
Is there another place where I can downloa...Hi all,<br />Is there another place where I can download the IR Query plugin Authored by Denes Kubicek. I want to experience it in a printing project under Apex 5 where I need to get the Interactive report Query. The related Download Link on apex-plugin.com is broken. And all the messages sent to the site Administrator still remains unanswered. I am trying a customization solution suggested by Karen Cannell in the book "Expert Oracle Apllication Express". Any personal assistance by sending me a download link would be appreciated.<br /><br />RegardsAnonymoushttps://www.blogger.com/profile/00489358956402142459noreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-15032271977743681952015-11-18T23:32:19.808+01:002015-11-18T23:32:19.808+01:00Agreed Denes, Static ID is the way to go, so for V...Agreed Denes, Static ID is the way to go, so for V5 change the query to be:<br /><br />SELECT region_id<br />INTO v_region_id<br />FROM apex_application_page_regions<br />WHERE application_id = p_app_id<br />AND page_id = p_page_id<br />AND source_type = 'Interactive Report'<br />AND static_id = '';<br /><br />You obviously then need to ensure your report region has the static id set accordingly.<br /><br />Thanks for this, helped me a lot, I refactored so I could pass in a list of columns as occasionally I only want a single column back. We use this so that we can drive a workflow based on the IR results (complete with any filters) on a subsequent page and allow users to move through the dataset without having to go back to the IR page. Jason McCaulnoreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-40027670889768545022015-11-18T23:30:13.411+01:002015-11-18T23:30:13.411+01:00This comment has been removed by the author.Anonymoushttps://www.blogger.com/profile/15198595139008903310noreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-41804979974785463752015-11-18T05:11:17.332+01:002015-11-18T05:11:17.332+01:00Try with Report Static ID.Try with Report Static ID.Denes Kubicekhttps://www.blogger.com/profile/01962373591521960882noreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-83546390073168206822015-11-18T03:12:21.173+01:002015-11-18T03:12:21.173+01:00V5 will allow multiple interactive reports, in 4.2...V5 will allow multiple interactive reports, in 4.2 you can only have 1, hence in 4.2 the "source_type = 'Interactive Report'" will always yield one row, in 5 it could return multiple, hence would need to refine the query further to ensure you select the correct report.Jason McCaulnoreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-19338170999817306542015-10-17T00:33:20.665+02:002015-10-17T00:33:20.665+02:00What changes need to be made for APEX 5?What changes need to be made for APEX 5?APEX Developer In the USAhttps://www.blogger.com/profile/06746962787261032815noreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-26798117217944252012015-08-05T09:25:25.484+02:002015-08-05T09:25:25.484+02:00What if you run it after region refresh?
DenesWhat if you run it after region refresh?<br /><br />DenesDenes Kubicekhttps://www.blogger.com/profile/01962373591521960882noreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-63819366121117145812015-07-30T18:48:50.559+02:002015-07-30T18:48:50.559+02:00Hi Denes,
The function looks great and leads me on...Hi Denes,<br />The function looks great and leads me on to an extra point, can I detect/trap changes to the IR filters so that I can rerun the function and possibly trigger other events depending on the results.<br /><br />Thanks,<br />AlanAnonymoushttps://www.blogger.com/profile/01832531446961842328noreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-47766949134222666042015-06-04T16:45:07.708+02:002015-06-04T16:45:07.708+02:00Hi Denes,
Thanks for such a wonderful post.
I ha...Hi Denes,<br /><br />Thanks for such a wonderful post.<br /><br />I have followed the post and i am able to populate the query to a textarea.<br />Can you pleas let me know how can i use the result query in a gantt chart. When ever i am trying, i am getting error with an invalid app name saying the ir is not exists.<br /><br />Thanks,<br />SanjayaSanjayahttps://www.blogger.com/profile/14398887438367543013noreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-72865907442369749182015-06-01T09:47:28.084+02:002015-06-01T09:47:28.084+02:00Hi all!
Stew Stryker's APEX_IR_PACKAGE worked...Hi all!<br /><br />Stew Stryker's APEX_IR_PACKAGE worked just fine until upgrade.<br />It needs to be corrected for APEX 5.0 if possible.<br />Anyone knows how to contact the author or care to fix the package (if IR 4.0->5.0 mapping is available)?<br /><br />Best regards from Croatia,<br /><br /><br />Igor Filkonoreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-20201061243179026692015-04-01T17:26:59.107+02:002015-04-01T17:26:59.107+02:00Hi,
Great post, and so easy to implement.
I am s...Hi,<br /><br />Great post, and so easy to implement.<br /><br />I am struggling with a related, but different, issue.<br /><br />I have a client who has a lot of data, and is using some SQL with loads of joins which is resulting in a very slow query. Sadly we cannot change the query, however, we can force the user to enter filters, and return nothing if a filter is not applied.<br /><br />I have found various hits which give me a solution like<br /><br />select count(*)<br /> from <br /> apex_application_page_ir_cond c,<br /> apex_application_page_ir_rpt r<br />where r.application_id = c.application_id<br />and r.page_id = c.page_id<br />and r.report_id = c.report_id<br />and c.application_id = :APP_ID<br />and c.page_id = :APP_PAGE_ID<br />and r.session_id = :APP_SESSION<br />and r.base_report_id = :P2_BASE_REPORT_ID<br />and c.condition_type in ('Filter','Search')<br />and c.condition_enabled = 'Yes'<br /><br />which if it results > 0 then the query returns results.<br /><br />However this does not seem to take into account whether the saved report has filters defined.<br /><br />It needs to<br />1. Check if there are filters/searches for both the saved reports as well as any filters/search done on the fly.<br /><br />Any help on this would be greatly appreciated.<br /><br />Thanks,<br /><br />JeremyJeremy Webbnoreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-12463371279952067052014-07-17T03:40:10.620+02:002014-07-17T03:40:10.620+02:00Mildly amusing thought while reading through this ...Mildly amusing thought while reading through this - all IR examples with presumptions like this<br /><br />AND source_type = 'Interactive Report'<br /><br />will need to change from APEX 5 ;-)Scott Wesleyhttps://www.blogger.com/profile/18106937181788036683noreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-43989126861026443312014-03-06T03:24:32.516+01:002014-03-06T03:24:32.516+01:00Just wish I could get an answer from the Oracle fo...Just wish I could get an answer from the Oracle folks on how THEY are doing it for CSV downloads...<br /><br />Thanks again for your help!!APEX Developer In the USAhttps://www.blogger.com/profile/06746962787261032815noreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-48918588338998880642014-03-03T09:19:35.285+01:002014-03-03T09:19:35.285+01:00Hello Tony,
I gues, this what you will need to do...Hello Tony,<br /><br />I gues, this what you will need to do. I didn't go so far in my testing and haven't tried to group.<br /><br />Regards,<br /><br />DenesDenes Kubicekhttps://www.blogger.com/profile/01962373591521960882noreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-6475680359083646892014-03-03T01:06:23.519+01:002014-03-03T01:06:23.519+01:00Denes,
The aggregate is produced in the interact...Denes,<br /> The aggregate is produced in the interactive report through the tools menu.. It sums up the selected column and displays as a sub-totla or total depending upon if you're grouping rows or not..<br /><br />In my case, it produces those annoying "APXWS_CC_001"columns in the output when we run t he query produced by the apex function you produced.. <br /><br />I tried posting to the forum asking ANYONE with Oracle how they handle getting the current query when they generate the CSV download option for a report and received NO response..<br /><br />So I guess I will have to go the route of doing some fancy manipulation to get rid of those "Ëxtra" columns..<br /><br />Thank you,<br /><br />Tony Miller<br />LuvMuffin Software<br />Ruckersville, VAAPEX Developer In the USAhttps://www.blogger.com/profile/06746962787261032815noreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-90503520100469712262014-01-22T08:59:52.974+01:002014-01-22T08:59:52.974+01:00I looked at it using computed columns - not sure w...I looked at it using computed columns - not sure what you mean by aggregate columns. The SQL returned in my case an SQL with a column named something like "APXWS_CC_001". This column doesn't appear in the list of columns when running <br /><br />SELECT *<br />FROM apex_application_page_ir_col<br /><br />Maybe it is contained in an another IR Report but I couldn't find it. Eventually you could do a string manipulation with some custom PL/SQL but it could be tricky.<br /><br />Regards,<br /><br />DenesDenes Kubicekhttps://www.blogger.com/profile/01962373591521960882noreply@blogger.comtag:blogger.com,1999:blog-1561253375800464394.post-67388758199376904112014-01-21T15:42:07.401+01:002014-01-21T15:42:07.401+01:00Denes,
My question is, and it's not in your ...Denes,<br /> My question is, and it's not in your code, but the APEX function used here:<br /><br />v_report_id := apex_ir.get_last_viewed_report_id (p_page_id => p_page_id,<br /> p_region_id => v_region_id);<br /> <br />v_report := apex_ir.get_report (p_page_id => p_page_id,p_region_id => v_region_id,<br />p_report_id => v_report_id);<br /> <br />v_query := v_report.sql_query;<br /><br />If a report uses aggregate summations, the call to v_report.sql_query, is returning the aggregate columns as columns in the query.<br /><br />My users are seeing this and wanting those columns removed.. Can you think of a way to handle this?<br />APEX Developer In the USAhttps://www.blogger.com/profile/06746962787261032815noreply@blogger.com