XML Publisher 5.6 Enterprise : Producing My First Reports
March 8th, 2006 by Mark Rittman
- 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:
Argentina Aileen Newkirk SIMM- 8MB PCMCIAII card SIMM- 8MB PCMCIAII card Memory Peripherals and Accessories 1 U P 672.72 Model SM26273 Black Ink Cartridge Model SM26273 Black Ink Cartridge Printer Supplies Peripherals and Accessories 1 U P 142.24 PCMCIA modem/fax 28800 baud PCMCIA modem/fax 28800 baud Modems/Fax Peripherals and Accessories 1 U P 304.62 Model K8822S Cordless Phone Battery Model K8822S Cordless Phone Battery Camera Batteries Photo 1 U P 136.68 DVD-RW Discs, 4.7GB, Pack of 3 DVD-RW Discs, 4.7GB, Pack of 3 Recordable DVD Discs Software/Other 1 U P 71.53 3 1/2" Bulk diskettes, Box of 50 3 1/2" Bulk diskettes, Box of 50 Bulk Pack Diskettes Software/Other 1 U P 49.15 PCMCIA modem/fax 19200 baud PCMCIA modem/fax 19200 baud Modems/Fax Peripherals and Accessories 1 U P 243.5 Unix/Windows 1-user pack Unix/Windows 1-user pack Operating Systems Software/Other 1 U P 902.75 2523.19
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.
Lindsey Smith 151167.2 Danielle Greene 128249.5 Louise Doran 88238.4 Oliver Tuvault 134415.2 Sarath Sewall 661734.5 Christopher Olsen 114215.7 Janette King 202617.6 Nanette Cambrault 171973.1 Allan McEwen 156296.2
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.
