Running WebDAV on your WFEs

So I’m going to start this blog post with a rant about SSRS, make reports outputtable to Excel 2007 format!

So with that said, we encountered a question poised to us from some of our users, “we have a report that is generated from SSRS that’s send to a file share. This has some KPI information we use in Sharepoint. It’s a manual process to get it converted and uploaded. Is there anything you can do to simplify this?”

If the SSRS was configured in Sharepoint Integration Mode, our lives would have been much simpler, but it wasn’t, so this is what we did.

AutoIT to the rescue. We created a script that will map 2 network drives, one to the network share where the files is sent from SSRS and the other to the Sharepoint document library where the file is to reside. Oh snap, can’t do a WebDAV connection to your Document Library in a SSL environment from the server! What to do?

We scratched our heads for a while and this is what we did, configured an AAM to point from localhost to our Sharepoint address. In IIS we added an additional entry in the advanced web site identification screen for the Sharepoint website that uses SSL. Then if we opened a command prompt and did a net use Z: \\localhost\sites\site\doclib, a successful mapped drive is created. Score!

After the drives are mapped with the AutoIT script, we excute a CMD file that copies the file from the file server to a temp directory, executes a VB script to convert the file to Excel 2007, copies the file to Sharepoint, then unmaps the drives and deletes the local files.

If you’d like to see examples of the script, let me know. What a pain in the…

Plurk This Post Buzz This Post Delicious Digg This Post Stumble This Post

Request for Sharepoint developer bloggers

I’ve got a request for the Sharepoint Developers out there that blog. A blog article or series I’d like to see is a simplified step by step process on how to take a simplistic javascript content editor web part solution and turn this into a globally deployable web part with a configuration screen when in edit mode.

We see a lot of good CEWP solutions out there that serve a particular function for a particular user. What would be great to see in detail is how to take that idea, make it user definable without having to crack open the javascript and edit settings, package it, and make it into a WSP file for global farm deployment.

Plurk This Post Buzz This Post Delicious Digg This Post Stumble This Post

Using authenticated RSS feed to Serve Public Data

I can only a little bit about this project now since we haven’t completed it but it’s really neat.

Currently Departmental phone numbers are tracked on a Sharepoint list and then once a year, formatted and sent off to the printers for publication. Some people decided we should make this information publicly accessible in an online version. The problem is the Sharepoint data lies in an authenticated NTLM environment, how do we get it out there for the public?

We, being Brian and I, created a simple AutoIT script that fetches the RSS feed of the list and saves it as an XML file. We added a service account to the Sharepoint site as a reader so it can access the information. The AutoIT runs as this user on a scheduled basis on a web server. With the XML file saved, it is passed through a ColdFusion script that parses the XML data to get the information we need and writes it into a SQL database. When users update a number in the list, the RSS feed sends this information along, the script gets it, processes it and dumps it into the database.

It’s probably a round about method of doing it and I’m sure there’s a more elegant Object Model method for this. As a non-developer, I relied on Brian’s ColdFusion ability to handle the data conversion.

We had a student programmer code an interface and it’s going to be rolled into our publicly searchable data soon.

Plurk This Post Buzz This Post Delicious Digg This Post Stumble This Post

Sharepoint Saturday Raleigh registration open

For all maybe 10 of my readers in the North Carolinas, registration for Sharepoint Saturday Raleigh is now open! If you’d like to attend, please go register, these events fill up quickly so be sure to sign up early.

Some great speakers are lined up and am looking forward to meeting them. Some I have already met from SPS DC and Charlotte. Really looking forward to the Sharepoint 2010 session as the cover will have been blown off the NDA because of the Sharepoint Conference.

I’ll be a volunteer helping out and sitting in on some sessions. Hope to see you there!

Plurk This Post Buzz This Post Delicious Digg This Post Stumble This Post

Removing HTML Person Group Fields in Sharepoint

Over on Stump the Panel, Sasha wanted to change the message being displayed on a Tasks list when no one had a task assigned. The hyperlinks for the Assigned To user on the DispForm and EditForm.aspx pages should also be removed.

First, the Disp/Edit forms. In order to make changes to these pages, we need to employ the tool pane URL hacking method. You’ll need to have a tasks list populated with some data or you won’t be able to see this in action. Click on one of the items to go to the DispForm page. Employ the tool pane hack by appending &PageView=Shared&ToolPaneView=2 to the end of the URL in the address bar and hit Enter. Success, we’re in Edit mode.

Add a content editor web part. Click the source editor button and paste in the following:

Substitute a local path to jQuery if it exists in your environment. You may be thinking to yourself that this script looks familiar, well it should. It is the same script from the employee training admin page modifications only repurposed slightly. I have changed the selector to ms-formbody to isolate the HTML Assigned To field. This makes the hyperlink disappear.

Next step, change DispForm to EditForm in the URL. Add a content editor web part to the page like before. Click the source editor and add the following into it:

Now the underline will be gone from the people picker control.

The original problem was solved by converting the view to an XSLT data view and entering in the default message to be displayed when no items were found. Then Sasha noted that the Assigned To name hyperlink should be removed. This again involved more recycled jQuery code. The XSLT Code for the Assigned To filed looks like this, note we added person as the selector:

<td class="{$IDADW2OC} person">
	<xsl:choose>
		<xsl:when test="@AssignedTo=''"></xsl:when>
	<xsl:otherwise><xsl:value-of select="@AssignedTo" disable-output-escaping="yes" /></xsl:otherwise>
	</xsl:choose>
</td>

And the jQuery to remove the HTML:

Plurk This Post Buzz This Post Delicious Digg This Post Stumble This Post

Access 2007 and sharing data

It’s been a while since I’ve posted something. The last few weeks were extremely busy with your avergae run of the mill Sharepoint tasks, answering questions, provisioning sites, working in our new test environment.

This week we had a data access problem to tackle. Someone wanted a database on a SQL server. This database was going to be linked to from Access and needed to be accessed by people off site and or not affiliated with us. No easy task with VPN and security concerns. We thought “What about Sharepoint?”

My initial thought was ok, ya, maybe let’s see how it works. I knew accdb files could be uploaded unrestricted to Sharepoint, that’s all well and good. I decided to publish the database to Sharepoint just to see what would happen. The tables were created into lists automagically, pretty slick. The database I was working with was pretty basic so I obtained 2 more robust databases and published these.

Again, my tables were converted to lists and I still had full functionality of the Access forms, reports, switchboards, and queries. This was pretty awesome.

By using Access as the database engine instead of SQL Server, we can provide a database for users to share data and not have to worry about firewall rules and VPN accounts. We can create accounts specifically for user not affiliated with the university to get Sharepoint access already, why not utilize that functionality?

Plurk This Post Buzz This Post Delicious Digg This Post Stumble This Post

Setting read only fields on an Editform.aspx page

So I was working on a proof of concept assignment that was relatively simple, but thinking about it there was an inherent security issue. The idea for this Sharepoint site is to have a list where someone would register for a research topic. They only get to choose 1 topic. I was going to go about it in a simple way since it’s a POC after all, so it’s just a 2 list solution with a workflow.

All the research topics are entered on one list, and users will just add their name to the desired topic and save the item back to the list, where Ill do some workflow things and remove it from the list of available topics. Since users have edit rights, I removed the toolbar so they can’t delete it on my custom EditForm.aspx page. The big security hoop is them changing the actual content of the topic and or description.

This is where my simple solution comes into play. The EditForm.aspx page renders code that looks like this for the Description field.

Simply change the ControlMode from Edit to Display, and you have a Read Only field on your Edit page. No jQuery, Javascript, or additional CSS required.

Plurk This Post Buzz This Post Delicious Digg This Post Stumble This Post

Page viewer web part and UNC paths

No it isn’t what you think, I’m still at ECU and not a Tar Heel. This post comes about from an internal project that I had done and now we are reworking. A bit of history, we are trying out using Sharepoint to serve as a file server for various software packages.

Some concerns were raised about performance because these large files live in the content databases, which is valid I suppose. So I have been experimenting with trying to find ways to deliver the files from a different file server so the files stay outside the Sharepoint databases. Easy, I’ll use the page viewer web part and link to the file on the file server, problem solved. Or so I thought.

IE is the only browser that will allow UNC connections in a page viewer web part and serve up the file. Firefox, Chrome, and Safari (horray for multi-platform environment) show the file path but don’t serve it. Searching for ideas on how to deliver the files, I turned to Tony and Brian, two web gurus I work with.

Trial and error later with little success, Brian had an idea to use AutoIt to build an installer with the UNC path embedded. This is then uploaded into a Sharepoint document library and linked to. This worked as desired. After some tweaking, we developed a little application that was all variable based and allowed us to create exe files on the fly. That AutoIt code is as follows:

Dim $uncpath;
 
$uncpath = InputBox("UNC Path", "Enter the UNC Path of the EXE");
If(@error = 1) Then
	Exit;
EndIf	
 
Dim $fileToWrite;
Dim $fileHandle;
$fileToWrite = InputBox("File to Create", "Enter the name of the desired executable (w/o extension)", "c:\fileName");
 
If(@error = 1) Then
	Exit;
EndIf	
 
$fileHandle = FileOpen($fileToWrite & ".au3", 2);
 
If($fileHandle = -1) Then
	MsgBox(16, "File Error", "Cannot open file: " & $fileToWrite );
 
	Exit;
EndIf	
 
 
FileWriteLine($fileHandle, "If(FileExists(""" &  $uncpath & """)) Then");
FileWriteLine($fileHandle, "MsgBox(64, 'Please Wait', 'Loading setup files.  Please wait.', 20);");
FileWriteLine($fileHandle, "	Run(""" & $uncpath & """);");
FileWriteLine($fileHandle, "Else");
FileWriteLine($fileHandle, "	MsgBox(16, 'Sorry', 'You are either off-campus or do not have permission to access this program.')");
FileWriteLine($fileHandle, "EndIf");
FileClose($fileHandle);
 
Run("C:\Program Files\AutoIt3\Aut2Exe\aut2exe.exe /in " & $fileToWrite & ".au3 /out " & $fileToWrite & ".exe /icon c:\jr.ico ");

The advantage of this is that small files (280k vs 700 mb) can be uploaded to Sharepoint which will then go out and grab the actual setup files for the application to install. While StoragePoint would be great to have to remedy this, it isn’t available due to fiscal responsibility to our budget.

Plurk This Post Buzz This Post Delicious Digg This Post Stumble This Post

Podcasting Kit

We recently got a new test server in our department, my initial thought was to install WSS on this so I could test solutions in that environment. Instead I installed MOSS standard with SP2 and applied the podcasting kit.

My first installation did not work at all. This was no fault of PKS but my own. This was the first install I had done of MOSS so I didn’t have all the service accounts and app pool accounts right. After getting those all configured, PKS deployed successfully and relatively easily. I tried it out with a few videos and WMA files I had, and I was impressed. I can see this as possibly being rolled out here.

My only concern so far has been opening this up to test with. The security permissioning in the documentation didn’t seem to jive with the install. I’d like to get all the permissions squared away before testing. If anyone has a matrix of what groups should have access to what, I’d love to see it. Or you can reply to the discussion thread I opened on the PKS site.

Plurk This Post Buzz This Post Delicious Digg This Post Stumble This Post

Employee Training template, official release!

After developing this custom template for an internal project, I knew I would have to do some work to be able to recreate and reuse this internally. After a couple weeks of tinkering, deploying and troubleshooting, I have what I believe to be an easily deployable template to manage internal registrations for employee training. After about 10 to 15 minutes of simple configurations, you can have this up and running in your environment.

All that is needed is Sharepoint Designer access and server access if you wish to deploy the STP file as a top level site in a collection. If not, the file can be uploaded to the Site Template Gallery on an existing collection and deployed as a new site.

This should be fully WSS compliant, if you have MOSS you can take advantage of the audience features and security trim things a little cleaner. Link to the original template, link to Dessie’s updates and bug fixes.

I have packaged this into 2 different STP files. Both contain configuration instructions and deployment batch files. One contains HTML emails the other contains more “stock” Sharepoint Designer work flow emails. Choose which ever you’d like.

• Training Template Non-HTML Emails version 1.1
• Training Template Non-HTML Emails version 1.2

P.S. – This looks great with the new Event Planning theme that was released by Microsoft. If you go that route, you’ll want to add some style tags to change the page titles to black so they are readable (and the reason why I included Heather Solomon’s clean calendar CSS file, that theme calendar isn’t styled).

Edit:7/15/09
I believe I have tracked down and resolved the problem people were having deploying the template and creating sites and site collections off of it. I have updated the zip file above. For support reasons, I am eliminating the HTML email version of the download. Please refer to this post on how to configure HTML emails. Sorry, it is too difficult to try and maintain 2 different installations and ensure they are all in sync with the bug fixes.

Plurk This Post Buzz This Post Delicious Digg This Post Stumble This Post