Case study: Data collection, tracking calendar, dashboards, reports, and archival

24Sep09

Solution overview

5280 Solutions created a system to solve a customer’s need to a) gather mission-critical meeting information from high-ranking officials, b) create dashboards with custom KPIs and charts, c) perform detailed statistical analysis, and d) archive all information. The solution consists of the following SharePoint capabilities:

  • SharePoint Designer Workflows + SharePoint lists: A master calendar sends reminders to form submitters, tracks successful form submissions, and sends a follow up e-mail if form is not submitted within a set amount of time. All reminders contain links that pass parameters to an InfoPath form, and all parameters are editable using SharePoint lists. An additional workflow sends scheduled alerts to staff for month-end reporting, and provides dynamically-generated links to reports for that particular month. 
    SharePoint Designer workflow diagram

    SharePoint Designer workflow diagram

     

  • InfoPath Forms Server + Visual Studio 2008 + CAML + SharePoint lists: A single browser-based InfoPath form displays different data depending on variables passed through the URL, and further refines the options available as data is entered into the form’s fields. Master-detail relationships are achieved using CAML queries to SharePoint list data, and are displayed in repeating tables. Form options, such as meeting participant names, are pre-populated depending on the end user’s department (passed through a URL parameter) and also which meeting the end user selects. When the form is submitted, event handlers are used to push form data to various lists which are then used to build dashboards and reporting views. Event handlers are also used to move attachments to secured folders, create a link from the list item to the correct attachment, and flag the list item as having or not having an associated attachment (visual indicator flag acts as the link for the user). As forms are submitted, they are marked as complete on the master calendar, thereby cancelling any further follow up alerts. 
    Dynamic InfoPath form

    Dynamic InfoPath form

     

  • Open source chart API + calculated columns: A chart API uses calculations performed on a list to display pie charts and percentages. The interface consists of charts, color-coded indicators, and list views to allow maximum flexibility for statistical analysis. A clickable visual indicator is also displayed to show whether or not an item has an attachment, as defined by the event handlers described above. 
    Custom dashboard

    Custom dashboard

     

  • Custom web part + event handlers: A custom web part and event handlers allow the customer to archive data quickly and easily. When archived, views on applicable lists are automatically created, and these dynamically generated views are then accessible via the month-end reporting links sent by the master calendar workflow. 

    Custom web part for archiving

    Custom web part for archiving

     

Tangible results

Before this solution was in place, the entire grant program was at risk because data collection, reporting, and archiving were entirely manual processes. The customer did not have sufficient staff or processes to sustain a manual solution at the levels required by the grant.

After this solution was implemented, the customer was able to gain excellent buy-in for the data collection and reporting process because of the following points:

  • The reminder functionality provides a direct link to the correct form, thereby lowering the “activation energy” required to submit meeting information
  • The InfoPath form is clean, user-friendly, and displays only applicable information and choices

The customer’s staff members were empowered to meet team goals in ways not possible before, such as:

  • Higher quality and quantity of data collected so that grant requirements could be satisfied
  • Easy report generation and viewing
  • Easy editing of workflow-generated texts and other variables to evolve the solution over time to meet changing business needs
  • Easy editing of information and choices displayed by InfoPath through the use of SharePoint lists
  • Method to audit records for missing data, low participation rates, and other issues within the program

Feel free to contact us or leave a comment if you have questions about this solution.

Advertisements


No Responses Yet to “Case study: Data collection, tracking calendar, dashboards, reports, and archival”

  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: