Simple Bar Chart Reports with SQL Developer

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,
count(data_type)
from all_tab_columns
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:

SQL Developer Report 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:

My Breweries by Country Summary Report Chart

So ‘Yes’, the USA is still #1 when it comes to important things like quality of life.