Grouping Data using report Parameter

ganesh1984

Hi All,

I am creating a simple report which will be having three report parameters as follows.
1) From date
2) To date
3) group by

The main goal is to fetch records where birth date falls between
' From Date ' and ' To Date '. These records will be grouped by "DAY", "WEEK" or "Month". The user has to enter 'From Date' , ' To Date' and has to select a value from the drop down list 'group by'. The 'group by' drop down list contains "DAY", "WEEK" and "Month". if user selects 'WEEK' i have to place week(birthDate) in ? of group by. likewise for DAY and Month.

Here the problem is, while creating the report parameter birt will ask for data type of the parameter value. if i select string then week(birthDate) will be replaced as ' week(birthDate) ' (a string). which is not at all valid. week(birthDate) is a mysql function. Basically we will enter values(String , integer, boolean etc...) for report parameters, but how to associate a mysql function or a column name for a report parameter.

Any kind of help is appreciated.

My query is :

select birthDate, count(birthDate),
sum(amount) , avg(amount),
date(birthDate) ,week(birthDate) ,month(birthDate)
from User where birthDate between ? and ?
group by ?

regards,
ganesh

ganesh1984

can some one help me to solve this issue . atleast suggest any alternatives to solve this.