This feature is going to be hard to cover in a single post, but I’m going to try anyway. The Reports interface received performance improvements, a few tweaks to make them easier to build, a slew of new charting options, and more visual control to tweak. Oh, it’s also available via the command-line interface.

Let’s cover the performance bit first.

The first time I try to open a report in ‘Edit’ mode in earlier versions, it can take almost 15 seconds for the thing to load. Ouch. Even though it’s faster on subsequent loads, that really isn’t acceptable. In version 4.0, the report editor should load in a second or two consistently.

Let’s Talk About Charts

In version 4.0, you don’t need to query a duplicate column just to get the chart to work anymore.

What do I mean? I mean to get a simple bar chart, I don’t need this anymore:

SELECT country,country, COUNT(*) FROM beer
GROUP BY country, country
HAVING COUNT(*) > 100
ORDER BY 3 DESC

Instead, I can JUST use this:

SELECT country, COUNT(*) FROM beer
GROUP BY country
HAVING COUNT(*) > 100
ORDER BY 2 DESC

You see, we need to know what to use the for the group, series, and value fields for charts. In version 4, we can assign the values from the available query columns manually. Less data to query, less I/O, and no need for you to think with your ‘SQL Developer Reports’ Hat on anymore – just concentrate on getting the data right.

I can now specify the series, group, and value properties manually.
I can now specify the series, group, and value properties manually.

Oh, and don’t worry about this ‘breaking’ your old reports – they’ll still work in version 4.

Ok, now that I have the ‘pretty picture’ to reference, let’s talk about the ability to preview the charts.

Live Preview Your Chart

Let’s assign a connection for our report while we’re in design mode. This only makes it easier to test your report, it doesn’t physically tie the report for a particular database going forward.

Check it 'on', then select a connection
Check it ‘on’, then select a connection

So in the upper right hand corner of the report editor, toggle that on and select your connection.

Then, look back over to the far left.

Live preview your charts!
Live preview your charts!

This ability to see what the chart will look like at run-time is reason enough to upgrade to SQL Developer version 4.0 all by itself – assuming you use our reports of course. No more setting it up, saving, closing, and running the report to see what will happen. Now you can leave your report design feeling pretty confident how it’s going to look when you run it.

This will also make it MUCH easier for you to experiment with our new types of charts.

There’s a LOT of new charts in version 4.

Like, more than 50. I can’t talk about all of them. But what I CAN do is show them all to you. Kinda. Check it out:

All the charts!
All the charts! Click on the image to load the animated GIF.

One last thing on the new charts – we’ll have some additional documentation for each chart type soon.

There’s quite a bit more you can do visually in the chart design. I can add titles, control the font, coloring, etc of that and the labels, etc. It’s pretty straightforward to start using, so I’ll end here and let you ask me the stuff you CAN’T figure out.

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.

23 Comments

  1. I’m still missing examples how to set up a “Hi-Lo-Close” Report or a “Combination”, what do I have to set for Dual/Dual Y reports, …?
    A lot of different types are available but features without documentation are mostly useless.
    Perhaps that’s why reports are among “Three (3) Things You’re Not Using But Totally Should Be” 🙂

  2. Hi Jeff,

    Please let me know if we could generate multiple reports in a single pdf document. Is there any way to exports all parent and child reports in a single pdf doc?

    Thanks,

  3. Jeff,
    Any way to automatically email the SQL Dev report (results only – not the query) to an end-user using SQL dev?

    • I’d probably use a database job to to that. Or use a reporting solution. You could setup a command-line interface job run and built a .bat or .sh script to take the output and email to to your end-user. Or build an APEX app. Or, or, or…

    • Yeah – knew that. Long story behind why I asked, though. I’ll save it for Kscope16 🙂

  4. Are those report charts work in the latest developer 4.x? when can we have the documentation for those chart specs, for example, an example sql query or data specs for a given chart like bubble, etc. would be very useful.

  5. Can we replace our old oracle discoverer 9i with sql developer reporting, we are using discoverer to generate SQL reports, now oracle is not supporting it & we are looking for better BI/Datawarehouse reporting option.

    • No…but you also need to remember that reports in SQL Developer are more of an ad hoc system for the average database user. We’re not a production reporting solution.

      But, if you really wanted to, you could look at the XML format of one of our reports and see if you can get a discoverer 9i report out in that format.

  6. Ricardo Cesar Reply

    Hi,

    Do you have an example of chart “Stock – Open-Hi-Lo-Close Candle” or similar?

    Regards,
    Ricardo Cesar

  7. Hi jeff,

    I want to generate a html report of all the procedures and function Schema “Test Run on codecoverage ” report. that is commad line and UI Mode.

  8. Stephen Keilholz Reply

    EDIT – please remove this line and my previous comment
    No. The report is documenting the count of transactions (vertical axis) that take certain number of seconds (horizontal axis) by category.

    Here’s my SQL:
    WITH xa_durations AS
    (SELECT xa_id, xa_type, MAX(xa_ts) – MIN(xa_ts) AS DUR,
    TRUNC(EXTRACT (SECOND FROM (MAX(xa_ts)-MIN(xa_ts)))) DELTA_GROUP
    FROM xa_log
    WHERE xa_date BETWEEN to_date(:earliest_date,’mm/dd/yyyy’) AND to_date(:latest_date,’mm/dd/yyyy’)
    AND xa_type IS NOT NULL
    GROUP BY xa_id, inf_nm
    ORDER BY xa_id DESC
    )
    SELECT delta_group, xa_type, COUNT(*)
    FROM xa_durations
    GROUP BY delta_group, xa_type
    ORDER BY delta_group ASC, xa_type ASC

  9. Stephen Keilholz Reply

    Jeff, is there a way to include bind variables in a chart title? For instance, if the data in the query is being selected by a date range, it would be important to show that on the chart, lest it be misinterpreted as all-inclusive.

    • I don’t think so…but I’m betting there’s another way to encompass that info in the report somehow. Are you not showing the date range in the chart on the axis?

    • Stephen Keilholz

      No. The report is documenting the count of transactions (vertical axis) that take certain number of seconds (horizontal axis) by category.

      Here’s my SQL:
      WITH xa_durations AS
      (SELECT xa_id, xa_type, MAX(xa_ts) – MIN(xa_ts) AS DUR,
      TRUNC(EXTRACT (SECOND FROM (MAX(xa_ts)-MIN(xa_ts)))) DELTA_GROUP
      FROM xa_log
      WHERE xa_date BETWEEN to_date(:earliest_date,’mm/dd/yyyy’) AND to_date(:latest_date,’mm/dd/yyyy’)
      AND xa_type IS NOT NULL
      GROUP BY xa_id, inf_nm
      ORDER BY xa_id DESC
      )
      SELECT delta_group, , COUNT(*)
      FROM xa_durations
      GROUP BY delta_group, xa_type
      ORDER BY delta_group ASC, xa_type ASC

  10. Is the HTML chart output size able to be modified? Semms like my chart Axis ID’s are being cut off.

    • Does it render OK in SQL Developer, but not when you send it to HTML? If so, that sounds like a bug.

  11. Another great post Jeff. It uncovered an issue though for me in SQL Dev 4. I’m running a desktop resolution of 1366×768 on my laptop and when I open a new report window it is vertically too large for my screen. That means the ‘Apply’ button is off the bottom of the screen and no amount of dragging or attempting to resize the window will let me see the buttons.

    • Ha Ha. Don’t worry too much Jeff, it’s only on my laptop I noticed the issue! My desktop is much nicer. That said, if Oracle want to start providing a 29″ widescreen monitor with every SQL Developer 4.0 download I won’t say no 😉

    • Jari Hämäläinen

      I have the same issue. Hopefully this is going to be fixed in the next version.

    • Jari & James – it’s fixed. In the next build the window is resizable so you can squeeze it down to fit.

Write A Comment