Category: Lists

Free Flash Chart Web Part for SharePoint

By , July 14, 2010

The first time I made a web part was a couple of years ago when I wanted to chart SharePoint data inside of SharePoint.  I found some free flash charts that seemed pretty functional and away I went.

The web part turned out pretty well and I may have gone overboard adding additional features to it, but it definitely served its purpose (been in production for 2 years without issues).  Since it was the first web part I ever made, the code isn’t the most efficient and it was my plan to always go back and rewrite it.  But, with the coming of SharePoint 2010 and it’s own Chart web part, I don’t think I will spend the time to improve this, so here it is.

It does Pie, Bar, Column, line, area, doughnut and funnel charts.  A couple of the charts support three series of data, 2 as columns and one line which is pretty impressive.  Here are a few images of it running in a production environment.  It works in MOSS 2007 and WSS 3.0.  I haven’t tested it in anything other than that.  Enjoy!

Download the Project Here

Download the WSP Here

 

Birmingham Users Group on March 9th

By , March 1, 2010

It looks like I will be presenting about the options of Business Intelligence in SharePoint at the Birmingham SharePoint User’s Group on March 9th.

This will be a similar presentation to what I gave a couple of months back here in Huntsville but I will go into more detail and add a couple of demos.  It will be 2007 focused but I will highlight some of the changes coming in 2010 and maybe throw a demo or two in there on 2010 if I have the time to get a suitable environment set up.

Below is information on the session and hopefully someone will feel like showing up to listen to me :)

Microsoft SharePoint Business Intelligence Solutions for Everyone

Steve McDonnell
Senior SharePoint Consultant, IE-Dynetics

With Microsoft SharePoint, Business Intelligence does not have to be reserved for executives or companies with multimillion dollar IT budgets.  Whether you have Windows SharePoint Services or Enterprise MOSS, there are several options for Business Intelligence that we will dive into.  If you want to see a detailed overview of what Microsoft BI has to offer filled with real time examples of dashboards and reports, then this is a presentation for you.

Specific topics to be covered in this presentation:

  • WSS 3.0 BI Capabilities
  • The Data View Web Part
  • Custom Web Parts and Dashboard Solutions
  • SQL Server Capabilities (SSIS, SSAS, SSRS)
  • Enterprise MOSS 2007 Capabilities (Excel Services, KPIs, BDC)
  • PerformancePoint Server 2007
  • The Future of BI With SharePoint Server 2010

Customizing the Smart Tools Grouped Chart to Accept Connections from Filter Web Parts

By , November 10, 2009

If you haven’t tried out the Smart Tools Web Parts yet I suggest that you do so. They are well done and pretty reliable, especially the chart web parts.  Well, for the most part they met my needs, but I wanted to be able to filter the data in the chart without having to create a lot of different views of a list.  I basically wanted some kind of drill down functionality, but I didn’t want to spend too much time developing my own tool, so I extended theirs!

I will go over how I added a little code to allow the Grouped Chart Web part to accept connections, filtering the data with the out of the box filter web parts in SharePoint Server 2007.   First though, here are a couple of screenshots so that everyone knows what I am talking about.

First the chart showing all of the Hours Worked, by every Employee, on every project this year.

No Filters

Then, in the next three images, I keep applying an additional filter until I have drilled down as far as I need. Note that I am using two different filter web parts – they all work and you can use any combination of them as shown.

No Filters

No Filters

No Filters

So, how did I do it? Very simple actually. They are nice enough to provide the source code for their charts at the Smart Tools codeplex site. From there, I added the c# file to a new web part project, added a little bit of code and it magically worked. First, we need to add a few using statements as shown. Now that I am writing this, I am pretty sure I don’t need all the Web ones, but oh anyways…

using wsswebparts = Microsoft.SharePoint.WebPartPages;
using aspnetwebparts = System.Web.UI.WebControls.WebParts;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections.ObjectModel;
using System.Collections;
using System.Runtime.InteropServices;

The first thing I added was the code that allows the web part accept connections and defines what the connection points created will be named.

//Tell SharePoint the Web Part can accept connections
List providers = new List();
[aspnetwebparts.ConnectionConsumer("Simple Consumer", "IFilterValues", AllowsMultipleConnections = true)]
public void SetConnectionInterface(wsswebparts.IFilterValues provider)
{
this.providers.Add(provider);
if (provider != null)
{
if (View != null && View != string.Empty)
{
List l = new List();
SPWeb contextWeb = SPContext.Current.Site.OpenWeb(SPContext.Current.Web.ServerRelativeUrl);

// Loop through all fields available in the specified View - We can filter on these
foreach (string fieldString in contextWeb.Lists[List].Views[View].ViewFields.ToStringCollection())
{
l.Add(new wsswebparts.ConsumerParameter(fieldString, wsswebparts.ConsumerParameterCapabilities.SupportsMultipleValues | wsswebparts.ConsumerParameterCapabilities.SupportsAllValue));
}
provider.SetConsumerParameters(new ReadOnlyCollection(l));
}
}
}

So at this point, the Chart web part can be connected to from out of the box filter web parts, but the filter does nothing yet. We need to add some logic to actually do some filtering. I decided to filter out everything that does not EQUAL what is selected in the filters. Alternatively, you could filter out things less than, greater than, etc, but I thought Equals made more sense.

The next thing I did was add the code to get all of the filters and add them to a List.

// Gets any filter parameters if there are connections Filter Parameters
bool validFilter = false;
List filterValue = new List();
List filterField = new List();
foreach (wsswebparts.IFilterValues provider in this.providers)
{
if (provider != null)
{
string prop = provider.ParameterName;
ReadOnlyCollection values = provider.ParameterValues;
if (prop != null && values != null)
{
foreach (string v in values)
{
if (v != null && v.Length != 0) // Apply the filter
{
filterField.Add(provider.ParameterName);
filterValue.Add("'" + v + "'");
validFilter = true;
}
}
}
}
}

The last thing we need to do is to do the actual filtering. There were a few ways to do this, but after seeing how the existing code was structured, I decided to just delete the rows I didn’t need from the Data Table they were using. The code is shown below.

if (dt != null) // Data available
{
// If there are filters via connections, remove entries not equal to the filter
if (validFilter)
{
for(int i = 0; i < filterField.Count; i++)
{
string expression = filterField[i] + " <> " + filterValue[i];
DataRow[] results = dt.Select(expression);
foreach (DataRow dr in results)
{
dr.Delete();
}
}
}
}

And thats it – with very little code I made a pretty big enhancement to the Web Part (at least for my needs). In fact, you can use the code above to add connections to almost any web part you may have if you want them to use out of the box filters. If you create your own filter, things get a little more tricky.

Note, for this to work you need to have the Smart Tools already installed – I did not include the VisiFire files with this solution package.

Download C# File
Download Project Zip
Download WSP

Filtering Data View Web Parts By The Query String With Out of the box WebParts

By , November 9, 2009

So, recently I have been building a Project Management SharePoint site to better manage our customer projects, deploying it on our Extranet server that runs WSS.  There is quite a bit involved as I have been working on it for the better part of two weeks, pulling data from our LOB systems and surfacing it into our SharePoint Extranet, but what I want to highlight here is how I am filtering a set of dataview web parts on one page in particular.  There were a lot of articles out there on how to do cool things with Data View web parts that i referenced in building my solution, but there wasn’t anything that I could find that did the filtering the way I wanted to.

Basically, from the home page of my Projects website, there are a few charts and a Data View web part listing every project – you can click on each one to take you to a project details page.  I wanted this Data View to pass the ID of the project in the querystring to the project details page.  Well, this is straight forward as I just did a little formatting within SharePoint Designer, formatting each Project Item as a URL.  The challenge was on the Project Details page itself.

You can easily create a parameter in a Data View Web Part as shown here, and it worked quite well.  Once someone clicked on a project, the Project Details opened up as shown below.  If you want to see how to make a Data View Web Part look like the chart below, see this blog.



Well, this is kind of cool, but what if someone wanted to see the details of another project without having to go back to the front page and selecting it? I needed a way for the user to input another project ID number and see its details. Well, a DropDown box would be ideal, but there are way too many projects for that to work correctly, so I decided to use the Form Web Part (Remember I am using WSS 3.0 for this application) which gives me a Text box I can connect to other web parts. So, I created the web part and connected them all up, passing the value of the parameter I created earlier for each DataView. This worked great when there was nothing being passed in the query string, but if something was in the querystring it would override the connections made in the web parts. So, I decided to see if I could make it edit the QueryString when someone clicked the Form Web Part. This was actually pretty simple, it just took a little time to figure out.

Turns out, there is a source control button on the Form Web Part where you can directly edit the JavaScript – Pretty Handy. Well, all I had to do was insert the following snippit of JavaScript, and I was done.

<script type="text/javascript">
function moveMe(n){
    window.location = "projectdetails.aspx?ProjectID=" + n
}
</script>

<div onkeydown="if (event.keyCode == 13) moveMe(document.getElementById('myText').value)">
<input type="text" name="T1" id="myText"/>
<input type="button" value="Go" onclick="moveMe(document.getElementById('myText').value)"/>
</div>

All this code does, is define one small function that will redirect the page to a new URL (Modifying the Query String) with the input from the textbox. The two events are for someone clicking the Go button and pressing enter.

So, as you can see (sort of) in the image below, I now have a textbox that lets you modify the querystring, which is then read by three data view web parts to filter their data.



Pretty neat for not needing to put any code directly on the server.

Getting the SPUser object out of a person or group list field

By , September 3, 2009

So, as it turns out it is kind of tricky to get at the SPUser object stored in a person / group field.

I am building a workflow that needs to grab the email of a user, based on an SPUser object stored in a list field, and it gave me a little trouble so I decided to share. Nothing that I found online gave me the exact answer I was looking for, although this article got me headed in the right direction. Below is the code to grab the email stored in a SPUser object from a person field within a SharePoint Workflow.

SPFieldUser userField = (SPFieldUser)workflowProperties.Item.Fields.GetField("Customer");
SPFieldUserValue fieldValue = (SPFieldUserValue)userField.GetFieldValue(workflowProperties.Item[userField.Id].ToString());
string customerEmail = fieldValue.User.Email;

Kind of ugly if you ask me, but hey it works.

Running SharePoint Code with Elevated Privileges – A Real Example

By , August 31, 2009

In case you didn’t know, there is a construct built into SharePoint that allows developers to create code that runs as the System Account instead of the logged in user, essentially giving that user Administrator Level Permissions in a confined space. I will go over the code required to do this below, but essentially you have a block of code within your larger project that needs the logged in user to have certain permissions that they may not have, and this small block of code will then be run as the System Account, giving it the permissions needed for the task at hand.

Well, at first thought everyone I have explained this to has the same questions and concerns, “Why would you ever want to do that?” or “That is a huge security risk!”.
I could never come up with a very good example to explain it and my attempts to reassure customers that the security risk is very minimal as the user will only have permission to do exactly what the developer lets them sometimes was not well understood.

Well I finally had a project that required me to do this, so I figured I would share it to give a fairly simple real world example.

Project Objective: Create an extranet to serve as a customer support site where users can log Service Requests, manage their account, order new products, etc. via the web or by phone.

This is fairly simple and something I have done several times for different customers, but this project had one difference. Service / Support Requests could be made by phone, where all of my other projects were strictly web oriented. Does anyone see the problem this raises yet? Well I eventually incorporated the Cisco phone system into the site to generate Service Requests, but that is not what I am talking about. With any system like this, it is vital that customers only have access to their own Service Requests (as in they cant view other user’s requests).

Typically, when using a SharePoint list one can turn on the settings in the Advanced Settings section of a List (seen below) so that users can only view and edit their own List Items. This is very handy and works very well when users are always the creator of their own items, but in this case when someone calls in, someone else will be creating the service request for them… see the problem? Well, I thought about trying to change the Created By field to the user that called in, but decided it would be a neat place to use the elevated permissions approach.

Advanced List Settings

The Solution: So, this is the summary of what I did. I didn’t give any customers permissions to the Service Request List at all from within SharePoint. All access that customers had to that List was done through custom forms and web parts that made use of the elevated permission construct. This ended up being 3 forms and 3 web parts that used Elevated Permissions. Customers would use one form to create a Service Request, another to view it, another to edit it and then three web parts to display the contents of the list on various pages.

I added a custom text field to the list called Customer ID which would store each customer’s unique ID, detailing who each Service Request belonged to. The custom forms that users would use would populate this field upon creation by looking to see who the current user was, and looking up their ID in another List. Likewise when a member of the support staff was creating a service request received over the phone, they would fill in the Customer’s ID before creating it (The phone system was later used to automate this). This way, we are not relying on the Created Field to limit what each user is allowed to see.

Below I will go through the basics of how Elevated Permissions work and then show in more detail how I used it in my solution. Then in the next few days I hope to stick out a web part that incorporates it as well.

This is the basic code snippet you need to run with elevated permissions:

private void yourFunction()
{
      // Non-Elevated Permission Code Goes Here

      SPSecurity.RunWithElevatedPrivileges(delegate()
      {
            // Elevated Permission Code Goes Here
      });      

      // Non-Elevated Permission Code Goes Here
}

Pretty much everything you find online about this has code that is poorly explained, so hopefully I can do a decent job and make it easy. The code above is simple enough right? Well here is one place people routinely run into problems. If you are using an instance of SPWeb or SPSite before your Elevated Permission Code block, you won’t be able to use it. You need to get a new instance of the SPWeb or SPSite object to use once you enter the elevated block. Below is the code that I use to do this.

private void yourFunction()
{
      SPSite site = SPContext.Current.Site;
      SPWeb web = SPContext.Current.Web;

      SPSecurity.RunWithElevatedPrivileges(delegate()
      {
            using (SPSite ElevatedSite = new SPSite(site.ID))
            {
                  using (SPWeb ElevatedWeb = ElevatedSite.OpenWeb(web.ID))
                  {
                        // Code Using the SPWeb Object Goes Here
                  }
            }
       });
}

I am pretty sure that little tidbit cost me about a day the first time I tried to play with Elevated Permissions, so hopefully that will help some of you out. After that, it is really simple as you just put your code in the middle of it just like you would if you weren’t using the elevated permissions.

As further example, in my NewSR.aspx form that users go to when creating a new Service Request, I use a runWithElevatedPrivileges block in two functions. The first is a private function I call within the OnPreLoad() function called LoadData(). Inside this function I am populating drop down menus from data in a SharePoint list for the Priority of the service request and the products that are affected.

The second function with a runWithElevatedPrivileges block is my onSubmit() function that is called when a user clicks the submit button.

New SR Form

So it is actually pretty simple. The only other thing I am going to demonstrate is how I made the view and Edit Forms Secure. As you probably know, every List Item has an integer ID (.ID) and a unique guid ID (.UniqueID). Well, the custom forms I made for view and edit look in the querystring for a guid which it then uses to look up the Service Request. (If you can guess the guid of another service request, well you broke my security but it seems quite unlikely) There isn’t anything fancy about accessing the querystring to get the guid and look up the Service Request, but I thought it would be handy to store a link to the item within the item itself as an additional field. This makes it easy to email the user a link to view their service request among other things.

So I created a text field called Link and populated it as shown below.

private void yourFunction()
{
      SPSite site = SPContext.Current.Site;
      SPWeb web = SPContext.Current.Web;

      SPSecurity.RunWithElevatedPrivileges(delegate()
      {
            using (SPSite ElevatedSite = new SPSite(site.ID))
            {
                  using (SPWeb ElevatedWeb = ElevatedSite.OpenWeb(web.ID))
                  {
                        srList = ElevatedWeb.Lists["Service Requests"];
                        SPListItem newItem = srList.Items.Add();
                       
                        // Do stuff to create the list item
 
                        ElevatedWeb.AllowUnsafeUpdates = true;
                        newItem.Update();
                        Guid temp = newItem.UniqueId;
                        newItem["Link"] = "<DIV><a href=\"https://YourSite.com/_layouts/custom/ViewSR.aspx?ID=" + temp.ToString("d") + "\">View SR</a></DIV>";
                        newItem.Update();
                        ElevatedWeb.AllowUnsafeUpdates = false;
                  }
            }
       });
}

The tricky part here is having to call item.update() twice. You have to call it twice because the first time you call it is when the list item is actually created. Before then, it does not have a Unique ID to reference. So once it is created, you can grab the Unique ID, and populate the Link field with a HTML formatted URL that references the item and can be easily inserted into a HTML based email or a page that has a content editor web part CEWP with the JavaScript in it found here. You then call update the second time to save your link field.

Support Screen with Link

The use of the link field and the JavaScript found above allows me to render links in a web part as shown above.

Well, hopefully this will be useful to someone – I think it would have saved me a day or so once upon a time when I first tried to do this.

Getting a list item’s attachments through code

By , August 10, 2009

So, I came across the requirement to create a list of links to all of the attachments of a list item in my custom aspx page. Seems easy right? I should be able to grab the list item and do something like item.Attachments to get a collection of some kind of SPAttachment Object… right? Well as it turns out, it’s not quite so simple.

SPListItem.Attachments returns a SPAttachmentCollection object which is fairly limited in what it allows you to do.  It does provide a simple way of adding and deleting attachments, but thats about it.  You can however treat the SPAttachmentCollection as if it was a StringCollection and enumerate through the collection as shown below to get the names of the files.

1
2
3
4
5
// item is a SPListItem
foreach (string attachmentName in item.Attachments)
{
     // attachmentName is the name of the attachment
}

Well that is better than nothing, but I needed links to the documents, not just a list of them. So the next thing we need to do is prefix the attachmentName string with the rest of the URL. It turns out that it is fairly simple to use a SPFolder object as seen below to grab the rest of the URL.

1
2
3
4
5
6
// item is a SPListItem, _Files is the Literal in my custom page
foreach (string attachmentName in item.Attachments)
{
     SPFolder attachments = item.ParentList.RootFolder.SubFolders["Attachments"].SubFolders[item.ID.ToString()];                                    
     _Files.Text += "<a href=\"https://SharePoint.com/" + attachments.Url + "/" + attachmentName + "\">" + attachmentName + "</a><br>";                              
}

So, while I don’t really like it, I think this is the easiest way of getting a link to the attachments for a given list item. If anyone has a better method, please feel free to share. Below is a little image of what resulting page may look like.

ListItemAttachments