Spreadsheets, such as in Excel, are a wonderful tool. In the hands of a skilled user, budgets and financial analysis can be created quickly and easily. So easily in fact that it becomes hard to resist the temptation to simply copy everything to another sheet or workbook. However, in no time, you’re presiding over dozens if not hundreds of spreadsheets – most of which become stale outdated representations of your data.
If your organization is drowning in spreadsheets, you’re well aware the associated problems…things like
- Data Security – Face it, laptops have a way of walking
- Data Validation – Errors in a spreadsheet can go unnoticed for long periods of time
- Multiple Versions of the Truth – Which spreadsheet is correct?? Yours or mine?
- Backup and Recovery – C’mon how often are you backing up your machine?? Are you willing to bet your data on it??
- Lack of Mobility – Spreadsheets generally aren’t suited for smaller mobile devices.
There is a solution out there that addresses each of these issues. If you’re an Oracle shop, you already have the tool in-house. It’s Oracle Application Express (APEX). If you’re not an Oracle shop you can employ a license-free version of the database called Oracle Express Edition. It too, is available free of charge..
Sound too good to be true? Well, if you can spare 90 seconds, I’ll demonstrate.
This is the main screen of the application builder. Click ‘Create’ to begin.
[Elapsed time: 5 seconds]
Several choices are available. Click the ‘From a Spreadsheet’ hyperlink.
[Elapsed time: 10 seconds]
We can import an entire CSV file or ‘Copy and Paste’. For this exercise, we’re going to ‘Copy and Paste’. Click ‘Next’ to continue.
[Elapsed time: 20 seconds]
Here you see the window, we’ll use to paste in our spreadsheet data.
[Elapsed time: 25 seconds]
I created a simple spreadsheet that lists employees, their salaries and departments. Copy everything on the sheet and paste it into the window.
[Elapsed time: 45 seconds]
The data is in place. Click ‘Next’ to continue.
[Elapsed time: 50 seconds]
Based on the data, APEX analyzes the data types and formats. You can edit column names or choose to exclude columns from creation and import. APEX conveniently displays the actual data to help you confirm your choices.
[Elapsed time: 1 minute]
APEX will automatically create a database table to house the data from the spreadsheet. You can also modify the actual labels visible on the application.
[Elapsed time: 1 minute 5 seconds]
This screen provides the means to name your new application and to specify whether it is “read only”.
[Elapsed time: 1 minute 10 seconds]
Should you want to specify a specific APEX theme, you can do so here. I would recommend accepting the default.
[Elapsed time: 1 minute 15 seconds]
Nothing left to do now but to ‘Create Application’.
[Elapsed time: 1 minute 30 seconds]
Application successfully created with time to spare!! Click ‘Run Application’
Provide your login credentials.
WOW! Here’s your new application and you did it in less than 90 seconds without writing a single line of code.
To add new rows, simply click ‘Create’.
To modify or delete rows, click on the pencil icon which takes you to a detail page.
Congratulations! You now have a multi-user application that is secure, valid, accurate, recoverable, and available on your mobile devices as shown below.
The next installment will explore the functionality provided by interactive reports (IR)
…and we’ll do it again without writing a single line of code!![/vc_column_text][/vc_column][/vc_row]