Creating Complext Charts Using Sharepoint 2010 OOB Capabilities


Hi Guys,

Our Business Leaders take lot of decisions based on charts and graphs trends.One of our client had a requirement to create chart in their dashboard page without using any kind of coding. we explored the ways in which we can create Charts in SharePoint 2010. After doing a quite good amount of research on Charts in Sharepoint, we came to know there could be 2 ways to show data in charts in sharepoint 2010:

1. PPS(Performance Point Service)
2. Chart webpart.

PerformancePoint Services in Microsoft SharePoint Server 2010 is a performance management service that you can use to monitor and analyze your business. By providing flexible, easy-to-use tools for building dashboards, scorecards, reports, and key performance indicators (KPIs), PerformancePoint Services can help everyone across an organization make informed business decisions that align with companywide objectives and strategy. Initially we thought PPS would be the perfect candidate for our requirement as it has lot of capablities it in for Dashboard,Scorecards and Kpi.But as our main requirement was to create chart webpart from sharepoint list and PPS has a limitation that we can only create charts on 3- dimensional object like Cube in SQL Server or Excel. so we had to move from PPS to Sharepoint OOB Chart webparts.

Chart Webpart gives us ways to create our Charts on top of List. Below can be the sources for Chart WebPart:

1. WebPart
2. List
3. BDC
4. Excel Services

There are lot of styling we can do with Chart Webparts along with Filtering of Data  and Grouping by some field. But SharePoint doesn't provide OOB agreegate functions to  Show data. But in the pratical senario's we would be more interested in showing agreegated data in Chart WebPart.If Chart WebPart doesn't provide way to agreegate data then in that case how will we show agreegated data using OOB Chart WebPart. I would be taking 2 examples that will show the way to implement complex charts using Sharepoint OOB WebParts.

 

Example 1:

we have a project list which will show details about the project like Project Name, Project Status, Country, Department e.tc. User is interested in looking at number of project by status. something like the chart in below fig: 

 

Follow the below steps to implement the above chart using OOB sharepoint Chart WebPart:

1. Create a list which will have master data of status-ProjectMaster.

 

2. Create a ProjectList wihich will have all project related columns, also it will have one column with the name project status which will be lookup column from ProjectMaster Title Column.

 

3.Create Column in ProjectMaster list which will be Lookup column from ProjectList's Project Status column like the fig shown below:

4. Once this column is created in Projectmaster Table, this column will return the number of project in each status.

5. Now we will just configure Chart WebPart for ProjectMaster table. we will configure Title on the X-axis and Project Status Count on Y-axis.

 

This Example was pretty easy to implement but not always we will get the simple senario's like the above one, so let's take little complex senario now.

Example 2:

In the same Project List, we would be adding one more column "Assign To" which will show the Project Assignee Name. Now we need show graph which will show the number of project which are assigned and which are not assigned.
this senario can't be implemented in the previous appraoch was as user who is creating or editing project will assign/Unassign project but hewon't be mentioning category for project like Assigned or UnAssigned. In these kind of senarios we need to populate our master list using SPD workFlow. Please follow the below steps to create graph like below :

1. Create a list which will have master data of status-ProjectResources.

2. Create a ProjectList which will have all project related columns, also it will have one column with the name AssignedUnAssigned which will be lookup column from ProjectResources Title Column.

 

3. Create Column in ProjectResources List  ResourceCount which will be lookup column from ProjectList's AssignedUnAssigned, the way we did it in previous example.

4. Create a SPD WorkFlow which will populate data in AssignedUnAssigned column of ProjectList. This workFlow will get triggered on Creation/Editing of ProjectList items. The workFlow will look like below:

5. Once this WF is attached to the ProjectList, it will populate data in AssignedUnAssigned field and which intern will populate data in ProjectResources ResourceCount.

6. Now we will just configure Chart WebPart for ProjectResources List. we will configure Title on the X-axis and ResourceCount on Y-axis.

These ways we can create nice graphs on Sharepoint List without Using Single Line of code. Hope this will help you in creating Graphs.

Thanks 🙂

Comments (15)
  1. Carlos says:

    This is great and I'll definitely be using it.

    How could I show a chart with Day (day as mm/dd/yy) on the X axis and count (# of occurances) on the Y axis?

  2. Very Nice and informative article

  3. Terry says:

    I have followed this article muliple times and I cannot get any type of count. Example 1, in Step One, do you create the caetgories? Step 3, you have in the "In this Column" you have "Project Status (Count Related)", is this a special field you created in the ProjectList?

  4. Asher says:

    Hi Terry

    A count related column appears when you have added the master column to the main list and then add another (count) column to your list that contains the master column.

    So in the above example, Title is the master column added to the project list under the name 'Project Status'.  When this column has been added, you can then go back to the list contaning 'Title' and add a new column from the list containing Project Status, and 'count related' will be an option.

    Hope this helps

    Ash

  5. Chris says:

    Thanks for this.. it helped immensely.  Especially once I figured out that the "look-up" column was supposed to be created in BOTH lists.  Terry's question (I was having the same issue) and Asher's answer provided the "Ah ha" I needed.

    Thanks Again.

    Chris

  6. Jesse says:

    Helpful for counts…what about doing a summation of a different number column for each category ( ie Total Cost for each completed / started / onHold)

  7. Negin says:

    Awsome!! Thank you so much… exactly what I was looking for

  8. Bob Milanov says:

    "Helpful for counts…what about doing a summation of a different number column for each category ( ie Total Cost for each completed / started / onHold) " – I would recommend you to check Nevron Chart for SharePoint – it allows for custom trendlines defined by C# code…

  9. Lionheart831 says:

    Did anyone figure out the summation?

  10. Tg says:

    It is an awesome Projects! love it :):)

    Thanks

  11. Jas says:

    How can I do count of status using two criteria. so a count of all projects grouped by project managers but using status not completed. I was able to use your count to get all projects grouped by project managers but it shows all status (In progress, Not started etc…) I just want the count for those not started. I have spent hours looking for this workaround but calculated columns do not capture look up columns… any help would be greatly appreciated.

  12. Twain says:

    Thanks for the detailed post, Amit. A chart web part is as powerful as the number of sources it can pull data from. In that sense, I find Collabion to be one of the good 3rd party apps. In addition to the ones you listed, it also supports SQL DB, Oracle DB, and CSV/text file (http://bit.ly/YFAJyA).

    Also, those who need a similar solution for 2013 need to use a 3rd party web part. Bummer, but that's the way it is!

    Thanks for the great post again.

  13. Pratima says:

    Is it possible to apply multiple filters? For e.g, I want chart in which Y axis will represent count. And x-axis will represent Countries. All the bars will be stacked columns containing count of on hold, in progress and completed projects. Is it possible OOB?

  14. Ramiya says:

    How to convert it to pie chat?

  15. webharvest2 says:

    This is a great post and very helpful.  I want to create a bar chart that looks at how many items have been submitted to a list by month.  When an item is created in my list it saves a "submitted date".  How can I make it so the it counts how many items have a submitted date for the month/year based on that date so I can graph it.  And is there an easy way to make it a rolling 12 month graph without manually selecting the dates each month?

Comments are closed.

Skip to main content