Calculating visit length by first and last visit time in SSRS

Writing a blog is an exhausting experience, most of the times you would be writing your blogs at the expense of your sleep or after a hard day’s work. But then there is a very fulfilling feeling, a joy that you get when you come to know that your work is helping people all around the world. I am sure most of the authors get a huge kick by the number of hits their blog receives and by the number of different countries visitors come from. For collecting such statistics, I use StatCounter and I rate it one of the best free add-ons for getting real-time web stats. There are a lot of features and one among the many is the Visit Length stat. Let me quote the definition of Visit Length from the StatCounter site itself – “ The time between when a visitor accesses your first webpage of their visit, and when they access the last is what we call the ‘Visit Length’. Unfortunately it is not possible to detect with the Standard StatCounter Project when they left your website for a different website. Instead we take the time they accessed your last webpage of their visit as their ‘exit time’. Although this isn’t 100% accurate it is very close and nonetheless provides a valuable insight into your visitors. Based on this information you can see how much ‘pull’ and ‘interest’ your website is generating for your visitors. If you have a low ‘Visit Length’ you may want to think up more ways to encourage your visitors to stay around for longer “. Now you might be wondering why I am advertising StatCounter here ( nopes, I don’t get paid by them, I am just a happy customer ). The actual reason for all this introduction was to introduce you to a scenario where we have data of the visits stored and we need to calculate the visit length in a SSRS report. Let me first get the data before I explain the scenario. For getting the data having the blog visitor information, I will be using a random page of my blog’s StatCounter and then importing the data into the database. A sample page from StatCounter is given below:- After importing the data, I created a report which will show the visitor info as shown below:- Problem Now that the source data is ready, I will explain the requirement. We need to find the first visit time and last visit time for the same visitor from the above data. A visitor is considered to be the same if he has the same Country, Place, Browser, Resolution and back to back entries. For eg, the first 2 entries for Coimbatore, India is considered to be the same visitor as it has back to back entries and also all the other fields except time is the same. Using the same logic, Mountain View, USA is not considered to be the same visitor in the last and third last rows as it has no back to back entries even though all the other columns are same. If there is no back to back entry, the last visit time would be the same as the first visit time. The required output would look like shown below:- Solution The very first thing that comes to our mind on seeing such a problem is to group by all the fields except time and then get the first and last Time for that group. However, we can’t do that here as a) we need to consider visitors that do not have back to back visits as separate visitors. A Group By clause will group all the visitors together even if it doesn’t have back to back entries. In the above example, it will group the visitor from Mountain View as one entry even though we require it as two. b) it will group entries from different dates also as one entry. So suppose one visitor came on the first and last day of the month, he would be grouped as one entry and his visit length would become 1 month which would be incorrect. After brainstorming and googling upon this for a lot of time, I stumbled upon a valuable post in the MSDN forums by Raymond Lee (moderator) in which he posts a custom code for achieving this type of grouping. Follow the steps below to apply his solution in our problem:- 1) Go to the Design section of the report and press Alt+R. Select Report properties from the menu and select the code tab. Paste the code given below there Dim Address As System.Collections.Hashtable
Dim sumGroup as Integer

Function MyFunc(ByVal _Address As Object) As integer
Dim flag as integer
If (Address Is Nothing) Then
Address = New System.Collections.Hashtable
End If
If (Not Address .Contains(_Address)) Then
Address .clear()
sumGroup = sumGroup + 1
Address .Add(_Address, nothing)
else
sumGroup = sumGroup
End If
MyFunc = sumGroup
End Function 2) Drag and drop a table into the design and add all the required fields except time into the table. Make a group for the fields with group expression as given below =Code.MyFunc(Fields!Place.Value+Fields!Country.Value+Fields!Browser.Value+Fields!Resolution.Value) The argument for the function would be all the fields on the basis of which we need to check if there is a back to back entry. In our case, it is all the fields except time and we are concatenating it as one field and sending it as input to the function. 3) Once this is done, we should be having the table in the design section looking as shown below Notice that there is only one group and the group name is Group2. 4) Now all we need to do is to get the first and last visit time for each entry which is easily done with the below expression =first(Fields!Time.Value,"Group2")+" – "+last(Fields!Time.Value,"Group2") Take care to substitute Group2 in the above expression with the group name of your table. 5) With a little bit of tidying up and colouring, we should be able to attain the results shown below