The below presentation examines how Value Lists can be used in the design of Time and Labor reports that require the grouping of Time Reporting Codes.
**Note that a similar approach in Absence Management would be to use Element Groups to group Absence Takes together for reporting.
Showing posts with label XML Publisher. Show all posts
Showing posts with label XML Publisher. Show all posts
Friday, December 24, 2010
Thursday, March 26, 2009
Automating Release Management - the XMLP way
Most organisations have a process where a document containing all the objects of a Peoplesoft project that is to be migrated to another environment is prepared. This document will be the reference point for the DBAs or the release management team who will be responsible to migrate Peoplesoft projects across environments. For a developer, the task of preparing the document is an over head especially if it's a project with a large number of objects - it's close to being a painful exercise of going through all the objects in the project and reflecting that in the document. Amongst the innovative tools developed here at TESCO we had a .NET based Automatic Release Notes generator. The user just had to input the name of the project and the tool would produce a file (again we had the choice to select the output type required - .doc or .xls) containing the details of all the objects in the project including the comments attached to each individual object definition wherever possible. But this tool had few limitations - (a) The output format was not in the template dictated by our standards (b) The tool was not available locally, we had to login to a remote desktop to access that and (c) The source code was in .NET and that was not appealing to a bunch of Peoplpesoft developers!
So the team could not use the tool and had to resort to a manual preparation of the release notes. It was then that we came up with the idea of writing an XML Publisher report to do the same. XML Publisher is a perfect tool to produce letters and documents because we directly develop the template in MS Word and that was just perfect in our case. For the initial version, we decided to just print the details of the objects in the project and not the comments for each object. Printing the comments would mean that we had to query the metadata table of each of the objects and that would be nearly impossible with a simple PS Query.
The datasource of the XMLP report was a simple PS Query that pulled data from the PSPROJECTITEM table. We added some prompts to the PS Query so that the user could input details to be printed in the release notes like the change number, name of author, change description etc. It's a very simple template which I am sure your organisation can develop with minimal effort. This tool has surely automated a non-value add area in our development process and helped our developers spent more time of development rather than documentation!
A small presentation on the report and how to use that is given below:
So the team could not use the tool and had to resort to a manual preparation of the release notes. It was then that we came up with the idea of writing an XML Publisher report to do the same. XML Publisher is a perfect tool to produce letters and documents because we directly develop the template in MS Word and that was just perfect in our case. For the initial version, we decided to just print the details of the objects in the project and not the comments for each object. Printing the comments would mean that we had to query the metadata table of each of the objects and that would be nearly impossible with a simple PS Query.
The datasource of the XMLP report was a simple PS Query that pulled data from the PSPROJECTITEM table. We added some prompts to the PS Query so that the user could input details to be printed in the release notes like the change number, name of author, change description etc. It's a very simple template which I am sure your organisation can develop with minimal effort. This tool has surely automated a non-value add area in our development process and helped our developers spent more time of development rather than documentation!
A small presentation on the report and how to use that is given below:
Release Notes generation using XML Publisher
View more presentations from jijucvengal.
Thursday, March 19, 2009
Leaving it to XSL to do the dirty work!
The biggest misconception I had about XML Publisher was it's ability to do any logical processing - for me it was merely a presentation layer when I initially saw the product and far inferior to anything SQRs could do. How wrong I was! And this post is about the power of using XSL to do the programming logic within the template of XML Publisher to design faster and better reports.
When we initially started designing XML Publisher reports we stuffed in most of the calculations and logic in the data source itself - majority being PS Queries. This meant we had pretty bulky and costly queries with a number of PS Query expressions. This was not helping the cause at all with the reports taking an unacceptably long time to run. But as we got to understand the tool, we started doing more and more of the calculations and logical processing within the template using xsl - the groupings, summations, summaries etc. This has lead to dramatic improvements in performance and we believe that's the right way to approach XMLP reporting.
For example in one of our reports we had to print the total scheduled hours for all employees in a department along with some other information. The xml schema consisted of individual employee's data along with the deptid. A normal for-each-group:row;DEPTID tag was used to print individual departments, but to retrieve the total scheduled hours for a department an expression was used in the PS Query. This lead to significant performance drop, the query itself was taking over 30 minutes to execute. By eliminating the expression and summing up the scheduled hours for the department in the template itself the report was running in less than 5 minutes. A further grouping was done inside the DEPTID grouping to sum the scheduled hours of each employee (note that there were multiple rows for a single employee in the data set, but grouping by emplid would result in a single distinct row) and the set_variable function was used to add the scheduled hours to a variable.
In short keep the data structure of your data source as flat as possible and leave all the dirty processing work to the XMLP template! (And keep learning more of XSL to leverage the capabilities fully).
When we initially started designing XML Publisher reports we stuffed in most of the calculations and logic in the data source itself - majority being PS Queries. This meant we had pretty bulky and costly queries with a number of PS Query expressions. This was not helping the cause at all with the reports taking an unacceptably long time to run. But as we got to understand the tool, we started doing more and more of the calculations and logical processing within the template using xsl - the groupings, summations, summaries etc. This has lead to dramatic improvements in performance and we believe that's the right way to approach XMLP reporting.
For example in one of our reports we had to print the total scheduled hours for all employees in a department along with some other information. The xml schema consisted of individual employee's data along with the deptid. A normal for-each-group:row;DEPTID tag was used to print individual departments, but to retrieve the total scheduled hours for a department an expression was used in the PS Query. This lead to significant performance drop, the query itself was taking over 30 minutes to execute. By eliminating the expression and summing up the scheduled hours for the department in the template itself the report was running in less than 5 minutes. A further grouping was done inside the DEPTID grouping to sum the scheduled hours of each employee (note that there were multiple rows for a single employee in the data set, but grouping by emplid would result in a single distinct row) and the set_variable function was used to add the scheduled hours to a variable.
In short keep the data structure of your data source as flat as possible and leave all the dirty processing work to the XMLP template! (And keep learning more of XSL to leverage the capabilities fully).
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.
Subscribe to:
Posts (Atom)