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 same information can be found here:
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?
How often do you answer the same question?
Someone wants to know many widgets we sold to customer X?
Someone wants to know how much money someone is making?
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 🙂
44 Comments
How to pass parent value to child query in child report.
This will show how.
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
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
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.
We can generate HTML output, use the DBMS_OUTPUT style and print HTML tags. You can also see what David has done with this here – some very cool stuff 🙂
Why would the Reports Tab not be visible? Our system was set up by a third party, and I am rather new.
someone closed it
View > Reports will bring it back
Thanks,
It comes up on the left-hand side of the screen, which is fine with me.
john3
you can dock the panel anywhere you want
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?
Sorry, there is not
But maybe we could help you tune your report/SQL so it’s faster?
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.
Just for reference; the java part of SQL Developer uses a swing object.
As such it only speaks HTML 3.2 and sadly not all aspects of it.
See here for complete list of CSS styles the java toolkit supports:
http://docs.oracle.com/javase/7/docs/api/javax/swing/text/html/CSS.html
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).
you can in fact do this
you can fire off updates from a grid style report – I have a post on how to do this, and you can also build script style reports.
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.
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.
Wow this is cool feature ,I am using this tool from 2006 and never looked into reporting side.
Thanks for sharing.
Sr
Welcome to the ‘cool kids’ club 🙂
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.
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’)
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?
How many do you have that you need a search?
At the moment not many… but I’m thinking of centralizing the reports of my team
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.
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.
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 🙂
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?
I’ve been thinking about the reports to menu/navbar a bit…
Great Thanks. Just tried it out on some of my common scripts and it is useful.
Sweet. Thanks – thanks for reading, thanks for trying it out, and thanks most for sharing!
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.
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.
Thanks Steve! Now help me spread the word 🙂