I was given the task to develop a new customized SharePoint solution that replaces a current reporting process for an Infrastructure department which had proven to be inefficient and cumbersome to maintain due to the following reasons:
- The solution required significant amount of manual processing and was built using MS Excel and Access.
- The solution was difficult to maintain and provided no automation or integration with SAP. Users had to enter all the project information manually often resulting in errors.
- The solution required double entry of SAP financial information.
- The SAP financial information had to be entered manually which was prone to errors.
The solution was to devise a new customized SharePoint Solution with the following objectives:
- To minimize the manual process associated with generating infrastructure and IT PMO reports.
To provide an interface with SAP so that the relevant project financial information can be displayed on the appropriate report automatically
- Eliminate the double entry of data and gain confidence that information provided for decision making process is one version of truth.
- Eliminate the manual entry wherever possible and thus reduce the possible errors.
Programming Language: Visual C#.NET
SharePoint Version: Microsoft SharePoint Server 2010 Enterprise
IDE/Tools/Services Used: Visual Studio 2010, Microsoft InfoPath 2010, Microsoft SharePoint Designer 2010, Business Connectivity Service
Secure Store Service,
Database Server: SQL Server 2008 R2
The proposed solution was to use browser enabled InfoPath forms which would allow users project managers to fill up the forms online. Upon entering the project number or name in the form, the form will retrieve the SAP master and financial data from a shared network folder using Business Connectivity Services. Users can then go on to enter the manual information in the form.
There's scheduled process which extracts the data from SAP and dumps it into CSV files in the configured network folder. This is a scheduled daily process. The information in these CSV files is modeled into an external list by.NET assembly BCS connector.
The external list is referenced by InfoPath forms to gather SAP information. Users when enter a project number of select a project name from the dropdown list in the form, initiates a query to this external list which retrieves all master and financial information for that project. This information is loaded into the InfoPath form and saved when users save the form. The InfoPath form contains managed code written in C#.
During the monthly reporting period, the admin user goes to the administrative web part which contains the logic to submit the information to SQL staging database. During this process, forms which have been marked with "Completed" status are analyzed and their data is extracted and submitted to the SQL staging database.
After the data have been exported to SQL staging database, the information is then used by Business Objects for reporting purposes.
1. CSV Folder
SAP team was already using an automated dump approach for one of their other existing projects. So, it was decided to use the same approach for this project. The CSV files containing the SAP master data and financial data are dumped into a network shared folder by an automated process daily. This ensures that the data is up-to-date and users see the latest information when they open the form. Permission is set up in such a way that the account used to access this folder has at least read permission. Multiple CSV files are generated at this instance. So, the information in these CSV files are modeled into an external list using .NET assembly BCS connector and data is made readily available.
2. .NET assembly Business Data Connectivity Model, and Secure Store Service
A .NET assembly BCS connector was developed which reads information from these CSV files in a shared network folder, models the information and displays them in an external list. The result is a Business Data Connectivity Model with Code behind that contains the transformation logic. The logic is simple which correlates the data from different CSV files and creates a master tabular data which is displayed in external list.
The Business Data Connectivity Model is configured with a Secured Store Service application. Because this is a .NET assembly model, it is not straight forward to configure Secure Store Service with it. However it is possible and there are two possible ways, Code and No-Code approach, as indicated in this article How to Use the Secure Store Service from within a .NET Connectivity Assembly. I implemented the No-code approach as it is simple and better approach. With this, the Secure Store Impersonation credential is available within the .NET assembly connector which can then be used to impersonate before accessing those CSV files. With these configured properly, the external list should be able to display the information modeled from the CSV files.
This external list is then used by InfoPath forms and various other custom web parts to fetch the necessary SAP information. The figure below shows a snapshot of the data displayed in external list from three CSV files.
3. Administrator approved InfoPath Form with managed code
The main component of this project is browser enabled InfoPath form which is used to gather the user input. This is an administrator approved form template which contains coded logic. The form is complex with tabbed structure containing different types of fields and validations. When the form loads, it performs a query to the external list to retrieve the list of projects and populates them to the Project Name dropdown. When a search is made, it loads the latest master and financial information of the project into the form. The user just needs to worry about the information which is supposed to be manually entered.
The form itself is complex and contains many features. The figures below demonstrate the developed browser enabled InfoPath form.
4. Submitting the data to SQL Server using Custom Web Parts
A custom web part has been developed which basically go through all InfoPath forms that have been marked as "Complete", extracts the information and saves them into SQL staging database. This is a once in a month process where the administrator, after verifying that the forms have been completed, submits the data for reporting. The submitted data in SQL staging database is then used by Business Objects to generate relevant reports.
There is also an administrative statistics Web Part which displays the statistical information of the project forms (Number of forms, No of completed/incomplete etc.). This helps the administrator in determining the overall state of the project forms.
Below is a snapshot of a generated report.
Solution deployment and Packages
The entire system was developed and deployed as SharePoint WSP solution files. The solution contained following WSP packages:
- Common.wsp: Contained all common utilities like SharePoint ULS logging.
- CSVBDCModel.wsp: .NET assembly Business Data Connectivity Model
- InfoPathForm.wsp:Deploys the administrator approved InfoPath formwith managed code.
- Sandboxed solution: A sandboxed solution which configures the lists/libraries.