Display Pie Chart by Month

[shaoen01]

Hey,

I am trying to display a pie chart that shows the monthly sales from Jan to Dec for a particular year for example. I have no problem inputting the parameters and my pie chart can load. My problem is how do i label the pie chart based on the name of the months like Jan, Feb, etc?  The query ain't so right yet as i am just testing iReport as i have never used it before. So based on the purchaseDate given say 16th june 2008, i want to take all the months in year 2008 and put the number of tickets sales in a pie chart categorized by months.

Any clues or suggestions please? Thanks!

 

 

 

Code:

I created a variable and this is the expression value: "$F{ticket_PURCHASEDATE}.getMonth()".Query Code:SELECT     count(ticket.`ID`),     ticket.`PURCHASEDATE` AS ticket_PURCHASEDATEFROM     `ticket` ticketWHERE     ticket.`PURCHASEDATE` = $P{purchaseDate}GROUP BYticket_PURCHASEDATE

[kchaudhry]

The parameters should be a date range otherwise with your current query structure it will only return results for one date and then in turn one month. Here is a rough sample of the query. Hopefully this will point you in the right direction.

 

Code:

select count(id) 'Count',       datename(month,yourdate) 'Month'fromyourtablewhereyourdate between $P{startdate} and $P{enddate}group bydatename(month,yourdate)

[shaoen01]

I think i know how to do it! Thanks!

[shaoen01]

I tried it out with the datename(), but it gives me an error in my report query: SQL Problems:Function datename does not exist.

[kchaudhry]

Well datename is a sql server function, your syntax will depend on your database type and version.

[shaoen01]

I think you're right. I have tried date_format() for mysql and it should work i hope. Thanks!