Everyone likes pictures. I would never create a report that didn’t have at least one chart in it. You just can’t risk busy people glancing at your reports and not coming away with the key information you’re trying to impart.
We have lots of chart types in SQL Developer – 50+ in version 4.0.
But we also have gauges, and I’ve talked about them before.
But what about dials?
So we can graph numbers, but how do we know if a number is ‘high’ or not?
To define our gauge, set the gauge style to ‘Dial.’
My query:
SELECT SUM(salary) VALUE, 10000 MIN, 400000 MAX, 45000 LowThrs, 175000 HiThrs FROM hr.employees WHERE department_id = :DEPARTMENT_ID
I still think Reports are one of our most under-utilized features in the tool. Would you call it a ‘must have’ one?
14 Comments
Wanted to do one with multiple dials. Is there no place to put a description of each dial?
I’ve adopted some complex Oracle based applications that were built without UIs or much in the way of procedural controls. You have to know which tables relate to which processes and how things tie together. SQL Developer essentially becomes the UI. Getting people up to speed to be able to support things like that can be time intensive.
I began codifying a lot of stuff within SQL Developer Reports. Lots of parent/child/drillable reports, color coding cells with values of interest, charts to visualize results without having to jump to excel, even drilling to PL/SQL based reports which would update tables, giving users the ability to do stuff like turn processes on/off right with a right click in a report.
One could easily argue that may be a little more than SQL Developer was built for, and that a real application should have been implemented. Sometimes time, money, bureaucracy, does’t allow it. To that end, SQL Developer reports have been extremely useful. I can hand off reports to to people with no previous expose to the application and expect some level of understanding on their part.
That’s not to say SQL Developer is without it’s shortcomings. I usually have to resort to manually updating XMLs if I want reports ordered a particular way. Can’t reverse the color of gauges so they can confuse people in some cases (and are sometimes avoided). Now that I’m spending more time with HTML5/CSS/JS, what I wouldn’t give for a renderer that was optionally powered by an external browser so that it would stay up to date. If I really need reverse color gauges or something more complicated not supplied in SQL Developer, I would be able to build them with SVG; …I can always dream!
check out what David built, I think you’ll like it
That’s a thing of beauty. I don’t know how I haven’t come across that before. Thanks for the link!
David does a lot of cool stuff with SQLDev reports.
OK, thanks. I’ll modify my reports accordingly.
Massimo
I found the issue. It did not prompt me because I had the quotes around bind variable.
This was my query:
select x.name “DCI Name”, d.name “View Name”
from RXC.DATA_EXTRACT_VIEWS x, rxc.dcms d
where x.dcm_id = d.dcm_id
and x.clinical_study_id = d.clinical_study_id
and x.clinical_study_id = (select s.clinical_study_id
from rxa_des.clinical_studies s
where s.study like upper(‘:CLIN_STUDY’))
FYI, quoting the variable worked in SQL Developer – and still works in SQL*Plus – when it was set as &CLIN_STUDY.
So, is the subsititution variable syntax officially changed in SQL Devloper?
No it should work either way, just hoping to find you a workaround until we can get it fixed.
As Lloyd above, since I upgraded to SQL Developer 4.1.0.18, I have been getting LINE_COMMAND_ERR when I try to run reports that have parameters (which I specify as ¶meter in the query).
This is has always worked before!
Again, do we need to use another substitution symbol?
This is such a fundamental function that almost makes the Reports feature useless…
Does :parameter work?
No. I guess it expects it to be a bind variable.
i have a report of type script
i have a query of
select * from hr.employees where salary > :salary
I run said report
It prompts me for value
What exactly are you doing?
Hi Jeff,
I am also using reports more often now! Thanks for your effort writing these helpful articles.
One question about Dial Gauge – how (if at all possible) I can reverse the colors in this control? I am tracking on folders size growth and I would like to have green up to 70% of the gauge to be green, 70%-90% to be yellow and the last to be red.
Is this at all possible?
Jeff,
I find myself using Reports more and more and have tried to champion the cause to other members of our teams that use SQL Developer. Hopefully you guys will not stop adding features and making existing features better.
I have noticed one thing when trying to run a “script” report using the 4.1EA release (4.1.0.17). Using 4.1 I always get a LINE_COMMAND_ERR message displayed in the results window when I was hoping for the “Enter Substitution Variable” window to pop up. When I click to run the report…bam…LINE_COMMAND_ERR !!
Running the same script report in 4.0.3 is always successful.
Are there any changes in how substitution variables (&&enter_user_name) are coded within the SQL statement in 4.1 or is this perhaps an opportunity to fine-tune 4.1 before it is released to the masses?
And yes…”A picture is worth a thousand words”!
Thanks…
Lloyd