Reporting Enhancements for Oracle SQL Developer v4

thatjeffsmith SQL Developer 21 Comments

Tell Others About This Story:

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.

Tell Others About This Story:

Comments 21

  1. 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,

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author

      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…

  2. 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.

  3. 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.

    1. thatjeffsmith Post
      Author

      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.

  4. 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

  5. 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.

    1. thatjeffsmith Post
      Author
      1. 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

    1. thatjeffsmith Post
      Author
  6. 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.

    1. thatjeffsmith Post
      Author
      1. 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 😉

      1. thatjeffsmith Post
        Author

Leave a Reply

Your email address will not be published. Required fields are marked *