June 2011 - Posts - Mel Balsamo
in

InfoPath Dev

This Blog

Syndication

Mel Balsamo

June 2011 - Posts

  • 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!

  • Make that repeating group display two rows per item instead of just one!

    The default Expense Report form that’s shipped with InfoPath 2007 makes use of a table that shows the expense item details per row. Your requirement is to add a new field where users can enter notes or comments for that particular expense item – you’d like to display this new field underneath the already-existing row in the table, therefore having 2 rows for each item now.

    The technique used in this blog doesn’t only apply to the Expense Report form but to all others where you’d want to show two rows per item in a repeating group, instead of just one.

    Adding a new field is easy:

    1. In your main data source, locate the items group > item repeating group and add a new field called notes (you can set it either as Text (string) data type or a Rich Text (XHTML) depending on your requirement).

     

    Your main data source should look similar to this:


    The tricky part is the layout – displaying the controls in two separate rows, and making the header/footer not repeat like how it is in a repeating table.

    To achieve the layout we want, we need to skip using a repeating table and use a repeating section instead. Follow along…

    2. Place your cursor where you want to display the section on the canvas - the Itemized Expenses layout table would probably be your pick. Don’t delete the existing repeating table just yet so you can copy/paste or drag/drop some items over later.

    3. In the Data Source taskpane, right-click on the items parent group and select Section – this will insert a section right where your cursor is on the canvas. Set this section’s padding to 0px to get rid of the unnecessary white spaces inside the section and that all its contents will stay as close to the edges as possible.


    4. With your cursor placed inside the newly-added section, go to the Table menu > Insert > Layout Table… and insert a 5 columns by 3 rows table.

    You may specify the number of columns you want depending on the fields you want to display. We’re adding 3 rows however, so we’ll have the top row for our table header, 2nd row for the repeating section that will hold the expense item details (so merge the cells to make it 1 whole row without columns), and the 3rd for the total amount of the expenses.

     

    5. Place your cursor in the second row (the one you just merged) and again, set all the paddings to 0px.


    6. Without moving your cursor where it’s at, right-click on the items repeating group and then select Repeating Section. This time, set all its paddings and margins to 0px.

     

    In InfoPath 2007, sections and repeating sections have some kind of invisible borders by default. Remove this by selecting the repeating section, and selecting None in the Borders and Shading dialog.


    Again, we’re doing all this to eliminate all the white spaces as possible in order to achieve that all-in-the-same table effect (like what a repeating table would give) even though we’re using a repeating section.

    7. With your cursor still inside the repeating section, insert a 5 columns by 2 rows table. The 1st row will hold the default details (Date, Description, Category, Cost and the arrow button) while the second is for the new notes field.

    8. Add the controls inside the table (you can drag and drop from the existing repeating table and delete it afterwards).

    Adjust the formatting to your liking – resize the cells making sure that all the columns and rows are perfectly aligned, fix the paddings and margins, add borders/shading, etc.

    Your layout might look similar to this:


    And in Preview, something like this isn’t bad at all:


    Have fun! :)

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