May 2010 - Posts - Hilary Stoupa
in

InfoPath Dev

Hilary Stoupa

May 2010 - Posts

  • SQL Reports Love InfoPath Pictures

    Wouldn't it be nice to be able to use images attached to an InfoPath form in a SQL report? If you are using DBXL to submit your InfoPath data to a SQL database, you are in luck.

    InfoPath stores image files in Base64 encoding. Using your document type mapping in DAT, you can shred the value of your picture attachment field to a varchar or nvarchar field in your database. SQL report picture controls can then turn that Base64 back into a picture for display in your report!

    For this demo, I'm going to modify the sample Asset Tracking form that ships with InfoPath 2007. I'm going to assume you know how to use InfoPath and modify forms in design mode, that you have DBXL installed and know how to create a document type and a SQL mapping, and that you know how to create a basic SQL report.

    First, add a field for pictures to the main data source:
    Data Source

    Next, add a control to the form for the pictures:
    Form in design mode 

    After the form is ready to go, set up your tables for shredding, including a field for submitting your pictures:
    Table

    Next, in DAT, make sure your form's picture field is mapped to shred to your table:
    DAT Mapping

    Create a new report that uses the tables you are shredding your data to. Mine has a simple table bound to my dataset:
    Basic Report

    Then, in your report, add a picture control for the image data. If you are using SQL 2008, good on you, this is going to be super easy:
    Image properties

    But hey, if you are working with SQL 2005 (and lots of us are, don't worry about it), it is still pretty easy -- you just need to use an expression and convert your Base64 string. After adding your image control to your report, modify the properties to use the expression =System.Convert.FromBase64String(your picture field here):
    Image properties, SQL 2005

    Fill out and submit a few forms to DBXL:
    filled out form

    And, after you have data with pictures in your SQL tables, run your new report to see the images:
    Final Report

    The best of all worlds -- InfoPath to fill out your forms, DBXL for saving your data to SQL, and SQL reporting to give you aggregated info, including your images!

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