{"id":1242,"date":"2012-11-15T15:13:51","date_gmt":"2012-11-15T14:13:51","guid":{"rendered":"http:\/\/iamwcew.fr.oracle.com\/blog\/?p=1242"},"modified":"2012-11-15T15:13:51","modified_gmt":"2012-11-15T14:13:51","slug":"reporting-data-from-a-webcenter-site-catalog-with-apex","status":"publish","type":"post","link":"https:\/\/gpmfactory.com\/index.php\/2012\/11\/15\/reporting-data-from-a-webcenter-site-catalog-with-apex\/","title":{"rendered":"Reporting data from a WebCenter Site catalog with APEX"},"content":{"rendered":"<div>Oracle Application Express (Oracle APEX) is a declarative, rapid web application development tool for the Oracle database. It is a fully supported, no cost option available with all editions of the Oracle database. Using only a web browser, you can develop and deploy professional applications that are both fast and secure.<\/div>\n<p><!--more--><\/p>\n<div><\/div>\n<div>\n<ul>\n<li>APEX can easily leverage the relationnal data model of webCenter Sites<\/li>\n<li>The data model of Webcenter Sites is publicly documented (cf <a href=\"http:\/\/docs.oracle.com\/cd\/E29495_01\/doc.1111\/webcenter_sites_11gr1_bp1_developer_guide.pdf\">Flex<\/a><a href=\"http:\/\/docs.oracle.com\/cd\/E29495_01\/doc.1111\/webcenter_sites_11gr1_bp1_developer_guide.pdf\">family<\/a><a href=\"http:\/\/docs.oracle.com\/cd\/E29495_01\/doc.1111\/webcenter_sites_11gr1_bp1_developer_guide.pdf\">Asset<\/a><a href=\"http:\/\/docs.oracle.com\/cd\/E29495_01\/doc.1111\/webcenter_sites_11gr1_bp1_developer_guide.pdf\"> Model<\/a>)<\/li>\n<li>APEX provides out of the box features for <strong>exporting report data as excel or PDF<\/strong> format, filtering and highlighting informations.<\/li>\n<li>Report Build is very simple: Once the sql statement is done (cf appendices for a sample based on a Catalog of products), a wizzard helps user in adding a new report, targeted for browsers or Mobile devices<\/li>\n<\/ul>\n<div>Note:<\/div>\n<div style=\"padding-left: 30px;\">this approach means that the chosen database for Webcenter Sites repository is Oracle .<br \/>\nThe last version (4.2) of APEX has been used for the demo.<\/div>\n<div>Below is a sample Sql Statement which extracts data from the underlying tables of webcenter Sites. The goal is to rotate the rows into columns value. The classicl DECODE function is used there, but there are others ways to achieve the same result (ie: PIVOT operator as from DB 11). This statement can be used for any Flex family, subject that one adapt name of tables.<\/div>\n<pre>select p.name name,\n MAX(p.id) id, \n MAX(to_date(substr(p.createddate,1,10), 'YYYY-MM-DD')) created,\u00a0 \n MAX(DECODE(c.name, 'title',pm.stringvalue)) title,\n MAX(DECODE(c.name, 'section',pm.stringvalue)) section,\n MAX(DECODE(c.name, 'family',pm.stringvalue)) family,\n MAX(DECODE(c.name, 'category',pm.stringvalue)) cat,\n MAX(DECODE(c.name, 'supplier',pm.stringvalue)) supplier,\n MAX(DECODE(c.name, 'sapcode',pm.stringvalue)) sapcode,\n MAX(DECODE(c.name, 'vendor_number',pm.stringvalue)) vendor_number,\n MAX(DECODE(c.name, 'leveltype',pm.stringvalue)) leveltype,\n MAX(DECODE(c.name, 'description_short',pm.stringvalue)) \n                     description_short,\n MAX(DECODE(c.name, 'millesime',pm.stringvalue)) millesime,\n MAX(DECODE(c.name, 'isseasonal',pm.stringvalue)) isseasonal,\n MAX(DECODE(c.name, 'gme',pm.stringvalue)) gme,\n MAX(DECODE(c.name, 'page',pm.stringvalue)) page,\n MAX(DECODE(c.name, 'areaName',pm.stringvalue)) areaName\n from \u00a0 catalog_product_mungo pm,\n \u00a0 catalog_product p,\n \u00a0 catalog c\n where pm.cs_ownerid = p.id\n and c.id=pm.cs_attrid\n group by p.name<\/pre>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Oracle Application Express (Oracle APEX) is a declarative, rapid web application development tool for the Oracle database. It is a fully supported, no cost&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21,7,10],"tags":[],"ppma_author":[150],"class_list":["post-1242","post","type-post","status-publish","format-standard","hentry","category-anglais","category-dev","category-notes"],"authors":[{"term_id":150,"user_id":1,"is_guest":0,"slug":"admin8700","display_name":"Patrick","avatar_url":"https:\/\/secure.gravatar.com\/avatar\/209d5ed69b74d288390621ab4c1d3773?s=96&d=mm&r=g","0":null,"1":"","2":"","3":"","4":"","5":"","6":"","7":"","8":""}],"_links":{"self":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts\/1242","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/comments?post=1242"}],"version-history":[{"count":0,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts\/1242\/revisions"}],"wp:attachment":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/media?parent=1242"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/categories?post=1242"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/tags?post=1242"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/ppma_author?post=1242"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}