jump to navigation

Multi Rowset Output from Query for XMLP January 15, 2008

Posted by Tipster in Oracle, PeopleSoft, PeopleTools, XML Publisher.
trackback

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-each-group:row[<section_field>='<value>'];
./<section_field>?>

for example, mine was:

<?for-each-group:row[A.EP_SECTION_TYPE='Section'];
./A.EP_SECTION_TYPE?>

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:
http://forums.oracle.com/forums/forum.jspa?forumID=245&start=0

About these ads

Comments

1. Graham - January 18, 2008

We have used this technique succesfully with Crystal Reports too. We used it because there was always (and still was as at PT8.47) a bug/feature in Crystal subreports whereby Crystal selects ALL rows from the query that drives the subreports and then filters based upon the current parent row. This has fatal memory implications if your child subreports query returns thousands of rows and your main query returns thousands of rows.

Graham

2. Lexman - February 6, 2008

We are also in the middle of using XML publisher for invoicing.
What we have done is loaded all the required data in to a staging area and then used ps query to generate the xml data.

Inside RTF template, I used regrouping

But the challenge is we have different last pages.
I tried using
and it works ok in preview mode in template, but when it thru scheduler it does not work. (used word feature File > page setup >Layout )
How can we create a section break?

3. Silvia - February 24, 2008

I have used App Engine to generate XML & XSD file having multiple rowsets.
Then I created a new Data Source of type rowset and uploaded thses 2 files.
But when I try to create a new Report Definition with data source type as rowset and the Data source name created before it throws the following error

Invalid XSD File: (235,18)
This XSD file is not valid. Please correct the error and upload again.

Could you assist me in this issue?

4. PeopleSoft Tipster - February 27, 2008

Silvia,
have a look through your XSD file and see what is invalid about it. If you can’t work it out maunally I believe you can get online tools to automate the process. The technique mentioned above is for Query, but there is no reason that it shouldn’t also work for AE. After all, you have much tighter control of things when using an AE than a Query.

Bear in mind that it isn’t really multiple rowset output, it’s still a single rowset (containing more than one set of data) and it’s more of a workaround Query’s limitation that a technique to apply to everything.

Duncan

5. krishnaraj - March 4, 2008

BI Publlisher Guru’s,

I have one requirement i need to covert the SQR Report into BI Publisher report. For this we need to create XMLP report from scrach or is any other way can we convert the SQR report into XMLP report.

Thanks, Krishnaraj Sundararajan.

6. Milly - July 1, 2009

I have generated the xml and xsd files and they get posted correctly.
But the pdf file I have getenerated does not post and I get an HTTP error.

I am unable to figure out why the pdf does not post and why I cant see it in process monitor.
I am using the ReportDef.Publish for that and have a rowset data defintion.

7. Tipster - July 18, 2009

You won’t see the output in Process Monitor, only Report Manager.

8. Katia - December 7, 2009

Dear tipster, on the first post you say ‘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.’ Do you have a sample on how to do this ?

Also, I can see several delivered report defns in 8.5 that have data source of XML file but no corresponding app engine – how else could the data be extracted and passed to the reports ?

9. Tipster - December 7, 2009

Katia,

It’s great that you’re doing some exciting work with XMLP, but it has moved on in the 2 years since I wrote this post, particularly if you’re looking at Tools 8.5.

For complex queries you’ll now want to be using Connected Queries as I believe you can no longer use an App Engine rowset as a data source.

Duncan

10. Clive George - June 11, 2010

Hi Duncan,

I use AE to produce xml files. I know that Crystal can use xml as a data source, can I combine the two in PeopleSoft?

11. Tipster - June 11, 2010

Apologies Clive, but I’m not too hot on Crystal. Try one of the forums (IT Toolbox, PeopleSoft Fans or the Oracle Forums) and you might have more luck.


Sorry comments are closed for this entry

Follow

Get every new post delivered to your Inbox.

Join 419 other followers

%d bloggers like this: