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 🙂

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.

44 Comments

  1. I am wondering why I didn’t use this cool feature(SQL developer reports) these many days… I have started playing with it i have one doubt how to add where clause from patent value ….Ex if I click on deptno in master report it has to show Dept employee details in child report…Pl suggest how to do it… Thanks

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

    • I’d need an example to see what you’re talking about but this does sound like a bug I logged in that general time frame..

    • 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

  3. Trevor North Reply

    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.

  4. John Garmon Reply

    Why would the Reports Tab not be visible? Our system was set up by a third party, and I am rather new.

  5. Luis Sierra Reply

    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?

    • Luis Sierra

      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.

  6. james roberts Reply

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

  7. Hi!
    How to view command line in report ? it is run very fast because i can not view it.

    • You mean, how do you see the report generated by the command line interface? It spits out the report file you asked it to, it’s not immediately viewable in the CLI itself.

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

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

    Thanks for sharing.

    Sr

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

  11. I started trying out SQL Developer Reports yesterday and I’m liking them a lot.
    Can reports prompt for parameters?

    • Nevermind, I figured it out to_date(:latest_date,’mm/dd/yy’)

  12. 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?

  13. OracleNewbie Reply

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

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

    • 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

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

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

    • PROBABLY b/c we only support HTML 3 in SQLDev and there’s something coming back that exceeds that standard.

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

    • 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 🙂

  16. 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?

  17. Great Thanks. Just tried it out on some of my common scripts and it is useful.

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

  19. Steve Maxwell Reply

    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.

Write A Comment