Wednesday, July 9, 2008
SQR and the XML Publisher Juggernaut
I have been surprised at the vigour with which Oracle has been promoting its Fusion reporting tool - XML Publisher. Any documentation/presentation/webcast on Fusion Tools talks in great length about the features and advantages of XMLP, how it has been integrated into Peopletools and more importantly how its going to be the only reporting tool for Fusion moving forward. One of the earliest documents I read on Strategies for Peoplesoft customers to be fusion ready had abandoning the use of SQRs and embracing XMLP as one of the main fusion oriented decisions for a Peoplesoft customer. Toeing this line, almost all new Peoplesoft implementations are religiously rejecting SQRs and embracing XMLP as the standard reporting tool.Is this the end of the road for SQRs? Probably yes!As much as I have seen, XMLP is a wonderful reporting tool. Creating a report using XMLP is many times easier, faster and elegant than SQRs. Its powerful feature that the layout can be designed separately will make it a hit with the business end users - who can now design their own desired layout on a Microsoft Word Document without any help from IT (I had a good laugh when I realised that all that I had to do to add a header with the Company Logo in XMLP was to add a header in MS Word! Huh? That's it?! Good bye to all the SQR coding! At hindsight another major advantage is with spacing the report. Adding a new column to an SQR report will require programatically adjusting the column number of various variables being printed to align to the new layout. But with XMLP, it would just require us to drag the fields to their appropriate places!). The Peoplesoft team at Oracle have done well to integrate XMLP in the Peopletools environment. XMLP is the newest addition to the Reporting Tools in PIA and there are a flurry of new Peoplecode classes (refer Application Packages starting with PSXP_) that facilitate the running of XMLP reports from onlines pages, AEs etc. But is XMLP a complete replacement to SQRs? XMLP taken alone is just a reporting tool, while SQR is a programming language. The flexibility and power of SQRs to handle extremely complex reporting requirements (working with multiple data sets, calculations, conditional logic, usage of data structures etc.) can not be matched by XMLP. For example can I write a XMLP report that fetches all the employees who returned from sick and maternity leave today (for this I will need to fetch the action reason of the LOA row previous to the latest RFL row assuming that sick and maternity are captured using the LOA action) and execute different processing logic depending on the leave reason? No. XMLP is not designed to work with multiple data sets and can not handle such a scenario where conditional logic has to be executed on multiple data sets (first on the current effective dated row to fetch all RFL rows and second to find out those RFL employees who had gone on LOA due to sickness or maternity). This is the forte of SQRs - the ability to handle any complex logic being a procedural language. So the more important question is - is it wise completely abandoning the use of SQRs as a matter of strategy? Both XMLP and SQRs have their own strengths and why can't we continue to use SQRs for requirements that are easier to code and better handled by that tool? For me, the most prudent approach will be to have a mix of both tools and to judiciously choose the right tool for the right requirement.