SharePoint with Attitude…

If you’re implementing branching logic, to submit a SharePoint survey you have to navigate to the last question and click the Finish button. This is how it behaves, it is designed so that the Finish button only appears in the last question, before that, you’re only allowed to save the survey.

Saving the survey make it visible only to the owner of the survey, not even for the system account, and this is because when the survey is saved, it actually does not exist in the list yet. If you make a test and use the RunWithElevatedPrivileges to iterate in the survey list, you’ll notice that you won’t find a survey that has been saved by another user.

For one of my projects I had a requirement to have the Finish button on every question, because it was not required for the user to respond to all questions, and the user needed to finish the survey at anytime.

So, I added a new button to the survey question page, this button will contain the Finish functionality. But what is the Finish functionality? There is not Finish or submit option for the list item using the object model, when is it considered as finished? When I complete all questions? No, because some of them can be blank, Is only when you are on the last question that you see the Finish button, interesting.

I consulted my good friend “Reflector” to see what is going on in there, the results: Survey Items have an internal method: “CheckIn()”, when you are in the last question and click finish, the item is checked in and actually created in the list. So bad that this Check in method is not public… anyways this is how I implemented the checkin for my new Finish button on the question page:

One of the clients that I was working with needed some extensibility to the out of the box SharePoint Usage Reports.

The data displayed on the reports didn’t meet their requirements, first, they needed to match the data with the user data in SAP in order to get the reports by Region and other parameters, and second, they needed historical data, and not only the last 30 days of information.

The first requirement was not a problem, and at the beginning we didn’t think the second would be a problem either, but oh we were wrong.

Using the out of the box usage reports provided by SharePoint was of course not even an option, we needed a way to get the data and create our own reports.

I spent some time doing research and this is what I found: 

• Using the Object Model. The SPWeb object contains the method GetUsageData(). This is the method called by the Usage Repor pages in sharePoint. It Returns a table that contains information about the usage of a Web site, based on a time interval. Unfurtunately there are only two options for the time interval, either today or last month. So, no way to get more than 30 days of information using the Object model.
• Web Services. No web service exposes usage report data, so I thought about using owssvr.dll, but again, you can only get the last 30 days of data.

Usage Report data is stored in log files in the IIS and also in the Shared Services database, and not only for the last 30 days, but for all the time since the usage reports were activated, so how is it possible that SharePoint doesn’t have a way to expose this data? Well, that’s how the world works.

Working directly with the SharePoint Databases is not supported by Microsoft, so it seemed that my only option was to create a program to read the IIS files. Have you taken a look at those files? Well, they are quite a mess and I didn’t want to have to do anything with them. So I consulted one of my closest friends: “Reflector”, and once again he gave me the solution I was looking for

I found out that the PortalContext object contains a reference to the Shared Services Database through the property “AnalyticsSqlSession”, using this property you get a connection to the database without having to worry about server name, database name, etc.

Since all the usage data is stored in the Shared Services database this was definitely helpful, but let’s not forget that working directly with the databases is not supported, and although I was not accessing directly the database using SQL server, I was however connecting to it and making some queries (READ ONLY).

We presented our solution to Microsoft, and to our surprise, the solution was accepted because somehow we were using the Object Model to get the data, nice!

Having the solution and Microsoft approval, everything got easier. These are the tables in the database that are related to the usage reports:

As you can see, everything is in there! We can do the queries we want and get the data we are looking for.

Going back to the AnalyticsSqlSession property of the PortalContext object, this is how I got it:

Worked like a charm!