Skip to Content

In this blog i would like to explain an approach to build the target file in the desired Excel format using the xsl style sheet.  As we are aware SAP BusinessObjects Data Services accesses Excel workbooks as sources only (not as targets). So to overcome this limitation we can adopt this approach to display our output in the desired excel format with the help of XSL.

Details on the approach

In this approach we will be building a xml file using the BODS and will be displaying the xml content in the desired tabular format with the help to XSL.

So first we have to create a batch job that creates a xml which contain the required data. Special care must be taken while designing the Xml structure that holds the data need to be displayed in tabular structure. Consider this excel structure in the below example.

XlStructure.JPG

In this we have two tabular structure one to hold the header part and second to hold the category part. So when we define the xml structure in the BODS we need to create two schema to hold the Header tabular information and Category tabular information.And these schema will hold the records that need to be populated in the target.So for our sample scenario the xml structure will be as follows

XmlStructure.JPG.

Next we have to build the xsl to describes how to display an XML document. An XSL style sheet is, like with CSS, a file that describes how to display an XML document of a given type.XML does not use predefined tags (we can use any tag-names we like), and therefore the meaning of each tag is not well understood. So a without an XSL sheet browser does not know how to display xml document.

XSL consists of three parts:

XSLT – a language for transforming XML documents

XPath – a language for navigating in XML documents

XSL-FO – a language for formatting XML documents

The root element that declares the document to be an XSL style sheet is <xsl:stylesheet>

xsl_header.JPG

An XSL style sheet consists of one or more set of rules that are called templates.A template contains rules to apply when a specified node is matched.

The <xsl:template> element is used to build templates.The match attribute is used to associate a template with an XML element.(match=”/” defines the whole document. i.e. The match=”/” attribute associates the template with the root of the XML source document.)

Xsl_Template.JPG

The XSL <xsl:for-each> element can be used to select every XML element of a specified node-set. So we cab specify how to display values coming in that specified note-set. Considering our sample scenario we can select every element in the Header & Category schema to mention how to display values coming inside that node set.The <xsl:value-of> element can be used to extract the value of an XML element and add it to the output stream of the transformation.

xsl_for each.JPG

After building the xsl file we need to place that file in the target folder where BODS will be building the target file. And we also need to alter the XML header in the target XML structure inside the job. Default Header defined in the XML header will be <?xml version=”1.0″ encoding = “UTF-8″ ?> we need to change that to<?xml version=”1.0” encoding = “UTF-8″ ?><?xml-stylesheet type=”text/xsl” href=”<xsl_fileName>”?>

XML_Target.JPG

And  In our target XML, hearder will be like this

Xml_File_Header.JPG

Target xml generated after the execution of the job can be opened with Excel. where you will promted with option to open the xml after applying the stylesheet. And  in that we need select our stylesheet to get the output in the desired Excel format.

ExcelOpenOption.JPG

And our output in Excel will be displayed as given below

Final_target.JPG

Note: Both the XSL file and the xml target file should be available in the same folder for getting the desired output.

Attaching the sample xsl and xml file for reference.

To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

    1. Titto Antony Post author

      Sorry neena i didnt keep the back up of xsd. You can generate that xsd from BODS itself using the Generate XML Schema option in the Query transform. First generate the target structure and in the Query transform and generate teh XMl Schema. PFA for referenceTst.jpg

      (0) 

Leave a Reply