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).