Qants – Tips in the field of Java Performance & Software Testing

I have long awaited for this functionality in Jasper Server. And it was beautifully served starting Jasper Server 3.7.5

What i needed to do, was to do a cascade query, which would allow me eventually to select a single (or list) of id’s, based on the previous selections. Since i have spent some time integrating this in my reports, i thought it could help others acchieve this also, since it is not really “out of the box”.

I will start by defining the scope of the report, the ER diagram of the tables, and will then dive into configuring the needed resources, both in Jasper Server and IReport.

As i am using Jasper Reports for building performance reports, i am using the following four tables (relevant for this document):

• info {id,build,portal_version,revision_number) – containing the primary key, software major version name, and the portal version (the hudson build id). I am using the primary key  in a one to many relationship in the following table, where i hold the map between the tests that i ran, and the software version (the revision_number is obsolete, as it is the same with the hudson build id)
• testrun {build_id,test_id} – containing the map between the build and the tests (one to many)
• testdescription{test_id,test_date,threads,users,rampup,scenario_id, architecture_id,etc.} – holding test information for each test that has been run (numbe of threads, date, etc., and the component tested, which in this case is represented by (bad naming) “scenario_id“
• testresults {id,testrun_id,t,lt,ts,lb,…etc} – holding the test results imported from JMeter reports

For a better understanding, i have provided the ER Diagram for this four tables:

ER Diagram Performance Reports

So, what we want to do, is building a report from the testresults, that reside in “testresults” table. For that, we need the “testresults.testrun_id” value, which we want to extract after previously having selected the needed information. We will follow the following steps:

1. Query and display  the contents of  the table “info“, and select the major version and revision number that we are interested in. We will store the result (only “info.id” ) into a parameter called ic_version, that we will use in the second query for finding all components that have been tested against this software version
2. Query and display the tested components (scenario_id) from the table “testdescription” based on the chosen info.id, which we now have in the ic_version parameter. We will do a join over the “testrun” table in order to sort that information. We’ll store the result of the selection (only “testdescription.scenario_id“) into a second parameter, called ic_component that we will now use to find the tests that have been run against this component
3. Query and display all tests from “testrun” that match ic_component criteria ( all tests that have been run against a component, on a specific software version). We will store the result of the selection in a third parameter, called ic_testconfig, that we will pass on to the internal report query

Let’s do this, one by one. We will start by creating the needed resource in Jasper Server.

Server Side Configuration – Jasper Server

1. Query and display the contents of “info”, store results into a server side parameter
   1. Log in as Jasper Administrator to your Jasper Server instance
   2. Go to “Input Data Types”. Right click > Add resource – Query
   3. Name it “q_version_select” and label it “Query version select”
   4. Choose the server sided Data Source (From the Repository)

   5. Type in your query:
      select id,build,portal_version ,revision_number from info order by id

   6. Now that we have the query, we will need a server sided parameter to store the selection that we are going to do from the result set of this query. We need to create an Input Control on the server
      Go to “Input Data Types”. Right click > Add resource > Other – Input Control
   7. Name it “ic_version“. Prompt text should be “Choose software version”. Type  should be “single select query”.Click “next”
   8. Select the query from the repository ( the q_version_select query that we just created in step 1-5)
   9. Select “id” as value column (this will be the value stored in the parameter), and whatever you want for the displayed (visible columns). They will only be displayed to make your selection easier (i chose them all)
2. Query and display the tested components on the software version, using the parameter “ic_version”
   1. We need to create the second query, which will use the “ic_version” parameter
   2. Go to “Input Data Types”. Right click > Add resource – Query
   3. Name it “q_select_component” and label it “Query component select”
   4. Choose the server sided Data Source (From the Repository)

   5. Type in your query:
      select distinct tr.test_id,td.scenario_id,td.architecture_id
      from testrun tr
      inner join info ON tr.build_id = info.id
      inner join testdescription td on td.test_id = tr.test_id
      where info.id=$P{ic_version} group by td.scenario_id order by scenario_id,architecture_id

   6. Now that we have the query, we will need a server sided parameter to store the selection that we are going to do from the result set of this query. We need to create an Input Control on the server
      Go to “Input Data Types”. Right click > Add resource > Other – Input Control
   7. Name it “ic_component“. Prompt text should be “Choose tested component”. Type  should be “single select query”.Click “next”
   8. Select the query from the repository ( the q_select_component that we just created in step 1-5)
   9. Select “scenario_id” as value column (this will be the value stored in the parameter), and whatever you want for the displayed (visible columns). They will only be displayed to make your selection easier (i chose scenario_id and architecture_id)
3. Query and display the test id and test configuration (in case you had different load scenarios for the same test and same component)
   1. We need to create the third query, which will use the “ic_component” parameter
   2. Go to “Input Data Types”. Right click > Add resource – Query
   3. Name it “q_select_configuration” and label it “Query test id and configuration”
   4. Choose the server sided Data Source (From the Repository)

   5. Type in your query:
      select
            td.test_id,
            td.scenario_id,
            td.architecture_id,
            td.test_date,
            td.threads,
            td.users
      from
      testdescription td
      inner join
      testrun tr ON td.test_id = tr.test_id
      inner join
      info ON tr.build_id = info.id
      where
      td.scenario_id=$P{ic_component}
            and
      info.id=$P{ic_version}
      order by
            td.architecture_id,td.test_date

   6. Now that we have the query, we will need a server sided parameter to store the selection that we are going to do from the result set of this query. We need to create an Input Control on the server
      Go to “Input Data Types”. Right click > Add resource > Other – Input Control
   7. Name it “ic_testconfig“. Prompt text should be “Choose test id and configuration”. Type  should be “multi select query” (maybe you need to create a report from multiple tests). Click “next”
   8. Select the query from the repository ( the q_select_configuration that we just created in step 1-5)
   9. Select “test_id” as value column (this will be the value stored in the parameter), and whatever you want for the displayed (visible columns). They will only be displayed to make your selection easier (i chose test_id and test_date and threads (virtual users))

We have now reached the final step, where we have stored the selected test id in a parameter called “ic_testconfig”. We will use this parameter in the internal report query in IReport. Let’s just see how the cascading looks at this point (of course, a report is already created in IReport, but i will detail that below):

Last, we select the tests that we want to build the load report from

So, the main idea is, that based on the first selection, the second drop list changes dynamically. The third list changes according to the second selection, and so on. You could have as many drop down lists as you like in your form.

Client Side Configuration – IReport

In order to build a server hosted report, which uses the resources we defined above, we need to synchronize the resources. Therefore, we need to add the server side input controls we just defined to the report. You do that by following these steps:

1. Open IReport and expand the report you want to use
2. Select the “Input Controls” subfolder of the report – Right Click > Link an existing input control (we want to use the one on the server, otherwise we would have defined it locally)
   

   IReport - Link existing input control

3. Choose datatypes as location for the input controls
   

   IReport - Add server located input control - Datatypes

4. Add the three input controls that we defined in the first part, one by one: ic_version, ic_component, ic_testconfig
5. For each server side input control, we need a local defined parameter with the same name and type. Add the following three parameters in the Parameters Tree:
   • ic_version of type string
   • ic_component of type string
   • ic_testconfig of type collection, with a default expression of: new ArrayList(Arrays.asList(new String[] {“a”}))

   We are now ready to build the local query (the report query), which will use the last selected parameter, ic_testconfig

6. Define the local query based on ic_testconfig parameter:
   select 
              tr.col1,
              tr.col2,
              etc.
   from
             testresults tr
   where
             $X{IN,tr.testrun_id,ic_testconfig}

As you can see, we use the ic_testconfig parameter that we used in the cascade for defining the test id for the load report.

That’s it. Here is a list of things to remember:

1. Every query should have an input control on the server
2. Every subsequent query should use the input control from step 1 as sql predicate
3. Every input control on the server has to be linked in the report as “existing input control”
4. Every input control defined on the server and linked as existing input control, has also have to be defined as local parameter, of the same type, and with the same name