Shared Components -> Report Queries -> Create
Report Query Name: first_query
Output Format: PDF
Click on Next button
SQL guery:
select "PHOTOS"."PHOTO_ID" as "PHOTO_ID",
"PHOTOS"."MIME_TYPE" as "MIME_TYPE"
from "PHOTOS" "PHOTOS"
Click on Next button
Click on Add Query button
Click on Create button
Click on Next button
Click on Next button
Click on Finish button
At now you get the following result (because we used the default report layout):
- we can't see the second query (select sysdate from dual), we can only see the first query in pdf
2) Create custom report layout
At first we have to see the xml data:
Shared Components -> Report Queries ->Click on first_query (which was created by us)
-> Source Queries -> click on Download button
Xml data which was generated by APEX:
<?xml version="1.0" encoding="UTF-8"?>
<DATA>
<ROWSET1>
<ROWSET1_ROW>
<PHOTO_ID>1</PHOTO_ID>
<MIME_TYPE>image/jpeg</MIME_TYPE>
</ROWSET1_ROW>
</ROWSET1>
<ROWSET2>
<ROWSET2_ROW>
<SYSDATE>31-MAY-10</SYSDATE>
</ROWSET2_ROW>
</ROWSET2>
</DATA>
If you see the xml data you can write layout for it. (Or you can use BI Publisher Desktop and MS Word for it)
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:fo="http://www.w3.org/1999/XSL/Format">
<xsl:output method="xml" indent="yes"/>
<xsl:template match="/">
<fo:root>
<fo:layout-master-set>
<fo:simple-page-master master-name="A4-portrait"
page-height="29.7cm" page-width="21.0cm" margin="2cm">
<fo:region-body/>
</fo:simple-page-master>
</fo:layout-master-set>
<fo:page-sequence master-reference="A4-portrait">
<fo:flow flow-name="xsl-region-body"> <!-- START of PAGE BODY -->
<fo:block > <!-- START of MAIN BLOCK in page body -->
<xsl:for-each select="DATA" ><!-- start of DATA -->
<fo:block font-weight="bold" font-family="Helvetica" font-size="18pt" padding-top="1pt" padding-bottom="20pt" text-align="center">PHOTOS</fo:block>
<fo:block padding-before="10pt" padding-bottom="10pt">
<fo:table border="0.5pt solid #000000">
<fo:table-column column-width="20mm"/>
<fo:table-column column-width="43mm"/>
<fo:table-column column-width="120mm"/>
<fo:table-body >
<fo:table-row>
<fo:table-cell border-right="0.5pt solid #000000" border-bottom="0.5pt solid #000000" padding-left="5pt" padding-top="3pt" padding-bottom="1pt">
<fo:block font-size="12pt" font-family="Helvetica">Photo_id</fo:block>
</fo:table-cell>
<fo:table-cell border-right="0.5pt solid #000000" border-bottom="0.5pt solid #000000" padding-left="5pt" padding-top="3pt" padding-bottom="1pt">
<fo:block font-size="12pt" font-family="Helvetica">mime_type </fo:block>
</fo:table-cell>
<fo:table-cell border-bottom="0.5pt solid #000000" padding-left="5pt" padding-top="3pt" padding-bottom="1pt">
<fo:block font-size="12pt" font-family="Helvetica">Photo
</fo:block>
</fo:table-cell>
</fo:table-row>
<!-- start of first query -->
<xsl:for-each select="ROWSET1" ><!-- start of ROWSET1 -->
<xsl:for-each select="ROWSET1_ROW" ><!-- start of ROWSET1_ROW -->
<fo:table-row>
<fo:table-cell border-right="0.5pt solid #000000" border-bottom="0.5pt solid #000000" padding-left="5pt" padding-top="3pt" padding-bottom="1pt">
<fo:block font-size="12pt" font-family="Helvetica">
<xsl:value-of select="PHOTO_ID"/>
</fo:block>
</fo:table-cell>
<fo:table-cell border-right="0.5pt solid #000000" border-bottom="0.5pt solid #000000" padding-left="5pt" padding-top="3pt" padding-bottom="1pt">
<fo:block font-size="12pt" font-family="Helvetica">
<xsl:value-of select="MIME_TYPE"/>
</fo:block>
</fo:table-cell>
<fo:table-cell border-bottom="0.5pt solid #000000" padding-left="5pt" padding-top="3pt" padding-bottom="1pt">
<fo:block font-size="12pt" font-family="Helvetica">
<!-- start of image -->
<!-- end of image -->
</fo:block>
</fo:table-cell>
</fo:table-row>
</xsl:for-each><!-- end of ROWSET1_ROW -->
</xsl:for-each><!-- end of ROWSET1 -->
<!-- end of first query -->
<!-- start of second query -->
<xsl:for-each select="ROWSET2" ><!-- start of ROWSET2 -->
<xsl:for-each select="ROWSET2_ROW" ><!-- start of ROWSET2_ROW -->
<fo:table-row>
<fo:table-cell cell-spanding="2" border-right="0.5pt solid #000000" padding-left="5pt" padding-top="3pt" padding-bottom="1pt">
<fo:block font-size="12pt" font-family="Helvetica">Sysdate </fo:block>
</fo:table-cell>
<fo:table-cell border-bottom="0.5pt solid #000000" padding-left="5pt" padding-top="3pt" padding-bottom="1pt">
<fo:block font-size="12pt" font-family="Helvetica">
<xsl:value-of select="SYSDATE"/>
</fo:block>
</fo:table-cell>
</fo:table-row>
</xsl:for-each><!-- end of ROWSET2_ROW -->
</xsl:for-each><!-- end of ROWSET2 -->
<!-- end of second query -->
</fo:table-body>
</fo:table>
</fo:block>
</xsl:for-each><!-- end of DATA -->
</fo:block><!-- END of MAIN BLOCK in page body -->
</fo:flow> <!-- END of PAGE BODY -->
</fo:page-sequence>
</fo:root>
</xsl:template>
</xsl:stylesheet>
And now add the picture for report query and layout
Shared Components -> Report Queries ->Click on first_query (which was created by us)
select "PHOTOS"."PHOTO_ID" as "PHOTO_ID",
"PHOTOS"."MIME_TYPE" as "MIME_TYPE" ,
'http://YOUR_WEBSERVER/DAD/USER.my_image_display?p_image_id='||PHOTO_ID as FILEPATHVALUE
from "PHOTOS" "PHOTOS"
YOUR_WEBSERVER => the server's IP or canonical name
DAD => Database Access Descriptor
USER => schema name where my_images_display procedure is located
my_image_display => see the my_image_display procedure
Add the following code your custom layout:
....
<!-- start of image -->
<fo:block font-size="10pt">
<xsl:variable name="filepathValue" select="FILEPATHVALUE" />
<fo:external-graphic >
<xsl:attribute name="src">
<xsl:value-of select="$filepathValue"/>
</xsl:attribute>
<xsl:attribute name="width">
<xsl:value-of select="'2cm'"/>
</xsl:attribute>
</fo:external-graphic>
</fo:block>
<!-- end of image -->
....
Upload your layout to APEX
Shared Components -> Report Layouts -> Click on Create button -> select Named Columns (XSL-FO)
Layou name: first_layout_query
Report Layout File: select your custom layout file
Click on Create Layout button
Shared Components -> Report Queries ->Click on first_query (which was created by us) -> Change "Report Layout" ->select "first_layout_query"
At the end you can see the picture in pdf
