As most of you all know report
plays major role in any of the project, as business giant he don’t dig into and
look he just look for end result i.e. Report. To generate the Excel report from
SoapUI you need to just follow the below steps as i mentioned.
Create Test Data for SendSMSToIndia
request as follows. [Here I have taken
SendSMS WebServices for example.
(http://www.webservicex.net/SendSMS.asmx?WSDL)]
As all of you know how to do Data Driven testing as i
mentioned in my previous blog. In the same way you have to create Test data in
excel file.
Once you are ready with Test Data creation, then create one
more excel file as follows for DataSink Report.
Here I have created
the sample Report format as shown in the below screen shot. I have given Sheet
name as “SendSMSReport” and saved it in the local system Driver with the name as Report_SendSMSToIndia.xls,
here one more important note is while saving the excel file after creating the
test data we should save as “.xls”and SoapUI is not support any
other format other than .xls.
Now it’s time to create SoapUI
project in SoapUI Pro tool as shown in below,
Make sure you have
created the project and added the Request to Test Suite, to Test Case, to Test
Step and DataSource, DataSink, Groovy Script and DataSource Loop should be as
shown in below screen shots.
Now add Data Sink
to the Test Step as shown in the below screenshot. To add Right Click on the
Test Step and Select Add Step then select DataSink option.
Now the DataSink step will be added to your Test Step as
show in below Screen Shot.
In the same way Add Groovy Step to the Test Steps which is
used to validate or verify our expected result with actual result and same
result can be used to update in DataSink.
In below screen shot you can see the
hierarchy of the Test Steps, make sure in your project should be in the same hierarchy
as same as below.
DataSource:- Is Use to reads test data from external source(Excel, File,
GRID, XML, Database) into properties of request or can be used for validation.
DataSource Loop:- Is Use to loop or iterate the DataSource till the last row. Here
we have select the DataSource and target step where we have to provide our
input data.[will be explain in below section].
DataSink:- Is used to write Number of propertied to the External source.
GroovyScript:- Is used to write specific script for validation.
Now Open the DataSink by double
clicking on it or just Right click and select open editor you will see below
DataSink editor. There you can see DataSink with Dropdown list in the
screenshot.
Now click on the Dropdown button. You
will see list of Option to generate the report in different format.
In that select Excel Option from the list to generate the
report in the Excel format as you created the sample format above. Once you
select the Excel option you will see below screenshot.
In above Screenshot you can see File, Out File, Worksheet
and Start at Cell with (Re)Generate Properties with One green button. Here all
the fields are explained below.
File :- This
is the file where which imports the sample format which we created above.
Out File :-
This file is used to Update our results or reports from Datasink and stores
into the specified file at under given
path.
Worksheet :-
The worksheet name of the file to export the result
Start at
Cell :- From which Cell you want to export the result.
Change the Option Start at Cell Default
value from ‘A1’ to ‘A2’ because in our excel sheet cell A1 is our headings and
our actual report updates starts from cell A2 hence it should be A2.
Now To import al the headers which are created in excel file to
DataSink properties click on the browse button locate the Excel file path which
you created above, do it same for File and Out File, once you located you will
see as same as below screenshot.
Now click on the ‘Green’ button (Re)Generate Properties:. Once you click on this button you will one more popup window named
‘Manage properties’ as shown in below screenshot.
In the Insert row number: enter value as 1(one) because in our
excel file we have just created on row that too header so we have to give as 1
only. Now click on ‘OK’ button.
Now you will see in DataSink left pane editor all the headers we
have created in the excel file are automatically created see below screen shot
for more.
And also see the same property names are displaying in the below
section in above screenshot.
Now Open the Groovy Script by double
clicking on it or just Right click and select open editor you will see below
Groovy Script editor. Here you can retrieve the data from any of the test step
within the Test Case or from Projects and also from Other Test Suite and Test
Cases with some limitations we will discuss this topic later.
Now follow the steps below to
retrieve the data or result or value from the DataSource, From Response
received for request.
Step 1:- Right Click on the Groovy
Script Editor,
Step 2:- Goto Get Data click on
DataSource select the property name which you want to compare with the actual
result, here i am comparing the Actual and expected Status of the message.
Once you
select the Expected_Status property then you will see one popup as below, click
ok or you can provide your own name or just click ok.
Then SoapUI
will generate code for as in below screen shot.
Now select
the value from the response as you selected property value for DataSource.
Follow the
steps..
Again right
click on the Groovy Script editor as shown in below screenshot and goto
“SendSMSToIndia - Request” click on it and click on Property[Response].
Now will see
one more Select XPath window there
you can see response which has all the fields, in that select the required
field and click on OK.
Then you
will see popup as shown in below screen shot click on, then SoapUI will
generate code for you as shown in below screenshot,
Now write
‘IF.. Else’ condition to compare the two Actual and expected status value. If
you know java little bit then you can write it, for those who don’t know here
it is.
Now moving
to Data Sink above you have created Data Sink step and also added excel report
format. Now associate the values from Data Source, from Response or from Groovy
Script result.
Follow the
steps below,
Open the
Data Sink editor you can at right hand side of the pane four properties as
TC.No :- The
test case number we are taking it from the DataSource step property name
‘TC.No’
Expected
Result :- Expected Result we are taking it from the DataSource step property
name
‘Expected_Status’
Actual
Result :- Actual result we are taking from the Step ‘SendSMSToIndia - Request 1’
as we
taken value in Groovy Script in same way.
Test Result
:- Test result we are taking it from the Groovy Script result.
For more
detailed follow the steps,
For TC.No =
Right click and follow the Screen shot.
For Expected
Result = Right click and follow the Screen shot.
For Actual
Result = Right click and follow the Screen shot.
Click OK
once you selected the required field.
For Test Result = Right click and follow the Screen shot.
Now all set
to run the Test Case for all the test data in the DataSource to generate the
Report in excel format. Running the Test case you have to open the Test Case
Running window you can double click on the Test Case or Right click and select
‘Show Test Case Editor’ option from the menu.
And one more important note is the
steps in the Test Steps should be in the same arrangement. Like DataSource
should be at first Step and Request in Second, Groovy Script and Data Sink and
DataSource Loop should be always last.
Once execution start you can see the logs in
Test Case Log pane ate below and a gree bar with “Finished”message at the top
indicates test case execution completed for the all the rows in the DataSource
and also Report is also generated in the path specified for Outfile.
The report will be looks like as below
Screenshot.
Now you are ready with custom report.
That’s all Folks.
Thanks. Excellent Blog. Need some more information on mock services and mock response creation in SoapUI.
ReplyDeleteSunnyboy, Was an very good blog and was very informative. It helped me a lot in learning usage of SOAPUI..
ReplyDelete--SAM
Thanks,explained very clearly.
ReplyDeleteGreat Buddy!!!!
ReplyDeleteDid you get a chance to test the same on linux ?
ReplyDeleteHi Anonymous,
DeleteSorry, I worked and tried only on Windows not on linux and uinx OS.
This post solves my problem. Thank you very much for a clear explanation!!
ReplyDeleteThank You Mate..
DeleteGreat post, thanks.
ReplyDeleteBut I don't know why when I try this in my machine I faced two issues:
1.The header of my report in excel file is disappeared after running the test case if I leave the field "File" in data sink configuration panel blank
2.No values are generated in my excel file after running the test case if I input the path for "File" is the same with "Out File" field.
Anyone can help?
Hi, I sent email. Please check.
DeleteHi,
ReplyDeletei am not able to see TC. No as you shown in the screen shot..
For more detailed follow the steps,
For TC.No = Right click and follow the Screen shot.
can you please help
Hi Kishor,
DeleteThanks for your time for reading this blog,
As i mentioned in my post, please follow the steps correctly as it is, for 'TC.NO' you need to create this parameter in the DataSource, then call that parameter in your DataSink, as it shown in the post,
Hi, Thank you for the good post. I followed all your steps, for some reason system saving only last record to datasink xls spreadsheet. Can you help me on this.
ReplyDeleteThanks
Sorry for my post, I am able to resolve above issue (not saving all records to spread sheet). I forgot to provide Worksheet name in DataSink, that is causing system to overwrite previous record.
DeleteThank You
how to get all test suit report in single xls
ReplyDeleteHi Thanks for your time for reading my blog,
DeleteUse the different sheet in excel file in each Data Sink report, you will get all the test suite result in one single file.
Regards,
SunnyBoy
This comment has been removed by the author.
ReplyDeleteMy groovy script is returning Fail all the time.
ReplyDeletehere is the snippet
if((expectedRes).equals(actualRes))
{
return "PASS"
}
else
{
return "FAIL"
}
I confirmed that it should pass by pulling both the expectedRes and actualRes into the excel using data sink.
Can you please help
Hi Thanks for your time for reading my blog.
Deleteplease check the what value you are getting in both variable, and check you are comparing the values which you expecting.
log.info(expectedRes)
log.info(actualRes)
verify both are returing same value then your test is pass, if not check the value which you are reading. if not please feel free to post your comments.
Regards,
SunnyBoy
Hi Sunny thanks for this useful blog.
ReplyDeleteWill you be able to help me, I'm new to datasink. My datasink does not populate all the headers/columns i created in the sample report file. And instead it only populates the first column and creates 3 property duplicates.
I'm testing a different web service and i want my output file to have the request and response xmls used. So my Headers are GetConWS Reqeust and GetConWS Response.
Hi,
DeleteThanks for your time to read my blog.
I have a small question here, what are the headres you have in your sample file i mean Inputfile.
provide same file path in Inputfile and Outfile in Datasink and put A2 in Start at cell, and next click on "Generate Properties" button, next enter 1 in "Insert Row Number" and click OK.
now you will see all the headers are coming in datasink.
try this again, if you not get please free to reply me..
hi Sunny, thanks for your immediate response.
DeleteIm still unable to generate the header name as the property. it keeps on generating only the first header name with duplicates. what i just did is create my own property names and did not click on "Generate Properties" since it will just destroy the properties i have manually set and i just run the test suite and it works.
I'm not sure if its soapui related problem. I will probably try using the "Generate Properties" button again. Thanks again for this blog.
By the way, is there any required cell format for the Header name? My header names are GetConWS Request and GetConWS Response.
DeleteIs there a way to do the same in SOAP UI 5.0 (not SOAP UI Pro)
ReplyDeleteHi Sunny,
ReplyDeleteI am Praveen. I am very much new to the SoapUI, and I know about Data-Driven testing. But I want to enhance the same with Groovy scripts. Can you please help me by guiding me on how to do this?
Hello,
DeleteFollow the link below for your query.
http://lgsofttest.blogspot.no/2014/06/datadriven-testing-in-soapui-open.html
Regards,
SunnyBoy
Hi Sunny,
ReplyDeleteI read your blog it very good and useful. I have one question for me datasource is jdbc and datasink is excel can I use your method?
Hello,
DeleteYes you can use, only thing is you need to read the table values cell by cell and feed to request if you are using jdbc as datasource, and rest of the things are same.
Regards,
SunnyBoy
Ok thank you sunny
DeleteNice Info..Thanks.. Can you Please help in capturing responses for all the steps of particular test case.
ReplyDelete