Today’s post is courtesy of this question from a reader:
Can the SQLDeveloper Report/Chart feature create a line chart where x contains date/time and y the values to be plotted?
The answer is ‘Yes!’
I’m going to use HR.EMPLOYEES to give me my data.
I start with…
SELECT Hire_date, Salary FROM Hr.Employees ORDER BY Salary ASC FETCH FIRST 20 ROWS ONLY;
So let’s turn this into a report.
SHORTCUT: In your query result-set, right-click and select ‘Save Grid as Report…’
We’re going to want a CHART report. So set that, and then go to the chart properties.
Now I’m going to need to assign values for the Series, Group, and Value.
This sample rendering shows 5 different Series, or lines plotted.
My query only has 2 values to choose from, the salary and the date. If I assign either of those to the Series property, I’m going to have a bunch of single point lines, which aren’t really lines at all.
I need a 3rd value.
So back to our query.
SELECT 'Hire-to-Pay', Hire_date, Salary FROM Hr.Employees ORDER BY Hire_Date ASC fetch FIRST 20 ROWS ONLY
‘Hire-to-Pay’ will be static across all of the returned rows. So I can use THAT for my Series.
Back to the properties.
Now run your report.
So I want a ‘line’ for each department. The hires are by day, so not every department hired someone on every day. To get a bit closer, I consolidated it to YEAR. And since no one knows the department_IDs, I joined to departments to get the names.
I also set the chart scale to Logarithmic to make the values/lines a bit easier to evaluate. There are tons and tons of chart properties you can set. I also asked for the series lines to be smoothed out vs straight line point-to-point.