There are many good articles out there that go into detail for creating reports with Oracle SQL Developer. I was reading my predecessor’s write up in Oracle Magazine (May 2007) on the subject, and keyed into the following snippet:
Building a Chart
When creating a report with the Chart style, the rule of thumb is to use SELECT group, series, data FROM table . So the basic tabular report you created earlier also has the right ingredients for the Chart style. Another good use of the Chart report style is for looking at your system information graphically. For example, to look at the trends of the datatypes used in columns in each table in a particular schema, create a new report, by setting Style to Chart and using the following query as the SQL:
select table_name, data_type,
where owner = ‘HR’
group by table_name, data_type
Except in the chart report I wanted to create, I only had 2 columns. Here is the query I was using to feed data to the chart in SQL Developer:
SELECT country,count(*) FROM beer group by country having count(*) > 100 order by 2 desc
Running MY query gives me data that looks like this
–pretty HTML output generated by SQL Developer
OK, how do I turn this into a chart report?
You can create a new user defined report manually or you can use the grid context menu (accessed via right-mouse-click) for the result-set of your query and say ‘Save Grid as Report.’ Here’s the problem though, Sue said that SQL Developer needs the following to build a chart report:
group, series, data
I only have 2 columns though. If I try to execute the report with that query straight up, then I get this ‘weird’ error message:
Well it’s pretty clear what my problem is, my query is only providing a group and series for the chart, but no data to feed to it.
For some reason my reptilian brain wanted me to add rownum to my query to ‘cheat’ the report. So the dummy in me tried that and it didn’t go so well. My advice, most times your solution is staring you straight in the face and is often much simpler than you might imagine. You see, what if your group and series happen to be the SAME?
So, the solution was to change my query to be this instead:
SELECT country,country, count(*) FROM beer group by country, country having count(*) > 100 order by 3 desc
Once I made this change, I got my beautiful report:
So ‘Yes’, the USA is still #1 when it comes to important things like quality of life.