Home > JASPER REPORTS, JMETER, MySql, Uncategorized > IReport / Jasper Reports – Using main dataset parameters in subdatasets in Jasper Reports

IReport / Jasper Reports – Using main dataset parameters in subdatasets in Jasper Reports


The best way to build a complex graph in IReport is definitely the MultiAxis chart. One thing that i love about it, is that every subchart you add to the main MultiAxis chart can use it’s own dataset, so you do not have to fight complex querys and joins in your main query, loosing a lot of time on processing when displaying the graph.

What i needed, was to display the response times of individual HTTP Requests, grouped by second, on a timeline, and the number of transactions, grouped by second, on the same timeline of course.

Grouping by http requests, and timestamp delivered me the following results:

time_stamp     average     max     request            transactions
10:48:08     23                     24     Login                              3
10:48:08     4,1852            5        Book                               27
10:48:08     11,6667         13      Homepage                   3
10:48:09     13                     14      Join Game session   3
10:48:09     4,3704           6        Book                               27
10:48:09     24                     27     Login                              3
10:48:10     11,3333          12      Homepage                   3
10:48:10     5,2333            22      Book                              30
10:48:10     13,6667         14       Join Game session   3
10:48:11     24,3333          25      Login                             3
10:48:11     6,8333            32       Book                              30
10:48:11     11,3333          12       Homepage                   3

When printing out the chart, the response times were grouped correctly, but the number of transactions was of course wrong, because it was not the sum of all transactions, but was grouped by timestamp and classname as well

Well, one solution would have been modifying the query, and adding a join on a temporary table, comparing by the only field that could be of help, time_stamp. Starting with MySql 5.0, you are allowed to do that. Thanks to my good friend Lucke for pointing that out to me (very helpful in other scenarios) The query, in case you will ever need it, would look like this:

select
tr.testrun_id,
DATE_FORMAT(DATE_ADD(‘1970-01-01 00:00:00′ ,INTERVAL ts*1000 MICROSECOND),’%H:%i:%s’) as time_stamp,
avg(t) as average,
max(t) as maximum,
tr.lb,
count(tr.t) as transactions

from
testresults tr
inner join

(

select
DATE_FORMAT(DATE_ADD(‘1970-01-01 00:00:00′ ,INTERVAL ts*1000 MICROSECOND),’%H:%i:%s’) as time_stamp,
lb,
count(t)
from testresults
group by time_stamp) a
on tr.lb=a.lb
where
tr.testrun_id=25646
and
lt>0
group by
lb,
time_stamp
order by
time_stamp

The problem with this query is that if you are running it agains a large number of records, you will definitely wait a LOT. But i thought it is worth mentioning it, for those still looking for “multiple selects:)

So i had to find another solution. I needed an extra query only for the transactions. So i’ve built myself a subdataset in my main report, having this query associated:

select

DATE_FORMAT(DATE_ADD(‘1970-01-01 00:00:00′ ,INTERVAL ts*1000 MICROSECOND),’%H:%i:%s’) as time_stamp,
count(t) as userscount,
lb

from
testresults

where testrun_id in ( 2646 )

group by lb,time_stamp

order by time_stamp

I used a secondary line chart on my multi axis chart, and displayed the values returned by this dataset. Still….

Now was the tricky part, for which i did not find an answer without trying, failing and trying again for some time. I needed to plot those values for a specific test_run, not for all of them. So i needed to pass parameters from the main report to the sub dataset. And here is how you do it:

  1. First of all, all parameters that you send from the main report, have to be declared (and of the same type) in your sub dataset. In our example, we would be passing the run_id parameter
  2. Go to your multiaxis chart
  3. Select your second chart, the one plotting the transactions count
  4. Right click > Chart data
  5. Make sure your subdataset is set to the one you just created
  6. Select the “Parameters” tab
  7. Click Add
  8. In the upper listbox select your main report parameter ; In the lower box select the value expression by pointing to your sub dataset newly declared parameter
  9. Now modify your query accordingly: where testrun_id in ( $P!{run_id} )

That’s it. In the end  it should look something like this ( the only difference is that i am passing also the http request for further filtering, and another parameter that i need):

Sending parameters to sub dataset Jasper Report

Sending parameters to sub dataset Jasper Report

And this is how my report looks in the end:

Performance Measurement Chart Jasper Reports

Performance Chart Jasper Reports

Of course, i replaced the names of the request. And it is a small scaled version of the report displaying:

  • On the left side the scale of transactions
  • On the right side the maximum time in a second and the average response times of the requests
  • On the bottom is a timeline: this is where the timestamps are displayed, ordered by time

This post was meant to clear how to pass parameters in datasets in IReport and Jasper Reports. I hope i will save some time for others looking for an answer in this are.

Cheers,

Alex Ersenie

About these ads
  1. September 16, 2010 at 10:50 am

    Thanks for this post. It was exactly what I was looking for and saved me a few hours of digging.

  2. September 28, 2010 at 2:41 pm

    Hi David,
    happy to hear that.

    Cheers!

  3. brad
    October 14, 2010 at 6:35 pm

    But what if I don’t want to use my subdataset for a chart/graph? I just want to use the main report’s parameter in the subdataset query.

  4. April 19, 2011 at 9:36 am

    Hi Alex!

    I’m using iReport ver. 3.7.6 and I need to access a variable in a dataset from a subreport. How can it be done?

    Thanks, mannyee

    • April 19, 2011 at 4:02 pm

      I am afraid i missed the point here. You have a subreport with it’s own dataset. You need to access a variable that the subreport is returning?

      • April 20, 2011 at 4:57 am

        In my main report I have a table and a subreport. The table has its own dataset. One of the variables $V{total} defined in the dataset is used to sum up a field $F{salary} and then displayed in the table footer. I need to access the final evaluated value of this variable(defined in the dataset for the table) from the subreport. I am not able to return the variable.

      • April 20, 2011 at 1:36 pm

        Have you read this already? I guess it is what you are trying to do:

  5. mannyee
    April 21, 2011 at 11:48 am

    Yes, I’ve read and there’s no problem passing the parameters to the datasets. I just wanted to know if there’s a way to pass back the variables from the dataset. :-)

  6. December 6, 2011 at 9:34 pm

    What\’s up colleagues, its great piece of writing regarding educationand fully explained, keep it up all the time.

  7. saeid
    December 11, 2011 at 2:44 pm

    Hi
    I dont want to use subreport.I created a secondary dataset and drag a List Component
    into my report and assign secondary dataset to it.

    now I want , pass some values to dataset parameters .

    how can i do that ?

    thanks…

  8. Jeck
    March 2, 2012 at 12:14 am

    This is only example of chart. How about for cross tab. Passing the main parameter to cross tab dataset. I follow that instruction to a cross tab but it didn’t work. What could be the problem?

  1. March 28, 2011 at 3:19 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 29 other followers

%d bloggers like this: