In a recent short discussion on FriendFeed , Benjamin Good asked me what are the reporting tool I've used. On my potential list there was:
.
But the only tool have used so far to produce a PDF document is a
XSL-FO document converted with
Apache FOP .
XSL-FO is an
XML vocabulary for specifying formatting semantics and
FOP is a
print formatter driven by XSL formatting objects (XSL-FO) and an output independent formatter. It is a Java application that reads a formatting object (FO) tree and renders the resulting pages to a specified output. Output formats currently supported include PDF, PS, PCL, AFP, XML, Print, AWT and PNG, and to a lesser extent, RTF and TXT. The primary output target is PDF. Here is an example: Say I've got the following
MYSQL result formatted as XML (option
--xml on the command line).
<resultset statement="select chrom,chromStart,name,avHet from snp129 where avHet>0 limit 300 "> <row> <field name="chrom "> chr1 </field> <field name="chromStart "> 4332 </field> <field name="name "> rs3210717 </field> <field name="avHet " xsi:nil="true "/> </row> <row> <field name="chrom "> chr1 </field> <field name="chromStart "> 4363 </field> <field name="name "> rs1045871 </field> <field name="avHet "> 0.4278 </field> </row> <row> <field name="chrom "> chr1 </field> <field name="chromStart "> 1023856 </field> <field name="name "> rs6698368 </field> <field name="avHet "> 0.459861 </field> </row> </resultset>
To convert this result as a XSL-FO
Table we need the following XSLT stylesheet (available
here ). This stylesheet will recognize the rs### numbers and will create a link to the NCBI for each SNP.
<xsl:stylesheet version="1.0 "> <!-- <xsl:output method="xml " version="1.0 " encoding="UTF-8 " indent="yes "/> <xsl:template match="/ "> <fo:root> <xsl:comment> Created with mysql2fo Pierre Lindenbaum http://plindenbaum.blogspot.com </xsl:comment> <fo:layout-master-set> <fo:simple-page-master master-name="main " margin-top="36pt " margin-bottom="36pt " page-width="210mm " page-height="297mm " margin-left="1cm " margin-right="1cm "> <fo:region-body margin-bottom="50pt " margin-right="50pt "/> </fo:simple-page-master> </fo:layout-master-set> <fo:page-sequence master-reference="main "> <fo:flow flow-name="xsl-region-body "> <xsl:apply-templates select="resultset "/> </fo:flow> </fo:page-sequence> </fo:root> </xsl:template> <xsl:template match="resultset "> <xsl:choose> <xsl:when test="count(row)>0 "> <xsl:variable name="cols " select="count(row[1]/field) "/> <fo:block> <xsl:value-of select="@statement "/> </fo:block> <fo:table table-layout="fixed " border-collapse="collapse " width="100% " font-size="12pt " font-family="Arial "> <xsl:for-each select="row[1]/field "> <xsl:element name="fo:table-column "> <xsl:attribute name="column-width "> <xsl:value-of select="100.0 div $cols "/> <xsl:text> % </xsl:text> </xsl:attribute> </xsl:element> </xsl:for-each> <fo:table-header color="white " background-color="blue " font-weight="bold "> <fo:table-row> <xsl:for-each select="row[1]/field "> <xsl:element name="fo:table-cell "> <xsl:attribute name="padding "> 2pt </xsl:attribute> <xsl:attribute name="background-color "> brown </xsl:attribute> <xsl:attribute name="color "> white </xsl:attribute> <xsl:attribute name="font-weight "> bold </xsl:attribute> <xsl:attribute name="font-size "> 14pt </xsl:attribute> <xsl:attribute name="text-align "> center </xsl:attribute> <fo:block> <xsl:value-of select="@name "/> </fo:block> </xsl:element> </xsl:for-each> </fo:table-row> </fo:table-header> <fo:table-body> <xsl:for-each select="row "> <xsl:element name="fo:table-row "> <xsl:attribute name="background-color "> <xsl:choose> <xsl:when test="position() mod 2 = 1 "> #FCF6CF </xsl:when> <xsl:otherwise> #FEFEF2 </xsl:otherwise> </xsl:choose> </xsl:attribute> <xsl:for-each select="field "> <fo:table-cell padding="2pt " border="1pt solid black "> <xsl:choose> <xsl:when test="@xsi:nil='true' "> <fo:block> <fo:inline font-style="italic " color="gray "> NULL </fo:inline> </fo:block> </xsl:when> <xsl:otherwise> <fo:block> <xsl:call-template name="content "> <xsl:with-param name="text " select=". "/> </xsl:call-template> </fo:block> </xsl:otherwise> </xsl:choose> </fo:table-cell> </xsl:for-each> </xsl:element> </xsl:for-each> </fo:table-body> </fo:table> </xsl:when> <xsl:otherwise> <fo:flow flow-name="xsl-region-body "> <fo:block font-size="14pt " line-height="16pt "> </fo:block> </fo:flow> </xsl:otherwise> </xsl:choose> </xsl:template> <!-- analyse the content and tries to create an hyperlink --> <xsl:template name="content "> <xsl:param name="text "/> <xsl:choose> <xsl:when test="string-length($text)>2 and starts-with($text,'rs') and string-length(translate(substring($text,3),'0123456789',''))=0 "> <xsl:element name="fo:basic-link "> <xsl:attribute name="color "> blue </xsl:attribute> <xsl:attribute name="external-destination "> http://www.ncbi.nlm.nih.gov/projects/SNP/snp_ref.cgi?rs= <xsl:value-of select="substring($text,3) "/> </xsl:attribute> <xsl:value-of select="$text "/> </xsl:element> </xsl:when> <xsl:when test="starts-with($text,'http://') or starts-with($text,'https://') or starts-with($text,'mailto://') or starts-with($text,'ftp://') "> <xsl:element name="fo:basic-link "> <xsl:attribute name="color "> blue </xsl:attribute> <xsl:attribute name="external-destination "> <xsl:value-of select="$text "/> </xsl:attribute> <xsl:value-of select="$text "/> </xsl:element> </xsl:when> <xsl:otherwise> <xsl:value-of select="$text "/> </xsl:otherwise> </xsl:choose> </xsl:template> </xsl:stylesheet>
The transformed XSL-FO document:
<fo:root> <!-- Created with mysql2fo Pierre Lindenbaum http://plindenbaum.blogspot.com --> <fo:layout-master-set> <fo:simple-page-master master-name="main " margin-top="36pt " margin-bottom="36pt " page-width="210mm " page-height="297mm " margin-left="1cm " margin-right="1cm "> <fo:region-body margin-bottom="50pt " margin-right="50pt "/> </fo:simple-page-master> </fo:layout-master-set> <fo:page-sequence master-reference="main "> <fo:flow flow-name="xsl-region-body "> <fo:block> select chrom,chromStart,name,avHet from snp129 where avHet>0 limit 300 </fo:block> <fo:table table-layout="fixed " border-collapse="collapse " width="100% " font-size="12pt " font-family="Arial "> <fo:table-column column-width="25% "/> <fo:table-column column-width="25% "/> <fo:table-column column-width="25% "/> <fo:table-column column-width="25% "/> <fo:table-header color="white " background-color="blue " font-weight="bold "> <fo:table-row> <fo:table-cell padding="2pt " background-color="brown " color="white " font-weight="bold " font-size="14pt " text-align="center "> <fo:block> chrom </fo:block> </fo:table-cell> <fo:table-cell padding="2pt " background-color="brown " color="white " font-weight="bold " font-size="14pt " text-align="center "> <fo:block> chromStart </fo:block> </fo:table-cell> <fo:table-cell padding="2pt " background-color="brown " color="white " font-weight="bold " font-size="14pt " text-align="center "> <fo:block> name </fo:block> </fo:table-cell> <fo:table-cell padding="2pt " background-color="brown " color="white " font-weight="bold " font-size="14pt " text-align="center "> <fo:block> avHet </fo:block> </fo:table-cell> </fo:table-row> </fo:table-header> <fo:table-body> <fo:table-row background-color="#FCF6CF "> <fo:table-cell padding="2pt " border="1pt solid black "> <fo:block> chr1 </fo:block> </fo:table-cell> <fo:table-cell padding="2pt " border="1pt solid black "> <fo:block> 4332 </fo:block> </fo:table-cell> <fo:table-cell padding="2pt " border="1pt solid black "> <fo:block> <fo:basic-link color="blue " external-destination="http://www.ncbi.nlm.nih.gov/projects/SNP/snp_ref.cgi?rs=3210717 rs3210717 </fo:basic-link> </fo:block> </fo:table-cell> <fo:table-cell padding="2pt " border="1pt solid black "> <fo:block> <fo:inline font-style="italic " color="gray "> NULL </fo:inline> </fo:block> </fo:table-cell> </fo:table-row> <fo:table-row background-color="#FEFEF2 "> <fo:table-cell padding="2pt " border="1pt solid black "> <fo:block> chr1 </fo:block> </fo:table-cell> <fo:table-cell padding="2pt " border="1pt solid black "> <fo:block> 4363 </fo:block> </fo:table-cell> <fo:table-cell padding="2pt " border="1pt solid black "> <fo:block> <fo:basic-link color="blue " external-destination="http://www.ncbi.nlm.nih.gov/projects/SNP/snp_ref.cgi?rs=1045871 rs1045871 </fo:basic-link> </fo:block> </fo:table-cell> <fo:table-cell padding="2pt " border="1pt solid black "> <fo:block> 0.4278 </fo:block> </fo:table-cell> </fo:table-row> <fo:table-row background-color="#FCF6CF "> <fo:table-cell padding="2pt " border="1pt solid black "> <fo:block> chr1 </fo:block> </fo:table-cell> <fo:table-cell padding="2pt " border="1pt solid black "> <fo:block> 1023856 </fo:block> </fo:table-cell> <fo:table-cell padding="2pt " border="1pt solid black "> <fo:block> <fo:basic-link color="blue " external-destination="http://www.ncbi.nlm.nih.gov/projects/SNP/snp_ref.cgi?rs=6698368 rs6698368 </fo:basic-link> </fo:block> </fo:table-cell> <fo:table-cell padding="2pt " border="1pt solid black "> <fo:block> 0.459861 </fo:block> </fo:table-cell> </fo:table-row> </fo:table-body> </fo:table> </fo:flow> </fo:page-sequence> </fo:root> All in one : we use
FOP to transform the mysql result with the stylesheet and to create a PDF document
fop -xml source-mysql.xml -xsl mysql2fo.xsl -pdf result.pdf
Here is the result uploaded on Scribd:
XSL-FO test That's it.
Pierre
But the only tool have used so far to produce a PDF document is a 
