How to create multiple charts using result data from one query
I have a query that returns 40 rows to me, each row has a numbered_o__block_closed and private_data. The closed value is from May to June.
Now I need to create 3 report charts in JasperReports
- The graph shows the number of closed errors every day.
- The graph shows the number of closed bugs every week.
- The graph shows the number of closed bugs each month.
Can we create these 3 charts using the result data of one query, or did we create 3 different queries?
Can it be done in jasper with some script or some other way?
source to share
Hopefully you can use some kind of date validation in your request. Something like
where is the date between may and jun
Instead, create two parameters: and . $P{TimeCheck}
$P{TimeRange}
In which the $ P {TimeCheck} parameter will be used in the query to compare the time , and the $ P {TimeRange} parameter will output the value of the $ P {TimeCheck} parameter . Pass different $ P {TimeRange} values for different charts when displaying parameters , for example, for a month chart $ P {TimeRange} = 'month' , for a week chart , $ P {TimeRange} = 'week' and for a daily chart, $ P {TimeRange} = 'all' .
The $ P {TimeCheck} parameter must have the following expression "$P{TimeRange} == 'week' ? 'date in (startdate or enddate of everyweek)' : $P{TimeRange} == 'month' ? 'date in (startdate or enddate of every month)' : 'date between may and jun'"
Now use this parameter in where clause as where $P!{TimeCheck}
Note. Don't use the main query. Try to create a dataset and use a query.
I hope this should resolve your question.
source to share
Better to use different data for each graph. Or you can use the same dataset, but it will be so difficult to design. You can try this by following these steps:
you need to add a column (like week number) to your sql query and calculate week number for dates in that column. for example select week (yourdate) gives you the week number.
You now have three columns in your dataset with the week number.
If you use week_number as a category in your Jasper charts, it will give you charts, but the value for that doesn't match.
reason: jasper adds a value for the number_of_bugs_closed column, but also divides it by week_number. For example: you have two lines for the first week. then he will calculate it and divide it by two.
This means that you need another column (say week_number_of_bugs_closed) that is a multiple of the number of week_number and number_of_bugs_closed.
To calculate the number of weekly numbers, you need to make a connection that will give you the total number of days in that particular week.
You can link below to the request which shows the structure of the request.
select a.*,b.week_count, week_number_of_bugs_closed * b.week_count as week_value from
(select xyz.number_of_bugs_closed , xyz.closed_date ,week(closed_date) as week_number from xyz) a
join
(select week(closed_date) week_number, count(*) as week_count from xyz group by week(closed_date) ) b on
a.week_number = b.week_number
Now you can use the week_number category and its value as week_value for the weekly schedule.
You can use a similar approach for a monthly schedule.
Hope I can explain the concepts.
source to share