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.

How bad is your SSRS report performance?

In SSRS, the execution data is stored in a table called ExecutionLogStorage table in ReportServer database. You can view this data using the views provided by SSRS named ExecutionLog3, 2 and ExecutionLog. All these views read the data from same table, with only difference is that they interpret data and give you different views.

Following query will give you data on which report sucks.

use ReportServer
go
-- overall suckers
Select Catalog.Name,
TimeDataRetrieval+TimeProcessing+TimeRendering as TotalTime,
 ExecutionLog.Format, ExecutionLog.[Parameters],  
 ExecutionLog.TimeDataRetrieval,
 ExecutionLog.TimeProcessing, TimeRendering,
 TimeStart, TimeEnd, Status, ByteCount, ExecutionLog.[RowCount]
from  ExecutionLog with (nolock) 
inner join Catalog on Catalog.ItemID= ExecutionLog.ReportID
order by TotalTime DESC

-- Query sucks
Select  Catalog.Name,
TimeDataRetrieval+TimeProcessing+TimeRendering as TotalTime,
 ExecutionLog.Format, ExecutionLog.[Parameters],  
 ExecutionLog.TimeDataRetrieval,
 ExecutionLog.TimeProcessing, TimeRendering,
 TimeStart, TimeEnd, Status, ByteCount, ExecutionLog.[RowCount]
from ExecutionLog with (nolock) 
inner join Catalog on Catalog.ItemID= ExecutionLog.ReportID
order by TimeDataRetrieval DESC

-- SSRS Processing sucks
Select  Catalog.Name,
TimeDataRetrieval+TimeProcessing+TimeRendering as TotalTime,
 ExecutionLog.Format, ExecutionLog.[Parameters],  
 ExecutionLog.TimeDataRetrieval,
 ExecutionLog.TimeProcessing, TimeRendering,
 TimeStart, TimeEnd, Status, ByteCount, ExecutionLog.[RowCount]
from ExecutionLog with (nolock) 
inner join Catalog on Catalog.ItemID= ExecutionLog.ReportID
order by TimeProcessing DESC

-- SSRS Rendering sucks
Select  Catalog.Name,
TimeDataRetrieval+TimeProcessing+TimeRendering as TotalTime,
 ExecutionLog.Format, ExecutionLog.[Parameters],  
 ExecutionLog.TimeDataRetrieval,
 ExecutionLog.TimeProcessing, TimeRendering,
 TimeStart, TimeEnd, Status, ByteCount, ExecutionLog.[RowCount]
from ExecutionLog with (nolock) 
inner join Catalog on Catalog.ItemID= ExecutionLog.ReportID
order by TimeProcessing DESC

Very good UML Video tutorial

A very nice UML Video tutorial to start with.

Tutorial starts with basics and goes all the way. I would recommend you downloading Visual Paradigm for UML along http://www.visual-paradigm.com/product/vpuml/. With this tutor you can learn both UML as well as the tool.  VP can do a lot more, but very good start for both UML and visual paradigm is this.

Special thanks to PatriaLivreBrasil.

*don’t go by the first few videos, it might look slow and boring. Rest of the tutorials are really good.

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

Print specific ASP.Net control from a Web Page

I ran into a requirement, where i had to print only asp.net grid. And this had to work on all browsers. I had similar requirement to print a SSRS report on non IE browser.
So, I came up with this solution(rather, i would call it as workaround). Approach is to select the inner html of the control and print only this area. To do so, i am selecting the inner html  from the control and setting it as a HTML source of an IFRAME which is of size 0x0 and printing it immediately.
Following is the Javascript from the aspx page to this. I have taken reportviewer control for now.

If you are using this for reportviewer, just keep in mind that you are only printing the current page instead of all the visible pages.

I still need to work on getting the styles from the parent page and applying it on the IFRAME so that we dont miss any style information.

Error:Microsoft.ReportingServices.Diagnostics.Utilities.SecureConnectionRequiredException: The operation you are attempting requires a secure connection (HTTPS)

I had this problem from long time and was in such a urgency that i started working with HTTPS. But finally i had to move my machine out of the domain, and left with only option of fixing it.

First step i did was went to “C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles”, which you have already figured out i guess. At the latest log file, you will see this error listed. Which means that RS is configured only for HTTPS and not for HTTP.

To resolve… lets change it.

For not using SSL you need to modify following section in reportserver.config file, at C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer and change

<Add Key="SecureConnectionLevel" Value="2"/>

entry with value from 2 to “0”

How to change Win 7 logon screen

Windows 7 OS enables OEMs to set their own Log-on screen. So if you want to customize yours, you can make use of this registry setting.

• First you need to enable the custom wallpaper flag in registry. To do so, navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Authentication\LogonUI\Background

 

• Now set the DWORD value OEMackground to 1, by default it will be 0.
• Now you have enabled the custom background feature, to select the defaultbackground navigate to c:\Windows\System32\oobe\
• Create a new folder here called info then one more inside called backgrounds
• Copy a JPG file to this folder now and  call it backgroundDefault.JPG. Final structure should look something like this…
•  

Win + L, now you see it!!!