Knowledge Base and Samples

Reporting over InfoPath Libraries

Enesys RS Data Extension lets you build reports from InfoPath Libraries using the exact same approach as with standard lists. This is true as long as you are dealing with InfoPath Forms fields that are promoted as SharePoint columns. This article shows through a concrete example how you can go one step further and include in your own reports, forms data that are not mapped to SharePoint columns.

Introduction

InfoPath Form libraries are very similar to standard SharePoint lists when it comes to the Form fields that are promoted to SharePoint columns. Therefore, you can use Enesys RS Data Extension on InfoPath Form Libraries exactly the same way you would do with a "classic" list.

That's all good. However, an InfoPath form has generally more to offer than the fields that are being promoted as SharePoint fields. For instance, if you are handling "Invoices", your InfoPath forms will certainly contains a list of items that are being purchased. Obviously, because of the 1-N relation between an invoice and the purchased items, there is no way to promote such fields (item Quantity, item Unit Price) at the SharePoint item level.
Therefore it makes sense that you would like to include in your reports additional form fields beside the ones promoted as SharePoint columns.

Though Enesys RS Data Extension does not provide a direct path for handling this, we will show you in this article how you can achieve this result by using sub-reports and the XmlQuery statement.

Reporting over InfoPath Invoice Forms

We will go through a concrete example where we would like to build a report from InfoPath library used to store Invoices.

Infopath Library - Invoices

The invoice form template we are using is available as part of the Office templates provided by Microsoft:

Infopath Invoice Form

Though we could build a report over the promoted fields as with any SharePoint list, we will show how we can also display for each invoice the detail of the items purchased. Here is how the final report will look like:

Report that includes form data that is not part of the promoted fields

General Principles

The idea is that we will build a master report for designing the overall layout and display data from the regular SharePoint fields (fields that have been promoted from the InfoPath form). In order to display additional data from the InfoPath form, we will be using a sub-report that will retrieve the Form as XML data using Enesys RS Data Extension XmlQuery statement. The InfoPath XML URL will be passed to the sub-report as a parameter.

Let's put this into action.

Main Report

It is assumed that you have some experience with the creation of reports. We won't go into all the details and will mostly highlight the specifics needed for our reports.

In the main report, we will query over the InfoPath form library as we do with any other list.

We will first create a new report and add a new dataset to get data from our InfoPath library.

Inside the query designer we will add a new list statement and select the InfoPath form library as we would do for any SharePoint list:

You can select an InfoPath library exactly as you select a standard list.

We can select any form fields that have been promoted as SharePoint columns. Besides selecting the fields that we want to display within our report (Invoice Number, Invoice Date, Company and so on) it is important to select the field "EncodedAbsUrl". Because this is a hidden field, you will need to select "Show Hidden Fields" in the query designer. This field contains the absolute URL to the underlying InfoPath XML file for the item.

Query for the main report.

Let's save our query and design our report. We will be using a simple table layout and pick up the fields from the dataset we've just created. After polishing our design, we get a report similar to the following screen shot:

Preliminary design for the main report.

When previewing this report, we get something like shown in the following screen shot:

Preliminary Report Preview.

This is obviously not yet the final report we are expecting but we are on the right track. We will need to include the list of items purchased. And for this we will be using a sub-report that will display the needed data for each item (invoice).

Sub-report

The purpose of the sub-report is to display the list of items that have been purchased for a specific Invoice. Because such data can't be promoted as SharePoint columns, it is not possible to use a standard approach.

We will be using the XmlQuery statement. This statement lets you get data from an URL that returns data in XML Format. XmlQuery statement will parse the returned data so that you can use it in a "dataset" way. Because XML data can be quite complex, it is possible to specify an XPath expression that points to the data you need within the XML data.

We will first create a new report. We will add a new dataset to get the necessary data from the InfoPath form.

In the query designer, we will add an XmlQuery statement as shown in the following screen shot:

Adding a XmlQuery statement.

Right click on the newly created statement to edit the properties.

Edit XmlQuery statement properties.

As you can see in the following screen shot, instead of specifying a literal value for the URL, we are using a report parameter (@form-url!) so that it can work with any form being passed to the report.

XmlQuery statement Properties.

Another important property is the ElementPath that lets us point to the XML data we want by using an XPath expression. You can open a form in XML format in an editor to locate the XML path you need. In our case, the //inv:items lets us retrieve the list of all item purchased as shown in the following screen shot.

XML data for an InfoPath Invoice

Now we can execute the query within the designer just to ensure that we get the expected data. Because we are using a report parameter to specify the URL to the InfoPath form, we will need to provide some value for this parameter in the dialog box that pops up.

Enter some form URL for executing the sub-report query

We just need to enter the path to one of our InfoPath Form XML file. Be aware that you will need to add "?noredirect=true" at the end of the URL so that SharePoint does not redirect this URL:

        http://sps2010demo/sites/ersdedemo/InfoPath%20Invoices/Heavy%20Duty%20-%2020110624001.xml?noredirect=true
        
      

Once the query is executed, we can see that we get the expected data from the InfoPath form that we have specified the URL in the parameter.

After executing the query, the InfoPath Invoice data is displayed within the query designer.

We just need to save our query and build a very simple report from the dataset we have just created.
We just want to build a very minimalistic report so that it can display within the master report without clashing with the overall layout. We will use a simple table control and put the fields we need as shown in the following screen shot:

Sub-report layout.

After tweaking a few style properties, we can preview the sub-report in a standalone fashion (meaning haven't integrated it yet to the main report). It will be necessary to provide the path to some InfoPath form as part of the report parameter that is being used to point to an XML file. Of course this won't be necessary once the sub-report is being used as part of the main report:

Sub-report Preview.

We are now done with the sub-report. The last thing we need to do is to add it to the main report

Adding the sub-report to the main report

Time to finish our work. As initially explained, we want to have the list of purchased items to be displayed just below the Invoice Summary data. This is where we will need to put our sub-report. To do so, we will insert a line just below the current details as shown in the following screen shot:

Main report - insert a row below the current details row.

The newly created row has 4 cells because the report has 4 columns. We want the sub-report take all the available space in the row and will therefore merge the 4 cells:

Main report - merge cells.

To place our sub-report in our new row, we can just drag a subreport control from the toolbox. Once the subreport control has been added we need to specify the report that will be used as a sub-report. In order to do so, right-click on the sub-report and select "Subreport Properties...".
The "Use this report as a subreport" drop down lets you choose the desired report amongst the other reports available in your reporting solution. In our case, we will select the report that we have created earlier and that displays the list of purchased items:

Sub-report properties - specifying the sub-report.

The last thing we need to do is to specify the value that needs to be passed to our sub-report (as a report parameter) so that it can get the data from the appropriate InfoPath form. The "Parameters" section of the sub-report properties dialog box will let us do that.

In the parameters section of the subreport control we can specify the value that will be passed to the report parameter that is defined in our sub-report. In our case we need to pass the URL to the InfoPath form so that our sub-report can get the appropriate data for each invoice.

To do so, we will add a parameter and select the "form-url" parameter name in the drop down list (the report designer automatically discovers the report parameter that have been defined in the sub-report). The URL to the InfoPath form is available in our main report dataset as part of the EncodedAbsUrl field. However, as mentioned earlier, we need to pass "?noredirect=true" as part of the URL so that SharePoint return the raw XML data. Therefore, instead of using the field directly, we will be using an expression as shown in the following screen shot:

Sub-report properties - passing a parameter to the sub-report.

We are now finished and have our report that combines both InfoPath form data promoted as SharePoint columns and data living only inside the InfoPath forms.

Conclusion

Enesys RS Data Extension is already extremely capable when dealing with InfoPath data published as SharePoint columns. Its ability to join (even without a backing lookup field) and merge multiple lists and libraries makes it extremely useful.

The approach described in this article opens up even more interesting options.

Why would you want to report over InfoPath libraries and include data that is not part of the fields published as part of the SharePoint fields?

• Even though some fields may be published as SharePoint fields, you may not necessarily have control over the InfoPath template. Thinks of "comments" or "additional notes" it may not be natural to promote those fields. However you may want to include them in some report. The approach described here may be easier than going through a "request for change" process.
• Some data, like a collection of items, can't be published as SharePoint fields. In case you would like to display this data as part of a report, you don't have much options. This is exactly the scenario we have described in this article.
• Even though InfoPath Forms are already great for being printed or exported to PDF, one possible use of this approach is that you may have the needs for displaying the Form in a slightly (or completely) different way: say an invoice that should be displayed with less details for internal use.