Inside MSDN

Using InfoPath as a Reporting Solution

Larry W. Jordan Jr.

 

Code download available at:InsideMSDN0503.exe(355 KB)

Content

The Employee Data List
The Project Data List
The Status Reporting Form
InfoPath Merge Reporting
Form Design Elements
Charting
Conclusion

I love the part of my job that lets me write code and develop software. In addition to those responsibilities, however, I manage a development organization that's working on a number of large strategic projects. As you know, one of the challenges faced in managing a team is finding the right balance between getting the information I need to be an effective manager and avoiding the kind of intrusion on team members that can inhibit their work. To get my information without intruding, my team and I built a reporting solution that each team member can use to report project status. The application rolls up all of the data reported into multiple views that I can then analyze to get a sense of how all the various parts of the projects are progressing.

I based the solution on Microsoft® Windows® SharePoint® Services and on Microsoft InfoPath® 2003 SP1 because they're easy to use and deploy. Employees create, edit, and view reports in InfoPath, with SharePoint serving as a data and report repository. InfoPath makes it simple for the individual teams to deploy various project status forms to SharePoint sites. As a bonus, this allows us to maintain the security and privacy of the status data because SharePoint provides domain and user-level security. Plus, it is easy to administer. SharePoint also offers many data storage and reporting options, and InfoPath simplifies integration with Web services, SQL Server™, and Visual Studio®.

One of my main goals was to have the solution integrate across the development, test, and program management teams of MSDN® and TechNet. This required that the solution be maintainable by people who don't work in Visual Studio every day and it required that updates and changes to employee and project data could be made by anyone on the team.

I also wanted to collect a number of metrics to use in resource allocation decisions, including active development projects, developer and tester activity in the defect tracking system, check-in e-mails to the change management systems (source control), and discussions with the leads on my team.

I also had the following requirements:

• The app should be written in managed code.
• It should integrate our product defect and change tracking system metrics.
• It should use the charting capabilities of the Office Web Components.
• It should post the submissions to both a SharePoint site and a Web service interfacing to a SQL database.
• The overall organizational reports should be generated from the database and implemented using SQL Reporting Services.

The Employee Data List

Knowing up front what data will be harvested helps to drive future decisions, so the first step in authoring the solution was to arrive at a schema to be used for the individual status reports. Figure 1 shows the status reporting schema. This schema can then be laid out in a simple user interface, like the one shown in Figure 2.

** Figure 2 Simple UI for Status Report Based on Schema**

Figure 1** Schema **

Before I dig further into the InfoPath development, let's do some of the preparatory work to build and configure the SharePoint site and the lookup lists that will be used by the InfoPath client. In Office 2003 SP1, InfoPath has excellent integration with the lists provided by SharePoint and it makes cross-team maintenance and security very easy.

First, you must create the Employee Data list, which centralizes the lookup that will be used to assign the employee's manager, department, and team. On your SharePoint site, create a Custom List in Datasheet View, name the list "EmployeeData," and customize it to include the following columns:

Column	Type	Required
Name	Single line of text	Yes
Alias	Single line of text	Yes
Manager	Single line of text	Yes
ManagerAlias	Single line of text	Yes
Department	Single line of text	Yes
IsManager	Yes/No	Yes

Add in some sample data and designate one person in the group as the manager. This is done by having each person's Manager and ManagerAlias fields contain the manager's Name and Alias. The manager's IsManager field can then be set to Yes.

The Project Data List

The Project Data list will be used for the assignment of the projects an employee is working on and the allocation of time spent on various activities. First, create a Custom List in Datasheet View, name the list "ProjectData", and then modify it to include the following columns:

Column	Type	Required
Activity-ProjectName	Single line of text	Yes
Initiative	Single line of text	Yes
Commitment	Single line of text	Yes

Now you can add some sample data for projects, aligned to Initiatives and Commitments, as shown in Figure 3. This is the way we look at project resource allocations in my work group. You will want to organize your project list according to the way you want to track your own group's data.

Figure 3 The Project Data List

The Status Reporting Form

With the lists in SharePoint configured appropriately, you can now focus on wiring up InfoPath to work with this list data. The status reporting form itself is quite minimal and is designed to be filled out quickly.

There are several steps I take when I deploy a form to a new SharePoint site for a specific team. Since I have the standards for the schemas of the EmployeeData and ProjectData lists, I merely have to point the form to the location of those SharePoint lists:

1. Open up the Tools | Data Connections options in the InfoPath Designer.
2. Select "Add" (or select "Modify" if the connection already exists) to take you through the wizard to connect to the SharePoint list.
3. Set the form to point to your top-level site that hosts the ProjectData list and click Next.
4. Select the ProjectData item from the list and click Next.
5. Highlight the values that you will use in your form (Initiative, ActivityProjectName, or Commitment) and click Next.
6. To finish, indicate the name of the ProjectData data source (which I'll keep as ProjectData so my XML code works without modifications), tell InfoPath to "Automatically retrieve data when form is opened," and click Finish.

I can think of many opportunities to make this programmatic and configurable so that the step of modifying the form does not need to happen, but in my experience, the five minutes it takes to deploy a new instance outweighs the abstraction investment.

You will need to repeat steps 2-6 to associate the EmployeeData list with the form, but with a change in step 5 to indicate the schema of the EmployeeData.

Now that you have modified the form to pull from your SharePoint site as the source for the lists, you need to set up the data filter to use for the lookup of the EmployeeData. Remember that the EmployeeData list schema includes the employee's department. This value is used to set the header on the form and to filter the list of employees based on the specific department. In order for the list to work, I use the filtering capabilities built into the InfoPath designer. Again, there is another opportunity to make this more programmatic by setting the department to a field value on a hidden view or the like, but for the purpose of this column I want you to see how I'm doing it.

In the InfoPath designer, highlight and right-click the Name dropdown list and select "DropDown List Box Properties" from the context menu. Select the Option button next to the "Entries" textbox and bring up the Data selection for "Select Field or Group." You should note that the Name field is highlighted. Click the Filter Data button and set the formula to filter on the "Development" department. Select Modify, then choose "Type text..." from the dropdown, and enter "Development."

Now that you have your data sources for Employee and Project lookups configured, you will need to add some code to facilitate the population of the fixed fields such as Manager Alias, Department, and so forth.

The code in Figure 4 is associated with the Name DropDown list that runs when you change the employee name. The code looks up the employee in the associated EmployeeData data source and fills in the fields in the XDocument DOM.

Figure 4 Look Up Employee

// The following function handler is created by Microsoft Office InfoPath. 

// Do not modify the type or number of arguments.

[InfoPathEventHandler(

 MatchPath="/sr:Team/sr:EmployeeReport/sr:Employee/sr:Name", 

 EventType=InfoPathEventType.OnAfterChange)]

public void Employee_Name_OnAfterChange(DataDOMEvent e)

{

    if (e.IsUndoRedo)

    {

        // An undo or redo operation has occurred; the DOM is read-only.

        return;

    }



    try

     {

        string sEmployeeName = e.NewValue.ToString();

        if (sEmployeeName != null && sEmployeeName != string.Empty)

        {

            // -- load the XML Data for EmployeeData

            xmlEmployeeDataDoc.LoadXml(

                thisXDocument.DataObjects["EmployeeData"].DOM.xml);

            

            // -- set EmployeeData namespace managers

            XmlNamespaceManager nsEmployeeDataMgr = 

                new XmlNamespaceManager(xmlEmployeeDataDoc.NameTable);

            string dfNamespace = "https://schemas.microsoft.com/office" + 

                "/infopath/2003/dataFormSolution"

            nsEmployeeDataMgr.AddNamespace("dfs", dfNamespace);

            nsEmployeeDataMgr.AddNamespace("dsf", dfNamespace);



            XmlNode xmlEmployeeDataNode = 

                xmlEmployeeDataDoc.SelectSingleNode(

                    "/dfs:myFields/dfs:dataFields/" + 

                    "dsf:EmployeeData[@Name=\"" + sEmployeeName + "\"]", 

                    nsEmployeeDataMgr);



            // -- set to the form xml and update the data

            IXMLDOMNode xmlNodeEmployee = thisXDocument.DOM

                .selectSingleNode("//sr:Employee");

            IXMLDOMNode xmlNodeReportDate = thisXDocument.DOM

                .selectSingleNode("//sr:ReportDate");

            

            xmlNodeEmployee.selectSingleNode("//sr:EmailAddress").text = 

                xmlEmployeeDataNode.SelectSingleNode("@Alias").Value;



            xmlNodeEmployee.selectSingleNode(

                "//sr:Manager/sr:EmailAddress").text = 

                xmlEmployeeDataNode.SelectSingleNode(

                "@ManagerAlias").Value;



            xmlNodeEmployee.selectSingleNode(

                "//sr:Department").text =

                xmlEmployeeDataNode.SelectSingleNode(

                "@Department").Value;



            xmlNodeEmployee.selectSingleNode("//sr:IsManager").text =

                xmlEmployeeDataNode.SelectSingleNode("@IsManager").Value;

            

            // -- Set Focus to Item element under Accomplishments section

            IXMLDOMNode xmlNodeAccomplishment = thisXDocument.DOM

                .selectSingleNode("//sr:Accomplishments/sr:Item");

            if (xmlNodeAccomplishment != null)

            {

                thisXDocument.View.SelectText(

                    xmlNodeAccomplishment, Type.Missing);

            }

            

            // -- update global filename

            SetFileName();

        }

    }

    catch (Exception ex)

    {

        thisXDocument.UI.Alert(ex.Message + " Inner: " + 

            (ex.InnerException != null ? ex.InnerException.Message : "");

    }

}

There is an additional function to set the field associated with the file name and this is used when the user submits the form so that you can save the file in a specific format and parse the value on the parallel submission to the back-end Web service (see Figure 5).

Figure 5 Set the File Name

void SetFileName()

{

    try

    {

        // file name

        sFileName = ActiveDate.Year.ToString() + "_";



        // -- set working variables

        int iYear = ActiveDate.Year; 

        int iMonth = ActiveDate.Month; 

        int iDay = ActiveDate.Day;

    

        if (iMonth < 10) sFileName += "0" + iMonth.ToString() + "_";

        else sFileName += iMonth.ToString() + "_";



        if (iDay < 10) sFileName += "0" + iDay.ToString() + "_";

        else sFileName += iDay.ToString() + "_";



        // -- set file name

        IXMLDOMNode xmlNodeEmployee = thisXDocument.DOM.selectSingleNode(

            "/sr:Team/sr:EmployeeReport");

        if (xmlNodeEmployee != null)

        {

            sFileName += xmlNodeEmployee.selectSingleNode(

                "//sr:EmailAddress").text;

            sFileName += ".xml";

            xmlNodeEmployee.attributes.getNamedItem(

                "my:Filename").text = sFileName;

        }

    }

    catch (Exception ex)

    {

        thisXDocument.UI.Alert(ex.Message + " Inner: " + 

            (ex.InnerException != null ? ex.InnerException.Message : "");

    }

}

If you compile the form and preview it, you'll get a screen that looks like Figure 6.

Figure 6 Status Report Integrated with SharePoint Data

InfoPath Merge Reporting

InfoPath works well with custom merge forms and rollups. Up to this point I have focused on the individual status reporting which is useful and easy to open, fill out, and submit. However, viewing these forms individually is inefficient when I want to see what the 27 developers on my team are working on or when I want to see trends and other views of the data.

Rollup reporting encapsulates the schema associated with the Individual Reports under the EmployeeReport node, which is configured as a Group and set to be a repeating node, as shown in Figure 7.

Figure 7 Rollup Reporting

The final design incorporates all of the additional fields that you need for capturing attributes such as number of days in the working week, number of employee working days (the sum of the employee working days times the number of working days in the week), number of people on vacation, and so on. These attributes provide the metrics and the charts that graph out the resource allocation by projects.

Form Design Elements

The first action taken when rolling up the form is the selection of the Department (the radio button list in the upper-right corner of Figure 7). This selection is the filter value that is used by the code to determine rollups and aggregations for the specific team.

The next interesting section is the summary area towards the top of the report. I can bring up the metrics for number of workdays and total workdays, and then highlight for the managers the Risks, which indicate any actions or support the people on the team may need to complete their work.

InfoPath provides an OnAfterImport event method that you can customize for handling actions after the merge of the forms has occurred. This is where all the power of the implementation comes in. For example, the merge form bubbles up the Risks in each individual report by calling into the node collection and appending it to the Risks node under the main Team node for the form. The code in Figure 8 is part of a larger loop that works through each individual report. It acts upon the data and sets flags, looks up the active bugs in the source defect system, checks the activity for the developer in the source configuration system, and so on.

Figure 8 Bubbling Up Risks

// -- append in the summary of risks

foreach (IXMLDOMNode xmlNodeEmpRisk in xmlNodeEmp.selectNodes(

    "//sr:EmployeeReport[sr:Employee/sr:EmailAddress=\"" + 

    sEmailName + "\"]/sr:Risks/sr:Item"))

{

    if (xmlNodeEmpRisk!= null)

    {

        string sRiskText = xmlNodeEmpRisk.text.Trim();

        if (sRiskText != string.Empty)

        {

            string sNSUri = thisXDocument.DOM.selectSingleNode(

                "/sr:Team/my:Risks").namespaceURI;

            IXMLDOMNode xmlNode = thisXDocument.DOM.createNode(

                1, "my:RiskItem", sNSUri);

            xmlNode.text = sName + ": [" + sRiskText + "]";

            thisXDocument.DOM.selectSingleNode("/sr:Team/my:Risks")

                .appendChild(xmlNode);

        }

    }

}

Charting

Next, let's take a look at the capabilities I built into the merge application. Fill out reports for each member of your team and submit it to the SharePoint site. Then open the merge application and aggregate the four reports. When completed, you'll get the presentation of the risks.

Take a look at some of the charts you can generate from this aggregation. You can use the Office Web Components charting ActiveX® control within the merge application.

Actually implementing the charts is a little more difficult than you might think, so I'll present some of the tricks I used during the development phase. The first step is to add the Office Charting Control to your toolbox. From the Control View pane in the InfoPath designer, click the option at the bottom to "Add or Remove Custom Controls..." Then click the Add button on the dialog and select the "Microsoft Office Chart 11.0" control from the list. The next screen will ask if you want to include the *.cab file for the ActiveX controls download. Since I expect the controls to already be installed in our enterprise, I pass on this option.

The next dialog asks for the property of the ActiveX control that you want to bind to your element. There is only one property that you can bind to without custom wrapping the control, so you must choose the XMLData property.

Bypass the next screen, which asks you to enable or disable properties. Then, the next option in the wizard asks you to select the type of element to bind to the XMLData property of the control; select Text. Then select Finish and close the Add Control dialog box. The control pane will now include a custom control at the bottom that you can drop onto your form.

When you drop it onto the form, it will prompt you to select a field from your form's schema to bind to. I have created text elements within my application specifically dedicated to the XML associated with the view that I want the chart to show. I recommend you create another view and work on that surface to design and test your chart.

In my example "Employee By Initiatives" chart, I used an element of the team schema called XMLInitiativeChartDataStackedByEmployee. This element is populated during the OnAfterImport event, which fires after the merge of the individual reports I am rolling up into the full report.

The process works like so:

1. The data is merged and the calculation of working days and initiative assignments are completed.
2. The code then fires a method to extract the project resources nodes for each person. It creates a new XML document that simplifies the nodes and creates the proper hierarchy.
3. An XSLT is then applied that transforms the data into the schema that is expected by the Office Charting Control.

That all sounds fine, except that the schema associated with the XMLData property for the control is not documented! In order to test and model the data, I created text fields that exposed the XMLInitiative ChartDataStackedByEmployee element on the form, copied the XML, and worked through the data transforms using a C# Windows Form application to model and test. (This tool is included in the code download accompanying this column, which is available from the MSDN®Magazine Web site). The transforms are then loaded into InfoPath as resource files and accessed through the DOM of the application.

Figure 9 shows an example of the code called to do the transform for the chart. Keep in mind the code is minimal compared to the effort spent in modeling and designing the XSLT files.

Figure 9 Transform Data to be Shown in a Chart

private void ShowChartInitiativeStackedByIndividual()

{

    // -- load the XML data file.

    try

    {

        // -- get the xslt xml

        XmlDocument xsltInitiativeChart = new XmlDocument();

        xsltInitiativeChart.LoadXml(thisXDocument.DataObjects[

            "ChartInitiativeStackedByIndividual"].DOM.xml);



        // -- create the XslTransform and load the stylesheet.

        XslTransform xslt = new XslTransform();

        xslt.Load(xsltInitiativeChart.CreateNavigator(), null, null);



        // -- get the doc loaded

        XmlDocument xmlXMLXFormProjectData = new XmlDocument();

        xmlXMLXFormProjectData.LoadXml(thisXDocument.DOM

            .selectSingleNode("/sr:Team/my:XMLXFormProjectData").text);



        // -- Transform the file.

        MemoryStream xmlOut = new MemoryStream();

        xslt.Transform(xmlXMLXFormProjectData, null, xmlOut, null);

        xmlOut.Position = 0;

        using(StreamReader sr = new StreamReader(xmlOut))

        {

            thisXDocument.DOM.selectSingleNode(

                "/sr:Team/my:XMLInitiativeChartDataStackedByEmployee")

                .text = sr.ReadToEnd();

        }

    }

    catch (XsltException ex)

    {

        thisXDocument.UI.Alert(ex.InnerException != null ?

            ex.InnerException.Message : ex.Message);

    }

    catch (Exception ex)

    {

        thisXDocument.UI.Alert(ex.Message);

    }

}

This view lets you examine the resource allocation by developer for each of your Initiatives within the organization by displaying a bar graph. Using the toolbar on the charting control, you can transpose the data to look at it from another perspective based on developers allocated to each project (see Figure 10).

Figure 10 Resource Allocation by Developer

Conclusion

InfoPath is a rapid application tool with an incredibly rich design surface and powerful development capabilities. The application I built here shows two sides of InfoPath development (the forms capabilities and the custom merge) and shows the range of possibilities that each approach provides.

I did not cover the integration of the Web services and SQL Reporting Services as that would considerably increase the scope of the column, but the design and benefits should be easy to grasp. The advantage of adding that functionality to the solution is that you get a back end for cross-department reporting integration.

Send your questions and comments to  insdmsdn@microsoft.com.

Larry W. Jordan Jr. manages the MSDN/TechNet development team. He has been a speaker at many Microsoft conferences over the years in areas pertaining to Microsoft.com architectures and services.