Tuesday, 8 March 2011

Queries with Hyperlinks

From PeopleTools 8.50 the PS Query tool allows the user to create a hyperlink to another Query, PeopleSoft component or an external link. You can also bind into the url string fields from the query. Very powerful. However, there are two BIG issues that I've discovered

1) if you schedule a Query to Excel then the Query parameters are displayed horizontally in the first rows of the worksheet. The code that inserts in the hyperlinks is not expecting the parameters to be there and so starts adding the links at row 2. This means that the hyperlinks are out of step with the rows of data and the last n rows have no hyperlink at all. Reported this to Oracle support and have a POC to fix this for 8.50.11.

2) The other issue is that hyperlinks seem to increase the size of the Excel file massively. So when a Query is run to window there is a size in bytes shown to the right of the links to download to Excel, CSV and XML. Not sure quite what this size is but it never matches any of the download sizes (could well refer to the JOLT data message in bytes). Anyway, the screenshot below shows the result of adding a single drilling link to an existing field. The Excel file without the link is 8,721KB and the same Query with the link added increases the size of the Excel file to a massive 36,516KB. Note the CSV and XML output types are similar sizes.

Logging this with Oracle.


Graham said...

Wanted to give an update to this issue.

Oracle Support have issued us with a POC (864979-01). It contains a new version of psoftapi.dll. This was compiled by the developer using a more recent version of MS C++ Redist files so you may need also install it from here (http://www.microsoft.com/downloads/en/details.aspx?FamilyID=fb01abe6-9099-4544-9aec-0ac13f19bc50)

This only needs installing on your batch server as it's only scheduled Queries that suffer this problem. Also, it appears you only need the server version of the dll (even though the POC supplies the client version too).

This POC was cut for 8.50.11 on a Windows 2008 platform against MS SQL Server.

All POCs are available from the Oracle FTP site. ftp://ftp.oracle.com/peoplesoft/outgoing/ptools/POC-IDDA

murugaganesh said...

Hi Graham,

Surprisingly I was landed into your post after spent the considerable time in the Oracle support site regarding the second issue which you specified here.

We are also facing the same issue when include the DrilDown URL the file size increased almost thrice.

So Please let me know if you find any solution for the second issue.

It will help me to fix the issue at my end.


Muruga Ganesh