Examples of usages of jasperreport and Jasperserver

First, see the first requirement as shown in the following figure:
The first option is to implement the grouping (grouped by quarter), as shown in the following test Sql:select (ob_created_time> ' 2016-01-06 09:51 ' and Ob_created_time < ' 2016-03-06 09:54 ') THEN ' the 1th quarter of 2016 ' when (ob_created_time> ' 2016-07-06 09:51 ' and Ob_created_time < ' 2016-  07-07 09:53 ') THEN ' 2016 2nd quarter ' when (ob_created_time> ' 2016-07-07 09:59 ' and Ob_created_time < ' 2016-07-08 09:51 ') THEN ' 2016 3rd quarter ' when (ob_created_time> ' 2016-09-06 09:51 ' and Ob_created_time < ' 2016-12-06 09:54 ') THEN ' 2016 4 Quarter ' end] quarter, COUNT (ob_uuid) usercounts from Am_object WHERE ob_object_class= ' User ' GROUP by quarter; Finally, a union is used to link the query results of different groupings (Quarter,year,month), and the test SQL is as follows:
 select (case   WHEN  ob_created_time>  ' 2016-01-01 09:51 '  and ob_ created_time <  ' 2016-03-31 09:54 ')  THEN  ' 2016 1th quarter '     WHEN  (ob _created_time>  ' 2016-04-01 09:51 '  and ob_created_time <  ' 2016-06-30  09:53 ')  THEN  ' 2016 2nd quarter '    WHEN  ob_created_time>  ' 2016-07-01 09:59 '  and ob_created_time <  ' 2016-09-30 09:51 ')  THEN  ' 2016 3rd quarter '     when  (ob_created_time>  ' 2016-10-01 09:51 '  and ob_created_time <  ') 2016-12-31 09:54 ')  THEN  ' 2016 4th quarter '    end)  t,count (ob_uuid)  userCounts  from am_object where ob_object_class= ' User '  and  ' quarter '  like  ' quarter '  group by t UNION SELECT (case   WHEN  ob_created_time>  ' 2015 '  and  ob_created_time <  ' 2016 ')  THEN  ' 2015 '     WHEN  (ob_created_time>  ' 2016 ')  and ob_created_time <  ' 2017 ')  THEN  ' 2016 '    WHEN  (ob_created_ time>  ' 2017 '  and ob_created_time <  ' 2018 ')  THEN  ' 2017 '     when  (ob_created_time>  ' 2018 '  and ob_created_time <  ' 2019 ')  then   ' 2018 '    end)  t,count (ob_uuid)  userCounts FROM am_object WHERE  ob_object_class= ' User '  and  ' year '  like  ' year '  group by t UNION SELECT    WHEN  (ob_created_time>  ' 2016-01 '  and ob_created_time <  ') 2016-02 ')  THEN  ' January '  WHEN  (ob_created_time>  ' 2016-02 '  and ob_created_ time <  ' 2016-03 ')  THEN  ' February '  WHEN  (ob_created_time>  ' 2016-03 '   And ob_created_time <  ' 2016-04 ')  THEN  ' March '  WHEN  (ob_created_time>  ' 2016-04 '  and  ob_created_time <  ' 2016-05 ')  THEN  ' April '  WHEN  (ob_created_time>  ') 2016-05 '  and ob_created_time <  ' 2016-06 ')  THEN  ' May '  WHEN  (ob_ created_time>  ' 2016-06 '  and ob_created_time <  ' 2016-07 ')  THEN  ' June '    WHEN  (ob_created_time>  ' 2016-07 '  and ob_created_time <  ') 2016-08 ')  THEN  ' July '    WHEN  (ob_created_time>  ' 2016-08 '  and ob_ created_time <  ' 2016-09 ')  THEN  ' August '  WHEN  (ob_created_time>  ' 2016-09 ')  and ob_created_time <  ' 2016-10 ')  THEN  ' September '  WHEN  (ob_created_time >  ' 2016-10 '  and ob_created_time <  ' 2016-11 ')  THEN  ' October '  when   (OB_CREATED_TIME&GT;&NBSP; ' 2016-11 '  and ob_created_time <  ' 2016-12 ')  THEN  ' November '  WHEN  (ob_ created_time>  ' 2016-12 '  and ob_created_time <  ' 2016-12-31 ')  THEN  ' December '  end)  t, count (ob_uuid)  usercounts from am_object where ob_object _class= ' User '  and  ' month '  like  ' month '  GROUP BY t; The results appear as follows:
Next, consider how to pass the start date and end date as a variable value, first query for all data during the start and end days, SQL is as follows: select * from am_object  Where ob _object_class= ' User '   and ob_created_time>= ' 2016 ' and ob_created_time<= ' 2017 ' The results of the query are then grouped according to the time, sql:  select (case   WHEN  (quarter (Z.ob_created_time) =1)  then   (CONCAT (z.ob_created_time), ' 1th Quarter of the Year ')  WHEN  (quarter (Z.ob_created_time) =2)  THEN  ( CONCAT (Z.ob_created_time), ' 2nd quarter of Year ')  WHEN  (quarter (Z.ob_created_time) =3)  THEN  (CONCAT ( Year (z.ob_created_time), ' 3rd quarter ')  WHEN  (quarter (Z.ob_created_time) =4)  THEN  CONCAT ( Z.ob_created_time), ' 4th quarter of Year ') ( end)  t,count (z.ob_uuid)  userCounts FROM         (     select * from am_object        where ob_object_class= ' User '        and ob_created_ time>= ' 2016 '      and ob_created_time<= ' 2017 '      ) z  where  ' Quarter '  like  ' quarter '  group by t display as follows:
After the SQL has been sorted out, the following are:
SELECT (case   WHEN  Quarter (z.ob_created_time) =1)  THEN  CONCAT (Year (z.ob_created_time) , ' 1th quarter ')  WHEN  (quarter (Z.ob_created_time) =2)  THEN  (CONCAT (year (z.ob_created_time), ' 2nd quarter ')  WHEN  (quarter (Z.ob_created_time) =3)  THEN  (CONCAT (year (z.ob_created_time), ' 3rd quarter ') )  WHEN  (Quarter (Z.ob_created_time) =4)  THEN  (CONCAT (year (z.ob_created_time), ' 4th quarter '))   End)  t,count (Z.ob_uuid)  userCounts FROM        (      select * from am_object       where ob_object_ class= ' User '        and ob_created_time>= ' 2016 '       and ob_created_time<= ' 2017 '      ) z  where  ' quarter '  like   ' quarter '  group by t UNION SELECT (case   WHEN  (Year (z.ob_created_time) =2014)  then  ' 2014 '  WHEN  (Year (z.ob_created_time) =2015)  THEN  ' 2015 '  WHEN  ( Z.ob_created_time) =2016  THEN  ' 2016 '  WHEN  (year (z.ob_created_time) =2017)  THEN  ' 2017 '  end)  t,count (z.ob_uuid)  userCounts FROM        (      select * from am_object       WHERE  ob_object_class= ' User '        and ob_created_time>= ' 2016 '       and ob_created_time<= ' 2017 '      ) z  where  ' year '  like  ' year '  GROUP BY t  UNION  select (case   WHEN  MONTH (z.ob_ Created_time) =1  THEN  ' January '  WHEN  (MONTH (z.ob_created_time) =2)  THEN  ' February '  when   (MONTH (z.ob_created_time) =3)  THEN  ' March '  WHEN  (MONTH (z.ob_created_time) =4)  then& nbsp;' April '  WHEN  (MONTH (z.ob_created_time) =5)  THEN  ' May '  WHEN  (MONTH (z.ob_created_time) =6 )  THEN  ' June '  WHEN  (MONTH (z.ob_created_time) =7)  THEN  ' July '  WHEN  (MONTH ( Z.ob_created_time) =8  THEN  ' August '  WHEN  (MONTH (z.ob_created_time) =9)  THEN  ' September '  WHEN  (MONTH (z.ob_created_time) =10)  THEN  ' October '  WHEN  (MONTH (z.ob_created_time) =11 )  THEN  ' November '  WHEN  (MONTH (z.ob_created_time) =12)  THEN  ' December '  end)  t, COUNT (Z.ob_uuid)  userCounts FROM        (      select * from am_object       where ob_object_class= ' User '        and ob_created_time>= ' 2016 '      and  ob_created_time<= ' 2017 '      ) z  where  ' month '  like  ' month '  group&nbsp By t
Here consider the      and ob_created_time>= ' 2016 '       and  ob_created_time<= ' 2017 '     is passed in as a time variable ($P {Startdatekey}, $P {Enddatekey}), while the quarter (' quarter '  like   ' quarter ') is also passed in as a variable value ($P {timegroup}), and the modified SQL is as follows: Select     (case   WHEN  Quarter ( Z.ob_created_time) =1  THEN  (CONCAT (z.ob_created_time), ' 1th quarter of Year ')  WHEN  quarter (z.ob_ Created_time) =2  THEN  (CONCAT (z.ob_created_time), ' 2nd quarter of Year ')  WHEN  quarter (z.ob_created _time) =3  THEN  (CONCAT (z.ob_created_time), ' 3rd quarter of Year ')  WHEN  (quarter (z.ob_created_time) = 4)  THEN  (CONCAT (z.ob_created_time), ' 4th quarter of the Year ')  end)  t,count (z.ob_uuid)  usercounts  FROM  (   select * from am_object      where ob _object_class= ' User '      and ob_created_time>= $P {startdatekey} &NBSP;&NBSP;&Nbsp;  and ob_created_time<= $P {Enddatekey}  ) a where  "quarter"  like  $P { Timegroup} group by t later, to simplify the SQL, replace the case, and then modify the SQL: Select     concat ( a.ob_created_time), ' Year ', Quarter (a.ob_created_time), ' quarter ')  t,count (a.ob_uuid)  usercounts      from ( select * from am_object      where ob_ object_class= ' User '      and ob_created_time>= $P {startdatekey}       and ob_created_time<= $P {Enddatekey}        ) a Where   "Quarter"  like  $P {timegroup} group by t is not very concise. Now that SQL has been written, you need to create the corresponding variable parameter under ireport, as follows
Among them, startdatekey as a fuzzy query variable is set as follows
In addition to calculating all usercounts, the total variable is added, which is set as follows


After you import SQL, you need to create another field variable