Pie Chart Issues

Hi,
add IF operator to your SQL query:

http://dev.mysql.com/doc/refman/5.0/en/con...-functions.html

Hi Jane,
I've looked at the trend supplied, however i'm completely lost!!!
Can you please provide some sample code ... PLEASE
Thanks in advance
Lisa

Thought I would add my 2 cents if it heps.

Lisa, i don't think what you are after is possible. If you use IF or CASE statements you can check if the value is equal to zero (null) then display a different value but that would be displaying a value that does not corrrespond to the real number (which is zero). If you want the pie chart to be displayed then it needs values and it looks like you want to display equal values (i.e if you give each category a value of 1 the pie would show 5 equal slices). This would in my opinion still confuse the users as you are not displaying teh 'real result' of your query even of your label would say 0 (displaying a slice does suggest that the value is not null).

I think a better solution would be to display an image when your sql query retruns null. (I don't know if this is possible with anychart pie chart). If possible you could display a jpg or gif showing any dummy data you like.

I use pie charts and am happy with it displaying no slices when my query retruns 0.

Hi Jane,
Can you please provide a fix.
Thanks Hich for your comments.
Thanks in advance
Lisa

Hi Lisa, sorry I could have completed my reply with what you might have been after

[codebox]SELECT posting_category,

CASE

WHEN (select COUNT() FROM postings WHERE DateDiff(now(), postings.posting_date)<60 AND ((booking_status = '0' AND deleted_status = '0') AND (userstatus = 'true'))) IS NULL THEN 1

ELSE

COUNT()

END AS cnt

FROM postings

WHERE DateDiff(now(), postings.posting_date)<60

AND ((booking_status = '0' AND deleted_status = '0') AND (userstatus = 'true'))

GROUP BY posting_category[/codebox]
the 1 in the above query (after THEN) is the value you would like to show when your query returns NULL so replace it with whatever value you want to display. You might try IF or IFNULL functions if you prefer.

This might not 100% what you are after and may be jane has a better idea. Hope this helps anyway

Hi Jane,
Hich ... Sorry did not work.
Can anyone please provide a solution?
Should a fix be available, i simply want the chart screen to display the legends as:
Sales (0)

Manager (0)

Office (0)

Marketing (0)

Accounts (0)
I know that the actual pie chart will not be displayed as there is no data.

However there should be the pie chart indicators displaying the above categories along with the values.
Is there any way of removing the label 'NaN%' ???
Here is an example of what i'm after:

This solution would be more appealing the the customer than a blank screen.
Thanks in advance
Lisa

I know that the actual pie chart will not be displayed as there is no data.

Hi Lisa,

If you want to remove the NaN% you would have to change that in dchartdata.php (not always recommended).

You probably would need to add a test to check if the value is null and if so display a different value.

You need to have a test around this in the dchartdata.php

search in the code area for pie series for the following: ({%YPercentOfSeries}{numDecimals:2}%)

When you have a value of zero the percentage not displaying 0%, so may be Jane can help build a test around this to hide the percentage when values are null.

Hope this helps

Thanks Hich.
Jane,

I still need a workaround to the initial SQL query syntax
Any help would be appreciated.
Lisa

Hi,
what SQL query do you mean?

SQL query with IF operator will work in the same was as SQL query with subqueries.

Hi Jane,
Currently I use the following code held in the 'Edit SQL query' for the chart.
SELECT posting_category,

COUNT(*) AS cnt

FROM postings

WHERE DateDiff(now(), postings.posting_date)<60

AND ((booking_status = '0' AND deleted_status = '0') AND (userstatus = 'true'))

GROUP BY posting_category
As i have mentioned previously, when this project is executed and there are no rows containing posting_category:
Sales, Manager, Office, Marketing, Accounts
then the chart output is simply a blank screen. i.e. no pie chart or legends.
If the condition is as above (i.e. no rows) i simply want to display the chart as per my previous screenshot provided.

I'm open to any options of achieving the above. All i ask for is some sample code to achieve this solution.
Thank you in advance
Lisa

Hi,
you can use IF operator in the query or subqueries (hich recommendation).

Result chart will be the same for both methods.