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 – “
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:-
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
If (Not Address .Contains(_Address)) Then
sumGroup = sumGroup + 1
Address .Add(_Address, nothing)
sumGroup = sumGroup
MyFunc = sumGroup
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
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