Displaying Image from File Attachment control in InfoPath form inside SSRS report.


I was assigned the task of displaying images attached to InfoPath form using file attachment control in one of the SQL Server Reporting Services Report.

For this we need to add an image control and set it’s property in the following manner

Image Source :- Database

Then select appropriate DataSet, Image Field and MimeType

Here to display the Image properly we need to write a Report Assembly with the following code.

public static class FileDecoder
    {
        /// <summary>
        /// Takes string value for image from the infopath form
        /// and converts it to proper bytes for displaying it as an image
        /// </summary>
        /// <param name="imageData">string representation of the image</param>
        /// <returns>corrected byte array</returns>
        public static byte[] GetCorrectedByte(string imageData)
        {
            byte[] attachmentNodeBytes = Convert.FromBase64String(imageData);
            // Position 20 contains a DWORD indicating the length of the
            // filename buffer. The filename is stored as Unicode so the
            // length is multiplied by 2.
            int fnLength = attachmentNodeBytes[20] * 2;
            // The file is located after the header, which is 24 bytes long
            // plus the length of the filename. 
            byte[] fileContents = new byte[attachmentNodeBytes.Length – (24 + fnLength)];

            for (int i = 0; i < fileContents.Length; ++i)
            {
                fileContents[i] = attachmentNodeBytes[24 + fnLength + i];
            }

            return fileContents;
        }

    }

 

And set the value for image control as

=(Namespace).FileDecoder.GetCorrectedByte(Fields!fieldName.Value)

ImageFromDB

 

Bye..

InfoPath forms and SQL Server Reporting Services


Hi,

I recently worked on a report project having InfoPath form in SharePoint’s InfoPath library as it’s Data Source.

For understanding purpose let’s start with a very simple InfoPath form having two text boxes in it named txtFirstName and txtLastName.

Form

Let’s first define a Data Source for our report.

Name:- InfoPathDataSource

Type:- XML

Connection String : (Suppose the url for our InfoPath library is following

http://nrana-1710-vm1:2575/Sample/Forms/AllItems.aspx and the name of the InfoPath form is PersonalInfo.

So in this case our connection string would be

http://nrana-1710-vm1:2575/Sample/PersonalInfo.xml

Now let’s create a new DataSet having InfoPathDataSource as its data source.

Name :- InfoPathDataSet

Data source :- InfoPathDataSource

Command Type :- Text

Query String :-  ( For defining query string we need to first open up the PersonalInfo.xml file. Paste the following url in IE http://nrana-1710-vm1:2575/Sample/PersonalInfo.xml and download and open the xml file.

Suppose this is the content of the PersonalInfo.xml file

<?xml version="1.0" encoding="utf-8"?>
<?mso-infoPathSolution
name="urn:schemas-microsoft-com:office:infopath:Sample:-myXSD-2010-05-26T09-16-03"
solutionVersion="1.0.0.10"
productVersion="12.0.0.0"
PIVersion="1.0.0.0"
href="
http://nrana-1710-vm1:2575/Sample/Forms/template.xsn"?>
<?mso-application
progid="InfoPath.Document"
versionProgid="InfoPath.Document.2"?>

<my:myFields xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2010-05-26T09:16:03" xml:lang="en-US">
    <my:txtFirstName>Nishant </my:txtFirstName>
    <my:txtLastName>Rana</my:txtLastName>
</my:myFields>

The  Query String to retrieve First and Last name value would be

<Query xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2010-05-26T09:16:03"
xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-us">
    <ElementPath>
        my:myFields           
    {
        my:txtFirstName(String),
        my:txtLastName(String)       
    }
    </ElementPath>
</Query>

We also define a parameter in our data set to which we would pass the url to the infopath form i.e.

http://nrana-1710-vm1:2575/Sample/PersonalInfo.xml

That’s it, now if we run the query we can get values for first name and last name.

In case if your InfoPath xml form is looks like this

<my:myFields xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2010-05-12T05:10:51" xml:lang="en-US">
    <my:group1>
        <my:group2>
            <my:field1>2</my:field1>
            <my:field2>nishu</my:field2>………….

Than the Element Path of Query String would be like

<ElementPath>
        my:myFields/my:group1/my:group2          
    {
        my:field1(String),
        my:field2(String)       
    }
    </ElementPath>

Bye…

Calculation on Repeating Table Changed Event in InfoPath


We had a requirement of creating an InfoPath form that would use a repeating table and to do certain calculations on the values in rows of the repeating tables and display the result in a separate section.

Here is a screen shot of the same !

infopathCalculation

So above here, based on activity selected and time spent specified for it, the Total hours and Total days ( total hours/ 8) needs to be calculated.

Here is the sample code for that

public void group7_Changed(object sender, XmlEventArgs e)
       {
           // Create an XPathNavigator from the main data source
           XPathNavigator domNav = MainDataSource.CreateNavigator();
           // Create an XPathNodeIterator to iterate through all the rows
           XPathNodeIterator rows = domNav.Select(
           "/my:myFields/my:group6/my:group7", NamespaceManager);
           // Create Navigator for each of the fields where total needs to be displayed
           XPathNavigator lblTotalSpecHours = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:lblSpecHours", NamespaceManager);
           Int32 intTotalSpecHours = 0;
           XPathNavigator lblTotalConsultancyHours = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:lblTotalConsulHours", NamespaceManager);
           Int32 intTotalConsultancyHours = 0;
           XPathNavigator lblTotalDevelopmentHours = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:lblTotalDevHours", NamespaceManager);
           Int32 intTotalDevelopmentHours = 0;
           XPathNavigator lblTotalTestingHours = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:lblTotalTestingHours", NamespaceManager);
           Int32 intTotalTestingHours = 0;
           XPathNavigator lblTotalDeploymentHours = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:lblTotalDepHours", NamespaceManager);
           Int32 intTotalDeploymentHours = 0;

           // loop through all the rows of repeating table
           while (rows.MoveNext())
           {
               // Get values for Activity dropdown field and Time Spent field
               string fldActivity = rows.Current.SelectSingleNode(
               "my:ddlActivity", NamespaceManager).Value;
               string fldTimeSpent = rows.Current.SelectSingleNode(
               "my:txtTimeSpent", NamespaceManager).Value;                 

               if (fldActivity == "Specification" && fldTimeSpent!="")
               {
                   intTotalSpecHours += Convert.ToInt32(fldTimeSpent);                       
               }

               if (fldActivity == "Consultancy" && fldTimeSpent != "")
               {
                   intTotalConsultancyHours += Convert.ToInt32(fldTimeSpent);
               }

               if (fldActivity == "Development" && fldTimeSpent != "")
               {
                   intTotalDevelopmentHours += Convert.ToInt32(fldTimeSpent);
               }

               if (fldActivity == "Testing" && fldTimeSpent != "")
               {
                   intTotalTestingHours += Convert.ToInt32(fldTimeSpent);
               }
               if (fldActivity == "Deployment" && fldTimeSpent != "")
               {
                   intTotalDeploymentHours += Convert.ToInt32(fldTimeSpent);
               }          

           }

           // for Specification
           RemoveNilAndSetHours(lblTotalSpecHours, intTotalSpecHours);
           XPathNavigator lblTotalSpecDays = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:lblSpecDays", NamespaceManager);
           RemoveNilAndSetDays(lblTotalSpecDays, intTotalSpecHours);       

           // for Consultancy
           RemoveNilAndSetHours(lblTotalConsultancyHours, intTotalConsultancyHours);
           XPathNavigator lblTotalConsultancyDays = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:lblTotalConsulDays", NamespaceManager);
           RemoveNilAndSetDays(lblTotalConsultancyDays, intTotalConsultancyHours);

           //// for Development
           RemoveNilAndSetHours(lblTotalDevelopmentHours, intTotalDevelopmentHours);
           XPathNavigator lblTotalDevelopmentDays = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:lblTotalDevDays", NamespaceManager);
           RemoveNilAndSetDays(lblTotalDevelopmentDays, intTotalDevelopmentHours);

           //// for Testing
           RemoveNilAndSetHours(lblTotalTestingHours, intTotalTestingHours);
           XPathNavigator lblTotalTestingDays = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:lblTotalTestingDays", NamespaceManager);
           RemoveNilAndSetDays(lblTotalTestingDays, intTotalTestingHours);

           //// for Deployment
           RemoveNilAndSetHours(lblTotalDeploymentHours, intTotalDeploymentHours);
           XPathNavigator lblTotalDeploymentDays = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:lblTotalDepDays", NamespaceManager);
           RemoveNilAndSetDays(lblTotalDeploymentDays, intTotalDeploymentHours); 
       }

       // RemoveNil for the fields and set value
       public void RemoveNilAndSetDays(XPathNavigator xpathNav, int totalHours)
       {
           if (xpathNav.MoveToAttribute("nil", "http://www.w3.org/2001/XMLSchema-instance"))
               xpathNav.DeleteSelf();
           int days = totalHours / 8;
           xpathNav.SetValue(days.ToString());
       }

       public void RemoveNilAndSetHours(XPathNavigator xpathNav, int totalHours)
       {
           if (xpathNav.MoveToAttribute("nil", "http://www.w3.org/2001/XMLSchema-instance"))
               xpathNav.DeleteSelf();
           xpathNav.SetValue(totalHours.ToString());
       }

 

 

Change the extension of the file from .doc to .xsn !!!

Bye..

Schema validation found non-data type errors in InfoPath


We could get the above error while trying to set value for a field using XPathNavigator. The reason could be that the field has the nil attribute set

Most of the fields with the exception of String Type uses nil to indicate “no value”.

So before we could set the value for those fields, we need to remove the nil attribute from it.

We could use the below code for that.


XPathNavigator node= MainDataSource.CreateNavigator()

.SelectSingleNode(“/my:myFields/my:lblSpecHours”, NamespaceManager);

if (node.MoveToAttribute(“nil”,

http://www.w3.org/2001/XMLSchema-instance&#8221;))

node.DeleteSelf();

node.SetValue(txtValue);


Bye..

%d bloggers like this: