Wednesday, March 25, 2009

A while ago Dimitri Gielis helped us to display column groups in Interactive Reports (IR) in APEX. Please see his blog posting to get a background of how to enable column groups in Interactive Reports.

After some testing we've made several changes to the code. The updated code fixes a bug that would display multiple group headers. It does not load the group headers via AJAX so it speeds up response time. I've included the updated code below. The example of this is here.

// Only print the col group header for the previous entry. This allows us to set the col span for similar groups // Have to do it this way to support IE (otherwise we could look at the previous entry and update it's col span

dispIRColGrpHeader(); // This time out is required since after the report is refreshed via AJAX, need to reattach the l_LastFunction command setTimeout( function(){ gReport.l_LastFunction = function(){dispIRColGroups();}; }, 1000 ); }

Tuesday, March 24, 2009

We've been working with Interactive Reports (IR) over the past few months and have had to do a lot of customization to meet some of our customers requirements. Over the next few weeks my posts will be focused on these customizations.

To start, here's an example of how to modify the download button in IRs. Just some background on this, we're only supporting CSV downloads now. When the clients would download IRs they would have to click the "Download" link then click on the "CSV" button.

We found this extra step unnecessary so here's a way on how to do it in one step so that it will download as soon as they click "Download". We used jQuery to help out a bit. Here's a working example.

Here's a detailed summary of how it works: If you scroll over the "CSV" download button you'll noticed that it looks something like : "f?p=102:3:207941080701500:CSV:". If we break that down it's really: "f?p=:::CSV:". In Javascript you can reference each as follows:

Of course you can use your own codes but I'd suggest sticking with these codes to utilize the APEX views. You can supplement the codes with the custom authentication status text (apex_util.set_custom_auth_status)

When creating the report, change the number of rows from 15 to 5 for this example. This is important for this demonstration!

Now create a page item called P800_LETTER and a submit button who submits and redirect to P800

If things went well you'd have a report that looks like this:

To illustrate the problem click on the "Next" pagination link so that we are seeing rows 6~10.Now, in the text box enter the letter "I" (this will restrict to 4 rows) and click submit. You should see the following error message:

To summarize what just happened, you initially requested a report with 14 rows and told APEX to display 5 at a time. When you clicked the "Next" pagination button you told APEX for the report you're currently looking at display rows 6~10. So now, if you were to refresh the page APEX would continue to show rows 6~10. When you filtered the report, by restricting to employees with the letter "I" in their name, you get a result with only 4 rows however APEX is still trying to display rows 6~10. I hope you now see the problem with this.

So now that we've demonstrated the problem, how do you fix it? Well there's several ways. To solve our current issue, on P800 in the submit branch we could check the "reset pagination for this page". This means that each time you submit the page APEX will set the display rows back to 1~5.

There are also other ways to reset the pagination automatically. You can reset pagination in the URL: In the "Clear Cache" section you can enter "RP" to clear the page's cache. Please see the APEX documentation for more information. There is also a page process to reset the pagination. This allows you to also set the scope of the current page, multiple pages, or all pages.

I know the issue has come up to reset the pagination for an individual report rather than the entire page. I currently don't know of any method, however if you do please post!

Monday, March 16, 2009

Last year Scott Spendolini gave our company an excellent 3 day training session on APEX. He also showed us a very nifty function (new in 10g I think) to help not having to escape single quotes when defining strings. This is not APEX specific, however it will help you if you ever have to write a block of PL/SQL to return an SQL statement for reports.