Oracle SQL Developer’s Best Kept Secret: Reports

thatjeffsmith SQL Developer 41 Comments

Tell Others About This Story:
You KNOW about them...

You KNOW about them…

The feature that everyone knows about yet does not use? That would be the reports.

I say that everyone knows about them because we throw them in your face..at least in terms of the user interface. It’s feature prominently next to where you see your database connections and objects.

Yet, when I ask a room full of people, I routinely get about 10-15% positive responses.

So, where is the disconnect?

Do You Routinely Ask the Same Questions, Run the Same Queries?

Then you should be building reports.

If you have every wondered what your session is doing, or someone else’s, and you opened up the Tools > Monitor Sessions page, then you have used a Report.

This is ACTUALLY a report

This is ACTUALLY a report

This same information can be found here:

Yup, same data, same look and feel

Yup, same data, same look and feel

And if I wanted to stay in the realm of the Monitor Sessions feature, I could tease you a previous post on how to make it show you the information YOU want to see by customizing the report.

Like DBMS_XPLAN?

DBMS_XPLAN results for current query executing

DBMS_XPLAN results for current query executing

How often do you answer the same question?

Someone wants to know many widgets we sold to customer X?

Click on pie chart slice, get details for that customer...

Click on pie chart slice, get details for that customer…

Someone wants to know how much money someone is making?

Who is making too much money? I mean, who is a future-to-be member of the upper class?

Who is making too much money? I mean, who is a future-to-be member of the upper class?

Someone wants to know how many log switches were happening, and what hour of what day the database was REALLY thrashing?

— this nifty-awesome report via David Mann.

Just about any question can be resolved via a query. Why not formalize it with a report? Reports can be reused and shared. You’re less likely to make a mistake then re-typing or rebuilding your code from scratch. And if you really get tired of answering the same questions, you can simply send the report to the end user for them to run for themselves – assuming they have database access.

And Reports Are Soooooo Much Easier To Design Now

Iterative design sums it up in version 4. You can live-preview your report as you build it. This means you can make sure it looks exactly like you want. Previously you had to edit, save, run to see what it would look like.

Plus, we have 50 new chart styles to choose from.

Plus, you can launch reports from the command-line-interface.

I don’t like to actively sell a feature too much. But, this feature CAN REALLY save you guys a lot of time. And if I have to use my bully-pulpit, then so be it 🙂

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 41

  1. Hi Jeff
    We are using reports with charts (lines), after version 4.0.3 SQLDev begun to plot NULLs as zeros (doing the line to get down in the chart). We also use the trend line option then the result get totally wrong.

    Was this change proposital?

    Thanks for your attention

    1. thatjeffsmith Post
      Author
      1. Jeff
        I did a test to show you the point:

        select sysdate, ‘VALUE’, 10 AS VALUE FROM DUAL UNION ALL
        select sysdate+1, ‘VALUE’, null AS VALUE FROM DUAL UNION ALL
        select sysdate+2, ‘VALUE’, 10 AS VALUE FROM DUAL UNION ALL
        select sysdate+3, ‘VALUE’, 10 AS VALUE FROM DUAL UNION ALL
        select sysdate+4, ‘VALUE’, 10 AS VALUE FROM DUAL UNION ALL
        select sysdate+5, ‘VALUE’, null AS VALUE FROM DUAL UNION ALL
        select sysdate+6, ‘VALUE’, 10 AS VALUE FROM DUAL;

        “Save Grid as Report…” option and chose Chart/Line and you will see
        second and sixth points to drop to zero.

        I think that points should be ignored.
        I am in Version 4.1.2.20.

        Thanks again

  2. Would be even cooler if you could specify several reports and have it generate a html output (but like AWR report).

    I’m doing some performance work and running a series of SQLs in SQL Developer and then copy/pasting into a document to record the before and after config of some tables as well as some performace stats.

    Would be fantastic if it was possible to define a set of SQL Dev reports that contain the info needed, enter a few bind vars for the specific data and get a html report that could then be printed to PDF to capture all the key info on each run – would save a lot of time and allow using some of the other neat features in SQL Developer Reports like using graphs.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  3. Hi,

    When i Configure a report to refresh every x seconds, a window message indicating that is executing the report with two buttons: Execute in background or cancel task.

    How can I configure that the report always execute in background a not show that window message?

    1. thatjeffsmith Post
      Author
      1. There are some reports that I use to monitor, with agregate functions. So when I’am working on sqldeveloper is really frustrating every 2 minutes clic to execute in background.

  4. I love reports….so much better than a text file/wiki page to cut-paste from.
    Wouldn’t it be great if there were ‘update reports’ , or ‘action reports’ where I could fire off updates or pieces of PL/SQL. (e.g. the things to reset the database following a test).

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  5. Reports really make life easier. The only issue that I’m unable to figure out (so far) is why I’m getting loads of blank lines at the heading and footer of my reports. When output to HTML, these show up in the browser as “force_print”.

    Otherwise, I’m really liking what I’ve been seeing in the latest iterations of the tool.

  6. Wow this is cool feature ,I am using this tool from 2006 and never looked into reporting side.

    Thanks for sharing.

    Sr

    1. thatjeffsmith Post
      Author
  7. I’ve now finished my report which has several child reports (some of them tables, some of them charts). Can I export the report with all the child reports to a single PDF?

    I’ve just seen a single table report exported to PDF, and I can’t seem to export any of the chart reports to PDF.

  8. I’m experimenting SQL Developer Reports for a few days and found it very useful.
    But I’m missing a way to search for my User Defined Reports by name or description. This functionality already exists?

    1. thatjeffsmith Post
      Author
  9. Report is a great feature,
    Can I create a report and send it to mails on scheduled time…?!
    It will be helpful to me…! 🙂

    1. thatjeffsmith Post
      Author

      We have a command line interface available for reports now. You can schedule the HTML output of a report. The mailing bit is possible, or have it send an email saying the web URL has been updated.

      1. Can you elaborate on how to automatized a daily production of personnal reports (PDF or EXCEL) and sending by email with sql developer.

        Thanks
        Hugo

        1. thatjeffsmith Post
          Author

          Excel, no go.

          PDF, no go.

          But HTML, you’d run the report via the command line interface, then pass that file name onto your shell script and have it do the email bit. Although I’d probably FTP it up to a webserver and email someone the link or notification that the report was avail/refreshed.

          We generate the report for you, everything after that is up to you.

  10. Riddle me this. I have a child report in which I am doing this :

    SELECT DBMS_SQLTUNE.report_sql_detail(
    sql_id => :SQL_ID,
    type => ‘ACTIVE’,
    report_level => ‘ALL’)
    from dual

    I would like for the result set to be displayed as if it was in a browser. I chose CODE and it renders the HTML syntax but not in “browser” form. Is there a way to do that? I tried a few things unsuccessfully. Obviously. 🙂 Normally, I take the resultset, save it to an html file then open it up. Was looking to save a couple of steps.

    1. thatjeffsmith Post
      Author
  11. Usually in our company reporting is done by Oracle Reports. Developed, tested and rolled out with the next release, though only when you can convince the manager that the information is really important. But sometimes the users need some interactive reporting a long time before the next release. In SQL Developer this is easily done with a master-detail or even master-detail-detail report where the user only has to provide some parameter values. Friendly users will even get some drill-downs 🙂
    The reports are accessible from a network drive and used as user defined extensions. So we developers have control over the SQL and the users alway have the latest version. Cheaper than some BI solution and the users don’t have to know about the data model.

    1. thatjeffsmith Post
      Author

      I should say our reports shouldn’t be confused with an enterprise reporting solution – but for ad hoc reporting and folks that already have SQL Developer, I think they’re great. But of course I’m biased. Which is why I love it when folks like you chime in 🙂

  12. Reports are great, but there’s currently a few things limiting their capabilities.

    If only I knew how can I make custom actions for reports (like those neat trace/kill session options in context menu for “monitor sessions”, or “go to” in PLSQL code search), I would make about 5 custom reports right away, replacing half of my routine PL/SQL scripts. Also, absence of these functions makes customizing “monitor sessions” pretty much useless, as you can SEE the session, but you still need to open up SQL worksheet and type your way to kill or trace it.

    Next, there should be more options for customizing Gauge – like colors or style. It looks cool when stuffed into longops child report in session for showing % complete, but it could look even better if I could customize it further.

    Last, could we get a way to put our reports to menu or navbar? It’s almost empty anyway, why not allow us to put very-frequently-used reports there, rather than going to reports tab and then a few clicks down the tree?

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  13. The ability to store runnable SQL is also great. I used to keep folder on disk of the various one-off tasks I have to perform every millennium. Now they sit in the Reports tab ready to fire when the customer calls and says, “Remember how I messed up X last year? Well I did it again.” Instead of trying to figure out the intricacies of how I fixed it, I just dive into the Reports tab and rerun.

  14. Hi Jeff, I agree that reports is one of the great features of SQL Developer. I use them extensively for all manner of things such as quality checking APEX applications, database monitoring, performance tuning, and looking up information.

    1. thatjeffsmith Post
      Author

Leave a Reply

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