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.
- Min Version: Enesys RS Data Extension 3.6.26 - Standard and Enterprise Editions
- Last Updated: 12/13/2011
Update: Starting from version 3.7, Enesys RS Data Extension InfoPath statement
lets you select any fields from your InfoPath form library. Though you can still use the
approach described in this article, you may want to use an InfoPath statement
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
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
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
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
Let's put this into action.
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
We will first create a new report and add a new dataset to get data from our InfoPath
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
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).
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.
Even though we are using the term "sub-report" to distinguish this
report from the main report and because we know that we will be using it as a sub-report,
this is only a standard report we are creating at this stage. A sub-report is really the
execution and rendering of a report inside another report.
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
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:
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
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:
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:
We are now done with the sub-report. The last thing we need to do is to add it to the
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
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
Sub-report properties - specifying the sub-report.
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:
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
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.
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
- 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.