Home > JMETER, Uncategorized > JMeter – Export to Excel and Analyse Results using Pivot Tables

JMeter – Export to Excel and Analyse Results using Pivot Tables


Most of you performance testers, newbies or experienced, you all came across this problem … how to export the load test results, and how to analyse them.

While working for a project in Viena, several years ago, Excel looked to be the best option, because of the relative powerful charts, functions, and code (VBA) were offering.  Back then, i was still using OpenSTA, which was at the time, the best open source loading tool on the market. (still is a very good option, depending on the needs) As usual, each Performance Tool has (or not) its integrated Graphs for reporting the test results, yet most of the time unsuited for the needs of the performance tester. So exporting to excel was the best option back then. And i used it.

Exporting was quite easy, because of the CSV export functionality OpenSTA was offering, and importing…well there is no point in continuing the discussion, as Excel has really powerful functions for importing and exporting all kinds of formats (starting with csv, ending with xml)

Remembering that project, and being in quite a “time crisis” for the current project, i took the decision to turn once more to the old Excel, this time using JMeter sample records.

Now, a few words about how JMeter exports it’s samples. By default, JMeter will save between other samplers:

  • timestamp of the http request
  • response time
  • size
  • response code
  • response message
  • request name

This is actually pretty much what you should be interested in in a very basic scenario. You want to measure some averages, and see some graphs.

Now, basically, the concept behind measuring the response times, is grouping the request names with their response times, and measuring the averate response times. Suppose you have four requests, two of them doing book, the other ones doing login and logout

Using one thread, one user, and generating the following samples(request name, response time in ms):

  • login: 240
  • book:450
  • book:650
  • logout:200

you would have an average of (450+650)/2=550 ms for the “booking” request.

Using this concept, we can group requests in Excel by using Pivot Tables. Let’s go through the steps for doing that:

  1. Import JMeter results into EXCEL

    JMeter (version 2.3.4) can export in multiple formats, with xml being the native one, and csv being optional. I chose to export into XML (it offers more configurable options when exporting to xml and not csv). As i previously said, EXCEL can handle XML Import pretty well, so we will use that
    Using Excel 2007, go to “Data”. You Have a “Connections” icon in the toolbar. Click on it. Add a new connection from the window that just opened. Don’t get annoyed by the window that just opened, just select “search for other elements” from the buttons below, and then navigate to your xml file.
    Import everything, and then keep the following columns only (delete other columns, or hide them)

    • t (response time)
    • ts (timestamp)
    • request name
  2. Prepare your data for pivot tables

    The problem with JMeter is that it exports its timestamp in UNIX Format. You will need therefore to create an extra column, called “Timeline”, and use the following formula for converting data into date format (we will only use hours, minutes and seconds. The grouping of all samples will be made with the second being the smallest unit. It really does not make sense granulating it all over to milliseconds):
    =DATE(1970;1;1)+LINKS(B2;10)/86400
    where B2 is the column containing the JMeter timestamps.
    After this step, select the new column (timeline), and define its properties so that it returns only the hours, minutes and seconds. Click on the column, select the properties, and go to the last option in the first tab: “custom”. Select the following format: “hh:mm:ss”
    Ok, now you have your requests, your response times, and your timelines in readable format. On we go to the next step: Pivot Tables

  3. Generate Pivot Tables from JMeter Results

    Select all the columns in the worksheet. Go to “Table Tools” and select “Group with Pivot Tables”. Use a new worksheet for the Pivot Table. After Excel generates the new worksheet and the Pivot Table, you will have the columns available to select in the right side of the screen. (pivot table field list) Now do the following:

    • Drag and drop field “Request Name” to the legends (there are four boxes down there, use the upper right one)
    • Drag and drop field “Timeline” to the “Axis fields” (lower left)
    • Drag and drop “t” (or “Response Time” column) to the “values” field (lower right)
    • Select the “t” field in the lower right area, click on it, and modify the properties, so that the “Field Settings” point to “Average” instead of “Sum”
    • Now you can generate a “Chart” from the “Pivot Table Tools” > “Pivot chart”
    • In the pivot table, go to the  end of the rows. You have a row called “Totals”. Modify the field properties for each of the columns so that it calculates the average, and not the sum. Generating a simple chart out of this values is not the purpose of this article and should be REAAALY dumb simple

    There you have it. All you need to generate some graphs in EXCEL using JMETER Samples. The final results look pretty much like this:

  4. Pivot Charts from JMETER Results 

    JMeter Average Response Times for 4 Samplers

    JMeter Average Response Times for 4 Samplers

    Average Response Times Totals

    Average Response Times Totals

  5. Final words

    I strongly recommend using Jasper Reports for Reporting. Jasper Reports is incredibly strong, and in combination with a Jasper Report Server, it will do wonders. This is going to be one of my next topics.
    JMeter Reporting by using Pentaho Data Integration, IReport, JasperReports, MySQL and Jasper Report Server.

Cheers and enjoy,

Alex

About these ads
  1. Srinivas
    September 6, 2009 at 12:34 pm

    Hi,

    I am very glad to say BIG Thanks.

    Thanks a lot for the guidance in making the Reports using Excel. I’ve successfully game Demo of JMeter usage to my CMD today.

    Thank you so much.

  2. noodles26
    September 7, 2009 at 1:29 pm

    Glad to say i’m glad it helped :)
    Cheers,
    Alex

  3. Laurent
    December 9, 2009 at 2:46 pm

    Hi,

    You can skip the second step by adding the following line in the file jmeter.properties :
    jmeter.save.saveservice.timestamp_format=HH:mm:ss

    Laurent

    • Alex Ersenie
      December 10, 2009 at 9:40 am

      excellent. long over excel, but will save me a lot of trouble. Thanks

  4. Sylario
    June 11, 2010 at 2:32 pm

    Hi i’m using excel “where they decided to do strange icons menu without help>about so i cannot retrieve my version number” that must be some kind of office 2007.

    I struggle with your function : =DATE(1970;1;1)+LINKS(B2;10)/86400

    Excel says he does not know LINKS function (highlited after saying the function is not valid), what is it supposed to do?

    • June 11, 2010 at 2:43 pm

      Hi Sylario,
      as i am using a german version of excel, the LINKS function is nothing but the LEFT function in english. It is used to return the first character, or the first characters of a string. In this case, it will return the first 10 characters of the value stored in B2.
      I hope it is clear now.
      Best regards,
      Alex

  5. Sylario
    June 11, 2010 at 2:51 pm

    Thanks! It works fine now.

  6. Nirali Vyas
    June 20, 2010 at 12:18 am

    It was really helpful. Thank you.

  7. Ashfaque
    May 25, 2011 at 4:12 pm

    Hi,
    Can you plese tell me the unit of the average time displyed in aggregrate graph.
    when i worked it out on my application(ONLY FOR LOGIN THE USER) it showed total average time as 1834(i donno the unit)but when i did the same thing using a stop clock to record the same it showed 18.3 seconds..so can you please help
    thanks

  8. nazareen
    August 18, 2011 at 10:42 am

    how to use jmeter for desktop application performance testing

  9. Laurent
    March 7, 2012 at 9:52 am

    Hello,

    We started by using Excel as well to make some graphs but it required too much manual steps, so we now use gnuplot instead. We use cygwin so we can do bash scripting and use awk or perl to transform/extract data.
    We have wrote a small article here:
    http://labs.bsb.com/2011/02/load-testing-enterprise-applications-efficiently-23/

  10. André Andrade
    July 19, 2012 at 1:19 pm

    Useful and clean guide to simple usage. Thanks!

  11. September 16, 2012 at 1:06 am

    If some one needs expert view about blogging after that i
    advise him/her to visit this website, Keep up the nice work.

  12. Mike
    July 9, 2013 at 6:43 am

    Great article, thank you. But when speaking of the unix time stamp, I suggest doing the following:
    Just add this line:
    jmeter.save.saveservice.timestamp_format=HH:mm:ss
    to the end of the user.properties file (in the bin directory), and you won’t have any problems with Unix time any more.
    This is about the test results saved to a jtl file (when running in a non-gui mode).

    Here is my example:
    # Enable Proxy request debug
    #log_level.jmeter.protocol.http.proxy.HttpRequestHdr=DEBUG
    jmeter.save.saveservice.output_format=csv
    jmeter.save.saveservice.data_type=false
    jmeter.save.saveservice.label=true
    jmeter.save.saveservice.response_code=true
    jmeter.save.saveservice.response_data.on_error=false
    jmeter.save.saveservice.response_message=false
    jmeter.save.saveservice.successful=true
    jmeter.save.saveservice.thread_name=true
    jmeter.save.saveservice.time=true
    jmeter.save.saveservice.subresults=false
    jmeter.save.saveservice.assertions=false
    jmeter.save.saveservice.latency=true
    jmeter.save.saveservice.bytes=true
    jmeter.save.saveservice.hostname=true
    jmeter.save.saveservice.thread_counts=true
    jmeter.save.saveservice.sample_count=true
    jmeter.save.saveservice.response_message=false
    jmeter.save.saveservice.assertion_results_failure_message=false
    jmeter.save.saveservice.timestamp_format=HH:mm:ss
    jmeter.save.saveservice.default_delimiter=,
    jmeter.save.saveservice.print_field_names=true

  13. September 14, 2013 at 11:32 am

    No matter if some onne searches for his essential thing, so he/she needs to be available
    that in detail, therefore that thing is maintained over here.

  14. July 7, 2014 at 10:15 am

    After I initially left a comment I appear to have clicked on the -Notify me when new comments are added- checkbox
    and now every time a comment is added I receive four emails with the exact same
    comment. Is there an easy method you can remove me from that service?

    Kudos!

  1. No trackbacks yet.

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: