Using Excel as a datasource May 19, 2008Posted by Duncan in PeopleTools, XML Publisher.
This post is from a coupl of months ago, but I don’t know how I missed it. Tim Dexter discusses how to use an Excel spreadsheet as a data-source.
This has the potential to be quite useful as clients frequently ask for some kind of Excel output which they then update ‘offline’ (i.e. disconnected from PeopleSoft) and then need a method of reloading the updates back into PeopleSoft.
Xls XMLP Output opens in Browser – Workaround April 18, 2008Posted by Duncan in PeopleTools, XML Publisher.
There is a potential workaround for the issue detailed here, but it’s far from a graceful solution. Perhaps someone out there can suggest a more elegant implementation of the workaround.
Almost by accident while troubleshooting this issue I discovered that if I took the output text file (which incorrectly opens in the browser) within the Report Repository and editted it, then tried to open it again it would open in Excel. Strange! It seemed to make no difference what change I made – or even if I reversed out a previous change – all that mattered was that I’d editted it. Furthermore it only worked if I editted it with certain tools.
The upshot was that when you edit a text file using Windows Notepad it inserts an invisible non-printing character at the start called a BOM (Byte Order Mark). This is all related to whether the format is ANSI, or UTF-8 etc. Using a fuller-featured editor (I used Notepad++, see the Applications tab above for more) I toggled whether the BOM is present or not and this affected whether the file opened in Excel or a browser. Further testing seemed to show that it didn’t have to be a BOM character, as long as I didn’t interfere with the tags or their data content this workaround still worked.
Now although I have a method, I can’t decide the best method of implementing it. The method I use at the moment is after the file is generated – but before it is published – I insert the character. Using PeopleCode the only method I can think of to achieve this is to create a new text file, insert the character to ensure Excel output, then copy in the contents of the original output file. The only alternatives I can think of involve external calls from PeopleCode.
Any smarter ideas?
The Future for XMLP April 17, 2008Posted by Duncan in Oracle, XML Publisher.
Tim Dexter’s presentation on XML Publisher at Collaborate’08 gives us an insight into what lies ahead for XMLP.
The first portion of the presentation is more of an introduction so will be familiar to many of you, from slide 32 onwards however it got more interesting.
- XBRL Publisher?
- Excel Templates (finally!)
- Master Templating (like the slide master in Powerpoint, or CSS in HTML)
The presentation can be found on Tim’s post here:
Xls XMLP Output opening in Browser, not Excel April 16, 2008Posted by Duncan in PeopleTools, XML Publisher.
I have an issue that I wouldn’t have thought I’m the first to encounter, but I can’t see any record online of others discussing it. The problem is as follows:
I’ve written an XML Publisher report that outputs an Excel file. When I open the output from within Report Manager it opens the file in the browser, when I want it opening in Excel.
I’m aware that XMLP doesn’t output binary .XLS files, but what’s described as Excel(HTML) – which is basically a text file containing HTML that can be read and rendered as a spreadsheet when opened with Excel. The file in the report repository has an xls extension, and the Report Manager Hyperlink points to the right place. If – instead of opening the file – I save it locally then open it with Excel it renders perfectly.
Also, if I change the output to RTF instead of XLS then it opens in Word correctly. It’s also not a template issue as if I preview the output from within the XMLP Report Definition it opens in Excel. This happens on more than one client PC, and in different browsers so it doesn’t look like it’s a local machine configuration issue.
If I place the output xls file on the web server and create a hyperlink on a PIA page it works fine. However if I repoint the hyperlink to the same output file in the report repository it doesn’t work – so it seems as though the psreports servlet might be the issue. If I use a tool to examine the HTTP Header for each of these requests I can see that the Mime Type is set to “Application/MSExcel” for a PIA page hyperlink (which works), and “text/html” for the same file from the Report Repository. The mime types are set correctly (by default) in the Web Server profile, but I can’t see anywhere to set the mime-types for psreports.
I’ve been in dialogue with GSC for the last 6 weeks or so, but they say that it isn’t a misconfiguration or bug, but a ‘”potential future development with a status of ‘Awaiting review’.” The case id is:
1750412000 – “8.50: Support MHTML Excel format in XML Publisher”.
It seems Oracle eBiz suite has the same issue, but they can fix it with a configuration change.
I do have a workaround (not a solution) for this – posted here – but has anyone else faced this issue? And if so, how have you dealt with it?
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 …
XMLP and Checkboxes in PDFs January 2, 2008Posted by Duncan in Oracle, PeopleSoft, PeopleTools, XML Publisher.
I’ve been having problems getting checkboxes to print correctly when creating PDFs using XMLP. I was faced with tiny little diamonds like those on the left here, when I was expecting ‘standard’ checkboxes like those on the right:
It turns out that the XML Publisher default PDF output font does not include a glyph to represent a checkbox and this is an issue faced by one or two Oracle people already (Tim Dexter has written about it here). I thought I add a quick write-up as I’m tackling the issue from a PeopleSoft perspective.
XML Publisher and Out of Memory Errors October 30, 2007Posted by Duncan in Oracle, PeopleSoft, PeopleTools, XML Publisher.
I’m getting a lot of comments on the XML Publisher posts about ‘Out of Memory’ errors. I’ve not experienced this myself so can’t give a fix or workaround, but wondered whether anyone else had hit this issue and found a resolution?
Doing a bit of digging there are a number of suggestions, none of them relate to the PeopleSoft implementation of XMLP however so I’m unconvinced by any solution in particular.
There’s a Tools patch out for this issue (8.48.06 and up contains the fix), so check your version of Tools:
There’s a suggestion to change a parameter for XMLP (I guess this would be on the Process Defn or more likely the Process Type in PeopleSoft):
There’s a suggestion to create a config file (it is not delivered) to specify a temporary directory for processing large files:
And there’s also a suggestion that a patch will fix it:
If I was troubleshooting it and was already on 8.48.06 or greater, I’d probably try the parameter followed by the config file.
If anyone has encountered this error and fixed it, it’d be great if you posted a comment to let us know the resolution.