SSRS Record & Play testing

In my previous article I wrote about SSRS performance measurement. This time we will take a look at how we can make use of this data for our performance testing itself.

To test we would need the report, report path and the parameters; all of these we can get from the report server itself

Select catalog.name, [Catalog].Path,
ExecutionLog.Parameters
from ExecutionLog with (nolock)
inner join
 Catalog on Catalog.ItemID= ExecutionLog.ReportID

This is like our manual tests being recorded, lets see how we can use this for replaying this rest.

As you know, we can access SSRS reports directly from  the server using reportServer web service URL. Using this URL we can pass query strings too!

<iframe height="100px" width=100%" src="http://172.16.150.29/ReportServer/Pages/ReportViewer.aspx?#ReportPath/ReportName#&amp;rs:Command=Render&amp;#Parameters#"/>

Use excel to import the results and use =CONCATENATE(A2,B2,C2,D2,"""/>")  in excel to generate this HTML code. Now copy these line and place it between HTML tags and your page which can open all reports in one go is ready.

Open this HTML page from multiple machines or tabs; so that it generates sufficient load. Use report server ExecutionLog view to measure the performance of your test.

Popping up Fancybox from SSRS Report

When do I really need it? You might want user to take some action depending on the report you are showing up. Ex. You are generating a report for 3 products and its performance over a period, now you may want to put the product on the showcase.

What you really do is, call a page and pass the product id to it and user might want to add some comments and take some action.

How do I do it? Using Javascript. You create a text field and set the action on the text field as Go to URL. In the URL field add the expression as

="javascript:void(MyScriptMethod('"+First(Fields! ProdId.Value, "DataSet1")+"'))"

Now the method should be residing on the page which is hosting the report viewer control.

function MyScriptMethod(prodId) {
   
  // fancy box enabled link
    var link = $("#lnkProdEval").attr("href");
    
   //remove the old query string parameter   
    link = link.split('?')[0];
    
   //add a new parameter - product Id
    link = link + '?Id=' + prodId ;
    $("#lnkProdEva").attr('href', link);
  
  //now click the link through the code. 
  // Make sure you put this link in a invisible div, so user cannot
  // click it directly
   $("#lnkProdEval").click();
}

lnkProdEval is the ID of the Anchor, on which fancy box is enabled. You can get more detail about hooking a fancy box @ http://fancybox.net/howto

Compare SQL Databases

If you ever come across a situation where your application behaves differently with different DBs you have and not sure if any of the Stored procedures have changed...?
Following SQL script can be used to compare the Stored procedures.

select top SYSCOM_1.text, DB2.text 
from MyDATABASE_1.dbo.syscomments SYSCOM_1 
inner join 
sysobjects so on so.id=SYSCOM_1.id inner join 
(select SYSCOM_2.text,SYSCOM_2.id, so.name from MyDATABASE_2.dbo.syscomments SYSCOM_2inner join MyDATABASE_2.dbo.sysobjects so on so.id=SYSCOM_1.id )
DB2 on so.name = DB2.name COLLATE DATABASE_DEFAULT 
where DB2.text <> SYSCOM_1.text COLLATE DATABASE_DEFAULT