Jaspersoft BI Tools

iReport,Jaspersoft Design Studio,Jaspersoft Server, Schema Workbench,Analysis,Dashboards, Self Service BI, Domains,Topics,Ad-Hoc Reports, Jaspersoft ETL, Embedding BI,HTML-5 charts, Fusion Charts,JFree Charts,Drill down reports , Drill down Dashboards,Visualize.js, Performance Tuning, D3 Charts, Jaspersoft Customization

Thursday, March 27, 2014

Fixed values on Fusion Bar Chart category axis using SQL query

Hello guys,
Here is my recent work out on fixing values on category axis using SQL query for fusion bar chart.

This tip will give idea on fixing the X-axis values constant on category axis.

Note that there is no property to set max and min category values on fusion bar chart category axis. Hence a thought of implementing using SQL query.

Problem scenario :
Plot 5 A.M to 5 P.M constant times on  category axis when you have random time hours in your query result set.
Query output :
Time   Count
6 A.M    80
7 A.M    45
11 A.M  89
12 P.M  65
3 P.M    76

If you directly give it in

SELECT * FROM
(
SELECT CAST(5 as integer) as hour from DUAL
UNION
SELECT CAST(6 as integer)as hour from DUAL
UNION
SELECT CAST(7 as integer) as hour from DUAL
UNION
SELECT CAST(8 as integer) as hour from DUAL
UNION
SELECT CAST(9 as integer) as hour from DUAL
UNION
SELECT CAST(10 as integer) as hour from DUAL
UNION
SELECT CAST(11 as integer) as hour from DUAL
UNION
SELECT CAST(12 as integer) as hour from DUAL
UNION
SELECT CAST(13 as integer) as hour from DUAL
UNION
SELECT CAST(14 as integer) as hour from DUAL
UNION
SELECT CAST(15 as integer) as hour from DUAL
UNION
SELECT CAST(16 as integer) as hour from DUAL
UNION
SELECT CAST(17 as integer) as hour from DUAL
)x
LEFT JOIN
(
SELECT
hour,
count(files) as filecount
FROM files_table

) y

Again if you want to see them with A.M, P.M 
make the above query as sub query 

SELECT
  CASE
       subTable.hour
          when 12 THEN '12 P.M'
   when 13 THEN '1 P.M'
            when 14 THEN '2 P.M'
           when 15 THEN '3 P.M'
           when 16 THEN '4 P.M'
           when 17 THEN '5 P.M'
    ELSE CONCAT(to_char(x.hour),' A.M')
  END  Common_Hour,
subTable.file count
FROM
( write above query
)subTable

That's it.

Sample figure :


Thank you.