How to Use Report Builder to Build Queries and Retrieve Data from DBXL - Mel Balsamo
in

InfoPath Dev

This Blog

Syndication

Mel Balsamo

How to Use Report Builder to Build Queries and Retrieve Data from DBXL

“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.

image

6.       Click on the Open link to open a new form using the template. Fill out and submit at least three to four documents.

image

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

image

9.       Launch the tool by opening InfoPath, clicking New and selecting Qdabra Report Builder listed under the Installed Forms section.

image

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.

image

12.   Start building your query by clicking Add Query > Create in the Queries table.

image

13.   Select the document type you created in Step 5.

image

14.   Click on the Schema icon (image) – 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.

image

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 (image) to expand and see the query result, like such:

image

You can click on the Export All Queries to Excel should you wish to use the data in Excel and do reporting.

image

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):

image

20.   Select QueryDocumentsNodeSet from the list of operations.

image

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.

image

22.   Click Next twice; give the data connection a descriptive name and then uncheck the box ‘Automatically retrieve data when form is opened’.

image

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:

image

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.

image

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.

image

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

image

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).

image

d.      Add another action in the same rule that queries for data in the QueryCompanies data connection.

image

30.   Preview your form template – your dropdown should auto-populate with data from your DBXL doctype:

image

There’s your form data from another form – no database queries/mapping involved!

Comments

No Comments
Copyright © 2003-2018 Qdabra Software. All rights reserved.
View our Terms of Use.