Rittman Mead - Delivered Intelligence

In
yesterday’s article I took a look at the new XML Publisher 5.6 release
candidate, and worked my way through the demo reports that it ships with. In
today’s article I’ll start looking at the process of creating your own reports
and delivering them through XML Publisher 5.6 Enterprise, and to make this all a
bit more relevant I’ve put together a scenario that should help me try out a few
of the features.

In this scenario, I’m putting together a reporting suite for my organisation.
The audience for the reports include:

• The managing director, who wants an overview of the organisation’s
  performance (dashboard)
• The Sales Manager, who wants to see how sales are going for various
  products and territories
• Finance analysts, who need to analyze sales both online and in Microsoft
  Excel

The data I require is in the SH sample schema that ships with Oracle 9i and
Oracle Database 10g. As all of the work goes on at the XML Publisher end, it
doesn’t matter what version of Oracle you’re using and there’s no need to use
Oracle Application Server - OC4J 10.1.2 or 10.1.3 is all you need, as well as
XML Publisher 5.6 of course.

Eventually, I want  to set up a security system such that the HR, and
Sales Manager can only see their reports, the Finance and Sales managers can see
the sales reports, and the Managing Director can see everything. As the report
developer, I will develop all of the reports, with data being obtained either by
directly connecting to the database, or through an XML extract from the HR
system.

The first step then is to obtain the data for the reports. A difference
between XML Publisher and OracleBI Discoverer is that with Discoverer, you use
the workbook wizard to interactively query the end user layer and then obtain a
dataset for later analysis, whilst with XML Publisher to generally obtain the
dataset before you use the product, which you then use to publish your data. XML
Publisher 5.6 can report on data that comes from one of three sources - an SQL
query via JDBC, XML over HTTP (using GET or POST), or XML via a Web service. If
you’re going to use an XML datasource you need to get an extract of your data as
either an XML document or an XML schema, so that you can use the elements in the
extract to lay out your report template.

Going through this step, and having worked just with sample XML datasets in
the past, it became clear fairly early on that getting the SQL or XML dataset in
the first place was where the bulk of the work was in defining a new report.
Because there’s no graphical SQL query builder in XML Publisher Desktop (more on
this later) you have to write the SQL yourself by hand, although I ended up
using Discoverer and extracting the SQL using the View > SQL Inspector feature.
Getting an XML extract was actually fairly straightforward in the end in that I
used Oracle Reports Builder, put the report together using that and then saved
the report output as an XML file. Probably in reality getting the sample data
would be a bit easier as the application administrator would provide the SQL or
XML to the report writer, but it still took a fair bit of time for me to do this
from scratch.

Anyway, the data sources that I put together were as follows:

Query 1 : breakdown of sales by product, channel, time, promotion and
customer. This is the dataset that I’ll hopefully be using with the "Analyze"
feature in XML Publisher Enterprise that lets me put an interactive pivot table
together:

-- product, product subcategory and category,
-- promotion category and calendar year, quarter and month

SELECT ch.channel_class
, ch.channel_desc
, cu.cust_city
, c.country_region
, pr.prod_name
, pr.prod_subcategory
, pr.prod_category
, pm.promo_category
, t.calendar_year
, t.calendar_quarter_desc
, t.calendar_month_desc
, sum(s.amount_sold)
, sum(s.quantity_sold)
FROM sh.channels ch
, sh.countries c
, sh.customers cu
, sh.products pr
, sh.promotions pm
, sh.sales s
, sh.times t
WHERE ch.channel_id = s.channel_id
AND c.country_id = cu.country_id
AND cu.cust_id = s.cust_id
AND pr.prod_id = s.prod_id
AND pm.promo_id = s.promo_id
AND t.time_id = s.time_id
GROUP BY ch.channel_class
, ch.channel_desc
, cu.cust_city
, c.country_region
, pr.prod_name
, pr.prod_subcategory
, pr.prod_category
, pm.promo_category
, t.calendar_year
, t.calendar_quarter_desc
, t.calendar_month_desc
/

Query 2 : sales over 12 months in 2001 : I’ll be using this in the Executive
Dashboard later on.

-- Query #2
-- return sales and quantity sold over the 12
-- months in 2001

SELECT t.calendar_month_desc month
, sum(s.quantity_sold) quantity_sold
, sum(s.amount_sold) amount_sold
FROM sh.sales s
, sh.times t
WHERE t.time_id = s.time_id
AND t.calendar_year = 2001
GROUP BY t.calendar_month_desc
ORDER BY t.calendar_month_desc asc

Query 3 : sales by category over all time : This will be used for a table and
a graph for the sales manager report

-- Query #3
-- return sales by product category over time

SELECT pr.prod_category
, sum(s.quantity_sold)
, sum(s.amount_sold)
FROM sh.products pr
, sh.sales s
WHERE pr.prod_id = s.prod_id
GROUP BY pr.prod_category

Query 4 : sales and quantity sold by subregion : I’m hoping to include this
in the dashboard, and have it link off to report #5

-- Query #4
-- returns amount and quantity sold by subregion

SELECT     cn.country_subregion
,          sum(s.quantity_sold)
,          sum(s.amount_sold)
FROM       sh.countries cn
,          sh.customers c
,          sh.sales s
WHERE      cn.country_id = c.country_id
AND        c.cust_id = s.cust_id
GROUP BY   cn.country_subregion
ORDER BY   sum(s.amount_sold) desc

Query 5 : sales and quantity sold for countries in a subregion : to be called
from report #4

-- Query #5
-- return amount and quantity sold for a specified subregion
-- broken down by country

SELECT     cn.country_name
,          sum(s.quantity_sold)
,          sum(s.amount_sold)
FROM       sh.countries cn
,          sh.customers c
,          sh.sales s
WHERE      cn.country_id = c.country_id
AND        c.cust_id = s.cust_id
AND	   cn.country_subregion = 'Western Europe'
GROUP BY   cn.country_name
ORDER BY   sum(s.amount_sold) desc
/

Query 6 : Orders broken down by customer and country, this time taken from
the OE schema. This is an XML document generated by Oracle Reports Builder, with
orders nested inside customers, inside countries. To do this in SQL you’d need
to use a CURSOR() function. The following is an extract from the file, showing
the details for one customer:

<?xml version="1.0" encoding="WINDOWS-1252"?>
<!-- Generated by Oracle Reports version 10.1.2.0.2 -->
<ORDERS_REPORT>
  <LIST_G_COUNTRY_NAME>
    <G_COUNTRY_NAME>
      <COUNTRY_NAME>Argentina</COUNTRY_NAME>
      <LIST_G_CUSTOMER_NAME>
        <G_CUSTOMER_NAME>
          <O101020_CUST_FIRST_NAME_O10102>Aileen Newkirk</O101020_CUST_FIRST_NAME_O10102>
          <LIST_G_PROD_NAME>
            <G_PROD_NAME>
              <PROD_NAME>SIMM- 8MB PCMCIAII card</PROD_NAME>
              <PROD_DESC>SIMM- 8MB PCMCIAII card</PROD_DESC>
              <PROD_SUBCATEGORY>Memory</PROD_SUBCATEGORY>
              <PROD_CATEGORY>Peripherals and Accessories</PROD_CATEGORY>
              <PROD_WEIGHT_CLASS>1</PROD_WEIGHT_CLASS>
              <PROD_UNIT_OF_MEASURE>U</PROD_UNIT_OF_MEASURE>
              <PROD_PACK_SIZE>P</PROD_PACK_SIZE>
              <SUM_O101094_AMOUNT_SOLD_>672.72</SUM_O101094_AMOUNT_SOLD_>
            </G_PROD_NAME>
            <G_PROD_NAME>
              <PROD_NAME>Model SM26273 Black Ink Cartridge</PROD_NAME>
              <PROD_DESC>Model SM26273 Black Ink Cartridge</PROD_DESC>
              <PROD_SUBCATEGORY>Printer Supplies</PROD_SUBCATEGORY>
              <PROD_CATEGORY>Peripherals and Accessories</PROD_CATEGORY>
              <PROD_WEIGHT_CLASS>1</PROD_WEIGHT_CLASS>
              <PROD_UNIT_OF_MEASURE>U</PROD_UNIT_OF_MEASURE>
              <PROD_PACK_SIZE>P</PROD_PACK_SIZE>
              <SUM_O101094_AMOUNT_SOLD_>142.24</SUM_O101094_AMOUNT_SOLD_>
            </G_PROD_NAME>
            <G_PROD_NAME>
              <PROD_NAME>PCMCIA modem/fax 28800 baud</PROD_NAME>
              <PROD_DESC>PCMCIA modem/fax 28800 baud</PROD_DESC>
              <PROD_SUBCATEGORY>Modems/Fax</PROD_SUBCATEGORY>
              <PROD_CATEGORY>Peripherals and Accessories</PROD_CATEGORY>
              <PROD_WEIGHT_CLASS>1</PROD_WEIGHT_CLASS>
              <PROD_UNIT_OF_MEASURE>U</PROD_UNIT_OF_MEASURE>
              <PROD_PACK_SIZE>P</PROD_PACK_SIZE>
              <SUM_O101094_AMOUNT_SOLD_>304.62</SUM_O101094_AMOUNT_SOLD_>
            </G_PROD_NAME>
            <G_PROD_NAME>
              <PROD_NAME>Model K8822S Cordless Phone Battery</PROD_NAME>
              <PROD_DESC>Model K8822S Cordless Phone Battery</PROD_DESC>
              <PROD_SUBCATEGORY>Camera Batteries</PROD_SUBCATEGORY>
              <PROD_CATEGORY>Photo</PROD_CATEGORY>
              <PROD_WEIGHT_CLASS>1</PROD_WEIGHT_CLASS>
              <PROD_UNIT_OF_MEASURE>U</PROD_UNIT_OF_MEASURE>
              <PROD_PACK_SIZE>P</PROD_PACK_SIZE>
              <SUM_O101094_AMOUNT_SOLD_>136.68</SUM_O101094_AMOUNT_SOLD_>
            </G_PROD_NAME>
            <G_PROD_NAME>
              <PROD_NAME>DVD-RW Discs, 4.7GB, Pack of 3</PROD_NAME>
              <PROD_DESC>DVD-RW Discs, 4.7GB, Pack of 3</PROD_DESC>
              <PROD_SUBCATEGORY>Recordable DVD Discs</PROD_SUBCATEGORY>
              <PROD_CATEGORY>Software/Other</PROD_CATEGORY>
              <PROD_WEIGHT_CLASS>1</PROD_WEIGHT_CLASS>
              <PROD_UNIT_OF_MEASURE>U</PROD_UNIT_OF_MEASURE>
              <PROD_PACK_SIZE>P</PROD_PACK_SIZE>
              <SUM_O101094_AMOUNT_SOLD_>71.53</SUM_O101094_AMOUNT_SOLD_>
            </G_PROD_NAME>
            <G_PROD_NAME>
              <PROD_NAME>3 1/2&quot; Bulk diskettes, Box of 50</PROD_NAME>
              <PROD_DESC>3 1/2&quot; Bulk diskettes, Box of 50</PROD_DESC>
              <PROD_SUBCATEGORY>Bulk Pack Diskettes</PROD_SUBCATEGORY>
              <PROD_CATEGORY>Software/Other</PROD_CATEGORY>
              <PROD_WEIGHT_CLASS>1</PROD_WEIGHT_CLASS>
              <PROD_UNIT_OF_MEASURE>U</PROD_UNIT_OF_MEASURE>
              <PROD_PACK_SIZE>P</PROD_PACK_SIZE>
              <SUM_O101094_AMOUNT_SOLD_>49.15</SUM_O101094_AMOUNT_SOLD_>
            </G_PROD_NAME>
            <G_PROD_NAME>
              <PROD_NAME>PCMCIA modem/fax 19200 baud</PROD_NAME>
              <PROD_DESC>PCMCIA modem/fax 19200 baud</PROD_DESC>
              <PROD_SUBCATEGORY>Modems/Fax</PROD_SUBCATEGORY>
              <PROD_CATEGORY>Peripherals and Accessories</PROD_CATEGORY>
              <PROD_WEIGHT_CLASS>1</PROD_WEIGHT_CLASS>
              <PROD_UNIT_OF_MEASURE>U</PROD_UNIT_OF_MEASURE>
              <PROD_PACK_SIZE>P</PROD_PACK_SIZE>
              <SUM_O101094_AMOUNT_SOLD_>243.5</SUM_O101094_AMOUNT_SOLD_>
            </G_PROD_NAME>
            <G_PROD_NAME>
              <PROD_NAME>Unix/Windows 1-user pack</PROD_NAME>
              <PROD_DESC>Unix/Windows 1-user pack</PROD_DESC>
              <PROD_SUBCATEGORY>Operating Systems</PROD_SUBCATEGORY>
              <PROD_CATEGORY>Software/Other</PROD_CATEGORY>
              <PROD_WEIGHT_CLASS>1</PROD_WEIGHT_CLASS>
              <PROD_UNIT_OF_MEASURE>U</PROD_UNIT_OF_MEASURE>
              <PROD_PACK_SIZE>P</PROD_PACK_SIZE>
              <SUM_O101094_AMOUNT_SOLD_>902.75</SUM_O101094_AMOUNT_SOLD_>
            </G_PROD_NAME>
          </LIST_G_PROD_NAME>
          <SUMAMOUNT_SOLDPERCUSTOMER_NAME>2523.19</SUMAMOUNT_SOLDPERCUSTOMER_NAME>
        </G_CUSTOMER_NAME>

Query 7 : Salesperson sales totals (XML document). A simpler XML document
with just a single level, again generated using Oracle Reports Builder against
the OE schema.

<?xml version="1.0" encoding="WINDOWS-1252"?>
<!-- Generated by Oracle Reports version 10.1.2.0.2 -->
<MODULE2>
  <LIST_G_SALESPERSON>
    <G_SALESPERSON>
      <SALESPERSON>Lindsey Smith</SALESPERSON>
      <TOTAL_SALES>151167.2</TOTAL_SALES>
    </G_SALESPERSON>
    <G_SALESPERSON>
      <SALESPERSON>Danielle Greene</SALESPERSON>
      <TOTAL_SALES>128249.5</TOTAL_SALES>
    </G_SALESPERSON>
    <G_SALESPERSON>
      <SALESPERSON>Louise Doran</SALESPERSON>
      <TOTAL_SALES>88238.4</TOTAL_SALES>
    </G_SALESPERSON>
    <G_SALESPERSON>
      <SALESPERSON>Oliver Tuvault</SALESPERSON>
      <TOTAL_SALES>134415.2</TOTAL_SALES>
    </G_SALESPERSON>
    <G_SALESPERSON>
      <SALESPERSON>Sarath Sewall</SALESPERSON>
      <TOTAL_SALES>661734.5</TOTAL_SALES>
    </G_SALESPERSON>
    <G_SALESPERSON>
      <SALESPERSON>Christopher Olsen</SALESPERSON>
      <TOTAL_SALES>114215.7</TOTAL_SALES>
    </G_SALESPERSON>
    <G_SALESPERSON>
      <SALESPERSON>Janette King</SALESPERSON>
      <TOTAL_SALES>202617.6</TOTAL_SALES>
    </G_SALESPERSON>
    <G_SALESPERSON>
      <SALESPERSON>Nanette Cambrault</SALESPERSON>
      <TOTAL_SALES>171973.1</TOTAL_SALES>
    </G_SALESPERSON>
    <G_SALESPERSON>
      <SALESPERSON>Allan McEwen</SALESPERSON>
      <TOTAL_SALES>156296.2</TOTAL_SALES>
    </G_SALESPERSON>
  </LIST_G_SALESPERSON>
</MODULE2>

As I said, in reality the XML data would be retrieved via HTTP or via a Web
Service, but for the purposes of defining the report template, I’ve generated a
sample file which I’ll then load into Microsoft Word.

The next step is to define the report templates. I thought I’d start off with
report #7 first as it’s a simple XML document which XML Publisher Desktop should
presumably be able to deal with easily. The first thing to do then is to start
up Microsoft Word and then use the XML Publisher Desktop add-in to load up the
XML sample file, like this:

Note that there’s a new menu entry in 5.6 - "Load XML Schema" -
which gives you the additional option of loading an XML Schema document that
fully defines the dataset that you’ll be working with, as opposed to the "Load
XML Data" option that might only feature a subset of the possible XML elements
you’ll be reporting on. Of course the XML Schema document doesn’t contain any
data and you’re therefore not going to be able to look at a live view of the
report output with data in.

Once you’ve brought the XML data in, you now want to produce a
report that contains a table with repeating rows of salesperson sales totals. In
5.5 you
used a dialog that let you build up the table by dragging nodes from the
hierarchically-arranged dataset, but with 5.6 there’s a table wizard that steps
you through the process, much like you get with Oracle Reports Builder. The
wizard lets you pick what data items to include in the table, what fields you
group the data on, what fields you sort by and what labels you use for the
column headings.

Once you’ve run the wizard, you end up with the first draft of
the template, as an RTF file, which you can then change the colour and font of,
add pictures in the form of GIFs, PNGs, JPGs and Word clipart, and by
double-clicking on the field, change the format to a currency, number or other
text format.

You can then preview the report as a PDF by selecting Preview >
PDF from the XML Publisher toolbar. My report looked like this when previewed:

Not bad, certainly a lot more straightforward than laying out a
similar report using Oracle Reports Builder.

Next up was Query #3, sales by product category. This is a
report based off of an SQL data source, which was possible in XML Publisher 5.5
but only in a very basic manner. I was keen to find out how this had developed
with version 5.6.

Starting up the Word add-in again, this time I selected a new
item in the Data menu, "SQL Report Wizard":

Using this menu item brings up a wizard that steps you through
the process of defining an SQL query. It’s a bit more user-friendly than the 5.5
release in that it first prompts you for the datasource name, then the connect
details, and then the SQL that retrieves the data, but it still requires you to
have put the SQL together beforehand and there’s no GUI query builder as you get
with Oracle Report Builder.

If you’ve already put the SQL as I did, it’s fairly
straightforward and the wizard then prepares the RTF template for you and turns
the SQL resultset into an XML document that you then work with. After that it’s
a case of formatting the results again, and in my case adding a graph based on
the same dataset.

Now at this point I could have gone on and produced RTF
templates for all the other reports, but I thought I’d take a look at the XML
Publisher Enterprise application, as firstly this seems to give you an online
way of defining reports, and secondly at some point I’ll need to upload the
reports to here so that my users can view them.

Logging on to XML Publisher Enterprise as the admin user, I
first created a new folder called "Sales Reports Demo" and then uploaded the
file with an ".xdo" suffix that the SQL Report Wizard had created for me along
with the RTF template file. The .xdo file brings together references to the
dataset, the templates that use it, and any parameters and their associated
lists of values. The uploading process looked like this:

Once the report is uploaded it appears on the right-hand side of
the page, with links to run the report, schedule it and edit it. I clicked on
the "edit" link to open up the report definition, and then upload the RTF
template file that defines how the report is displayed:

Now, I can run the report:

Ok, so that’s good. The next steps then are to finish off the
rest of the report templates, upload them, and then try and put some more
interesting reports together - the dashboard that contains multiple charts and
graphs running against multiple data sources, and a report where items in the
table hyperlink through to another report. Back in a few days.

Posted in Uncategorized |