The necessity of data receiving from a certain list and generating a report often appears when sending e-mail alerts or creating a document. For instance, a user needs to display the list of his active tasks, administrator needs information about new users in the system, a client wants to see his active issues, etc. In other words, it’s highly important and effective to create a report based on data list.
Activities of business process automation are often implemented with Microsoft Office SharePoint Designer, which is easy to use and suitable for quick implementation. But unfortunately, generating reports is not available with Microsoft Office SharePoint Designer.
This article describes how to create a report with VirtoSoftware Workflow Activities Kit for SharePoint and prepare it for sending/processing.
Install and activate VirtoSoftware Workflow Activities Kit for SharePoint on your site.
Then add fields.
ame Type Description IssueOwnerEmail Single line of text Responsible Person or Group Description Multiple lines of text Enhanced rich text (Rich text with pictures, tables, and hyperlinks) Priority Choice (menu to choose from) Choice:
IssueStatus Choice (menu to choose from) Choice:
Closed Resolution Multiple lines of text Enhanced rich text (Rich text with pictures, tables, and hyperlinks)
After completing that, add several issues to this list where Issues with different responsible users are contained and whether they are active or closed Issues.
Creating a Report
Let’s run Microsoft Office SharePoint Designer 2007, connect to the site and select File/New/Workflow in the menu. Then enter name “Create report for Responsible“ in the displayed window, and select list ”Company Issues” that was created before. Since we need to generate a report, both while creation and modification of a list, we should check all the boxes in “Select workflow start options for items in …” block.
After checking all the boxes, click Next to create activities.
To create a report, you only need to create one activity – “Generate report”. “Generate report” activity produces the report template parameters and parameters of collecting items from a list. The generated report is stored to a string variable. You can even describe the data source, filters and data sorting on the activity level. You should also describe the view structure of your report with the help of header, footer and list item templates.
Scheme of the report:
[Row for List Item 1]
[Row for List Item 2]
[Row for List Item N]
Since “Generate report” activities work with rows, we can generate the report in any text format: simple text, XML, HTML, etc.
To add new activity, click Actions and select More Actions in the drop down. In the displayed window select Virto Actions category and select “Generate report” action. Then click “Add”.
You will see the new activity in the list as a result.
“Generate report” activity contains the following parameters:
Parameter Description this header Optional. The template for the heading section of the report. this row template Optional. The template for the report rows. Use string [%FieldName%] construction to resolve field value from current list item. FieldName can be field display name, field id (GUID) or field index (int). this footer Optional. The template for the footer section of the report all Optional. List item filter. The filter string supports View Name, View ID and CAML string (…). Empty filter returns all items.
Use … element to sort list items. current list Optional. Source list. Default value is current list.
We will leave Current list field without any modifications since we are going to select data from the current list. In your projects you can select any available list as a data source.
We should point CAML request instead of all. CAML request will select open Issues for a current responsible user and sort them by priority.
CAML request which selects all Issues sorted by priority for user ID #123 looks as follows.
We should insert the text highlighted with bold instead of “all” value. This means that we will not insert Query tags and replace user ID with Responsible value from the current row. Unfortunately, if we add direct link to the value of Responsible field, Workflow will replace it to the row of DOMAINuser type while implementation. To receive user’s ID we need to use one more activity from Virto Activities Kit – “Lookup site user property”. This activity stores the value of select user profile in the variable. Let’s add this activity before report generating.
Insert responsible variable to CAML request.
Instead of CAML request description, you can use Web interface to create new view, filter and sort required rows, and enter name or its ID in the activity.
For simplifying reasons, the report will not contain too much HTML. Use the following values instead of this header, this footer and this row template values.
this header Active issues:
If we leave at least one of these values without any modifications, empty row will be used while report generating. The most significant in these rows is the fact that you can use both Workflow Lookup and links to field values in row template description. To do this you should use [%Field name%] constructions. They will be replaced with real values while report generating.
The description of “Generate report” activity parameters is completed. Now, while creating or modifying of a current Responsible in the Issue, the report will be generated.
So after completing all that, send this report by e-mail. In order to do this, let’s add Send Email activity after report creating. We can use standard activity or one of the Virto Activities Kit. In this case you will have an opportunity to attach files, add sender, or adjust SMTP server which you want to use for e-mail sending.
The full scheme of the business process looks as follows:
Save this business process. Then go to your site, add a new Issue or close (edit) the existing one. The responsible user will receive an e-mail notification to his e-mail address with the list of active Issues.
With the help of Virto Software Workflow Activities Kit for SharePoint, we have found the solution for business tasks regarding HTML report generating based on SharePoint list data. Further versions may include table display improvement and adding HTML links on objects. This is helpful to navigate to objects from the e-mail message directly.