Simple Bar Chart Reports with SQL Developer

thatjeffsmith SQL Developer 23 Comments

Tell Others About This Story:

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:
[sql] SELECT country,count(*) FROM beer
group by country
having count(*) > 100
order by 2 desc
[/sql]

Running MY query gives me data that looks like this
–pretty HTML output generated by SQL Developer

COUNTRY COUNT(*)
United States 4140
Germany 1911
United Kingdom 639
Russia 544
Canada 435
Japan 348
France 267
Brazil 223
Belgium 198
Australia 195
Italy 172
Poland 171
Czech Republic 166
China 133
Denmark 130
Austria 128
New Zealand 104

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:
[sql] SELECT country,country, count(*) FROM beer
group by country, country
having count(*) > 100
order by 3 desc
[/sql]

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.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 23

  1. Thank so much Jeff

    I have working Reports Bar and Pie charts in my schema.
    Do you have any sample used plsql scripts? I am trying with Reports Style code but I don’t get return any table 🙁

    oscar

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author

      Sure, just create a new report for this query

      SELECT ‘Beers’, country,count(*) FROM beer
      group by country
      having count(*) > 100
      order by 3 desc

      Set report type to ‘chart’ – set property of chart to ‘pie’ – hit OK and run.

  2. I am new to reporting tool. I use SQL Developer to write code that runs in a UNIX/Oracle nightly batch cycle. Is it possible to use the reporting tool to create code that I can run nightly and will create an HTML type report that the system will email to management?

    1. thatjeffsmith Post
      Author
  3. Sorry for my late reply too Jeff, but yes that is what i observed…

    I worked my way around it by generating a copy of the original and removing the slow and detailed graphs from the original. This way the impatient users can use the fast and less detailed graphs, and if they really need to dive into it look into the slower more detailed version. Guess a drill down report could do the trick as well, but at the time I logged this I was frustrated with it.

  4. Cool stuff, which I just discovered.
    I use it in a parent child report. I found that when you have multiple child reports of the chart type, all these graph queries are run, but it will also run the appropriate query when I open up that tab. I would like to disable this behaviour, so it only runs the query for the report on the current tab. (but did not find it in generated XML)

    1. thatjeffsmith Post
      Author

      Sorry Frank, just noticed your question here.

      Are you saying if you have multiple child chart reports, that when you open one, you notice we’re running the queries for ALL of the child reports, not just for the active one you clicked on?

  5. Looks like this DOES NOT work within Management Studio within SQL Server
    This blog-post is for Oracle & not SQL Server.

    Am I correct?

    1. thatjeffsmith Post
      Author
  6. Brilliant. How can one export a chart report to pdf though (or other format), my right click wont bring up any context menus fore example like it would on other types of reports?

    1. thatjeffsmith Post
      Author

      Making the charts available in reports outside of SQL Developer is something we are working on for a future release. It’s the obvious ‘next’ step and you can expect many improvements in the reporting features soon.

        1. thatjeffsmith Post
          Author
    1. JeffS Post
      Author
  7. Hi Jeff,
    is there any chance to define max value for Y scale rather than rely on Developer
    to calculate it? Let’s say I have numbers between 10 and 50 but max can be 100
    and I want to present it in a graph.
    Also question about colors for chart – Developer assings them to categories on a fly
    and after refresh it might be different color assigned to the group as before
    so with automatic refresh it can be very misinterpreting

    Thank you,
    Andrey

    1. JeffS Post
      Author

      i don’t think so on your first question Andrey.

      What I would try though is to write a ‘select 100’ from dual and UNION ALL that to the original query and have that serve as a placeholder to force the scale that you want. Not an elegant solution, but that’s my first take.

      The colors are chosen on the fly, so you can’t build meaning out of the color in a way that I think you’re asking. It’s just for differentiating data points.

      Now the gauge report on the other hand – this allows for defining the range in values and for setting threshold points. Have you seen those?

      1. Thank you, Jeff

        I thought about the same solution, I will try it.
        for gauge report – I saw it but it is not the type of chart that I want to use

        Thanks,
        Andrey

  8. Pingback: Simple Bar Chart Reports with SQL Developer – All Things Oracle

  9. Pingback: Simple Bar Chart Reports with SQL Developer – All Things Oracle

Leave a Reply

Your email address will not be published. Required fields are marked *