Multi Rowset Output from Query for XMLP January 15, 2008Posted by Duncan in Oracle, PeopleSoft, PeopleTools, XML Publisher.
One of the issues with using PSQuery to generate the XML for an XMLP report is that you can only get a single rowset from a query, meaning your report had to be fairly simple. In the past, for more complex reports I’ve just created an App Engine instead as it gives me greater control over the XML generated.
Another method exists however …
and that is to create a query containing a union. This way you can have your seperate rowsets either side of the union, and just add a field at the beginning to enable the template to ascertain which section of the rowset it is dealing with – I refer to this field below as ‘section field’. (You may also have to pad each side with some empty fields to ensure that the columns in both sections match up.)
Once you have your data outputting, your template can now be designed. Lay out the content, and where you want to display only data from a particular half of your rowset, wrap it in a ‘for-each-group’ with a criteria specifying your section type:
for example, mine was:
and add a corresponding ‘<?end for-each-group?>’ after your content.
Before you can preview effectively you may have to edit the sample XML a little as the sample data generated by Query isn’t that good. You’ll probably want to manually edit it so that you get at least one row from each side of your union coming through.
You can of course expand this further and have many more than 2 sections split by a single union. You can have as many unions as you like, although this will make the Query a little unwieldy (alternatively place all your SQL into a single view and base your query on that).
Finally, I had a problem with one of my reports that I needed to union together when the sql contained Long fields. As I’m sure you’re aware, union statements don’t work with long fields, you have to use a UNION ALL, and PSQuery doesn’t – as far as I could find – allow a way of incorporating a UNION ALL. In the end I had to create a View containing the UNION ALL to base my query upon.
If you have problems getting your XMLP reports to work, I’ve found this is the best place to ask: