“Would you like to create detailed reports from your XML data without going to the trouble of creating a database mapping? With DBXL v2.5 and Report Builder, now you can.”
In this blog post, I’ll detail the steps on how to do simple XML reporting in your forms, using DBXL’s QueryDocumentsNodeSet and the Report Builder tool: • Extract data from your InfoPath forms even if you don’t have a database mapping
• Build your queries using DBXL v2.5’s Report Builder tool
• Export the results to Excel for pivoting and charting
Publish a sample InfoPath form to DBXL
1. Download the sample InfoPath form template (CompanyContactInfo.xsn) – this is a simple InfoPath form that has a few fields and allows submit to DBXL via the SubmitDocument operation.
2. Open the form template in InfoPath Designer.
3. Go to File > Save As and save the XSN locally.
4. Launch the DBXL Administration Tool (DAT) from your local Database Accelerator Control Panel (the access URL would typically be in the format http://localhost:8000/QdabraWebService/default.htm).
5. Create a new document type configuration – give it a descriptive name and then attach your XSN. When done, click Save.
6. Click on the Open link to open a new form using the template. Fill out and submit at least three to four documents.
Build your queries using the Report Builder tool
7. Download the Report Builder tool via this link.
8. To install, first make sure that InfoPath is not running (close all active InfoPath windows); run a command prompt as an administrator, change the directory to the location of your installation files, and then execute install.js
9. Launch the tool by opening InfoPath, clicking New and selecting Qdabra Report Builder listed under the Installed Forms section.
10. In the General section of the Report Builder tool, enter the Web Service URL Prefix, e.g. http://localhost:8000.
11. Enter QdabraWebService as the Web Service Name and then click Connect.
12. Start building your query by clicking Add Query > Create in the Queries table.
13. Select the document type you created in Step 5.
14. Click on the Schema icon (
) – this will display the custom task pane at the right, double-click on the root node which in our sample XSN is /my:myFields.
15. Click Insert Item and start adding the fields you wish to retrieve. Give each field an alias which will be the label/title in the Excel report for that particular field/column. Note that we can’t use spaces in aliases.
Notice the Query XML section – here, you’ll see the query string we’ll be using to retrieve the data using DBXL web service QueryDocumentsNodeSet operation later on. Copy this string and paste it in Notepad for now.
16. Click Perform Query – you’ll be taken to the Query All tab, click on the left arrow key (
) to expand and see the query result, like such:
You can click on the Export All Queries to Excel should you wish to use the data in Excel and do reporting.
Use QueryDocumentsNodeSet to auto-populate dropdowns
We can now use the DBXL’s web service method called QueryDocumentsNodeSet to retrieve data in a form from another form.
17. In InfoPath Designer, create a new blank form.
18. Go to Data > Data Connections > Add and add a data connection that retrieves data from a SOAP Web service.
19. The URL to the DBXL Document Service would be in the format: http://<DBXLServer>/QdabraWebService/DbxlDocumentService.asmx, where <DBXLServer> is the machine where DBXL is installed (in this case, your local machine):
20. Select QueryDocumentsNodeSet from the list of operations.
21. Set Sample Value of the tns:query parameter to the query string generated by Report Builder – remember that you’ve pasted this in Notepad earlier.
22. Click Next twice; give the data connection a descriptive name and then uncheck the box ‘Automatically retrieve data when form is opened’.
23. Click Finish > Close to exit out of the Data Connections wizard.
24. In the Fields task pane, add a new field called Company – display this on the canvas in a dropdown list box control, like so:
25. Right-click on the dropdown and select Drop-Down List Box Properties.
26. Change the List box choices selection to Get choices from an external data source. The data source will default to the one you just added.
27. Click on the icon next to Entries and then expand the nodes until you see the Company node – select that.
28. You might want to check the box ‘Show only entries with unique display names’ in case you have multiple entries with the same value.
29. Next, we’ll add form load rules that first sets the query string and then queries the data connection.
a. From the InfoPath Designer menu, go to Data > Form Load
b. In the Rules task pane that appears, click New > Action > Add > Set a field’s value.
c. Allow the action to set the query parameter of the QueryCompanies secondary data source, to the query string generated by the Report Builder (again, you pasted this in Notepad).
d. Add another action in the same rule that queries for data in the QueryCompanies data connection.
30. Preview your form template – your dropdown should auto-populate with data from your DBXL doctype:
There’s your form data from another form – no database queries/mapping involved!