PS, in my quest for ways of manipulating data from SharePoint, I am looking into some third-party products. I will start by saying I don’t want to buy one, simply because I don’t want one more thing on the upgrade checklist. But, I am open to suggestions. Keep in mind, we develop systems outside of SharePoint, so we are only looking to extend SharePoint’s reach a little bit.
The week started with the alleged final step in our time reporting system, the Accounting View. We wanted what appeared to be a simple view, grouped by two columns and with totals on two others. Try as we might, we got totals without groups, or groups without totals. The later was the most frustrating because the “Total” operation was working, but it was performing a Count instead of a Sum. Research ensued to see if we missed an update, forgot to turn on a service, or simply didn’t understand the operation. Nope, our stuff looked just like the examples, it just didn’t work. “No problem”, I thought, “let’s just create an Access View.”
The Access View appeared promising at first, but something told us it wasn’t going to work. The example on Microsoft’s site clearly showed Access with a “Publish to SharePoint” option on the toolbar – ours said “Save to SharePoint”; cue the ominous music now. We quickly managed to get the report looking great, but it would not show up as a selectable view. Once again, we trotted out the usual suspects. This time we hit pay dirt; the Access services were not turned on. With those started, we tried and failed again. Then, my Systems Admin pointed out that we had to start these and one other service somewhere else. He made those changes but still no luck with the Access View. That’s OK, I hate Access anyway. Since we only run the report once a month, we will run it in Access this month and then setup the report using SQL Server Reporting Services. I hate SSRS too, but that’s because I pretty much hate anything that ever was advertised as a Report Writer. Programmers hate writing reports, but they hate using Report Writers even more. Programmers like writing Report Writers for other people to use.
While this “last little thing” was consuming my week, I tried to get back to the other SharePoint task on my list; setting up a demonstration of the ways SharePoint can work with back-end data. I set up a test database and a few tables on my development server. Then, I wired up a BDC connection and immediately fell into the “you don’t have permission to do this” trap. I knew I had done it before, but that was on a different test server, the one that crashed just before Christmas. No problem, my Systems Admin made all the appropriate entries and I was on my way. I also wanted to include a Data View Web Part example, but I discovered that, although SharePoint Designer can connect to SQL Server to create an External List, it can’t connect to create a Data Source. I’m still researching this one, so if you know the answer, please comment below. If you provide the answer and I ever meet you at an event, I will buy you several beers, I promise.
The good news is that we ended the week making progress on all fronts. The other good news is that our users seem to both understand that we are learning our way around new territory and they like the solution we are giving them.
If you found yourself reading this and thinking that I seem to dump all the hard stuff on my Systems Admin, you’re right, but not because I’m lazy. When SharePoint was my vision of the future, we ran a down and dirty installation. When we upgraded to SharePoint 2010, we knew SharePoint was a keeper and we installed everything the way you should install it. We also put an end to my just going out there and tweaking things based on a blog entry I read during the football game. I have a great Systems Administrator, and I have a great SharePoint developer working with me now, so we are trying to set ourselves up to operate according to plans, procedures, policies and rules. This might mean that problems take a little longer to solve, but when we solve them, we know we won’t have to solve them again.