Forum Stats

  • 3,876,129 Users
  • 2,267,066 Discussions
  • 7,912,443 Comments

Discussions

Syntax for pie chart report

mariita
mariita Member Posts: 52
edited Oct 22, 2014 10:46AM in SQL Developer

What is the correct syntax for a "pie chart - multiple" report in Oracle SQL Developer? I'm trying to create a master-child report in which the master report is a bar chart and the child reports are pie charts (multiple). The master report looks fine, but I can't get the child reports to display as pie charts. The child reports display correctly in tabular format, but if I change the format to pie chart then I get the error message: "The data is of zero or negative value and cannot be displayed".


Here is the current query for the child reports:

SELECT  year, college_name, student_id, count(student_id) as count_ids

FROM enrollment

WHERE college_name = :COLLEGE_NAME

GROUP BY year, college_name, student_id

ORDER BY year, college_name, student_id;

This is what the child report looks like in tabular format:

data.jpg

This is roughly what I want the pie charts to look like:

pie_charts.jpg

Tagged:

Best Answer

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,883 Employee

    Does it work if you remove college name?

    I'm able to do a multiple child pie chart off of a parent bar chart, w/o having to setup anything on the data properties for the child report definition

    multiple_pie.png

    Here's my child report query:

    select  year, team, sum(points) from hockey_stats

    where player = :PLAYER

    group by year, team

    order by year desc

    If you run your query, do you have any nulls, zeros, or negative values for count(student_id) ?

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,883 Employee

    Does it work if you remove college name?

    I'm able to do a multiple child pie chart off of a parent bar chart, w/o having to setup anything on the data properties for the child report definition

    multiple_pie.png

    Here's my child report query:

    select  year, team, sum(points) from hockey_stats

    where player = :PLAYER

    group by year, team

    order by year desc

    If you run your query, do you have any nulls, zeros, or negative values for count(student_id) ?

  • mariita
    mariita Member Posts: 52

    Removing the college name worked, thanks. I assumed the college name had to be in the SELECT statement because it is used as a bind variable. I do have some null values for count(student_id) but that doesn't seem to have been an issue.

    BTW, I don't seem to be able to mark questions as answered. I'm not sure if I don't have permission to do this, or if I'm just not seeing it.


  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,883 Employee

    You can reference the value from the parent in the WHERE w/o using it in the SELECT.

    It's not a big deal, but if you want to, you can click the 'Correct Answer' button that appears at the bottom left corner of each post...it's right above the 'Actions' link.

  • mariita
    mariita Member Posts: 52

    I don't seem to have a Correct Answer link at the bottom of each post, either on the left or on the right.

    correct_answer.jpg

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,883 Employee

    Weird...what browser/OS are you using?

    Not a big deal but we are running a pretty new version of the forums software here, so maybe something is off.

  • mariita
    mariita Member Posts: 52

    I mostly use Chrome on Windows 7, but I get the same behavior in IE 8.

This discussion has been closed.