You Don’t Have to Write PeopleCode to Create an XML / BI Publisher Report using PS Query!

This is the second post in the XMLP tutorial series and will help you create your first simple XML Publisher report using Query as the data source.

If you are a functional consultant or an end user trying to learn this reporting tool, you have come to the right place.

Let’s get started.

Create a Data Source (Query)

As you saw in the getting started post, XML Publisher clearly separates the data extraction logic from data presentation. Data extraction is handled by the Data Source. In this tutorial, we will be using a PS Query as the data source – the source of data for our report.

Apart from PS Queries, XML Publisher also supports Connected Queries and XML files as data sources. We will see Connected Queries and XML files in future posts.

Now, we will quickly look at SGK_VOUCHERS_US002, a query I’d already created. It fetches few fields from the VOUCHER table. To keep things simple, I’ve specified a criteria to limit the Business Unit to US002 so that only Vouchers belonging to this BU get fetched. The SQL underlying the query is given below.

PS Query SQL text

On running this query, you get the below output.

PS Query Output

You can create your own query by navigating to Reporting Tools > Query > Query Manager and then clicking the Create New Query link. The query you create would be your data source.

Now that our query is ready, let’s go ahead and register it.

Register the Data Source

To register a data source, navigate to Reporting Tools > BI Publisher > Data Source and clink on Add New Value.

Add XMLP Data Source

As mentioned earlier, PeopleSoft presently offers support for three data source types – Connected Query, PS Query and XML File. From this list of three, we will choose PS Query.

Data source types Rowset and XMLDoc were deprecated in PeopleTools 8.50 . So you will not be able to add these data source types in newer versions. However, you can still view and edit the existing ones.

Data Source ID is the name of the query we created in the previous step – SGK_VOUCHERS_US002.

Now click Add and fill in the Description and Owner ID.

Now click on the Generate links to generate sample XML data file and the XSD file. Please note that this feature is available only when PS Query or Connected Query is used as the data source. For XML file based data sources, you will have to manually create these files and upload them here.

Once the files are generated, the Generate links are replaced by Regenerate links as shown below.

Data Source after Generate link clicked

Download the sample XML data file onto your desktop and save it as SGK_VOUCHERS_US002.XML. In the next step, we will use this file for building our template using MS Word.

Pages — 1 2 3 4

Tags: , ,
Subscribe to Comments RSS Feed in this post
6 Responses
  1. Simply Awesome..this helps everyone a lot who are new to XMLP..

    Thanks
    Kalyan

    • @Kalyan – Glad that you found this to be useful.
      Looking forward for more feedback from you.

  2. This post is simply awesome and easy to understand the entire process of creating reports with BI publisher. keep posting like this..

  3. It is good…if we can work on more complex things, that helps the people to learn more… Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

*

Recommended for you:
xmlp-getting-started
Getting Ready to Generate XML / BI Publisher Reports

Close