Packaged Application: Data Reporter

I was talking with one of my co-workers yesterday about the Data Reporter packaged application, and it occurred to me that I never actually blogged about it. Which is a real shame, as it is one of our more powerful applications!

One of the really nice features of APEX is the interactive report, which has been around since (I believe) the 3.1 release. It’s especially nice for developers, because we can build a report once, and then allow the users to modify it to meet their needs, within reason. If they want columns re-ordered, or filtered, highlighted, shown/hidden, etc., they can make those changes themselves, without waiting for a developer to make the changes, get it QA’d, and then rolled out to production. They’re very powerful. One of the downsides, however, is that it still requires a developer to build the initial report, or update the query if someone comes up with additional columns that should be included.

Data Reporter, which was originally released in APEX 4.2.3, is our solution to that. Once the application is installed, any user with contributor or administrative access can log in and create a new report, querying whatever data they need. If someone wants to create a report, but doesn’t know SQL–or isn’t very well versed in it–an administrator can create a “data source” (really, just a query) which the users can then use as the basis for their report. As you can imagine, given that we’re giving the users the ability to write SQL against the database, security was a chief concern as we built the application; one chief protection we implemented is the mandatory white-listing of objects. If your query refers to a table or view that’s not on the whitelist, you won’t be able to create it.

You can create a variety of report “types” within the application–Interactive Reports (mentioned above); Calendars; Filter Reports (I blogged about them here); and Dashboards (intended to be quick-glance summarizations of your data). Additionally, if you’re running APEX through Oracle REST Data Services–formerly known as the APEX Listener–you can harness its ability to create PDF reports on the fly.

My co-worker was looking at setting Data Reporter on an instance where we’re trying to eliminate the ability for users to log in and query data directly from the database, without also blocking them from getting the data they need. Through this packaged application, we’ll be able to lock down the database accounts, give the users the data they need in usable formats, and keep a record of how often they look at which reports. Seems like a win all around to me.