rasti.hil@hilandco.com +41 79 367-9677

Monday, 31 May 2010

How to create a repoert query in APEX and custom report layout

1) Create report query in APEX
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

4 comments:

Tiago V said...

Good afternoon,

I've tried to doing this Tutorial,
Do you know how can i print a report page using this model???


Regards,

Tiago V

Zsuzsi Vadasz said...

If I understood you correctly you want to print this report. You can achieve by creating a button and use the following branch:
f?p=&APP_ID.:0:&SESSION.:PRINT_REPORT=first_query
where first_query is the name of report query.
You can see the Print URL under Report Query Attributes

Zsuzsanna Vadasz

Tiago V said...

Hi Zsuzsi Vadasz,

Thank you for respond me.
I know that you don't anderstood
correctly,
actually, i'd like to call the report and print it as it appear in the report-with the same values;
Or maybe, parametrize the query which is used to generate the report.

Do you know how can i do that??

Thanks,

Regards

Tiago V

Zsuzsi Vadasz said...

Using parameter in report query:

select "PHOTOS"."PHOTO_ID" as "PHOTO_ID",
"PHOTOS"."MIME_TYPE" as "MIME_TYPE"
from "PHOTOS" "PHOTOS"
where PHOTO_ID = :P1_PHOTO_ID

where P1_PHOTO_ID is item of page 1