1 / 77

2 for 1 PeopleSoft Connected Query and XML Publisher/BI Publisher

Session Number: 14023. 2 for 1 PeopleSoft Connected Query and XML Publisher/BI Publisher. Michael Stutz ( 360 ) 757-2251 michael@mscc.us www.MSCC.us. Presenter: Michael Stutz. Independent PeopleSoft Consultant Mostly Technical with some Functional

dora-aguilar
Download Presentation

2 for 1 PeopleSoft Connected Query and XML Publisher/BI Publisher

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Session Number: 14023 2 for 1PeopleSoft Connected Query and XML Publisher/BI Publisher Michael Stutz (360) 757-2251 michael@mscc.us www.MSCC.us

  2. Presenter: Michael Stutz Independent PeopleSoft Consultant Mostly Technical with some Functional Primary Focus on HCM and Campus Solutions 25+ years of IT industry experience 20 years of PeopleSoft consulting experience PeopleSoft v.2.11 – v.9.2 Lost Track of the number of PeopleSoft Projects I’ve been on Here to network . . . . . . and to share. Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  3. Agenda The Basics of Connected Query How to use Connected Query Connected Query – Step-by-step What is XML? The Basics of XML Publisher Using Connected Query with XML Publisher Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  4. The Basics of Connected Query Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  5. PeopleSoft Query and Connected Query • The PeopleSoft Query tool interacts with and is a key component of . . . • XML Publisher • Application Engine • Workflow • Security Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  6. The Basics of Connected Query • Query on Steroids Hierarchy Adds the element of Hierarchy to Query • Ability to use as an XML Source Allows complex data input for XML Publisher with a GUI type creation mechanism • Why the new tool? • Query alone didn’t quite cut it, especially for Hierarchical Reporting • Part of “migration” from Crystal Reports to XML Publisher Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  7. The Basics of Connected Query • Parent Query A parent query in a connected query is a query that has one or many child queries. The top level query in a connected query is the parent query. • Child Query A child query in a connected query is a query that has a single parent query. A child query can also serve as a parent query if it has one or more child queries in the Connected Query definition. A child query can have one or more sibling queries. Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  8. The Basics of Connected Query • Sibling Query A sibling query in a connected query is a child query that shares a parent with another child query • Mapping Query Fields Mapping query fields establishes the filtering relationship between values of selected fields in a child query and the corresponding fields in the parent query. Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  9. The Basics of Connected Query • EXAMPLE – Parent Query Person Name • EXAMPLE – Child Query Person’s Phone Numbers • EXAMPLE – Sibling (also a child) Query Person’s eMail Addresses Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  10. The Basics of Connected Query • Keys and Foreign Key • Pieces of data (fields) that define the relationship between Parent information and Child information • EXAMPLE: Employee ID Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  11. The Basics of Connected Query When to use a Connected Query • As a Data Source for XML Publisher • As a Data Source for other XML • When your adhoc reports need Hierarchy (and you don’t want to bother IT for it) Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  12. How to use Connected Query Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  13. How to use Connected Query • Connected Query Quick Start • Connected Query Manager • Connected Query Viewer • Schedule Connected Query Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  14. How to use Connected Query Connected Query Quick Start Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  15. How to use Connected Query Connected Query Quick Start Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  16. How to use Connected Query Connected Query Quick Start Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  17. How to use Connected Query Connected Query Quick Start Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  18. How to use Connected Query Connected Query Quick Start Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  19. How to use Connected Query Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  20. How to use Connected Query Connected Query Manager Insert a new query as a child of the selected query. Insert a new query as a sibling of the selected query. Delete a selected query. Open a related field selection section. Open a selected query in Query Manager in read-only mode. Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  21. Connected Query – Step-by-step Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  22. Connected Query – Step-by-step Select Reporting Tools, Connected Query, Connected Query Manager Select the Add a New Value tab. Enter a name in the Connected Query field. Define query ownership using the Public check box. Select the status of the connected query using the Status drop-down list box. Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  23. Connected Query – Step-by-step Optionally, enter description and comments for the connected query using the Description and Comments fields. Select a parent query using the Parent Query field. Optionally, click the View Query button to view the selected query. Optionally, continue building your connected query by adding child queries, linking fields between parent and child queries, and so on. Save your connected query. Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  24. What is XML? Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  25. What is XML? HTML: XML’s First Cousin • Hypertext Markup Language • Hypertext is text displayed on a computer or other electronic device with references (hyperlinks) to other text that the reader can immediately access • Uses “<“, “>”, “/”, and a few other characters to designate code <html> <head>The Hello World Page</head> <body>Hello World</body> </html> Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  26. What is XML? What is in a Name? • Extensible: • Capable of Extension. Expandable. Able to Grow. • Markup • Add comments using writing or marking. Annotate. • Language • System of complex communication. • Put it all together and what do you have? • An expandable system for describing and annotating information in a way that both contains the information but is also distinguishable from the information itself. Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  27. What is XML? Tearing XML apart <?xml version="1.0" ?> <query numrows="10" queryname="Z_SAMPLE_EMPLID_XML" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation=""> <row rownumber="1"> <EMPLID> 12345678901 </EMPLID> <NAME>Michael Stutz </NAME> <ADDRESS1> 12582 Eagle Drive </ADDRESS1> <CITY> Burlington </CITY> <STATE>WA </STATE> <POSTAL> 98233 </POSTAL> </row> <row rownumber="2"> <EMPLID>12345678902 </EMPLID> <NAME> Jimmy Joe "Jimbob" Jordan </NAME> <ADDRESS1>500 Bubba Lane </ADDRESS1> <CITY>Croc Creek </CITY> <STATE>Alabama </STATE> <POSTAL> 24123 </POSTAL> </row> . . . . . . . more data . . . . . . . </query> Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  28. What is XML? Tearing XML apart <?xml version="1.0" ?> <query numrows="10" queryname="Z_SAMPLE_EMPLID_XML" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation=""> . . . . . . . data . . . . . . . </query> Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  29. What is XML? Tearing XML apart <row rownumber="1"> <EMPLID> 12345678901 </EMPLID> <NAME>Michael Stutz </NAME> <ADDRESS1>12582 Eagle Drive </ADDRESS1> <CITY> Burlington </CITY> <STATE>WA </STATE> <POSTAL> 98233 </POSTAL> </row> <row rownumber="2"> <EMPLID>12345678902 </EMPLID> <NAME> Jimmy Joe "Jimbob" Jordan </NAME> <ADDRESS1>500 Bubba Lane </ADDRESS1> <CITY>Croc Creek </CITY> <STATE>Alabama </STATE> <POSTAL> 24123 </POSTAL> </row> Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  30. What is XML? Tearing XML apart <?xml version="1.0" ?> <query numrows="10" queryname="Z_SAMPLE_EMPLID_XML" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation=""> <row rownumber="1"> <EMPLID> 12345678901 </EMPLID> <NAME>Michael Stutz </NAME> <ADDRESS1> 12582 Eagle Drive </ADDRESS1> <CITY> Burlington </CITY> <STATE>WA </STATE> <POSTAL> 98233 </POSTAL> </row> <row rownumber="2"> <EMPLID>12345678902 </EMPLID> <NAME> Jimmy Joe "Jimbob" Jordan </NAME> <ADDRESS1>500 Bubba Lane </ADDRESS1> <CITY>Croc Creek </CITY> <STATE>Alabama </STATE> <POSTAL> 24123 </POSTAL> </row> . . . . . . . more data . . . . . . . </query> Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  31. What is XML? Here is where we find XML . . . • PeopleSoft Integration Broker • PeopleSoft Application Designer • PeopleSoft Query • PeopleSoft Connected Query • XML Publisher • WSDL** • WSIL** • SOAP** • AJAX** • Pagelets ** More of a general IT application for XML and although it may be used in and/or by PeopleSoft applications, the technology is not specific to PeopleSoft Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  32. What is XML? AJAX Asynchronus Javascript and XML Pagelet Definition and Rendering Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  33. The Basics of XML Publisher Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  34. The Basics of XML Publisher XML Publisher • Deals with the intricacies of XML • Allows you to set up and work with templates • Associates your XML with data sources • Integrates with • PeopleSoft Query • PeopleCode • Application Engine Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  35. The Basics of XML Publisher PeopleSoft XML Publisher Steps required to put together an XML Publisher report • Install XML Publisher Plug-in • Create / Register Data Source • Create / Upload Report Template • Define XML Publisher Report • Run XML Publisher Report Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  36. The Basics of XML Publisher PeopleSoft XML Publisher Flow Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  37. The Basics of XML Publisher Setup XML Publisher (ONE TIME SHOT PER COMPUTER) • PeopleSoft XML Publisher has multiple components. One of the key components is a “Plug-in” to Microsoft Word • PeopleSoft environment must be PeopleTools v8.48 or greater • Your desktop computer must have already installed: • Java Runtime Environment (JRE) v1.4 or later • Microsoft .net v2.0 or later • Microsoft Word version 2000 or later Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  38. The Basics of XML Publisher PeopleSoft XML Publisher Flow Download and Install Microsoft Word Plug-in From PeopleSoft Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  39. The Basics of XML Publisher Install XML Publisher Plug-in • Download the “plug-in” from the link provided inside of the PeopleSoft application • Unzip the downloaded file Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  40. The Basics of XML Publisher Install XML Publisher Plug-in • Run the unzipped file • Follow the instructions on the installation screens Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  41. The Basics of XML Publisher Install XML Publisher Plug-in • Complete installation • Open Microsoft Word to see the results Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  42. The Basics of XML Publisher Create / Register Data Source • Data Sources • Query (Easiest) • Connected Query • XML File • XMLDoc (Pre-PeopleTools 8.5) • Rowset (Pre-PeopleTools 8.5) Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  43. The Basics of XML Publisher PeopleSoft XML Publisher Flow Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  44. The Basics of XML Publisher Create / Register Data Source Remember these links for the next step . . . Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  45. The Basics of XML Publisher Create / Upload Report Template Using Microsoft Word, create and save a document in the .rtf (Rich Text Format) format Use the PeopleSoft delivered Microsoft Word Plug-in functionality to create your template Save the file in a location where you can find and upload it into the PeopleSoft application later in the process Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  46. The Basics of XML Publisher PeopleSoft XML Publisher Flow Sample Data (From Data Source) Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  47. The Basics of XML Publisher Create / Upload Report Template .rtf (Rich Text Format) file you create in Word Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  48. The Basics of XML Publisher Create / Upload Report Template .rtf (Rich Text Format) file you create in Word Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  49. The Basics of XML Publisher Define XML Publisher Report In the PeopleSoft system, you will create an XML Publisher Report Definition. Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

  50. The Basics of XML Publisher PeopleSoft XML Publisher Flow Michael Stutz - (360)757-2251 - michael@mscc.us - Michael Stutz Consulting Company

More Related