Apex Interactive Report Tip #1: Aggregating numbers in string columns

I have been working on an Interactive Report based on a collection recently, to work around the fact that the SQL query for Interactive Reports must be static, and my requirement was to run different queries based on user input.

One problem is that since all columns in a collection are varchars (strings), the built-in aggregation feature of Interactive Reports does not work (it only allows you to aggregate on numbers, which is quite sensible, I guess). Normally you could always convert the string column to a number directly in the underlying query, but since my queries were dynamic (the whole point of using collections), that was not an option.

However, after some fiddling around I discovered that you can aggregate based on computed columns.

So, let's say we have this "transaction amount" column in our report, which consists of numbers but which Apex interprets as a string since it is retrieved from a varchar2 column in the apex_collections view:



Use the "Compute" menu item of the Interactive Report to create a new column, using the TRUNC function (for some reason, the TO_NUMBER function is not available from the list of functions, but TRUNC works -- just remember to include the number of decimals as the second parameter if you need decimals) and setting the appropriate numeric format mask:



We can now see that the new column has been added as a numeric column, since it is right-aligned instead of left-aligned (and also with a nice format mask):



The new column is now available under the "Aggregate" menu item of the Interactive Report, so we can create a Sum of the numbers:




After adding a "Control Break" to the report, we can see that the aggregation works:



Did I mention that I really like Interactive Reports? :-)