Building Permit Application Solution: KPIs

This is the sixth and final installment in a series about the building permit application, an end-to-end solution built entirely with the 2007 Microsoft Office release. The previous post described how to enable form conversion for archiving in a SharePoint document library. This post focuses on key performance indicators (KPIs).

The Report Center is a new feature in Microsoft Office SharePoint Server 2007 that includes a number of business intelligence capabilities. One of these capabilities is the KPI, which allows users to report against data in a site collection. You can create a KPI for data in a SharePoint list or published Excel workbook, from SQL Server 2005 Analysis Services, or with manually entered information. The following figure shows the KPI list used in the building permit application solution. The list is displayed within a Web part on a team dashboard page in the Report Center.

The first two KPIs shown in this list are reporting against data in the Building Permit Application document library. They identify a percentage of applications with an OPEN status value that have been assigned to a particular compliance reviewer. Status icons located to the right provide an indication of whether goal or warning targets have been met.

Adding a new KPI for open applications assigned to a different compliance review is quite simple. From the New menu, you select the Indicator using data in SharePoint list command. On the KPI Definitions page, type a KPI name in the Name text box and then provide the URL for the SharePoint list in the List URL field. Then, in the Value Calculation subsection, you would use promoted SharePoint columns to create the logic for your KPI. In this case, you would be calculating the percentage of list items where the Permit Status column value is equal to OPEN and the Reviewer column value is equal to the name of the compliance reviewer.

After setting the logic in the Value Calculation subsection, you need to provide goal and warning values in the Status Icon section. When you are done, click OK and the KPI will be rendered in your list.

This post concludes my series about the building permit application solution. If you are interested in learning more about this solution, later this month I will be presenting it to Microsoft staff at TechReady3 as an instructor-led lab. For the broader audience, I will have an article about this solution published in the Windows Vista and Microsoft Office Beta Experience Newsletter, with downloads available. That article is scheduled to appear in Issue No. 5, and I will follow up with another post after it is published.

7 thoughts on “Building Permit Application Solution: KPIs

  1. Have you tried to hook up KPIs in MOSS with Analysis Services? I’m having trouble getting those two to work together in my installation. Any expertise you can pass on?

  2. Hi, Mike.

    Our group has created some solutions that use SQL Analysis Services KPIs. If you have a specific list of questions or issues, you can send them to We can at least get you started with your installation.


  3. Hi,

    Thanks for the great feedback.

    I have created a workflow for a list and works perfectly. As you can imagine I want to track the progress of that workflow with KPIs.

    In the all view I can see the name of the workflow and in the data the spatus. (in progress or completed).

    I tryed setting the "Percentage of list items where" for the name of the workflow (which is in the drop down) and is equal to Completed or "Completed" or [Completed].

    None of the above options works eventhough in my list I can see a list item marked as completed for the specific workflow. Any ideas?

  4. Hi, Evangelos.

    The status field to which you are referring has an integer value, even though it displays text ("Completed", "In Progress", "Failed", etc.) in the SharePoint list view. I believe the mapping for the "Completed" value is "5", so try that instead in your KPI definition.

    BTW: That field indicates the status of the workflow that is attached to the list. In the building permit application solution, the "Permit Status" field actually identifies the status of the permit application ("OPEN", "APPROVED", OR "REJECTED"). I’m sure you already figured that out, but I just wanted to clarify for others.


  5. Great!!!

    The solution worked.

    Can you please point me to some documentation so I can do some more constructive reading and figure out the other states of the workflow (In Progressm Failled etc)?

  6. Thanks a million.

    Here is what I found:

    public enum SPWorkflowStatus
    NotStarted = 0,
    FailedOnStart = 1,
    InProgress = 2,
    ErrorOccurred = 3,
    StoppedByUser = 4,
    Completed = 5,
    Max = 15,

Comments are closed.