Create a bar chart from flat data

[etienne.girot]

 Hi everybody,

 

I need a little help to get started with Jasper, I read the different tutorial but I can't find what I'm looking for:

 

I have the following "FavoriteFruit" table in my SQL DB:

User_id	Apple	Banana	Strawberry	Orange	Watermelon
1	1	0	0	0	0
2	0	0	0	0	1
3	1	0	0	0	0
4	0	1	0	0	0

And I want the following result:

My dataset query is "SELECT * FROM FavoriteFruit".

What do I need to insert in the bar chart assistant for the fields  "Values" and "Category" to obtain this?

 

[ernestoo]

Unfortuantely what you are looking for won't work with that type of data - we need to "pivot" it but there's no built in function for that; ultimately it's best if your data looks like this (key values). Could you do that in the query? The Category would be Fruit, Value is Value.

User_id	Fruit	Value
1	Apple	1
1	Banana	1
2	Apple	0
2	Banana	2

 

[hozawa]

If you're using mysql as the database, use the following sql and just set Value = $F{total} and Category = $F{fruit}

select fruit,
sum(apple) as total
from (
select user_id, apple, 'apple' fruit from fruits
union all
select user_id, banana, 'banana' fruit from fruits
union all
select user_id, strawberry, 'strawberry' fruit from fruits
union all
select user_id, orange, 'orange' fruit from fruits
union all
select user_id, watermelon, 'watermelon' fruit from fruits
) src
group by fruit

 

 

[etienne.girot]

Thank you for your help. Actually doing it the SQL query is already the solution I'm using (with highchart). I thought that introducing a BI tool such as Jaspersoft would save me the effort of formatting programmatically the data before-hand... I saw some videos of jasper where they were doing complete reports only with the UI but maybe it's what they call "Ad-hoc report designer" which is only available in the non-community version, can you confirm it?

[etienne.girot]

Thank you for your help. Actually doing it the SQL query is already the solution I'm using (with highchart). I thought that introducing a BI tool such as Jaspersoft would save me the effort of formatting programmatically the data before-hand... I saw some videos of jasper where they were doing complete reports only with the UI but maybe it's what they call "Ad-hoc report designer" which is only available in the non-community version, can you confirm it?

[ernestoo]

The ad-hoc report designer would still need the data to be converted for it to be displayed this way - we recommend that you use JaspersoftETL to pivot the data ahead of reporting, for example https://help.talend.com/display/KB/Converting+rows+to+columns

 

At some stage something has to do this conversion - it's expensive to do it in the reporting layer

[hozawa]

Etienne,

Adhoc reports is supported only in the commercial version. Nevertheless, Adhoc report is only a tool to create report on the web. From your description, it's more of a "Domain Designer" functionality that's only only available in the commercial version. With the "Domain Designer", you'll be able to create "virtual views" which can be used as a data source in your report. Nevertheless, something like the above sql statement is still needed to define the view.

The good news is, Tibco Jaspersoft has introduced Visualize.js (also only available in the commercial version) that enables developers to develop web UI interfaces to JasperReports. Hopefully, I would be able to develop web UI that will make user experience much more better than it is now.

[hozawa]

ernestoo,

If there are many rows in a report and if a report is to be created periodically, Jaspersoft ETL is a good solution, but if an users just want to create a report with few data and just want to visualize some data fo an if-then analysis, JaspersoftETL is not a good solution because Jaspersoft ETL it requires somebody else to develop flows and executes in batch instead of real-time (requires developer to switch from JasperReports to Jaspersoft ETL page with another login).

 

I actually already developed a page that creates database table, insert data, create datasource, and domain when an user just drag & drop an excel file on to the web page. Most end users know how to export data from database to excel and to reformat data in excel. This isn't an ideal solution when a report is to be created periodically, but I think it's a good solution for adhoc type of reporting. I'm now in a process of converting the code to a portlet so end-users will be able to more easily put this feature in their own web page.