» Publishers, Monetize your RSS feeds with FeedShow: More infos (Show/Hide Ads)
With some help from Bryan, I have uncovered a way of being able to debug or at least log what BIServer is doing when BIP sends it a query request.
This is not for those of you querying the database directly but if you are using the BIServer and its datamodel to fetch data for a BIP report. If you have written or used the query builder against BIServer and when you run the report it chokes with a cryptic message, that you have no clue about, read on.
When BIP runs a piece of BIServer logical SQL to fetch data. It does not appear to validate it, it just passes it through, so what is BIServer doing on its end? As you may know, you are not writing regular physical sql its actually logical sql e.g.
select Jobs."Job Title" as "Job Title", Employees."Last Name" as "Last Name", Employees.Salary as Salary, Locations."Department Name" as "Department Name", Locations."Country Name" as "Country Name", Locations."Region Name" as "Region Name" from HR.Locations Locations, HR.Employees Employees, HR.Jobs Jobs
The tables might not even be a physical tables, we don't care, that's what the BIServer and its model are for. You have put all the effort into building the model, just go get me the data from where ever it might be.
The BIServer takes the logical sql and uses its vast brain to work out what the physical SQL is, executes it and passes the result back to BIP.
select distinct T32556.JOB_TITLE as c1,
T32543.LAST_NAME as c2,
T32543.SALARY as c3,
T32537.DEPARTMENT_NAME as c4,
T32532.COUNTRY_NAME as c5,
T32577.REGION_NAME as c6
from
JOBS T32556,
REGIONS T32577,
COUNTRIES T32532,
LOCATIONS T32569,
DEPARTMENTS T32537,
EMPLOYEES T32543
where ( T32532.COUNTRY_ID = T32569.COUNTRY_ID
and T32532.REGION_ID = T32577.REGION_ID
and T32537.DEPARTMENT_ID = T32543.DEPARTMENT_ID
and T32537.LOCATION_ID = T32569.LOCATION_ID
and T32543.JOB_ID = T32556.JOB_ID )
Not a very tough example I know but you get the idea.
How do I know what the BIServer is up to? How can I find out what the issue might be if BIServer chokes on my query?
There are a couple of steps:
- In the Administrator tool you need to set the logging level for the Administrator user to something greater than the default '0'. '7' is going to give you the max. Just remember to take it back down after you have finished the debug.
- I needed to bounce my BIServer service
- Now here's the secret sauce. Prefix the following to your BIP query
set variable LOGLEVEL = 7;
Set the log level to that you have in the admin tool
- Now run your BIP report. With the prefix in place; BIServer will write to the NQQuery.log file. This is located in the ./OracleBI/server/Log directory. In there you are going to find the complete process the BIServer has gone through to try and get the data back for you
A quick note, if the BIServer can, its going to hit that great BIEE cache to get your data and you may not see the full log. IF this is the case. Get inot hte Administration page (via the browser login) and clear out your BIP report cursor. Then re-run.
This will hopefully help out if you are trying to debug that annoying BIP report that will not run or is getting some strange data.
Don't forget to turn that logging level back down once you are done. This will avoid the DBA screaming at you for sucking up all the disk space on the system.
I have been helping out one of our new customers over the last day or two and I have even managed to get to the bottom of their problem FTW! They use BIEE and BIP and wanted to mount a BIP report in a dashboard page, so far so good, BIP does that! Just follow the instructions in the BIEE user guide. The wrinkle is that they want to enter some fixed instruction strings into the dashboard prompts to help the user. These are added as fixed values to the prompt as the default values so they appear first. Once the user makes a selection, the default strings disappear.
Its a fair requirement but the BIP report chokes
Now, the BIP report had been setup with the Autorun checkbox, unchecked. I expected the BIP report to wait for the Go button to be hit but it was trying to run immediately and failing. That was the first issue. You can not stop the BIP report from trying to run in a dashboard. Even if the Autorun is turned off, it seems that dashboard still makes the request to BIP to run the report. Rather than BIP refusing because its waiting for input it goes ahead anyway, I guess the mechanism does not check the autorun flag when the request is coming from the dashboard. It appears that between BIEE and BIP, they collectively ignore the autorun flag. A bug? might be, at least an enhancement request. With that in mind, how could we get BIP to not at least not fail?
This fact was stumping me on the parameter error, if the autorun flag was being respected then why was BIP complaining about the parameter values it should not even be doing anything until the Go button is clicked. I now knew that the autorun flag was being ignored, it was a simple case of putting BIP into debug mode. I use the OC4J server on my laptop so debug msgs are routed through the dos box used to start the OC4J container. When I changed a value on the dashboard prompt I spotted some debug text rushing by that subsequently disappeared from the log once the operation was complete. Another bug? I needed to catch that text as it went by, using the print screen function with some software to grab multiple screens as the log appeared and then disappeared.
The upshot is that when you change the dashboard prompt value, BIP validates the value against its own LOVs, if its not in the list then it throws the error. Because 'Fill this first' and 'Fill this second' ie fixed strings from the dashboard prompts, are not in the LOV lists and because the report is auto running as soon as the dashboard page is brought up, the report complains about invalid parameters.
To get around this, I needed to get the strings into the LOVs. Easily done with a UNION clause:
select 'Fill this first' from SH.Products Products UNION select Products."Prod Category" as "Prod Category" from SH.Products Products
Now when BIP wants to validate the prompt value, the LOV query fires and finds the fixed string -> No Error. No data, but definitely no errors :0)
If users do run with the fixed values, you can capture that in the template. If there is no data in the report, either the fixed values were used or the parameters selected resulted in no rows. You can capture this in the template and display something like.
'Either your parameter values resulted in no data or you have not changed the default values'
Thats the upside, the downside is that if your users run the report in the BP UI they re going to see the fixed strings. You could alleviate that by having BIP display the fixed strings in top of its parameter drop boxes (just set them as the default value for the parameter.) But they will not disappear like they do in the dashboard prompts, see below.
If the expected autorun behaviour worked ie wait for the Go button, then we would not have to workaround it but for now, its a pretty good solution.
It was an enjoyable hour or so for me, took me back to my developer daze, when we used to race each other for the most number of bug fixes. I used to run a distant 2nd behind 'Bugmeister Chen Hu' but led the chasing pack by a reasonable distance.
This one is for standalone/BIEE uses of Publisher. All the ERP/CRM/HCM folks are already catered for and can tuck into a nut cutlet and arugala salad. Sorry, I have just watched Food Inc and even if only half of it is true; Im still on a crusade in my house against mass produced food. Wake up World!
If you have ventured into the world of sub templating, you'll be reaping some development benefit. In terms of shared report components and calculations they are very useful. Just exporting all of your report headers and footers to a single sub template can potentially save you hours and hours of work and make you look like a star. If someone in management gets it into their head that they would like Comic San Serif font rather than Arial in their report headers, its a 10 min job rather than 100 hours!
What about the rest of the report content? I hear you cry. Its coming in 11g, full master template support. Your management wants bright blue borders with yellow backgrounds for all the tables in your reports, 5 minute job!
Getting back to sub templates and my comment about all the ERP/CRM/HCM folks be catered for. In the standalone release there is no out of the box directory for you to drop your sub templates. Dropping them into the main report directory would make sense but they are not accessible there via a URL. An oversight on our part and something that will be addressed in 11g. Sub templates are now a first class citizen in the world of BIP, you can upload them and BIP will know what to do with them. But what do you do right now?
The easiest place to put them where BIP can 'see' them is to create a directory under the xmlpserver install directory in the J2EE container e.g.
$J2EE_HOME/xmlpserver/xmlpserver/subtemplates
You can call it whatever you want but when the server is started up, that directory is accessible via a URL i.e. http://tdexter:9704/xmlpserver/subtemplates/mysub.rtf. You can therefore put it into the top of your main templates and call the sub template.
<?import: http://tdexter:9704/xmlpserver/subtemplates/mysub.rtf?>
Of course, you can drop them anywhere you want, they just need to be in a web server mountable directory. Enjoy the arugala!
I was just checking up on Google charts this morning to see what was new. They now support QR codes, another barcode format that you might need in your outputs at some point.
similar to datamatrix barcodes, its used to store large amounts of data. Kudos to someone that can decipher the contents of the code on the left (no cheating folks :)
To quote Google,
QR codes are a popular type of two-dimensional barcode. They are also known as hardlinks or physical world hyperlinks. QR Codes store up to 4,296 alphanumeric characters of arbitrary text. This text can be anything, for example URL, contact information, a telephone number, even a poem! QR codes can be read by an optical device with the appropriate software. Such devices range from dedicated QR code readers to mobile phones.
Calling the code is a simple URL away, embedding it in your outputs, even simpler. No font files to load just create a field or dumy image with a URL pointing to the Google charts API. More information from Googlehere.
More information on BIP images here.
During upgrades you will need to uninstall Excel Analyzer, if you use it of course. I have hit a few issues getting my system cleaned out when uninstalling and trying to install the new version.
Junichi from the dev team has a useful crib sheet to ensure its cleaned out completely.
1. Uninstall Excel Analyzer from "Control Panel" -> "Add or Remove Programs"
2. Remove Excel Analyzer add-in
2.1 Excel 2003
2.1.1 Open a blank excel and go to "Tools" -> "Add-ins"
2.1.2 If you see "Xmlpreportaddin", click it to remove
2.2 Excel 2007
2.2.1 Click ms office button on the left upper in MS Excel
2.2.2 Click "Excel Options" -> "Add-ins
2.2.3 Select "Excel Add-ins" at the bottom then click go
2.2.4 If you see "Xmlpreportaddin", click it to remove
3. Open explorer and go to "C:\WINDOWS\assembly".
4. Check if the assemblies which start with "XDO" don't remain there.
5. Install Excel Analyzer again
Download a Excel file from BIP server and go to Login
or
If the OS is Vista, run ExcelAnalyzer.exe
6. Re-start your PC.
Happy Analyzing!
As many of you know BIP can generate Excel output from RTF template. However, being able to generate a multi-sheet Excel output is a bit more tricky.
For those of you using the standalone release the Excel Analyzer button can help you out. You can build Excel templates that can then be loaded back to the server and used as a template, at runtime generating binary multisheet Excel output. These templates rely on the data being dropped into a single worksheet and then building new sheets on top of the first worksheet. this approach provides masses of freedom in terms of adding native Excel functionality. However, you do need to generate flat data for the base sheet.
For those of you that don't use a standalone release and don't generate flattened data, what your options?
From around the Excel 2002, it has supported an XML format. Thankfully new versions of Excel support the older XML formats. I got a question today asking how do I create an XSL template to generate multisheet Excel output. It took some digging to find information about the Excel XML format. Being the consummate lazybones, I went off and found an XSLT stylesheet to get me started. It was just a simple case of then modifying that to work with my data and to get multiple sheets.
BI Publisher does not supply any XSL editor, because I'm so hard core, I used notepad. But there are several exercises out there some of them are even free, just choose your poison.
The basic structure of Excel XML is as follows:
<Workbook>
<Styles>
<Style>
<Alignment/>
<Borders>
<Border/>
</Borders>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Names>
<NamedRange/>
</Names>
<Worksheet>
<Names>
<NamedRange/>
</Names>
<Table>
<Column/>
<Row>
<Cell>
<NamedCell/>
<Data>
<Font/>
<B/>
<I/>
<U/>
<S/>
<Sub/>
<Sup/>
<Span/>
</Data>
<PhoneticText/>
<Comment>
<Data>
<Font/>
<B/>
<I/>
<U/>
<S/>
<Sub/>
<Sup/>
<Span/>
</Data>
</Comment>
<SmartTags>
<stN:SmartTag/>
</SmartTags>
</Cell>
</Row>
</Table>
<WorksheetOptions>
<DisplayCustomHeaders/>
</WorksheetOptions>
<WorksheetOptions>
<PageSetup>
<Layout/>
<PageMargins/>
<Header/>
<Footer/>
</PageSetup>
</WorksheetOptions>
<AutoFilter>
<AutoFilterColumn>
<AutoFilterCondition/>
<AutoFilterAnd>
<AutoFilterCondition/>
</AutoFilterAnd>
<AutoFilterOr>
<AutoFilterCondition/>
</AutoFilterOr>
</AutoFilterColumn>
</AutoFilter>
</Worksheet>
<ComponentOptions>
<Toolbar>
<HideOfficeLogo/>
</Toolbar>
</ComponentOptions>
<SmartTagType/>
</Workbook>
It's a big structure, but I doubt you'll need to use that Much of it. You can see how you can build the loop to generate multiple worksheets. Here's my complete XSLT stylesheet, I'll work through some of the features you can understand them better.
Here's the data I'm working with:
<EMPLOYEES>
<LIST_G_DEPT>
<G_DEPT>
<DEPARTMENT_ID>10</DEPARTMENT_ID>
<DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
<LIST_G_EMP>
<G_EMP>
<EMPLOYEE_ID>200</EMPLOYEE_ID>
<EMP_NAME>Jennifer Whalen</EMP_NAME>
<EMAIL>JWHALEN</EMAIL>
<PHONE_NUMBER>515.123.4444</PHONE_NUMBER>
<HIRE_DATE>1987-09-17T00:00:00.000-06:00</HIRE_DATE>
<SALARY>4400</SALARY>
</G_EMP>
</LIST_G_EMP>
<TOTAL_EMPS>1</TOTAL_EMPS>
<TOTAL_SALARY>4400</TOTAL_SALARY>
<AVG_SALARY>4400</AVG_SALARY>
<MAX_SALARY>4400</MAX_SALARY>
<MIN_SALARY>4400</MIN_SALARY>
</G_DEPT>
...
Just a employee by department data set with some structure provided by a data template.
<xsl:stylesheet version="1.0" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:user="urn:my-scripts" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"> <xsl:output method="xml" encoding="utf-8" indent="yes" omit-xml-declaration="no"/>
<xsl:template match="/">
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"> <ss:Styles> <ss:Style ss:ID="Default" ss:Name="Normal"> <ss:Font ss:Color="black" ss:Size="10" ss:Bold="1" ss:FontName="Arial"/> </ss:Style> <ss:Style ss:ID="x1"> <ss:Font ss:Color="black" ss:Size="10" ss:FontName="Arial"/> </ss:Style> <ss:Style ss:ID="xdo2"> <ss:Font ss:Color="black" ss:Size="10" ss:FontName="Arial"/> <ss:NumberFormat Format="$#,##0;[Red]$#,##0" Bold="0" /> </ss:Style> </ss:Styles> <xsl:apply-templates select="EMPLOYEES"/> </Workbook> </xsl:template> <xsl:template match="EMPLOYEES"> <xsl:for-each select=".//G_DEPT"> <Worksheet Name="{.//DEPARTMENT_NAME}"> <Table x:FullColumns="1" x:FullRows="1"> <Row> <Cell> <Data ss:Type="String">Employee ID</Data> </Cell> <Cell> <Data ss:Type="String">Employee Name</Data> </Cell> <Cell> <Data ss:Type="String">Email</Data> </Cell> <Cell> <Data ss:Type="String">Phone Number</Data> </Cell> <Cell> <Data ss:Type="String">Hire Date</Data> </Cell> <Cell> <Data ss:Type="String">Salary</Data> </Cell> </Row> <xsl:for-each select=".//G_EMP"> <Row> <Cell ss:StyleID="x1"> <Data ss:Type="String"> <xsl:value-of select="EMPLOYEE_ID"/> </Data> </Cell> <Cell ss:StyleID="x1"> <Data ss:Type="String"> <xsl:value-of select="EMP_NAME"/> </Data> </Cell> <Cell ss:StyleID="x1"> <Data ss:Type="String"> <xsl:value-of select="EMAIL"/> </Data> </Cell> <Cell ss:StyleID="x1"> <Data ss:Type="String"> <xsl:value-of select="PHONE_NUMBER"/> </Data> </Cell> <Cell ss:StyleID="x1"> <Data ss:Type="String"> <xsl:value-of select="substring(HIRE_DATE,1,10)"/> </Data> </Cell> <Cell ss:StyleID="xdo2"> <Data ss:Type="Number"> <xsl:value-of select="SALARY"/> </Data> </Cell> </Row> </xsl:for-each> </Table> </Worksheet> </xsl:for-each> </xsl:template>
</xsl:stylesheet>
We start with some standard style sheet declarations about the output format, the encoding, etc.
- here there are a bunch of namespace declarations. I have added in some Styles so that you can see how they are used in the worksheets. I then make a call out to the EMPLOYEES template. We could create the complete template in a contiguous fashion but just to break things out so you can see the separate sections have a separate template for the worksheets and their contents.
With the template match for EMPLOYEES, you can then see I am doing a simple for-each over the G_DEPT level in the data. Inside that loop you can see the Worksheet element.
<xsl:for-each select=".//G_DEPT">
<Worksheet Name="{.//DEPARTMENT_NAME}">
this is how we create a worksheet for every department. You can also see I can set the name of the worksheet to the department name, so it's completely dynamic.
Inside the department loop, you can see I just lay out the title row cells. Then I create a new for-each loop over the G_EMP level of the data.
<xsl:for-each select=".//G_EMP">
<Row>
<Cell StyleID="x1">
<Data Type="String">
<xsl:value-of select="EMPLOYEE_ID"/>
</Data>
</Cell>
...
I will now get a row in my spreadsheet for every employee for a given department. You'll also notice at the Cell level, I can define a style ID that references the styles defined at the workbook level.
Finally I close out my for each loops, remember you have two of them, G_DEPT and G_EMP.
That's it, it's a simple XSLT stylesheet from which you could probably build on quite easily. Microsoft has a few references to the XML format, this particular format, is for Excel 2002. As I mentioned they upwardly compatible. MSExcel XML Spreadsheet Reference
Deploying the templates is straightforward, no matter flavor of BI Publisher you running, it's just a simple case of uploading them into the template manager. There is a wrinkle thou, when running the template you are going to be generating XML output. The browser is not going to know that you have generated Excel XML and therefore needs to open Excel to view it. This can be seen as a limitation of the BI Publisher server. An enhancement to allow me to set what the output type is going to be would be great. For now, your users are going to have to export and save the XML to the client machine. Then, get Excel to open up the XML file, they will then have multisheet Excel coming from BI Publisher.
XSL template and data available here.
Mikhail from the PeopleSoft reporting team came up with a good question today. A customer that converted one of their Crystal Reports to BIP was having some issues. The report was not sorting the same way the Crystal report used to. They wanted the data sorted by Numbers , then uppercase followed by lower case strings.
It reminded me of the Cool Sorting post from 2 years ago. Dang, we have written a lot! With a little change I came up with a similar solution.
You need to use a trick in the sort
<?for-each:row?><?sort:string-length(substring-before ('01234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
,substring(PRODUCT_ID,1,1))) ;'ascending';data-type='number'?>
I have a the string '01234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' to provide the required sort order ie numbers , then upper case then lower case values.
The substring-before finds the sub string in the big string based on the first character of the PRODUCT_ID e.g. value starting with '3' will return '012'
The string-length then calculates the length of the string e.g. '012' returns 3.
We then sort by that string length value.
To simplfy the code a little I would put the string into a variable
<?variable: srtStr; "'01234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'"?>
thats a " surrounding the single quotes. Then reference it
<?sort:string-length(substring-before ($srtStr,substring(PRODUCT_ID,1,1))) ;'ascending';data-type='number'?>
Bit neater and more portable ie you can use the same variable for multiple sorts. RTF available here.
One of the outputs that BIP does not do so well at is plain ol text output. We normally suggest looking at the eText template or learning XSL to build an XSL to TXT template. Came across a couple of blog posts from Kevin on XSL templates that would help get you started.
http://kwoodrow.blogspot.com/2008/07/bip-xsl-text-templates-part-1.html
http://kwoodrow.blogspot.com/2008/07/bip-xsl-text-templates-part-2.html
Thanks Kevin, apologies for not spotting them until now!
Putting aside the scheduling posts, I have just one more. I got a good question this morning that I thought was simple but turned out to need, some probably long forgotten functionality.
How do I secure PDF output when bursting?
Just set the PDF security to 'on' and then set the security features you want in the report configuration page, right? Maybe, but the more detailed requirement is the password for the document needs to be the last four characters of the customer's account number. That requirement moves down to the burst output documents i.e. its more detailed than a password set at the report level. How can you achieve that?
A bit of thinking and I alighted upon the little used and almost forgotten since the advent of a nice and friendly user interface; ability to embed document properities into the RTF template. Using this method I can embed the properties at the right level ie document and I can pick up the password from the account number. Of course there will only be one account number to find because we are splitting by customer in our burst; if you are splitting by invoice then you will need to modify the data set to bring the password in as needed.
Adding the properties is straightforward enough in Word 200-2003 (File -> Properties.) Finding the property dialog in 2007 is a tougher. Hit the button, top left -> Prepare -> Properties -> Advanced Properties.
Now its case of getting to the Custom tab then entering:
Name - enter the XML Publisher property name prefixed with "xdo-"
Type - select "Text"
Value - enter the property value. To reference an element from the incoming
XML data, enter the path to the XML element enclosed by curly braces. For
example: {/root/password}
So to add the password to the PDF output document you will need the follwoing properties to be set:
Name:xdo-pdf-security - this enables the PDF security.
Value: true
Name: xdo-pdf-open-password
Value: {/root/password} - this will be 'looked up' at runtime
You can of course add anything else that you might like to use xdo-pdf-no-printing, xdo-pdf-no-changing-the-docment, encryption levels, etc.
You can now burst your documents to your customers, suppliers, staff, etc and provide various levels of security that you might need and they can use a password personal to them. Happy Bursting!
This post is for the standalone users out there, I'll explain why in a moment. I was with a customer in sunny, warm Nebraska last week, OK, OK it was sunny but 'warm' is a little of an exaggeration. It was freezing, somewhere around 3F without the windchill. As I drove into Omaha, I was wondering why it seemed deserted; upon opening the car door, it became all too clear, where was my pullover. I should have realized when picking up the car but that was under cover and they had pre-heated the car.
The customer was looking for a way to stop BIP's scheduling engine sending out report outputs on a public holiday, ideally stopping the job altogether and then re-scheduling it for the next non-holiday day. I was with a colleague, Joel from really sunny and really warm Texas. Putting our heads together we thought about using the scheduler in OBIEE, Delivers, to drive the process. For the uninitiated, Delivers, has a really neat feature. You can create a trigger request that gets executed first; if it returns a row then the main request is run. You can not directly run a BIP report from Delivers but there are ways to call the BIP scheduler using a java class and BIP's web services. Our plan was to set up the trigger request to check the current date against a table of excluded dates ie holidays. If there was a match then dont return a row and this would stop the whole process. If there was not a match then force a row to be returned and allow the process to continue. To get the BIP report to run we would employ the Advanced tab on the iBot definition. Here you can define a java class that can call the BIP web services.
When I got home to 32F Colorado (toasty!), I made a start on looking at the issue. I hit a few barriers with the Delivers/Web Services approach which I'll cover in another post. Not insurmountable, but quite a complex solution for the customer to implement. I took another look at the problem from the BIP side. BIP does not currently have a means of stopping a job run based using a trigger , would be a great feature. It also, does not support custom calendars, at least not yet. So, I have no way of stopping the job but I thought of a way to at least stop the delivery of the report.
Bursting Abuse
The bursting engine is normally used to split a set of batch data, then format and deliver the reports. With a little abuse we can use it to stop delivering its payload based on a single report data set rather than a batch of data. Create the report as you would normally, queries, parameters, templates, etc. You'll need to add an element in the top of the tree to act as a KEY column for the bursting query. Something like:
<ROOT>
<BURST_KEY>XXXX</BURST_KEY>
<DATA> ...
</DATA>
</ROOT>
Then add a bursting definition, you'll have to set a split and deliver by column but for this purpose we can just use BURST_KEY element in our data set ie we are not going to split it. Now comes the tricksy piece. Asume you have created a table in the db to hold the holiday dates that you want to avoid . you can then construct a CASE statement in the burtsing select statement to check the current date i.e. the date the report is running against the table of excluded dates.
select
case when (select count(1) from no_report_date e
where to_char(e.excluded_date,'YYYYMMDD') - to_char(sysdate,'YYYYMMDD') = 0)=0 then 'XXXX' else 'YYYY' end KEY,
'Certificate' TEMPLATE,
'RTF' TEMPLATE_FORMAT,
'en-US' LOCALE,
'PDF' OUTPUT_FORMAT,
'EMAIL' DEL_CHANNEL,
'tim.dexter@oracle.com' PARAMETER1,
'cc@nowhere.com' PARAMETER2,
'from@nowhere.com' PARAMETER3,
'SUBJECT: Enterprise Bursting Test' PARAMETER4,
'BODY: Test Report Body' PARAMETER5,
'true' PARAMETER6,
'replyto@nowhere.com' PARAMETER7
from
no_report_date d
where rownum=1
Rather than trying to match the dates I actually did a diff on them and tested for a zero value. If the 0 came back ie a match then it returns an invalid KEY value, then the busrting engine will not find a KEY match and will not deliver the document. The rownum statement ensures we only get a single row returned.
This approach has a some drawbacks:
- The report will effectively error out in the scheduler. Becasue the delivery will fail the scheduler will report the whole job as failing. There is a way around this, you can move the CASE statement to the delivery column. In my case the email address. If the we get a KEY match we return the correct email, printer, etc; if not, then we get return a dummy value that will not work. the report will report as completing successfully.
- When the user schedules the report they can not enter the delivery options for the report. As soon as the bursting option is selected the template, output and delivery options are hidden. therefore you will need to either hard code the values or retrieve them from the database in the bursting control query. Not so tough but still a drawback.
If the report should not be delivered today then what should happen? For an answer to that, we get in to the gray and murky area of updating the scheduler tables and we are going way off the reservation ... I'll save that for next time.
Being British, I of course love Indian food, its almost our national dish back in the UK. Roast beef and yorkshire pud is no longer the go to faire. Its a spicy hot chicken Pathia or Dal for me all the way. Thats sometimes hard to find here in Colorado, we have taken to making our own having found only a handful of Indian restaurants in Denver and Springs (suggestions welcome locals :0) Nan bread is now available in local supermarket so we have not built our own tandoori oven.
My tenuous bog post title is not referring to that delicious bread but to an XSL and therefore Publisher issue, well maybe be not an issue; more of something you may need to handle.
there is no native support to handle divide by zero errors or maybe mistakenly dividing a string into a number. Right now the only way around it is to check every value prior to using it in a calculation e.g.
<?if: XXXX >0?> <?XXXX div YYYY?><?end if?>
Not so bad, but if you have multiple calculations to handle in a template it can become a pain and of course is a bunch of code to write or at best copy and paste.
There was a questions on the forum this week asking if BIP had a safeDivide function like Actuate has? After a little digging to find out what the heck a safedivide function was, my initial thought was to create a template(function) for the template to call prior to any divide functions. I am from the school of 'why re-invent the wheel' so Google it took another hit. Lots of cool solutions out there; one of my favorite sites is from Dave Pawson of XSLT book fame - http://www.dpawson.co.uk/xsl/sect2/sect21.html. There is a mass of answers on Dave's site from a host of experts, well worth bookmarking.
Dave's site popped up this solution
Define a format name, like so:
<xsl:decimal-format name="MyFormat" NaN=" - " zero-digit="0"/>
And use it with your format statement:
<?format-number(@totalAmt,'#,###','MyFormat')?>
this is much simpler than building a function. The first statement needs to be placed at the top of your template. In this case if a divide by zero occurs i.e. a NaN then a '-' will be returned.
First thought, corrected and a solution found. My second thought, we need to provide something natively to handle this for folks. Some folks have differing requirements in separate parts of an output, maybe 'N/A' in sme cases and '0' for others. So, I logged an enhancement request to handle this. I logged 9355897, Template Meister Hok Min picked it up and implemented the enhancement within the day. Now waiting for a release vehicle but there's service for you, logged, coded and tested within the day. If you are desperate for it, you can get support to log a one off request on your behalf.
Been a while I know, crazy busy, which is not a bad thing but the blog suffers. Last week was spent embedded in all things 11g BI including the new version of Publisher. There are some very cool and more importantly useful, features coming. My favorite is the new data builder tool; no more 'notepad' to build more complex data structures, oh no!
Its a great drag and drop interface that lets you pull in multiple sources, join them and then group the data to generate a hierarchical data set. You can then abstract the element names to friendly functional names for your layout template builders. There are also some new sources, such as LDAP and Excel - cant wait to share more on the new stuff. Before you ask, I can not tell you when its coming ... you know the drill.
Back to my numbers to words update. String of mails flying around over the toWordsAmt function; typically used in check printing.
<?xdoxslt:toWordsAmt(12345.98)?>
generates 'Twelve Thousand Three Hundred Fourty Five and paise Ninety Eight' It always appears to return the subunit in 'Paise'. Upon investigation the 'paise' is hardcoded; this function was written for some Crystal conversion routines that made it into core code.
A few mails later and it emerges that we would like you to use another function that it a bit more robust and has some more options.
<?xdoxslt:to_check_number(12345.98,2)?>
Typical use of this function is :
xdofx:to_check_number(12345.67, 2)
--> Twelve thousand three hundred forty-five and 67/100
The user can also pass a currency for its precision.
xdofx:to_check_number(12345.67, 'USD')--> Twelve thousand three hundred forty-five and 67/100 Note that the currency is only for precision. No currency word, e.g. dollar or cent, is included in output. All capital output is available with CASE_UPPER key word.
xdofx:to_check_number(12345, 'JPY', 'CASE_UPPER')--> TWELVE THOUSAND THREE HUNDRED FORTY-FIVE
CASE_INIT_CAP, CASE_UPPER, and CASE_LOWER are available.
The user also change the style of decimal area, if needed.
xdofx:to_check_number(12345.67, 'EUR', 'CASE_LOWER', 'DECIMAL_STYLE_WORDS')--> twelve thousand three hundred forty-five and sixty-seven
DECIMAL_STYLE_FRACTION1(default), DECIMAL_STYLE_FRACTION2, and DECIMAL_STYLE_WORDS are available.
Please note for EBS customers this is only available for R12. Its not currently presnt on 11i. You could log a backport request via support thou.
Have a play and you'll be printing words in no time.
Finally, the end of the most manic week I think I have had for a while and that included a public holiday on Monday. Public holidays are a double edged sword for me. On the plus side, you can enjoy your Sunday, you get to lie in bed on Monday, go out for lunch with your partner, swim with the kids. On the downside, there is always a nagging feeling that its a day of work that you are going to have to somehow make up in the remaining four days of the work week. Two late nights this week that I'll be paying for later ...
During my presentation prep I came across a reasonably useful presentation I squirreled away on my hard drive on label printing. A little high level but gives you some information about how to get BIP hooked up to you Zebra printers. Get it here.
Officially a public holiday for us at Oracle today but for the rest of the world and some folks here in the good ol' US of A, a great write up from Andy and his team at Beyond Systems on handling dynamic sql requirements for BIP when connecting to BI Server.
Just the intro to 1. whet your appetite and 2. for those of you wondering what the heck Im talking about:
Recently while working on a high profile BI Publisher project we encountered a rather interesting challenge. The organisation that we were working for are very forward thinking, having adopted OBIEE as their corporate BI solution they had fully bought into the idea of having a single point of truth. This architecture meant that OBIEE server would be the single data source. BI publisher is a great product however it quickly became apparent to us that we had lost the ability to write dynamic SQL through the usual technique of creating a data template and executing a package procedure in the before report data trigger to change the SQL. Now this in itself was not a complete show stopper but the effort required to write a single all inclusive query, then filter the data and conditionally display it in the RTF template was going to be considerable. With this in mind we decided to do some research and came up with this alternative solution which we hope will help others adopt the OBIEE server architecture with BI Publisher. The beauty of this solution is that several reports can condensed into a single report which significantly reduces the development effort and gives the user a much simpler solution.
Document available here.
Thanks Andy and team, really appreciate the time you took to write it up and allow me to share it.
I have been trying to help out a customer recently with a nasty SSL setup for their BIP instance. Wont get into detail but although BIP is running on an SSL server its not seeing itself as running on it. Links and images in the outputs are not 'https' but rather 'http'. Having got the dev team to dig into their code, it appears to be the web server set up that is at fault.
I came up with an idea to work around the issue for now so that they could move forward with their project while the web server issue is addressed - a custom parameter.
Some of you that have turned debug on might have seen several parameter values going by in the log prefixed with 'xslt.' This is what gave me the idea.
You will need to set up the xdo.cfg file to hold the parameter. It needs to sit in the $JDK_HOME/jre/lib directory. Or under the config directory in the reports repository e.g. XMLP\Admin\Configuration for the standalone release.
Heres the beginning of mine:
<config version="1.0.0" xmlns="http://xmlns.oracle.com/oxp/config/">
<!-- Properties -->
<properties>
<!-- System level properties -->
<!-- PLEASE SELECT A VALID TEMPFILE DIRECTORY!!! -->
<property name="system-temp-dir">c:/Temp</property>
<!-- PDF compression -->
<property name="pdf-compression">true</property>
<!-- PDF security -->
<property name="pdf-security">false</property>
<property name="pdf-open-password">user</property>
<property name="pdf-permissions-password">owner</property>
<property name="pdf-no-printing">true</property>
<property name="pdf-no-changing-the-document">true</property>
<!-- Custom Properties -->
<property name="xslt.SERVER_PROTOCOL">"https"</property>
</properties>
I have a custom property(parameter) SERVER_PROTOCOL. Notice the double quotes surrounding the value, they are a must!
Now in my template I just need to declare the parameter
<?param:SERVER_PROTOCOL?>Notice you dont need the 'xslt.'prefix. Then I can reference the value
<?$SERVER_PROTOCOL?>
Im using this as a workaround in this case but Im sure you will come up with other uses.
I got involved in an interesting issue yesterday with Julie. She had two columns of data coming into her data set but she wanted to generate a stacked vertical column report, normally you would have three data points. One of the data points would be along the X-Axis. The count of the instances of the other data point would make up the column. Its an interesting problem ... well I thought it was. Read on if you are interested. The change in font will be explained at the end.
Using the following data:
<ROWSET>
<ROW>
<STE>CMP</STE>
<ACT_LEV>SHORT</ACT_LEV>
</ROW>
<ROW>
<STE>CMP</STE>
<ACT_LEV>SHORT</ACT_LEV>
</ROW>
<ROW>
<STE>CMP</STE>
<ACT_LEV>LNG</ACT_LEV>
</ROW>
<ROW>
<STE>CMP</STE>
<ACT_LEV>OPEN</ACT_LEV>
</ROW>
<ROW>
<STE>CMP</STE>
<ACT_LEV>OPEN</ACT_LEV>
</ROW>
<ROW>
<STE>COM</STE>
<ACT_LEV>SHORT</ACT_LEV>
</ROW>
<ROW>
<STE>COM</STE>
<ACT_LEV>SHORT</ACT_LEV>
</ROW>
<ROW>
<STE>COM</STE>
<ACT_LEV>SHORT</ACT_LEV>
</ROW>
...
</ROWSET>
Notice that
1, Data is de-normalized
2. The ACT_LEV for a given STE can contain any of 4 values
|
ACT_LEV |
|
LNG OFFLINE OPEN SHORT |
This is the required chart:
|
STE |
ACT_LEV | ||||||
|
CMP |
| ||||||
|
OM |
| ||||||
|
CVD |
| ||||||
|
DIF |
| ||||||
|
DRY |
| ||||||
|
IMP |
| ||||||
|
INS |
| ||||||
|
LIT |
| ||||||
|
LOT |
| ||||||
|
MTL |
| ||||||
|
PHT |
| ||||||
|
PTS |
| ||||||
|
WET |
|
This is going to require some customization of the chart commands. Once modified by hand the chart dialog will not be able to re-read the chart definition without losing the customization.
Start using the dialog to get the
following:
-
Drop the STE field into the Labels field. Set the Group Data checkbox
-
Drop the 'measure' ACT_LEV for as many times as you have possible values for ACT_LEV. In the attached data set its 4.
-
Set the chart to Bar-Vertical Stacked
-
Add your legend values
-
Be sure to set the aggregation to count. The graphic is showing Sum. I think there is a bug in the chart dialog, if I create the base chart, Close it and re-open then the aggregation is reset to Sum ... grrrr!
This will give us the following chart:
This is still editable by the chart dialog. It will stack the same ACT_LEV count values on top of each other for every STE. At this point we need to get counts for each of the possible values of ACT_LEV. We do this using an XPATH expression, its like an inline if or where condition e.g.
count(ACT_LEV where ACT_LEV = 'SHORT')
count(ACT_LEV where ACT_LEV = 'LNG')
count(ACT_LEV where ACT_LEV = 'OPEN')
count(ACT_LEV where ACT_LEV = 'OFFLINE')
In our XPATH we use a specific format:
count(ACT_LEV[.= 'SHORT'])
count(ACT_LEV[.= 'LNG'])
count(ACT_LEV[.= 'OPEN'])
count(ACT_LEV[.= 'OFFLINE'])
-
The [ ] enclose the command
-
'.' refers to the current value ie ACT_LEV
-
Then we test against a hard coded value of ACT_LEV
Now we need to get at the chart commands.
In Word 2000/2/3 - double click the chart image and go to the Web tab
In Word 2007 - right click the chart image -> Size -> Alt Text tab.
Rather than work in the window, copy and paste the text into the main document, its much easier. You'll get the following:
chart:
<Graph depthAngle="50" depthRadius="8" pieDepth="30" pieTilt="20" seriesEffect="SE_AUTO_GRADIENT" graphType="BAR_VERT_STACK"><LegendArea visible="true" /><LocalGridData colCount="{count(xdoxslt:group(.//ROW, 'STE'))}" rowCount="4"><RowLabels><Label>Short</Label><Label>Long</Label><Label>Open</Label><Label>Offline</Label></RowLabels><ColLabels><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Label><xsl:value-of select="current-group()/STE" /></Label></xsl:for-each-group></ColLabels><DataValues><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV)" /></Cell></xsl:for-each-group></RowData><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV)" /></Cell></xsl:for-each-group></RowData><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV)" /></Cell></xsl:for-each-group></RowData><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV)" /></Cell></xsl:for-each-group></RowData></DataValues></LocalGridData></Graph>
I have highlighted the pieces we need to change in bold. Right now they are all showing the same value, ACT_LEV. Just make the changes to add the XPATH expressions e.g. count(current-group()/ACT_LEV[.='SHORT'])
chart:
<Graph depthAngle="50" depthRadius="8" pieDepth="30" pieTilt="20" seriesEffect="SE_AUTO_GRADIENT" graphType="BAR_VERT_STACK"><LegendArea visible="true" /><Y1Axis majorTickStepAutomatic="false" majorTickStep="1.0"/><LocalGridData colCount="{count(xdoxslt:group(.//ROW, 'STE'))}" rowCount="4"><RowLabels><Label>Short</Label><Label>Long</Label><Label>Open</Label><Label>Offline</Label></RowLabels><ColLabels><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Label><xsl:value-of select="current-group()/STE" /></Label></xsl:for-each-group></ColLabels><DataValues><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV[.='SHORT'])" /></Cell></xsl:for-each-group></RowData><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV[.='LNG'])" /></Cell></xsl:for-each-group></RowData><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV[.='OPEN'])" /></Cell></xsl:for-each-group></RowData><RowData><xsl:for-each-group select=".//ROW" group-by="STE" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><Cell><xsl:value-of select="count(current-group()/ACT_LEV[.='OFFLINE'])" /></Cell></xsl:for-each-group></RowData></DataValues></LocalGridData></Graph>
To get the lines on the Y-Axis for each unit we added the following line
<Y1Axis majorTickStepAutomatic="false" majorTickStep="1.0"/>
Its in bold above for the position it needs to be set at.
Once you have made the changes to all four to handle the four different values. Copy and paste the XML back into the Web/Alt Text tab
Now you will have a chart that looks correct:
If the template builder throws and error similar to Error in expression: 'count(current-group()/ACT_LEV[.=Â’SHORTÂ’])'. It means that you have used smart quotes '' rather than plain ol quotes in the XPATH. Just open the Web/Alt Text tab, then delete and replace the quotes. In the dialog it will only use plain quotes.
If you need to make the chart larger or smaller, just resize the chart image. It will distort the chart in the template but should come through crisp in the output.
Don't forget, if you open the chart with the chart dialog it will blow away your customizations. It might be a good idea to store the base chart format without your changes so you do not have to re-build it each time. You could add it to the end of your template and wrap and if statement around it so that its hidden at runtime.
I actually built a self explaining template for all of this, rather than then copy and paste from Word over the to the blog client I have. I just ran the template to HTML, then copied and pasted the whole thing from firefox into the blog article, have you seen Word HTML ... bleeeeuch!. It came across pretty well, just a different font. If you are interested in the template and data you can get them here. Happy Charting!.
I have shiny new laptop and on that laptop is Office 2007 ... oh joy! Its been a bit of struggle but I thought I had found my way around Word. That was until I wanted to tweak a chart in a way the BIP dialog could not handle, hey, it happens.
Where the heck was was the Alt Text for the sample chart image we drop into the template? In 200 and 2003, just double click -> Web tab and there it is. I gave up and read the manual :0)
Right click your image, select Size and then the Alt Text tab and there it is in all its XML glory ready to be deciphered and modified ... phew!
Microsoft recently released an update to Microsoft Office Word 2007 and Microsoft Office Word 2003. See
http://support.microsoft.com/kb/978951:
This is an update to Microsoft Office Word 2007 (including Office 2007 suites) and Microsoft Office Word 2003 (including the Office 2003 suites). This update was issued as a result of a United States court ruling on December 22, 2009. Generally, customers who purchase or license Word 2007 or Word 2003 from Microsoft after January 10, 2010 for use in the United States and its territories will need to use updated software that does not include a particular custom XML tagging implementation.
Oracle BI Publisher (XML Publisher) has no dependency on the functionality that is being removed. Customers will still be able to create and use BI Publisher RTF layouts using the BI Publisher Template Builder Add-in when installed and used with these updated versions of Microsoft Office Word 2007 and Microsoft Office Word 2003.
Saw a patch release today slipping past my ever growing inbox. It had an intriguing title: OPTIONAL PRINTING OF PAGES IN PDF TEMPLATES, WTF?
Digging into the bug text; some customers would like the ability to not have to print the instruction sheets in their PDF documents. In an RTF template its pretty simple but if the document is already in a PDF format why re-invent the wheel. Its a nice enhancement and available all the way back to 11i (5.6.3)
There are two parts to the solution one piece in the data and another in the template itself. For the PDF template you need to add a field to the instruction page:
Name: SHOW-PAGE
Tooltip:<?show-page:PRINT-INSTRUCTION='Y'?>
this is the default command to print the instructions ie 'Y' or 'N' if you don't want them.
Now, in your data for the form you need to include a specific XML element:
<data>
<form1>Your Text Data</form1>
<form2>1234567890</form2>
<PRINT-INSTRUCTION>N</PRINT-INSTRUCTION>
</data>
Now as BIP merges the data in it will check the XML tag see whether it should be printing the instruction page or not.
The patch is not quite out of the gate but if you are interested look out for 8452335. If you need a template to check out the functionality, get it here.
Happy New Year everyone! Hopefully you are well rested and getting back into the swing of BIP. I took 2 weeks with a huge list of to do's ... sadly I managed only 2/10! A friend once told me to put a couple of things on a list that I had already done so I could tick them off immediately and feel good about my progress :0) Im pleased to say the 2 I did get done were real tasks so all was not lost.
Got a mail today from a customer having some issues conditionally showing bookmarks for a PDF output or a table of contents for HTML output. I blogged some of the solution a while back but thought I would share a template today to avoid any confusion.
Here's the template
- Param - <?param@begin:_xf;"html"?> - this declares the output format parameter that the server will pass to the template at runtime.
- if PDF - <?if:$_xf='pdf'?><?convert-to-bookmark:?> - if the output is going to be PDF then create the book marks
- Then we have the first instance of the TOC in the template
- EI - <?end convert-to-bookmark:?><?end if?> - closing out the if and the bookmarks
- if HTML - <?if:$_xf ='html'?><?copy-to-bookmark:?> - if the output is going to be HTML then leave things as a TOC
- Then we have the second instance of the TOC in the template
- EI - <?end copy-to-bookmark:?><?end if?> - closing the if and copy
Not too tough and once the template is deployed to the server you will get the desired effect in the output of choice. Template available here.