{"id":3855,"date":"2024-09-25T12:22:41","date_gmt":"2024-09-25T06:52:41","guid":{"rendered":"https:\/\/blogs.infosys.com\/infosys-cobalt\/?p=3855"},"modified":"2024-09-25T12:24:08","modified_gmt":"2024-09-25T06:54:08","slug":"how-to-build-ui-and-reports-in-oracle-visual-builder-excel-add-in","status":"publish","type":"post","link":"https:\/\/blogs.infosys.com\/infosys-cobalt\/cloud-applications\/how-to-build-ui-and-reports-in-oracle-visual-builder-excel-add-in.html","title":{"rendered":"How to build UI and Reports in Oracle Visual Builder Excel Add-in"},"content":{"rendered":"<p><strong>Oracle Visual Builder Excel Add-in Use Cases<\/strong><\/p>\n<p>Oracle Cloud Visual Builder Add-in for Excel\u00a0helps in integrating Excel spreadsheets with Oracle Cloud\u00a0REST services\u00a0to retrieve, analyze, and edit business data from the\u00a0service. You can download your data to an Excel spreadsheet, work with it, then upload your changes back to the service.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-medium wp-image-3837\" src=\"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-content\/uploads\/2024\/07\/VB_Arch-300x233.png\" alt=\"\" width=\"300\" height=\"233\" \/><\/p>\n<p><strong>Key Concepts, Components, and Terms:<\/strong><\/p>\n<p>Term &#8211; Description<br \/>\nIntegrated workbook &#8211; An Excel workbook configured to work with one or more business objects.<\/p>\n<p>Service &#8211; A web service that provides access to application data. The add-in works with REST services\/Web services.<\/p>\n<p>OpenAPI &#8211; OpenAPI is a specification that defines a standard interface to REST APIs. It lets us explore the capabilities of of a web service without accessing the underlying source code.<\/p>\n<p>Business object &#8211; A resource that has fields to hold your application&#8217;s data. A business object includes a collection path, an item path, a set of fields, and other properties.<\/p>\n<p>Business object catalog &#8211; A set of one or more business objects with a common host and base path.<\/p>\n<p>Path &#8211; A path identifies the specific resource in the host that a web client requests access to, for example:\/fscmRestApi\/resources\/11.13.18.05\/draftPurchaseOrders.<\/p>\n<p>Collection path &#8211; A service path (endpoint) that can be used to fetch multiple rows of data from the business object and\/or to perform operations on the collection.<\/p>\n<p>Item path &#8211; A service path (endpoint) that can be used to fetch, or operate on, a single row from the business object.<\/p>\n<p>Metadata path &#8211; A service path (endpoint) that can be used to fetch the service metadata for the business object.<\/p>\n<p>Layout &#8211; A way to display a business object in an Excel worksheet. VB Excel worksheet supports Table or Form-over-Table. Layouts are created by workbook developers and are visible to business users in published workbooks.<\/p>\n<p><strong>Supported REST Frameworks:<\/strong><\/p>\n<p>REST Framework\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0&#8211;\u00a0 Short Form<br \/>\nNetSuite SuiteTalk REST web services\u00a0 &#8211; NetSuite<br \/>\nOracle ADF REST Resource\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0&#8211; ADF REST<br \/>\nOracle REST Data Services\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0&#8211; ORDS<br \/>\nVisual Builder Business Objects\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0&#8211; VBBO<\/p>\n<p><strong>Installation:<\/strong><\/p>\n<p>We can download plug-in installation file from below mentioned URL.<\/p>\n<p>Download: https:\/\/www.oracle.com\/downloads\/cloud\/visual-builder-addin-downloads.html<\/p>\n<p>There are two types of installers (1) Current User (2) All Users. Current user type installer is preferred if the plugin will be used by only the user. Plugin is required for both end users and layout developers.<\/p>\n<p>More instructions about installing the plugin can be found in the<\/p>\n<p>Documentation: https:\/\/docs.oracle.com\/en\/cloud\/paas\/visual-builder-addin\/4.0\/excel-developer\/index.html<\/p>\n<p><strong>Layout Builder in Detail:<\/strong><\/p>\n<p>Once the plugin is installed Oracle Visual Builder tab should be visible in excel sheet.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3852 size-full\" src=\"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-content\/uploads\/2024\/07\/VB_Plugin_Menu.png\" alt=\"\" width=\"780\" height=\"385\" \/><\/p>\n<p>Menu Items:<\/p>\n<p>Publish \u2013 Option to publish excel sheet for end user usage.<\/p>\n<p>Download Data \u2013 To download data from the server<\/p>\n<p>Table Row Changes \u2013 Select the rows you want to upload to server.<\/p>\n<p>Upload Changes \u2013 Create \/ Update records in the server for the specific Business Object \/ REST API.<\/p>\n<p>Incase if the tab is not enabled , then ensure that the plug-in is enabled and listed in Active Application Add-ins<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Development Steps:<\/strong><\/p>\n<p>There are two basic components that helps in building excel layouts.<\/p>\n<p>(i)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Manage Catalogs<\/p>\n<p>Under Manage Catalogs we can add Business Objects (BO), Business Object Catalogs and REST APIs. These catalogs behave like a mediator between end users and servers.<\/p>\n<p>We can add the service in two ways, either we can add an empty BO catalog and define the UI path, parameters, request and response manually.<\/p>\n<p>The other option is to import the BO Catalog from Service Descriptions.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3839 size-full\" src=\"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-content\/uploads\/2024\/07\/VB_Excel_BO_Catalogue.png\" alt=\"\" width=\"512\" height=\"123\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>Oracle Fusion REST APIs for each module can be found in below URLs.<\/p>\n<p>https:\/\/docs.oracle.com\/en\/cloud\/saas\/sales\/faaps\/index.html &#8211; CX<\/p>\n<p>https:\/\/docs.oracle.com\/en\/cloud\/saas\/supply-chain-and-manufacturing\/24a\/fasrp\/index.html &#8211; SCM<\/p>\n<p>https:\/\/docs.oracle.com\/en\/cloud\/saas\/human-resources\/24a\/farws\/index.html &#8211; HCM<\/p>\n<p>https:\/\/docs.oracle.com\/en\/cloud\/saas\/financials\/24a\/farfa\/index.html &#8211; FIN<\/p>\n<p>https:\/\/docs.oracle.com\/en\/cloud\/saas\/applications-common\/24a\/farca\/index.html &#8211; Common<\/p>\n<p>&nbsp;<\/p>\n<p>In order to list all the business objects of a catalog, append the server\u2019s name to base URI path of the application and append describe at the end.<\/p>\n<p>e.g.\u00a0 https:\/\/&lt;servername&gt;\/crmRestApi\/resources\/11.13.18.05\/describe<\/p>\n<p>We can choose Basic Authentication or let it be as Default<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3838 size-full\" src=\"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-content\/uploads\/2024\/07\/VB_Excel_BO_Catalog_Editor.png\" alt=\"\" width=\"793\" height=\"267\" \/><\/p>\n<p>On clicking Next list of Business Objects defined under crm RestApi Business Catalog will be displayed.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3845 \" src=\"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-content\/uploads\/2024\/07\/VB_Excel_Designer.png\" alt=\"\" width=\"341\" height=\"202\" \/><\/p>\n<p>Once the Business Object is selected then it will be added to the list of Business Objects Catalogs. We can click the edit option to drill down and look for more details about the business object added to the catalog.<\/p>\n<p>Oracle by default adds all the related Business Objects along with Subscriptions Business Object selected in the list.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3841 size-full\" src=\"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-content\/uploads\/2024\/07\/VB_Excel_BO_List.png\" alt=\"\" width=\"780\" height=\"463\" \/><\/p>\n<p>We can drill down further in each Business Object and view the Fields, Child Business Objects, Attributes , Constraints and also if any LOV is assigned to the Business Object fields.<\/p>\n<p>(ii)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Designer<\/p>\n<p>Manage catalog option helps in integrating with data layer of the application through Business Objects \/ REST APIs, similarly Designer helps in building a UI Layout in Excel for the underlying Business Object \/ REST API, defined in the Business Object Catalog.<\/p>\n<p>On clicking Designer menu item, list of Business Objects defined in the excel sheet is displayed. We can choose the Business Object for which the New layout has to be created.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3840 size-full\" src=\"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-content\/uploads\/2024\/07\/VB_Excel_BO_Fields.png\" alt=\"\" width=\"780\" height=\"509\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>We can create a table layout or Form -over-Table Layout (more ideal for Parent-Child relationships).<\/p>\n<p><strong>Table Layout:<\/strong><\/p>\n<p>Form-Over-Table Layout:<\/p>\n<p>This layout is ideal when we have to extract data or do CRUD operations on Parent-Child relationship tables.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3848 size-full\" src=\"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-content\/uploads\/2024\/07\/VB_Excel_Form_Table_Layout.png\" alt=\"\" width=\"780\" height=\"380\" \/><\/p>\n<p>Define Query \/ Parameters for filtering the data from Business Objects<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3844 size-full\" src=\"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-content\/uploads\/2024\/07\/VB_Excel_Desginer_Query.png\" alt=\"\" width=\"355\" height=\"483\" \/><\/p>\n<p>In the Advanced tab, we can control the behavior of the layouts and also call custom Macros if defined in the excel sheet.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3843 size-full\" src=\"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-content\/uploads\/2024\/07\/VB_Excel_Desginer_Advanced.png\" alt=\"\" width=\"587\" height=\"728\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>Once the layout is designed, we can Publish the excel sheet and share with end users for extracting data or do CRUD operations. When the layout is published, designer and manage catalog options would be disabled, and user will not be able to make any changes in the Layout design.<\/p>\n<p><strong>Use Cases:<\/strong><\/p>\n<p>(i)\u00a0 Create Header \u2013 Table Layout for CRUD Operations<\/p>\n<p>In this use case we will build a Header \u2013 Table Layout for maintaining Subscriptions and its Products data. Subscriptions details is maintained at header, whereas Product details are maintained at line level.<\/p>\n<p>Oracle Fusion UI for maintaining Subscriptions and its Products data in Oracle Fusion Cloud CX applications.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone  wp-image-3982\" src=\"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-content\/uploads\/2024\/09\/Subscription_UI.jpg\" alt=\"\" width=\"1204\" height=\"535\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>Oracle Visual Builder Excel layout in VBCS plugin for the handling the same data.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3889\" src=\"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-content\/uploads\/2024\/08\/VB_Excel_Sub_Single-1.png\" alt=\"\" width=\"1162\" height=\"673\" \/><\/p>\n<p>(ii)\u00a0 Create Table Layout for Reporting \/ handling multiple records<\/p>\n<p>Search Option to filter data<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3846 size-full\" src=\"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-content\/uploads\/2024\/07\/VB_Excel_Designer_Params.png\" alt=\"\" width=\"603\" height=\"313\" \/><\/p>\n<p>Multiple Header &amp; Line Records:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3850 size-full\" src=\"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-content\/uploads\/2024\/07\/VB_Excel_Sub_Multiple_1.png\" alt=\"\" width=\"780\" height=\"467\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>Conclusion:<\/p>\n<p>Oracle Visual Builder Excel Add-in is a handy tool for building Adhoc UI for maintaining data in the system and also build Reports. Capabilities of this add-in can be enhanced by adding Macros to handle data on custom basis.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Oracle Visual Builder Excel Add-in Use Cases Oracle Cloud Visual Builder Add-in for Excel\u00a0helps [&hellip;]<\/p>\n","protected":false},"author":645,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[7,69,72,113],"tags":[195,277,270],"coauthors":[375],"class_list":["post-3855","post","type-post","status-publish","format-standard","hentry","category-cloud-applications","category-oracle","category-oracle-cloud-infrastructure","category-oracle-erp-cloud","tag-oracle-oic","tag-oracle-cloud","tag-oracle-erp-cloud"],"acf":[],"_links":{"self":[{"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/posts\/3855","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/users\/645"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/comments?post=3855"}],"version-history":[{"count":7,"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/posts\/3855\/revisions"}],"predecessor-version":[{"id":3983,"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/posts\/3855\/revisions\/3983"}],"wp:attachment":[{"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/media?parent=3855"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/categories?post=3855"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/tags?post=3855"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/blogs.infosys.com\/infosys-cobalt\/wp-json\/wp\/v2\/coauthors?post=3855"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}