In the last few posts (Part 1 on converting the spreadsheet to a database and Part 2 on creating the UI) we have built a functional web-based app out of our old excel spreadsheet. This application is now ready for business to use and make changes to, but we’d like to go a little further and create some graphs for the business users to look at. That’s actually pretty easy in Apex and what we will detail in this post.
We are going to add 2 graphs to the home page; one to show the total sales by qtr and one to show the breakdown of sales in the current qtr. To start, let’s edit the home page and click the ‘Add’ option in the right click ‘Regions’ menu. Select the ‘Chart’ option’
As you can see there are a number of different charts we can select from. And for each chart we can choose to render it in ‘Flash’ or ‘HTML5’. Flash used to be the preferred method and some of the graphs in the current version of Apex will render better in Flash, but going forward HTML5 is the preferred option (and will be the only option in the next release of Apex). So change the ‘Chart Rendering’ menu to ‘HTML5 Chart’ and then select ‘Column’
Once you’ve selected column Apex will show you all the different options for a 2D column, including stacking, ranges, 2D/3D etc. Let’s keep it simple and select ‘2D Column’ (feel free to come back later and choose a different rendering option, the query is generally the same)
The next screen controls options for the rendering of the box/region that the chart will go into. Change the title to ‘Sales by Quarter’ and click ‘Next’
The next screen controls options for the chart itself. Notice that you can also put a title in here. The difference between this title and the previous title is that the chart title will be shown within the chart (generally centered at the top), whilst the other title will be in the header for the box that contains this title. I generally rely on the region title, so you don’t need to include a title here. Before moving on, change the ‘Show Legend’ to ‘Right’ so that we can see what each bar on the chart refers to.
Now we have to write the SQL statement to collect the data for this chart. This may look daunting, but you can use the ‘Build Query’ button to help you construct the query and there are example queries under the ‘Chart Query Example for 2D Column’ area. Each chart type requires a slightly different syntax, for a 2D Column you have to return 3 columns:
- One that constructs a link that will be used when the user clicks a column. You can use this to allow a user to drill down to another page when selecting a column. Ie: this could take the user to a page with a detailed breakdown of that region. In this example we won’t be using the link, so just return NULL
- A column to control the label for the column. This will control the x-axis label. In our example we want to return the data broken down by quarter so we return a string like ‘Y2014Q1’
- And finally a column with the actual value for the column. This controls the y-axis value. In our example we will return the total of the Hardware, Software and Services sales. Note that whatever you all this column (it’s called NORTH in our example) will be used in the legend to name this column
Our resultant SQL looks like this:
SELECT NULL LINK 'Y' || YEAR || 'Q' || QUARTER LABEL, (HARDWARE + SOFTWARE + SERVICES) NORTH FROM SALES WHERE Region = 'North' ORDER BY YEAR, QUARTER
You will have noticed that in the previous query we only got results for the ‘North’ region. This is because we need to add the others in as extra series to the graph, otherwise all the results for each region would be included in the one result. To do that, once you’ve created the chart, right click the new ‘Sales by Qtr Region’ chart in the edit menu and click ‘Edit’
Click the ‘Chart Attributes’ tab and then click ‘Add Series>’ in the ‘Chart Series’ area.
Name the series ‘South Series’ and add the following SQL
SELECT NULL LINK 'Y' || YEAR || 'Q' || QUARTER LABEL, (HARDWARE + SOFTWARE + SERVICES) SOUTH FROM SALES WHERE Region = 'South' ORDER BY YEAR, QUARTER
Click ‘Apply Changes’
SELECT NULL LINK 'Y' || YEAR || 'Q' || QUARTER LABEL, (HARDWARE + SOFTWARE + SERVICES) EAST FROM SALES WHERE Region = 'East' ORDER BY YEAR, QUARTER
Before we are done, let’s fix the size of the graph (otherwise it will span across the entire page) by changing the ‘Column Span’ in the ‘Grid Layout’ section to 6 (there are 12 columns in the template, so this will make it span half way).
Click ‘Apply Changes’ and now run the page.Your home page should now look this this:
Let’s repeat the process and add a pie chart that has the breakdown by quarter for this quarter. To do that, go back and add a new chart region, but this time select ‘Pie & Doughnut’ as the chart type.
Then select 2D Pie
Let’s call this chart, ‘Latest Qtr Sales’ and move on
Change the ‘Legend’ to show on the ‘Right’ and move to the query screen. The structure for the query is basically the same as last time, but we will restrict it to just the latest quarter. At the moment this will need to be changed manually every quarter as we haven’t worked added logic to determine what the latest quarter is. You could add some logic to pick the largest year and quarter, or use today’s date to determine the quarter, but for the moment let’s leave it as is.
SELECT NULL LINK, REGION LABEL, (HARDWARE + SOFTWARE + SERVICES) TOTAL FROM SALES WHERE YEAR = 2014 AND QUARTER = 1
Apply the change and then change the ‘Column Span’ to ‘6’, but also change the ‘Start New Row’ to ‘No’. This will ensure that it spans across the the page on the same line as the other graph
Finally your page should look like this:
With that the business now has a modern application that they can not only enter data in, but also see the results in along with some useful graphs. From here you could continue to add relevant graphs, or maybe add more tables that users can edit. For more information on developing in Apex check out the Oracle Learning Library for Apex
If you have any questions about Apex or would like more information on how to set up an internal Apex cloud service in your organisation please contact your local Oracle Account Manager (or if you are in Canberra, Australia contact me!)