BIPgirl.com

Data Template Example : Passing A Parameter

2008-04-14T09:12:00.014-04:00

In my last post, I said that I would give you an example of a data template that is passing a parameter from a concurrent program. I said I would do it “tomorrow” but “tomorrow” turned into month and a half. Time sure flies when you’re having fun converting reports over to BIP! So here is a little example that I threw together …

This report is just going to pull back concurrent program information based on the reporting group that you enter as a parameter (nothing fancy, but you’ll see how the data template works).

Step 1 – Create your concurrent program

In the concurrent program I added 1 parameter called “REQUEST_GROUP_NAME” that is just a 30 char field where you will enter the reporting group name at runtime. For the executable, I used XDODTEXE. This is a java concurrent program and it is very important that you use this executable when writing your own data definition or else it won’t run. It is also important to set the output type to XML.

Step 2 – Add your concurrent program to a request group

For this example I just chose a request group that I was currently working in.
You can, of course, put your report wherever you need to.

Step 3 – Create your Data Template

I used jDeveloper to create my data template, but you can use
whatever text editor you choose. It is important that you include all
of the mandatory pieces of the Data Template or else it will error
out on you. Here is the example data template that I created, which
has my parameter name from the concurrent program and also the
query that is using the parameter. At the bottom there is a data structure
section where you will define your grouping, just like in the .rdf.

Step 4 – In this step you will create your Data Definition in the XML Publisher
Administrator responsibility. The data definition is linked to your concurrent
program, so you have to be sure your “CODE” matches the concurrent program
short name exactly.

In the Data Definition, there is an option to add your data template file at the
bottom of the screen. If you click on this button you can browse to your data
template file and upload it. Your data will now be fetched based on this data
template, not a .rdf.

Step 5 – Run your concurrent request

With the data template attached, you should be able to run your concurrent
request and view XML output. When you see this output, you should save the
file as .xml for use in your layout template design. Here is what my sample
XML data looks like :

Step 6 – Create your layout template in Word

In Word, load the sample XML data that you just saved and create your layout
template. For this example I created a very minimal template to display the data.

Step 7 – Create the Template Definition

Once you have previewed your layout template in Word and are happy with it,
you can go ahead and create a template definition and upload the template .rtf.

Step 8 – Run your concurrent request (again)

This time you will see that the layout template is attached to the concurrent
request, due to the template definition creation. When you view the output,
you shouldn’t see the XML data anymore, but the data and template merged
in report format

There you have it… a report written using a data template instead of a .rdf. Of course our reports tend to be a little more complex than this one, but a simple nonsense report is a great way to start and get some results out of the Apps.

This time I’m not making and promises about posing “tomorrow” but I haven’t disappeared (thanks Greg for checking on me)!

- BIPgirl

What is the data template?

2008-02-25T14:39:00.007-05:00

Some of you may be using an Oracle .rdf to generate your BIP reports and then attaching a .rtf layout template to it. This may be fine for your business needs at this point, but what if you could improve your report run time by 30-40%? That sounds nice, doesn't it? Well, if you were to use 'pure' BIP (which means writing your reports in XML) then it could happen. Of course every report is different, but you are putting less work on the BIP execution engine this way. This means that you have to take some time to explore XML if you don't already know it, but in the end it may speed up your reports.

The data template is how you communicate your request for data to the data engine. You write this data template in XML and the elements that you create define how the data engine will process the template and generate your XML output. A shell to create a data template looks like this :

The data template has a parameters section, a dataQuery section and a dataStructure section. Within each section there are elements. Below is a list of the elements

dataTemplate - required : declares that the xml you are writing is a dataTemplate to be used as your report.
parameters - consists of 1 or more child elements
parameter - the name of the parameter referenced in your dataQuery
lexicals - consists of 1 or more lexical elements to support flexfields
lexical - the 4 types are KFF segments, KFF, KFF where and KFF order by
dataQuery - where you write your sqlStatement elements
sqlStatement - a SQL query
link - where you link your sqlStatements together as a parent-child relationship
dataTrigger - the trigger and the event associated with it
dataStructure - required when writing and linking multiple queries
group - consists of 1 or more element elements
element - the tag name assigned to the element in the XML output

Those are the pieces of the data template that you will need to know.
In tomorrow's post, I will give you an example of a data template that is passing a parameter from a concurrent program.

1099-MISC Continued...

2008-02-02T21:24:00.000-05:00

Hello out there in BIP world! I have been getting comments about how I created my 1099-MISC .rtf files, so I thought I should write a follow-up to my last post.

I started off trying to download the .pdf files from the IRS website, but if you haven't noticed already, you have to write Adobe code in the .pdf docs to get the looping you want. So, that didn't work out too well for me seeing that I don't know how to code in Adobe.

The most effective way that I found to get the forms how we needed them, was to create a .rtf template to use. I was only required to create a format that we could print and mail out, which eliminated having to worry about business-2-business field compatibility and using the .pdf to send back to the IRS electronically. Basically, I just used the BIP desktop tool to re-create the IRS forms. I created 2 tables per page (one for Copy B and one for Copy 2) and then entered my for-each loops and tags accordingly.

You can download 2 sample files here :
Copy B/2
Copy C

These templates need some clean-up work with removing the table lines where they aren't needed (use the IRS downloaded forms as your guide). But you will get the overall idea on what you should use for this solution. If you have any additional questions, just post a comment and I'll get back to you.

My 1099-MISC Solution

2008-01-11T15:50:00.000-05:00

Is anyone else having trouble using BIP to get their 1099-MISC forms how they want them? Well, at my company we previously used pre-printed triplicate forms where Copy B and Copy 2 were sent to the recipient and Copy C was kept by us (the payer). The forms caused issues with alignment on the pin feed printer and were also time consuming because they had to be torn apart. Pin feed printer? Are those still around? Anyway....

Enter XML Publisher and the laser printer solution! Oracle provides a 1099-MISC template. The seeded template (called 1099 Forms if you want to do a template search) is a .PDF template and only contains Copy B that will print 1 recipient per page. The document doesn't have IRS wording on it and would require your company to include an additional piece of paper in the envelope. This format didn't meet our needs, so BIPgirl was called upon to save the day!

What was our solution? Create new .rdf templates of course! Seeing that we've been sending recipients Copy B and Copy 2, I created 1 .rtf template with those 2 layouts on it (going by what is on the IRS website). When inserting the XML tags from the seeded report APXT7F99, I have the same recipient's information print on 1 page. I then created a second template with Copy C. In this template I have 2 recipients print per page.

But what about the IRS wording? I was hoping that you wanted to know about that. Going back to how time consuming the triplicate forms were, we didn't want to stuff extra pieces of paper into envelopes. To get around this, we contacted our paper vendor and ordered custom stock. This new stock has a perforation in the middle so that Copy B and Copy 2 can be torn apart by the recipient and folded to fit into window envelopes by us. Then we had the IRS wording printed on the back of Copy B and viola! we have our forms to mail out in our custom window envelopes. As for Copy C, we just keep them for our records so they could be printed on white paper to save on the custom stock cost.

So if anyone else is out there struggling or spending too much time dealing with pre-printed forms, try this solution. It really saved us a lot of time and money!

Demystifying BIPgirl

2008-01-10T11:30:00.000-05:00

OK Tim... so you tagged me! Now I have to reveal 8 things that my readers didn't know about me.

For those of you who didn't see Tim's 1/8/08 post (Oracle BI Publisher Blog) , he blog tagged me! So I'm 'It' and I have to reveal my deepest darkest secrets. Haha.. I don't think so!

I am a Boston Red Sox fan and live in upstate NY.
Along with my CIS degree, I also have a degree in Early Childhood Education. I decided right after I received my Early Childhood degree that I would probably prefer hanging out in a cube all day to finger painting. I still feel that way, so I guess I made the right choice!
I am a cleaning fanatic. Monica on Friends... yes, that's me. Yet another reason why finger painting and I don't mix (see #2)
Every night when I get home from work I get on my treadmill and run just like a hamster on a wheel. I think exercise is great for clearing your mind!
I hate talking on the phone, which is weird because I remember falling asleep talking on the phone when I was a teenager. Email me, text me I'll get back to ya.
I am engaged to a .net developer! Check out his sites, they're great! http://www.sivatar.com and http://www.earnmoneytoblog.com
I play Halo 3 online (another good stress reliever), but I refuse to wear the headset and talk to people.
What is the one thing that is guaranteed to put a smile on my face? Buddy the wonder cat

Enough about me, let's hear from some other bloggers. Here are a couple of sites that I've been to recently (sorry, I don't have 8)

1. Oracle Newbies Blog - Lisa Dobson
2. Oracle & Google Technical Blog and More - Greg Partenach
3. MattTopper.com - Matt Topper

The BIP Puzzle

2007-12-30T12:34:00.000-05:00

I've been getting a lot of questions about how many different BIP components there are to create 1 report. This question comes at a good time, seeing that we are ready to discuss working in the Apps. The answer is that you will have 5 BIP components to make up 1 report.
You will have :
1. The Data Source or Data Template (.xml file or .rdf)
2. The Concurrent Program
3. The Data Definition
4. The Template Definition
5. The Template (.rtf or .pdf file)

The Data Source
The data source is an .rdf report or .xml report. For right now we are still going to use the .rdf report to pull all of the data from the database that we need. So, in the concurrent program you will keep it set up to run the executable for your .rdf. You can also write your report in XML language and retrieve data from the database just as the .rdf does, but we will take a look at that later on.

The Concurrent Program
Because we are going to use the .rdf file as our data source, the only change that has to be made to the concurrent program is to change the output format type to XML.

By doing this, the output from your .rdf report will be in XML format. You will no longer see your .rdf layout, but you will see XML tags. This can be a little confusing because it is not the actual XML code that got the data from the database, but the output from your .rdf report. This output is what you should save as .xml and load into the template builder tool in MS Word for use in your template design.

The Data Definition
The data definition is in the Apps under the XML Publisher Administrator responsibility. The data definition is linked to the concurrent program by the concurrent program shortname. You create a new Data Definition by clicking on the 'Create Data Definition' button. You can then enter any name you would like for your report (I like to keep it the same as the concurrent program name). In the Code box, you enter the concurrent program short name. In the Application box, you enter whatever module your report is in (ex. payables). If you wrote your report in XML rather than using the .rdf, this is also where you would attach your .xml file, by clicking on the Data Template - Add File button on the bottom half of the screen.

But again, we'll step through that at a later date. Also, you must create the data definition before you create the template definition.

The Template Definition
The template definition is used to link the data layer (data definition/concurrent program) with the presentation layer (.rtf/.pdf template). You can access the Template Definition screen under the XML Publisher Administrator responsibility, just like you accessed the Data Definition screen. There is also a tab on the top of the data definition screen that you can use if you're already in there.

You can create a new template definition by clicking the create template button. On the template screen you can enter any name you want (again, I would use the concurrent program name or at least the data definition name to keep everything organized). The code is the shortname from the concurrent program and you should find the data definition for report in the LOV. The application would be the same as the application you entered in the data definition. The type would either be .rtf for the template created in MS Word of .pdf if you created one in Adobe.

The Template
The last piece of the puzzle is the template, which we have already discussed. The template is attached through the template definition screen by navigating to the template file upload section ...

You simply browse to your template file and select a language.

And there you have it... the 5 pieces needed to generate your report. Once you have set all of this up, when you run the concurrent program and view the output you should see your template and data merged together.

A Different Header on Page 2?

2007-12-19T11:30:00.000-05:00

So you got your subtemplate working in the header of your reports, but what if you want a different header on page 2? You're in luck because that can be accomplished fairly easily.
For this example I modified the .xml file to include a notes field and a report name field. You can download the file from http://www.mediafire.com/?9xzyfy3bivd

I also created a new header/footer subtemplate and an example3.rtf template. They are also available for download at : http://www.mediafire.com/?8vb1vmteoay and http://www.mediafire.com/?1dxwodzd7mz .

Let's first look at the new header/footer subtemplate:

In the subtemplate header I am now passing 2 parameters (P_DATE and REPORT_NAME). I added REPORT_NAME so that for our examples at least, the name of the report will be dynamic. Then, in the middle column I called the value passed to the subtemple for REPORT_NAME by using <?REPORT_NAME?>. Now onto the new example3.rtf template

The template has a few changes that we can see (as indicated by the arrows). First, the value of REPORT_NAME from the .xml file is being passed to the subtemplate in our header by using ;$REPORT_NAME;$REPORT_NAME. The second change is in the main table indicated by arrow 2. The only change here, was that I added a second row to display our new vendor notes tag from the example3.xml file. The last change shown in the image above is the addition of the template 'BodyHeader' that will be used on pages other than the first page.

Back to the main topic of this post.... MS Word functionality gives us the ability to have a different first page. If you enter the header section of the template (View -> Header and Footer) you will see this icon

This has a properties dialog box where you can set 'different first page'.

When you click 'OK' you will be back out to your header section of the template. Enter what you would like for the header and footer (in our case, it is the call to ex3_subtemplate.rtf). Once you set this up on the first page, go to the end of your template layout and insert a page break. On the new blank page 2, go back to the header and footer section and enter what you would like on page 2. If you downloaded the example3.rtf file, you may not see page 2 because if I left blank lines in the template the output was showing an extra page. But, if you hit enter until you get to page 2, you will see different code in the header section.

This is the call to the template that we have in our example3.rtf. It is not a subtemplate that is used universally, but a subtemplate used within example 3. Because you are not allowed to enter tags in the header and footer sections of the .rtf, a call to a subtemplate will accomplish this. And here is our final product....

Happy BIPping!

What Are Subtemplates?

2007-12-12T20:53:00.000-05:00

Using Subtemplates

I found a fantastic way to make our lives as BIP developers a lot easier! It's called the subtemplate and it can be used over and over again. For example, if your company has a logo that they like to display on every report header, you can create a subtemplate that holds the image. Then, when you create the report template you call the subtemplate and your header logo is loaded into your report template. The same goes for report footers. If your company likes to display their name and address in the footer, you create another subtemplate with that information and call is in the footer of your report template. Everyone likes to find shortcuts, right? Well this shortcut could save you time and also make your reports more consistent because when you re-create the header for every report there is more room for error.

Another great thing about using subtemplates is that if you have to make a change to the company logo or address, you only have to make the change in one place and it shows on every report! Now, if you've already created templates without using subtemplates, I'm not trying to be a trouble maker and say to go back in and change them all.... but you should really convert over to this method for your next report. It will pay off in the long run!

So how do you use these things? You can create subtemplates by using XSL or XSLT (a transformation languages used with XML data for formatting) or you can create an .rtf document like we did in our last 2 examples. I found using an .rtf file is pretty straight forward in MS Word and it's also great for inserting a logo. You can still use XSL to format the data like we did in example1a for setting a default value, so don't worry about that.

Here is a short subtemplate example for ya....

First off, I modified the .xml file from example1a to add a date tag which will be used in our report header. You can download the new .xml file from http://www.mediafire.com/?0ce5ajbljmd
Next is our new subtemplate file that includes our company header and date that is an element name from our report output.

You can also download this from http://www.mediafire.com/?0ezjdthomyn . In this .rtf file we have our header and footer data as indicated by <?template:Header?> and <?template:Footer?>. Within those tags I created tables that are the same width as the report where I would like to add the header/footer. There is also a parameter tag <?param@begin:P_DATE?> . This tag creates a parameter that is used in the Header to display the date from the report data.

Lastly, we have our example2.rtf file where we call the subtemplate http://www.mediafire.com/?533dqzmjtjt

In MS Word, this is what is in the header. You are calling the 'header template' from the subtemplate and passing the value P_DATE from the .xml file to the subtemplate.

To link the report template with the subtemplate you have to import the subtemplate file in the report template body with the below syntax :
<?import:file:/// [path] subtemplate_file.rtf?>

You call the footer subtemplate the same way that you called the header subtemplate. As long as your header and footer are in the same .rtf file, you would only have to import the file one time in your report template body. Seeing that we aren't displaying and report data in the footer there are no parameters like in the header, but you could certainly add that if you so desire

So there you have it, our first look at using subtemplates.. enjoy!
Feel free to leave me comments and let me know if you found this helpful - BIPgirl

Enhancing your Layout

2007-12-10T21:36:00.000-05:00

Hopefully everyone had a chance to look at example1. Well now, I would like to change it. I had a comment on my blog about handling null values, so I thought I would address that in today's post. I have created example1a.xml to demonstrate how to handle a null phone number value and put a default value in it's place. You can then take the logic in the template and apply it wherever you need to.

Here is example1a.xml (you can download it from http://www.mediafire.com/?5qnnju1zg1i )

But first I have to step back a second and explain a little more about our template file. For example1a, I took the .rtf file from example1 and enhanced it. You can download this template from http://www.mediafire.com/?9ocfbtmtzlp

From our previous example I have added a title, vendor phone column and combined the address, city, state and zip fields into one column. I have also removed the table borders using normal MS Word functionality to make the data look more 'report-like' rather than "table-like".

What I didn't discuss in our first example is that each field that was entered through our template-builder table/form menu has a "text form field options" box. If you double-click on VENDOR_PHONE you will get a box that looks like this :

We have a lot of formatting and conditional options using this little option box!
If you click on the 'Add Help Text' button in the lower left hand corner you will
normally just see the XML tag from your data.xml file. This is how the XML data
is merged with the template.

So... I bet you're wondering how you enter a default value if our phone number just happens to be null. Am I right? In the same Text Form Field Options box, we can enter
<?if:VENDOR_PHONE=''?>Private Number<?end if?>
<?if:VENDOR_PHONE!=''?><?VENDOR_PHONE?><?end if?>

Just a note, you'll want to leave out any unnecessary white space when entering into this box because you are only allowed 138 characters.

When we run our example from the Previewer as PDF we will get the output below with our default value populated for BIPboy

Our First Example

2007-12-06T13:32:00.000-05:00

Here is a very simple example of XML code that you can copy into notepad and save as example1.xml

<?xml version="1.0"?>

<EXAMPLE1>
<LIST_G_MAIN_SELECT>
<G_MAIN_SELECT>
<VENDOR_NAME>BIPgirl</VENDOR_NAME>
<VENDOR_ID>00001</VENDOR_ID>
<VENDOR_LINE>1 BIPgirl Lane </VENDOR_LINE>
<VENDOR_CITY>Albany</VENDOR_CITY>
<VENDOR_STATE>NY</VENDOR_STATE>
</G_MAIN_SELECT>
</LIST_G_MAIN_SELECT>
</EXAMPLE1>

Once saved, in MS Word you go to your newly added toolbar and load the file. Select Load XML Data and then browse to example1.xml

You will get a message acknowledging that the data was loaded successfully. Then.. let the development begin!

For my first practice template, I went to the Insert Menu on the Template Builder toolbar and selected the Table/Form option. This is a quick and easy way to get the fields from your data file into your template with the correct syntax. If you select the top node from the data source pane and drag it to the template pane, you can drop all the nodes or selected ones. For this example I will just drop all the nodes and not touch any settings.

Your template will look like this :

Well that's fine and dandy, what will my user see? Another great feature of the XML Publisher Desktop Plug-in is that you can preview your output without loading it into the Apps. You must first save your template as Example1.rtf and then you can navigate back to our handy toolbar. You will see a Preview Menu on it.

This preview menu gives you 4 options for your output (PDF, HTML, EXCEL or RTF). Because my output in the Apps will be PDF, that is what I will choose to preview in.

Here is our first report!

Woo hoo! It may not look pretty, but we merged the data layer with the presentation layer on our desktop and after all, that is our goal here. In my next post I will show you how we can add our own personal touches to the output. Go ahead and give it a try!

Getting Started with BIP

2007-12-05T16:15:00.000-05:00

I’m sure the first thing you are asking yourself is “What is BI Publisher?”. Well…. BI Publisher is a template based publishing tool that is bundled up in the Oracle EBS (there is also a stand-alone version if you do not have the EBS). BI Publisher uses desktop word processing tools for your presentation file and merges it with the EBS data. The basic idea behind this publishing tool, is to separate the presentation and data layers.

EBS holds the collection of data ->You create the presentation template ->
EBS merges the two together at runtime

The first thing that you need to do to get started with BIP is to install the desktop plug-in for MS Word. You can also develop templates in Adobe Acrobat Professional, but I have found that MS Word is the way to go. The MS Word plug-in is called Template Builder and is available on Metalink through Patch 5887917 “Oracle XML Publisher Desktop 5.6.3”

Installing the XML Publisher Desktop Plug-in for Word

After you follow the on screen installation prompts, you will see a new menu in MS Word called “Oracle BI Publisher”. You will also want to add the Template Builder Toolbar by navigating to
View > Toolbars>Template Builder. You will then see it appear with your other toolbars

From this toolbar you will load your sample XML file to add fields to the template and preview your output.

I’ll let you poke around a little.

Welcome

2007-11-27T21:30:00.000-05:00

Hey all you BIPpers... Welcome to my blog!

I am embarking on a new adventure, creating Oracle Reports in BI Publisher. Everyone out there in 'Oracle World' is doing it, so why not me? And why not let everyone know any tips that I stumble upon along the way? Well, that's exactly what my blog is about. A step-by-step guide to how I'm creating my reports and any important tips that helped me out.

I have about 4 years experience in developing custom EBS reports in Report Designer 2.5 and Report Builder 6i. I know... 4 years of experience isn't a lot, but with this new reporting tool everyone is a newbie so I don't feel as bad ;)

Thanks for stopping by and feel free to email me about any of my posts!

BIPgirl