Attachment Template - Delimited File Generator

Post date: Oct 24, 2011 7:20:57 PM

This post outlines how to configure Delimited File templates using the Attachment module. It has the ability to generate text files for each table returned in the query. The file(s) can generated based on the exact content of the query or the format of the text files and fields can be overriden in the template Xslt. This template currently DOES NOT support passing a DataSet from a report to render content but instead executes astored procedure specified from the Xslt inside the plug-in to utilize a DbDataReader for performance considerations.

• • Download latest qbo.Attachment.Template project and ensure DelimitedGenerator.cs is included in project. Build and and deploy the dll. Default deployment location is:

    • C:/Program Files (x86)/Quandis/Quandis Business Objects v2.0.0/Bin/

  • Navigate to Configuration -> AttachmentTemplates. Add a new entry:

    • Name: DelimitedGenerator

    • Assembly: C:/Program Files (x86)/Quandis/Quandis Business Objects v2.0.0/Bin/qbo.Attachment.Template.dll

    • Assembly Namespace: qbo.Attachment.Template

    • Upon completion, AttachmentTemplate.config should contain an entry:

   <AttachmentTemplateAssembly>

        <Name>DelimitedGenerator</Name>

        <AssemblyFile>C:/Program Files (x86)/Quandis/Quandis Business Objects v2.0.0/Bin/qbo.Attachment.Template.dll</AssemblyFile>

        <NameSpace>qbo.Attachment.Template</NameSpace>

      </AttachmentTemplateAssembly>

• • Create Attachment Template:

    • MimeType: text/xml

    • AttachmentService: DelimitedGenerator

    • Report: <Assoicate Custom Report>. Note the report is only used to pass custom parameters to the Transform Xslt which instructs the plug-in to execute a stored procedure within the plug-in.

    • Transform: <See Below>

  • Create Report

    • This attachment template supports accepting a XmlDocument (which represents the report output) or executing a stored procedure inside the AttachmentTemplate code. This feature is available as a workaround to IAttachmentTempalte not supporting DbDataReaders. This pattern is used for large recordsets that could generate memory exceptions. Note:

      • Executing a stored procedure inside AttachmentTemplate - in this case, the report only needs to select a stored procedure name and parameter values as these will be passed to the AttachmentTemplate to execute.  The AttachmentTemplate's Transform Xslt will format the parameters so they can be passed to qbo.Data.StoredProcedure

  • AttachmentTemplate Transform - Used to set file attributes and format of columns. It also contains a Stored Procedure call inside AttachmentTemplate. Xslt needs to render a StoredProcedure with parameters. See example below:

<xsl:stylesheet version="1.0"

                xmlns:qbo="http://services.quandis.com/"

             xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

>

  <xsl:output method="xml" indent="yes" omit-xml-declaration="yes"/>

  <xsl:template match="*">

    <DelimitedFile>

      <!--Stored Procedure Call (standard format)-->

      <StoredProcedureCollection>

        <StoredProcedure name="rTestDeliminted">

          <ServiceOrderID>

            <xsl:text>1773</xsl:text>

          </ServiceOrderID>

        </StoredProcedure>

      </StoredProcedureCollection>

      <!--

          FileSpec - (optional) Default behavior for all files generated

          Deliminter - (optional) Defaults to "|"

          Prefix - (optional) If specified will set Attachment, AttachmentType = {Prefix}.{TableSequence}.{Extension}

          Extension - (optional) Specifies file extension

          IncludeHeader - (optional) Includes header row - default false

          Wrapper - (optional) wraps field values (default is blank)

          PublicIndexID - (optional) - Public PGP IndexID - if both supplied all files will be encrypted using these keys

          PrivateIndexID - (optional) - Private PGP IndexID

      -->

        <FileSpec Deliminter="|" Prefix="Test" Extension="txt" IncludeHeader="true" Wrapper="'" PublicIndexID="4" PrivateIndexID="15">

        <!--

          File - (optional) Overrides global behavior. Sequence of File must match sequence of dataset. Eg. If SP returns 3 tables, first table returned will match first FileNode

          TableName - (optional) If specified will set Attachment, AttachmentType = {Prefix}.{Extension}

          DefaultValue - (optional) if provided, will set value of field if field value is null

          FormatType - (optional) Custom formatting. Current enumerations are: DateTime, String

          FormatPattern - (optional) Pattern used by format type

          Note if you supply no Field nodes within a FileNode the Query columns will be used

          PublicIndexID - (optional) - Public PGP IndexID. Overrides global behavior.

          PrivateIndexID - (optional) - Private PGP IndexID

        -->

        <File Deliminter="," TableName="Person" Extension="txt" IncludeHeader="false" >

          <Field name="PersonIdentifier" Mapping="PersonID"/>

          <Field name="SubscriberID" Mapping="SubscriberID" DefaultValue="SubscriberID is missing"/>

          <Field name="Descriptor" Mapping="Person" DefaultValue=""/>

          <Field name="Last Login Date" Mapping="LastLogin" FormatType="datetime" FormatPattern="yyyyMMddhhss"/>

        </File>

        <File Deliminter="," TableName="SystemFunction" Extension="txt" IncludeHeader="true">

          <Field name="Function" Mapping="SystemFunction"/>

          <Field name="SystemFunctionID" Mapping="SystemFunctionID"/>

          <Field name="Comment" DefaultValue="Force Comment"/>

        </File>

      </FileSpec>

    </DelimitedFile>

  </xsl:template>

</xsl:stylesheet>

Encryption Requirements:

• • Encryption can be enabled at both the global level and File level (override). Both a PublicIndexID and PrivateIndexID must be supplied. This corresponds to the IndexIDs displayed in /Security/PKI/PublicKeyList.aspx of the application. Once must ensure the application root contains:

    • folder: {root}/Security/PKI

    • files: Quandis.Private.pkr, Quandis.Public.pkr

  • For example, to encrypt for Saxon,  use the QDS pkr files (copy to /Security/PKI) and set Saxon Indexes:

    • PublicIndexID = 4

    • PrivateIndexID = 15

Expected Output:

• • The plug-in will generate a text file for each separate SELECT statement returned in the stored procedure. It will save an Attachment record for each text file binding the file to an Attachment record. It will also generate an attachment of file type Xml containing a collection of Attachment records in QBO format for the files generated. This is the "parent" attachment and can be used to programatically determine which attachments are bound to the parent.