People like pictures and charts. People also like data, but the data often needs to tell a story. Looking a a spreadsheet of raw data vs looking at the accompanying charts for example.

We’ve known this of course for a very long time! If we gaze back at SQL Developer’s history, the reporting feature was included in version 1.1.

Write SQL, see data…in a table or chart (basically).

With our release of Oracle REST Data Services version 21.2 last week, the Database Actions feature got a major upgrade – the ability to make charts!

This is a long post, but don’t worry. I’ve included lots of pictures, so feel free to scan. There’s a pretty good chance I’ll publish a TL/DR; video to YouTube, soon’ish.

Building a Chart in Database Actions

There are two entry points. You can:

  1. write a query in the SQL worksheet and turn it into a chart or
  2. open the Charts card from the Launchpad (home page)

Let’s start with #2, just to be different.

You’ll notice we also added a nice advertisement in the Getting Started panel as well, this is a big feature!

EVERY user should have access to this card/feature.

Our layout for the Charts homepage will mirror what you’ve seen in the REST, Database Users, and similar screens – a series of ‘cards’ representing the objects. And up top, a ‘Create’ button.

If this is your first chart, you’ll be offered a Guided Tour of the interface –

Feel free to skip reading the directions, it’s pretty intuitive. In fact, feel free to skip reading this post!

I’ve already create a couple of reports, so my dashboard is populated with two cards.

Chart properties are shown on the card, actions are available from the top right ellipsis button.

In the desktop implementation of this feature, your reports are saved on the local machine, stored as XML files along with the rest of your SQL Developer application settings and files.

In Database Actions, we’ll be defining Charts in the database itself – alongside your schema’s REST APIs. This is a very important distinction. It determines just how SHARING and SECURING your charts will be accomplished.

But first, let’s build a chart from scratch.

1. Creating a Chart

Clicking the ‘Create Chart’ button will open a slider panel on the right hand side of your screen. You’ll see something like this –

You need to provide BOTH a chart name and description.

As we begin to type into the ‘code editor’ section of the report definition, a few things will start happening…

As I type…the SQL is validated by the parser (we will run it to get the column list).

For the query to be ‘valid’ for a chart, we need two columns – a numerical field. That’s going to be the ‘Value’ column. And then a second value to be used as the chart label. That can be a number or text field.

In my case I wanted to sum up the music catalog by ‘Artist,’ so I went back to my query and added a count(*) and GROUP BY clause.

The Chart dialog notices my query has changed, so it updates the label and value columns list.

After I’ve set those attributes, I can click the ‘Preview’ button – that will run the query, get the data, and print a mini-version of the Chart below.

That ‘Auto-preview’ check box? It does exactly what it sounds like, as you’re typing your query, we’ll be validating it AND executing it to print the chart below. This isn’t enabled by default, as your queries could be slow/expensive. We’ll let you tell us when you’re ready to run them.

After clicking the ‘Create’ button, we’ll be back at our Charts homepage, and I’ll have a new Card with my ‘music’ report.

You can filter or search your reports, and you can alternatively see your reports as a grid vs cards.

Ok, we’ve previewed the chart, but now I actually want to open/run it.

The default behavior will be to open as a new tab in your browser, I’ve undocked it for this screengrab.

This chart is OK..but not very useful. Let’s edit it!

I’ve got too many data points. Let’s trim it down some, with SQL. And also, I want to order the list.

I’m keeping the Auto-preview unchecked…I’ll have to hit the preview button when I’m ready.

You’ll notice there’s a SORTING control on the chart definition.

So as the data comes down to the browser, the chart itself can apply the sort. OR…I can have the database do this for me, via the SQL. If I have a lot of data points, thousands vs dozens, you’ll probably want to use ORDER BYs. Why? My DB is faster at processing large amounts of data with SQL than my browser is with javascript.

Charting Types

Let’s go through all of the types of charts.

I’m toggling on Auto-preview, and just switching the controls around.

I kind of like Polar, just because it’s different. You be the judge for when it’s the appropriate medium for telling your story.

Before running this report, I had no idea I had so many more Prince songs than say Led Zeppelin!

How it works

There are two major components in play, a RESTful Web Service that delivers the the data and the definition of the report, and our Oracle JET charts.

Running the report, you can see the GET request made to the API, and the data coming back, and array of JSON documents.

It’s calling /ords/hr/sdw/charts/music/data/

Tip: The JSON payload from the data/ API is going to be used to render the report in your browser. IF your API returns say..more than 3,000 ‘rows,’ your browser isn’t going to be happy. WHERE, GROUP BY, and HAVING clauses are going to be your browser’s friend.

Since the data and report are being delivered by an ORDS REST API, there should be a REST Module somewhere, right?

Yes! And you can navigate to it from the Chart card.

You can guess where we go from here, but I won’t make you – the REST workshop.

So in my REST workshop I’ll have one module per report. Each report will have two templates, each template having a GET handler.

The SQL is under data/ the report definition is under settings.

And we can look at these, if we want, just like any REST definition. We can also Edit them from here, but I wouldn’t. Better to edit the Chart from the Charts UI. It would be easy to make a mistake here, and render the Charts interface useless.

So instead of XML defining the report like we have in the desktop, we have SQL generating JSON.

Sharing Reports

It’s as simple as sharing the URL.

But, of course, nothing is quite that simple.

You’ll probably want to secure this report? (YES!) Since it’s backed by REST Module, access to the URI is gated by the authenticated user’s list of privs accessed by their given roles, e.g. Authorization.

So if we go back to the Module, and pull up the properties, we can see it’s current Unprotected. And we can assign a security setting.

You may want to create a new privilege just for your one or many charts.

I went with something that’s included with my Database Actions authenticated session (so database auth to HR) –

It’s fast, but you’ll see the first response is a 401 before being redirected to the login page.

Is this it?

Heck no! This is stage 1 of our evil plan to take over the world, pinky.

Our larger idea is to allow you to build dashboards. These dashboard would be a collection of charts. Sound familiar? Yeah, you could build your own Instance Viewer.

We’ll have the second stage of this feature ready later this year.

But you said there were two ways to build a chart!

Oh yeah. If you’re writing SQL in the worksheet, you can do this –

Type SQL, use the new ‘Chart’ button. That’s it.

thatjeffsmith
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Write A Comment

RSS
Follow by Email
LinkedIn
Share