BI Publisher: JD Edwards Style

1
BI Publisher JD Edwards Style

• Robert Robinson
• JD Edwards Systems Manager
• Health Care REIT, Inc. Toledo, OH

2
This Presentation is Rated R

• The presentation includes strong technical
  language, fast paced system action, some
  presenter violence and power user content.
  Attendee discretion is advised.

3
Outline

• Company Profile
• My Profile
• Overview of BI Publisher
• BI Publisher Setup, briefly
• Step-by-step example
• How to tips
• Key documents
• Summary

4
About The REIT

• Health Care REIT, Inc. is a self-administered,
  equity real estate investment trust that invests
  across the full spectrum of senior housing and
  health care real estate including independent
  living/continuing care retirement communities,
  assisted living facilities, skilled nursing
  facilities, hospitals, long-term acute care
  hospitals and medical office buildings.
• Founded in 1970, the company was the first real
  estate investment trust to invest exclusively in
  health care properties. The Company also offers a
  full array of property management and development
  services. As of September 30, 2008, the Companys
  portfolio consisted of 641 properties in 39
  states.

5
My profile

• 22 years on JDE (World and E1)
• Experience as end user, trainer, EDP auditor,
  consultant, CNC and crash test dummy
• Started at the REIT in July 2006
• President, Quest BI SIG Vice President
  Alliances, Quest R/E SIG Vice President JD
  Edwards, Merged Michigan Oracle Users Group

6
What is BI Publisher?

• A template-based desktop publishing application
• BI Publisher was created by Oracle and was first
  used in its E-Business Suite it was then bundled
  with the PeopleSoft Suite a few years ago
• In 2006, the first BIP application (Phase 1) was
  bundled with JD Edwards EnterpriseOne the 8.97
  Tools Release introduced the Phase 2 upgrade of
  BI Publisher (Phase 2 linked BIP to the JD
  Edwards Change Management process, which allows
  CNCs to manage BIP objects through OMW)

7
What is BI Publisher?

• BI Publisher improves reporting by putting a
  familiar face (Word, Adobe) on the report design
  process, allowing users to leverage existing
  reports without major rewrites of the associated
  UBE.
• End users can create and change reports with
  minimal CNC support, and without having to have
  their BIP reports updated through the JDE Change
  Management process
• The collection of the data can still be handled
  by existing report tools, but you can design and
  control how the report outputs will be presented
  (in separate template files). At runtime, your
  designed template files are merged with the
  report data to create a variety of output formats
  (PDF, Excel, HTML, PowerPoint, even flat files).

8
What is BI Publisher?

• Users can create
• Customer-ready PDF documents, such as financial
  statements, marketing materials, contracts,
  invoices, and purchase orders utilizing colors,
  images, font styles, headers and footers, and
  many other formatting and design options
• Excel output to create a spreadsheet of your
  report data
• PowerPoint slides for presentations
• "Filled-out" third-party provided PDF documents
  (such as 1099s). Download a PDF document, such as
  a government form, to use as a template. At
  runtime, the data and template produce a
  "filled-out" form

9
BI Publisher Setup

• Requirements
• JD Edwards EnterpriseOne release 8.11 SP1 or
  higher
• Tools Release 8.97 (Required for Phase 2 of BI
  Publisher)
• Baseline ESU (Check Metalink3 for most current
  version)
• Planner ESU (Check Metalink3 for most current
  version)
• Perform necessary Application and/or Tools
  Release
• Upgrades and ESU installs as per instructions

10
Outline of JD Edwards BI Publisher Process

• Familiarize yourself with the JD Edwards UBE that
  you want to use
• On the desktop, open a blank Word document and
  save it as an RTF document
• Upload the blank template to EnterpriseOne
• Create a report definition associating the batch
  application and the template
• Submit the report definition either on the web or
  on a fat client
• In the Report Definition Job Control Search, find
  the job for the report definition you just
  submitted and take the Row exit for view RD
  Output
• In the Report Definition Job Control Search find
  the job for the report definition you just
  submitted and take the Row exit for view RD
  Source. This will contain the XML source code.
  Download this source code to the directory where
  you have stored the RTF template
• Return to your desktop, and open the template in
  Word. Begin designing your template

11
Outline of JD Edwards BI Publisher Process

• Load XML Data and continue to markup your
  template (While you are designing your template,
  you can preview the look of the form by selecting
  the Preview function)
• Once you have completed and saved your template,
  save the template back to the JD Edwards XML
  Object Repository
• Re-submit the report definition
• Return to the Report Definition Job Control
  Search, find the report you just submitted and
  take the Row exit for View RD Output
• If the report requires revisions, return to the
  template and repeat steps 8-12

12
1. Identify UBE to use

• My UBE of choice

13
1. Identify UBE to use

• The small sample will allow me to get familiar
  with the UBE

14
2. Create rtf document
15
3. Upload blank template to JDE

• To upload the template, access the XML Publisher
  Repository
• and click Add.

16
3. Upload blank template to JDE

• Complete the screen as shown above, (Object Type
  TR, Product Codes 55,
• Region US, Language EN change the last
  two attributes to match your region
• and language, if necessary). Click OK

17
3. Upload blank template to JDE

• The File Upload prompt then appears. Click
  Browse

18
3. Upload blank template to JDE

• Identify and select the blank RTF template. Click
  Open

19
3. Upload blank template to JDE

• After the file name appears, click Load

20
3. Upload blank template to JDE

• Enter the current date directly, or click on the
  calendar icon.

21
3. Upload blank template to JDE

• Click on the check mark. This sets the current
  date as the
• effective date

22
3. Upload blank template to JDE

• The effective date is now entered. Click OK

23
3. Upload blank template to JDE

• At that point the new template was loaded into JD
  Edwards. The
• next step is to create the Report Definition

24
4. Create report definition

• Run the P95620 application on a FAT client.

25
4. Create report definition

• Click Add.

26
4. Create report definition

• Enter the name of the report, description, and
  55 in the Product Code fields.
• Enter the name of the UBE, click on the
  flashlight in the Template Name field.

27
4. Create report definition

• Identify and highlight the Report Definition
  template created earlier, highlight and click
  Select

28
4. Create report definition

• Click Next

29
4. Create report definition

• Select at least one Available output type, then
  select one Default output type. Click the check
  box for ALL Active Languages. Click Next

30
4. Create report definition

• Leave this screen unchanged. Click End

31
4. Create report definition

• The new report definition has now been added.
  Click Close

32
5. Submit report definition to batch

• On the XML Publisher Report Definitions screen
  (P95620), select the desired Report Definition.
  Click Submit

33
5. Submit report definition to batch

• Select a UBE version (if one was not designated
  earlier)

34
5. Submit report definition to batch

• Select sample data

35
5. Submit report definition to batch

• Complete the data selection and Processing
  Options (if necessary)

36
5. Submit report definition to batch

• Click OK, and then click OK again at the Printer
  Selection screen.

37
5. Submit report definition to batch

• Select Submitted RD Jobs from the Form exit.

38
6. Review RD output (blank)

• Status is X (new for BIP)

39
6. Review RD output (blank)

• BI Publisher (Phase 2) Job Status Codes X In
  process

40
6. Review RD output (blank)

• Click Find. Status changes to D (Done)

41
6. Review RD output (blank)

• Select View RD Output from the Row exit

42
6. Review RD output (blank)

• At the Report Definition Output screen, select
  View Output

43
6. Review RD output (blank)
44
6. Review RD output (blank)

• Initial output for XML report

45
7. Locate RD source/save to desktop

• Select View RD Source from the Row exit

46
7. Locate RD source/save to desktop

• Save the XML code to directory where the RTF
  template is stored

47
7. Locate RD source/save to desktop

• XML Source Code

48
STOP!

• Before you start to work on that template, take
  XML101

49
XML Basics What is XML?

• XML eXtensible Markup Language (XML)
• XML is at the heart of the technology
• The 8.96/8.97 upgrades allow JD Edwards to
  translate data from its native code into XML
• Data passes from the ERP system to the desktop
  over the web smoothly because the XML now used by
  JD Edwards is the same language that is (and has
  been) the basis of the Microsoft Suite for years
• This means that data can now be moved into
  distribution-ready report templates on the
  desktop without the additional data massaging
  that has been required in the past.

50
XML Basics What is XML?

• Knowledge of XML is not a prerequisite for being
  able to
• successfully create reports in BI Publisher
  however, familiarity
• with XML will help with the more intricate
  reports that you create.
• The Oracle Core Components Guide does a good
  job
• of detailing the complete list of need to know
  XML basics if you
• are hungry for more, a simple XML guide (like
  Teach Yourself
• XML in 24 Hours) is sufficient to fill in
  anything that the Core
• Components Guide leaves out.

51
Sample XML Code

• lt?xml version"1.0" encoding"utf-16"
  standalone"no"gt
• - ltR5503B4201gt
• - ltOn_Credit_Manager_S100000gt
• ltCreditManager_ID2gtAPGlt/CreditManager
  _ID2gt
• - ltCompany_Level_Break_S4gt
• ltAddressNumber_ID2gt1016lt/AddressNu
  mber_ID2gt
• ltNameAlpha_ID5gtStanley
  Shorten Real Estate Grouplt/NameAlpha_ID5gt
• ltDocumentType_ID2gtRNlt/Documen
  tType_ID2gt
• ltDocVoucherInvoiceE_ID5gt59023
  lt/DocVoucherInvoiceE_ID5gt
• ltDocumentPayItem_ID11gt001lt/Do
  cumentPayItem_ID11gt
• ltDateInvoiceJ_ID17gt2008-07-31
  lt/DateInvoiceJ_ID17gt (explained later)
• ltAmountGross_ID23gt2133.22lt/Am
  ountGross_ID23gt
• ltAmountOpen_ID26gt2133.22lt/Amo
  untOpen_ID26gt
• ltDateDueJulian_ID20gt2008-08-0
  1lt/DateDueJulian_ID20gt
• lt/Company_Level_Break_S4gt
• lt/On_Credit_Manager_S100000gt
• lt/R5503B4201gt

52
Sample XML Code

• XML files are composed of elements. An element
  is used to describe or contain a piece of
  information.
• Elements consist of two tags an opening tag
  and a closing tag. Opening tags appear as text
  contained between the two angle brackets (ltgt).
  Closing tags also appear within angle brackets,
  but they have a forward slash (/) just before the
  tag name.
• The data between the tags (text and/or numeric)
  is the value of the element.

53
Sample XML Code

• lt DateInvoiceJ_ID17gt
  2008-07-31 lt/DateInvoiceJ_ID17gt
• (Opening tag)
  (Element value) (Closing tag)
•  
• "DateInvoiceJ_ID17" is the tag name, and the
  element value of
• DateInvoiceJ_ID17 is "2008-07-31. The elements
  of the XML file
• have a hierarchical structure. Another way of
  saying this is that
• the elements have parent-child relationships.
  In the XML
• sample, some elements are contained within the
  tags of another
• element. The containing element is the parent and
  the included
• elements are its children.

54
XML Code in UBE output

• RD source code from a UBE is arranged as follows
• Report Properties
• Column Headings
• Page Headers/Footers
• Level Break Sections

55
7. Locate RD source/save to desktop

• Save the XML file to your PC

56
8. Open blank template/begin work

• Opened, with some preliminary work

57
9. Load XML data/ markup template

• Select Data / Load XML Data

58
9. Load XML data/ markup template

• Locate the XML file and select it

59
9. Load XML data/ markup template

• XML data now loaded

60
9. Load XML data/ markup template

• With XML data loaded, select InsertField
  from BI Publisher menu.

61
9. Load XML data/ markup template
62
9. Load XML data/ markup template

• Customer name added to form

63
9. Load XML data/ markup template

• Select PreviewPDF from the BI Publisher
  menu.

64
9. Load XML data/ markup template

• The sample customer name is in place

65
9. Load XML data/ markup template

• Marking up the as of date

66
9. Load XML data/ markup template

• The date field is now populated

67
9. Load XML data/ markup template

• Date format needs to be revised

68
9. Load XML data/ markup template

• Highlight the field right click, select
  Properties (or BI Publisher
• Properties)

69
9. Load XML data/ markup template

• At the BI Publisher Properties box, select Word
  Properties

70
9. Load XML data/ markup template

• Use drop down selector to change Type field
  from Regular Text to
• Date.

71
9. Load XML data/ markup template

• Use the drop down selector to select the
  M/d/yyyy option.

72
9. Load XML data/ markup template

• Word formatting blanks out the visible value of
  the date field

73
9. Load XML data/ markup template

• Date is in proper format

74
9. Load XML data/ markup template

• Markup without placeholder. Select area, choose
  value, click Insert.

75
9. Load XML data/ markup template

• Receivables Manager field has filled in properly

76
9. Load XML data/ markup template

• Highlight the area where the table is to be
  placed.

77
9. Load XML data/ markup template

• From the BI Publisher menu select
  InsertTable/Form.

78
9. Load XML data/ markup template

• Select Customer Level Break element (all
  invoices for customer).

79
9. Load XML data/ markup template

• Drag Customer Level Break element from Data
  Source view to
• Template view.

80
9. Load XML data/ markup template

• Tip drop all nodes, as it is faster to delete
  nodes than to add

81
9. Load XML data/ markup template

• To remove unneeded field, select the field, right
  click and select Delete

82
9. Load XML data/ markup template

• After deleting all unwanted fields.

83
9. Load XML data/ markup template

• Set the Insert Position for Amount Open
  element Same Level.
•  

84
9. Load XML data/ markup template

• Define layout the invoices. Display invoices as a
  table.

85
9. Load XML data/ markup template

• To insert the table into the template, click OK

86
9. Load XML data/ markup template

• Once table is inserted, reformat headings using
  Word.

87
9. Load XML data/ markup template

• Format of Amount Open revised

88
9. Load XML data/ markup template
89
9. Load XML data/ markup template
90
9. Load XML data/ markup template

• Click on folder with tools icon to activate
  Legacy Forms. Click on
• ab (Insert text field) icon.

91
9. Load XML data/ markup template

• Highlight space for form field. Right click,
  select Properties or BI
• PublisherProperties.

92
9. Load XML data/ markup template

• In the BI Publisher Properties box, select
  Word Properties.

93
9. Load XML data/ markup template

• Type descriptive text in Default text field.
  Click on Add Help Text button.

94
9. Load XML data/ markup template

• In Form Field Help Text box, click on Type
  your own radio button, and then type in XML
  code. Click OK

95
9. Load XML data/ markup template

• Copy and paste Start Level Break (for-each)
  tag to space where the end level break will go

96
9. Load XML data/ markup template

• Highlight copied section, right click and select
  Properties.

97
9. Load XML data/ markup template

• Edit XML code and related text in either BI
  Publisher or Word Properties section

98
9. Load XML data/ markup template

• Marked up template, ready for preview

99
9. Load XML data/ markup template

• Results appear, but there is no page break

100
9. Load XML data/ markup template

• Use Words Form Field capability to insert page
  break field just inside end of customer level
  break

101
9. Load XML data/ markup template

• Page breaks now in place

102
Final steps

• 10.Once you have completed and saved your
  template, save the template back to the JD
  Edwards XML Object Repository (step 2 with a
  loaded template)
• 11.Re-submit the report definition (using the
  P95620 application on the web)
• 12. Return to the Report Definition Job Control
  Search (P95630) to find the job you just
  submitted and take the Row exit for view RD
  Output. This time, the report is populated with
  data. If you selected other types of output (e.g.
  Excel), when you arrive at the Report Definition
  Output Repository, you will see more than 1
  record one for the standard (pdf) output, and
  extra lines for the other forms of output that
  you selected
• 13. If the report requires revisions, go back to
  the template and repeat steps 8-12

103
Final Steps
104
How To Tips

• Which standard EnterpriseOne reports are designed
  to work with XML Publisher? Currently, there is
  no standard content delivered for XML Publisher
  with EnterpriseOne. All of the standard
  EnterpriseOne reports have the capability to be
  adapted to work in some fashion with XML
  Publisher, but no UBE reports have been
  specifically tailored or designed to work
  seamlessly with XML Publisher design
  requirements.  Delivery of specific content that
  is explicitly designed to work out-of-the-box
  with XML Publisher is being considered for future
  releases.
• While some of the standard EnterpriseOne reports
  work fine with little or no modification, many of
  the large and complex reports require
  considerable programming effort at the template
  level in order to be successfully utilized in XML
  Publisher.  With customizations the output of
  some reports can be modified to fit the design
  requirements laid out by XML Publisher.  However
  this is considered a custom modification and
  therefore there is no guarantee that the report
  will work properly with XML Publisher.
•  
• Editors note In testing different types of UBEs
  with BI Publisher, I have found that Tabular
  reports only returned the first line of data in
  my templates. After some research, confirmation
  by Oracle support, Tabular reports lack level
  breaks, which are crucial for XML data
  translation.

105
How To Tips

• How do I get the plug-in and instructions to
  download XMLP Desktop -which includes a template
  builder? 1. Go to http//www.oracle.com/technolog
  y/software/products/publishing/index.html 2.
  Locate the download file for your BI Publisher
  phase       a. For Phase 1 XMLP click on Oracle
  XML Publisher Desktop 5.6.2 for Microsoft
  Windows.       b. For Phase 2 XMLP click on BI
  Publisher Desktop 10.1.3.3.2 for Windows
  (preferred). 3. Install this product by invoking
  Setup.exe. This will install a plug-in in MS
  Word, plus give a demo template, tutorial on how
  to use the template builder.
•  

106
How To Tips

• In my Microsoft Word template I have a field
  based on the JDESystem date that I'm using to
  return the current date, but it is formatted as
  yyyy/mm/dd how can I change this format? The
  System date item is an unsupported XML object and
  cannot be formatted within Microsoft Word,
  instead to return the formatted current date
  insert it from within Word itself. On the Word
  toolbar - Go to Insert - Date and Time
  - Now pick the format you wish the date to
  be returned in
• Once the Template is uploaded to E1 the date and
  time will pick up the current system date and its
  output will be in the format chosen within
  Word.(Note this process works for reformatting
  any date see the example)

107
How To Tips

• Although the start and end dates for templates
  are correct, they show as inactive in the Report
  Definition.  Why? The OCM Mappings for the
  business function B95600A XML Publisher
  Validation were not correct.  Once these mappings
  were changed according to the special
  instructions of ESU JK14530 the templates show as
  active.  The special instructions from this ESU
  for the business function are the following
• You must enter OCM mappings for the following
  objects.  Failure to do so could cause software
  components to not run properly.  Go to menu
  GH9011 and select the OCM application (P986110),
  or type 'OCM' from the fastpath.                
  Machine Selection  Both System and Server
  maps             Environment  All
  environments                             
  Object  Fusion Reporting Validation
  (B95600A)              Object Type
  Bsfn                                          
  Action  Add                                     
             Primary Data Source 'Enterprise
  Server - Logic'            System Role
  PUBLIC                                       
  Allow QBE  NA    

108
Summary

• Key skill sets for BI Publisher
• Knowledge of UBEs (especially how level breaks
  are handled in the UBE)
• Knowledge of XML (not required for basic report,
  but more important as your reports become more
  complex)
• Knowledge of Microsoft Word (the more known about
  how to manipulate Word tables and fields, the
  better you will be in creating and supporting the
  BIP templates)

109
Report issues

• Tabular reports do not run with BIP

110
Helpful Reading

• Oracle XML Publisher Core Components Guide
  Release 10.1.3.3, October 2007
• JD Edwards EnterpriseOne Tools 8.97 XML
  Publisher, October 2007
• Welcome to Oracle BI Publisher Template Builder
  for Word 10.1.3.3.0
• Creating BI Publisher Templates for JD Edwards
  EnterpriseOne, June 2008
• Scotts Tips XML Publisher Intro for E1
  COLLABORATE Session by Scott Beebe, cdgroup,
  2008

111
Helpful Reading

• Metalink3 Doc 661001.1 Validating XMLP Phase 2
  with 8.97 has been successfully applied
  (Solution 201065431, Knowledge Garden, April
  2008)
• Metalink3 Doc 650220.1 E1 XML How to Install
  the Word Plug-in for XML Publisher (Solution
  201022484, Knowledge Garden, May 2008)
• Metalink3 Doc 649178.1 E1 XML XML Publisher
  Phase 1 and 2 ESUs (Solution 201018186,
  Knowledge Garden, August 2008)

112
Helpful Reading

• Oracle XML Publisher Template Builder Demo,
  undated
• Teach Yourself XML in 24 Hours SAMS Publishing
  (knowledge of XML is not a requirement in
  creating BIP reports, but it is a big help as the
  reports become more complex)
• Office 2003, Office 2007 User Guides (Word
  section)

113
Helpful Reading

• Oracle XML Publisher Core Components Guide
  Release 10.1.3.3, October 2007
• JD Edwards EnterpriseOne Tools 8.97 XML
  Publisher, October 2007
• Welcome to Oracle BI Publisher Template Builder
  for Word 10.1.3.3.0
• Creating BI Publisher Templates for JD Edwards
  EnterpriseOne, June 2008
• Scotts Tips XML Publisher Intro for E1
  COLLABORATE Session by Scott Beebe, cdgroup,
  2008

114
Shameless SIG Plug

• If what you have seen has not caused too much
  fright, you are a candidate for the Quest
  Business Intelligence SIG
• See me for details.

115

• Questions?

116

• For a copy of the presentation e-mail
• rrobinson_at_hcreit.com
• Thank you for attending