Report Approval Workflow

  • Article

One of the visions of this reporting platform was to become the single source of truth for Marketing. It would be a centralized destination for marketers from around the global subsidiaries to, where primarily their task might be one among the following:

  1. Design, save or share reports
  2. View reports available on the platform
  3. Schedule often viewed report

Requirements 1 and 2 could conflict and contradict each other given the global nature of our platform. An example would be a marketer who builds a report a certain way and saves it to the platform. Another marketer views this report and there is no way for us, as a platform, to guarantee the validity of the report. Scenarios like this could certainly erode the trust and our vision to be the single source of truth.

To overcome this challenge while still satisfying the overall goal of an open platform which allows users to design, view, save and schedule reports, we introduced a concept of report approval workflow.

  • Use cases of scenario 1 would primarily design and save reports to a draft reports list.
  • Use cases of scenario 2 would primarily view published reports list which are certified reports

What happens between a) and b) is where the report approval workflow triggers and that's what makes the reports in published reports list certified.

  1. Users from use case 1 (let's call him contributor) above design and save a report to the drafts list. After going through multiple iterations, once the user is comfortable with the design of the report, he chooses "Publish" from the SPS context menu. The contributor is prompted to choose a destination report folder before he can submit the report for publishing (the report folder drop down is populated from a Report Approvers SharePoint list which contains the report folders and their corresponding approvers alias)
  2. At this point the workflow kicks in. Workflow looks up the name of the approvers for the report folder chosen by the user. It creates a SharePoint task for each approver and sends an email with content asking for report approval
  3. The approver(s) can choose to approve, reject or ask the user to make modifications to the report.

    1. If the report is approved, it is moved from the draft to the corresponding folder in the published reports list. If the destination folder doesn't exist, the workflow creates a new folder assigns full permissions only to the approvers/admins and RO to all other authenticated users.

    2. If the report is rejected or medications have been requested, the report stays in the drafts list

      An email is sent to the report contributor letting him know the outcome of the process

This workflow was developed using the Approval Workflow Sample from the SharePoint Server 2007 SDK as the base. Some of the features of this modified workflow that I would like to call out are:

  • Meta-data inside a SharePoint list: The workflow as well as certain pieces of UI needs the list of approvers for a given report folder which was stored in a SharePoint list. The nice thing of doing it this way is that a) you can re-use the list info in other places within SharePoint like DIP (document information panel), SharePoint web parts among other places b) You get both a persistence medium as well as nice UI for your site admins to manage the meta-data.
  • Config info inside a SharePoint list: Report approval workflow is heavily config driven where you can customize the same workflow to publish to a different list, modify subject/content of email to approvers, contributors, and enable/disable sending email among multiple other ones.
  • Logging: Centralized logging using the Logging component described here.

Some of the common activities that were fairly used across the workflow are:

  • Create Folder: Creating a folder on the destination Published Reports List requires that all List<Contact> admins have full permissions:

    SPFolder spFld = list.RootFolder.SubFolders.Add(folder);

    spFld.Item.BreakRoleInheritance(true);

    foreach (Contact admin in admins)

    {

    SPRoleAssignment userRoleAssignment = new SPRoleAssignment(admin.GetPrincipal(list.ParentWeb));

    userRoleAssignment.RoleDefinitionBindings.Add(list.ParentWeb.RoleDefinitions.GetByType(SPRoleType.Administrator));

    spFld.Item.RoleAssignments.Add(userRoleAssignment);

    }

    spFld.Item.Update();

  • File Exists: Check if a fileName exists in the destination Published Reports SPList list in the folder

    SPFolder subFolder = list.RootFolder.SubFolders[folder];

    SPQuery query = new SPQuery();

    query.Folder = subFolder;

    SPListItemCollection folderFiles = list.GetItems(query);

    for (int i = 0; i < folderFiles.Count; i++)

    {

    if (fileName.Equals(folderFiles[i].Name, StringComparison.CurrentCultureIgnoreCase))

    {

        ret = true;

    break;

    }

    }

  • Get Approvers: We stored the report folder name and its approvers in a SharePoint list. When the workflow needed to know the names of the approvers for a given report folder, we discovered that SharePoint doesn't exactly return what we store inside it where the pattern seemed to be: 1;#Joe;#23;#Henry;#19;#Andrew. So we wrote our little method to retrieve the List of approvers where Constants.APPROVERSDELIMITCHAR = ';':

    List<string> ret = new List<string>();

    string[] splitApprovers = approverItemVal.Split(new char[] { Constants.APPROVERSDELIMITCHAR }, StringSplitOptions.RemoveEmptyEntries);

    for(int i = 0; i < splitApprovers.Length; i++)

    {

    if(0 != i % 2) //The pattern needs us to split where every second one is our value

    ret.Add(splitApprovers[i].Split(new char[] { Constants.METAINFOPARSERCHAR })[1]);

    }