Skip to Main Content

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Taking Your OBIEE to the Next Level with SmartView VBA 11.1.1.7.1 [TECH ARTICLE]

Steve YeungMay 19 2015 — edited Jun 18 2015

Steve Yeung's article walks you through an advanced approach to starting your own Smart View for OBIEE, using Excel Visual Basic for Applications (VBA). The objective is to inspire you with what you can do with Smart View by providing solutions that do not exist in the current OBIEE versions.


by Steve Yeung

Introduction

Since the launch of Oracle Business Intelligence Enterprise Edition (OBIEE) 11.1.1.7, Smart View was introduced as a replacement for BI Office. From a Smart View point of view, the version number is a little different since it's evolving from the Hyperion Enterprise Performance Management (EPM) side. So, Smart View version 11.1.2.3 will work properly with OBIEE 11.1.1.7, but it is recommended that you use the latest version, 11.1.2.5.216 (at the time of writing).

The introduction of Smart View for OBIEE greatly enhanced the flexibility of personal Desktop Business Analytics that everyone can use; it can be both really simple and very advanced, providing end users with answers to questions and giving rapid results. Since Smart View highly leverages OBIEE, it allows end users to build BI ad hoc and Answer reports, and share insights by publishing to OBIEE catalog or even dashboards with all securities in place.

In this article, I will take you through the steps required to start your own Smart View for OBIEE in a more advanced way, using Excel Visual Basic for Applications (VBA). The objective of this article is to inspire you with what you can do with Smart View by providing solutions that do not exist in the current OBIEE versions.

Prerequisites:

This document assumes that you have basic knowledge of Smart View and have the following installed:

  1. OBIEE Version 11.1.1.7+ with Sample Sales Lite
  2. Excel 2007 or Excel 2010 with Developer Mode enabled
  3. Smart View 11.1.2.5+

Skills required for this exercise:

  1. Basic Answer Development Knowledge
  2. Basic Excel and Excel VBA

* Further reading for Smart View Oracle BI EE functions can be found here: https://docs.oracle.com/cd/E40530_01/epm.11123/smart_view_developer/frameset.htm?ch16.html.

Getting Started

Our objective was to meet the user requirement of having "Revenue Report by Product Type & Year" in a single Excel workbook, with individual sheets of the same report by Product Type and Year.

To meet this requirement, we must first create an Answer Analysis that has the following objects from Sales Sample Lite:

  1. Per Name Year
  2. Per Name Month
  3. Product Type
  4. Product
  5. Company
  6. Revenue
  7. Target Revenue

In Results, we will need to edit the default tableView!1 and drag Per Name Year as well as Product Type to Table Prompts. This will be the basic layout of the report needed for Excel Export.

image001.png Figure 1

To be able to generate multiple sheets in one Excel Workbook, we will need to create another table that allows Excel to store Per Name Year and Product Type variables to be used. Create another table view with only Per Name Year and Product Type, with all other objects in the Excluded area.

image002.png

Figure 2

We can now save this report; for demonstration purposes, I have saved it to a new folder in the Shared catalog called Test, with the report named as Untitled.

Creating your Excel VBA

We will create an Excel template so that future reports can leverage this macro workbook. Start a new workbook and rename sheet1 as "Info." In the range A2:A8, we will have the following names:

  • Hostname
  • Port
  • Friendly Name
  • Catalog Folder
  • Report Name
  • Reporting Table
  • Prompt Table
  • Output Folder

In column B, ranges B2:B8, we will fill in these variables. Example as follows:

Your normal analytics link: http://hostname:port/analytics

  1. localhost (hostname)
  2. 7001 (port for OBIEE Analytics)
  3. localhost (named when you first create a private connection or found when you try to rename)
    image003.png
    Figure 3
  4. /shared/test/ (Folder in the catalog storing the Answer)
  5. Untitled (Answer saved name)
  6. tableView!1 (The table for reporting)
  7. tableView!2 (The table storing the prompt combinations)
  8. C:\ (A folder you can access for storing your Workbook output)

You may also create additional instructions or format your worksheet. Here is an example:

image004.jpg
Figure 4

In the example above, there is a Command Button called Run MultiSheet Report. To achieve this, the followings steps are done:

  1. In Developers tab, click Insert > ActiveX Command Button.
  2. Draw the button as required.
  3. Right click the button and set properties.
  4. Modify the name to cmdRun and caption Run MultiSheet Report.

Smart View for OBIEE VBA Prerequisites

Before we continue, there are two important steps to be done.

  1. Import Smart View.bas

    Go to Developers tab and click Visual Basic.

      **![image005.png](https://objectstorage.us-phoenix-1.oraclecloud.com/p/BqK85Rn1zA5MP0vYiqbAdPgs7Z6OmMxw8SD3WCFVm5kY8uReidZ1KPIKkgJ1hCkG/n/axciphqpnohg/b/forums-legacy/o/uploads/jive_attachments/0/2/2/02282154egami.png)  
      Figure 5**
    
    
      Right click on VBAProject (Book1) and click Import File.
    
      **![image006.png](https://objectstorage.us-phoenix-1.oraclecloud.com/p/BqK85Rn1zA5MP0vYiqbAdPgs7Z6OmMxw8SD3WCFVm5kY8uReidZ1KPIKkgJ1hCkG/n/axciphqpnohg/b/forums-legacy/o/uploads/jive_attachments/0/0/2/00282154egami.png)  
      Figure 6**
    
    
      Find where your Smart View is installed and go to bin > smartview.bas. Click Open. In my case, C:\\Oracle\\Smart View\\bin\\Smart View.bas:
    
      **![image007.png](https://objectstorage.us-phoenix-1.oraclecloud.com/p/BqK85Rn1zA5MP0vYiqbAdPgs7Z6OmMxw8SD3WCFVm5kY8uReidZ1KPIKkgJ1hCkG/n/axciphqpnohg/b/forums-legacy/o/uploads/jive_attachments/5/2/2/52282154egami.png)  
      Figure 7**
    
    
      This will create a new module named SmartViewVBA.
    
      **![image008.png](https://objectstorage.us-phoenix-1.oraclecloud.com/p/BqK85Rn1zA5MP0vYiqbAdPgs7Z6OmMxw8SD3WCFVm5kY8uReidZ1KPIKkgJ1hCkG/n/axciphqpnohg/b/forums-legacy/o/uploads/jive_attachments/6/2/2/62282154egami.png)  
      Figure 8**     
    
  2. Enable references Oracle Smart View BI Extension and Oracle Smart View RC 1.0 Type Library. Continuing from above, go to Tools > References.
    image009.png
    Figure 9

      Choose Oracle Smart View BI Extension and Oracle Smart View RC 1.0 Type Library.
    
      **![image010.png](https://objectstorage.us-phoenix-1.oraclecloud.com/p/BqK85Rn1zA5MP0vYiqbAdPgs7Z6OmMxw8SD3WCFVm5kY8uReidZ1KPIKkgJ1hCkG/n/axciphqpnohg/b/forums-legacy/o/uploads/jive_attachments/8/2/2/82282154egami.png)  
      Figure 10**
    
    
      We are now ready to start our programming.
    

Smart View VBA

We should still be in Design Mode. To check, go to the developers ribbon and see if Design Mode is highlighted.

  1. Right click on Run MultiSheet Report, then click View Code.
    image011.png
    Figure 11
  2. Copy and paste the script below in between Private Sub cmdRun_Click() and End Sub:

Private Sub cmdRun_Click()

'Author: Steve Yeung

'About the Author: http://steveyeung.com

'Blog: http://MondayBI.com

'Created using SmartView Version: 11.1.2.5.216

'Date Created: 10 December 2014

'Version: 1.0

'--------------------------------------

'Compulsory, do not modify

'Reference

'https://docs.oracle.com/cd/E40530_01/epm.11123/smart_view_developer/frameset.htm?launch.htm

Dim obiee As IBIReport

Set obiee = New SmartViewOBIEEAutomation

Dim prompts() As BIReportPrompt

'--------------------------------------

Dim InfoWorkbook As Workbook

Dim InfoSheet As Worksheet

'MultiSheetReport.xlsm needs to be modified if this workbook save name changes

Set InfoWorkbook = Workbooks("MultiSheetReport.xlsm")

'Info needs to be modified if Worksheet name changes

Set InfoSheet = Worksheets("Info")

'--------------------------------------

'Below variables are automated from capturing Info worksheet filled in information

'There is no need to modify this unless there's a change in workflow

Hostname = InfoSheet.Range("B2").Value

Port = InfoSheet.Range("B3").Value

FriendlyName = InfoSheet.Range("B4").Value

CatalogFolder = InfoSheet.Range("B5").Value

ReportName = InfoSheet.Range("B6").Value

ReportingTable = InfoSheet.Range("B7").Value

PromptTable = InfoSheet.Range("B8").Value

OutputFolder = InfoSheet.Range("B9").Value

InsertServer = "http://" & Hostname & ":" & Port & "/analytics/jbips"

InsertAnswer = CatalogFolder & ReportName

'--------------------------------------

'Connect SmartView using FriendlyName from Info Sheet

'SmartView will ask you to input username and password

'If incorrect, program script will exit

X = HypUIConnect(Empty, UserName, Password, FriendlyName)

If X < 0 Then

MsgBox ("Login Failed")

Exit Sub

End If

'--------------------------------------

'Turns off Screen updating to avoid the annoying blink screen

Application.ScreenUpdating = False

'--------------------------------------

'To check if User has filled in the Prompt Table

'It is required to be filled in

'A validation can be done in Excel instead of VBA

'But for this case, VBA is used since I'm lazy

If PromptTable = "" Then

Application.ScreenUpdating = True

MsgBox ("Prompt Table is empty, please fill in and run again")

Exit Sub

End If

'--------------------------------------

'Initiate a new worksheet to store Table Prompt Variables

Sheets.Add.Name = "Prompts"

Dim PromptSheet As Worksheet

Set PromptSheet = Worksheets("Prompts")

PromptSheet.Activate

ActiveSheet.Range("A1").Activate

'--------------------------------------

'Inserts the Prompt Table from Answers to this worksheet

obiee.InsertView InsertServer, InsertAnswer, PromptTable, prompts, Default_Format, SameSheet

'--------------------------------------

'Counts and stores the Range for easy referencing later on

ActiveSheet.Range("A2").Activate

Dim LastRow As Integer

LastRow = PromptSheet.UsedRange.Rows.Count

'To get the total number of combinations to be used

Dim PromptTimes As Integer

PromptTimes = LastRow - 1

'The number of times to loop in the for loop later on

'This is variable (i)

Dim LoopRowCount As Integer

LoopRowCount = LastRow - 2

Dim LastColumn As Integer

LastColumn = PromptSheet.UsedRange.Columns.Count

'LoopColCount is used to check the number of Page Prompts that exist

'This is variable (j)

Dim LoopColCount As Integer

LoopColCount = LastColumn - 1

'--------------------------------------

'Create a new Workbook to store Reports

Workbooks.Add

'Uses Info sheet ReportName and the current time to save the report in the output folder

TempWorkbook = ReportName & "_" & Format(Now(), "yyyymmdd_hh_mm_AMPM")

TempWorkbook = TempWorkbook & ".xlsx"

ActiveWorkbook.SaveAs Filename:=OutputFolder & TempWorkbook

'Stores the workbook name for easy referencing

Dim ReportWorkbook As Workbook

Set ReportWorkbook = Workbooks(TempWorkbook)

'--------------------------------------

'Rename the new Report Workbook Sheet1 to TOC

'This sheet will be used to create a hyperlink table of contents

'The hyperlinks will be named by the Prompts Chosen

'So that end users will be able to quickly jump to the worksheet

'When too many worksheets are produced, this becomes useful

Dim TOCSheet As Worksheet

Worksheets("Sheet1").Name = "TOC"

Set TOCSheet = Worksheets("TOC")

'Names the Table of Contents with ReportName and Generated Time

TOCSheet.Range("A1").Value = "Table of Contents for Report " & ReportName & " Generated on "

& Format(Now(), "yyyymmdd hh:mm AMPM")

'--------------------------------------

'Setting for Number of Page Prompts

'LoopColCount is already derived previously

ReDim Pages(0 To LoopColCount) As String

'--------------------------------------

'Initialize the working sheet under generation later on

'This sheet will be the sheet inserting the Answer table for i number of times

'For each sheet, it will then edit the prompts and filled in automatically

Dim CurrentSheet As Worksheet

'--------------------------------------

'Starts the worksheet creation loop (variable i)

For i = 0 To LoopRowCount

'Go to the new report workbook

ReportWorkbook.Activate

'Add a new sheet to the end of all worksheets

'and initialize it with a name N + i times

'Where i is the variable for number of worksheets generated

Sheets.Add(After:=Sheets(Sheets.Count)).Name = "N" & i

Set CurrentSheet = Worksheets("N" & i)

CurrentSheet.Activate

CurrentSheet.Range("A2").Activate

'Insert the Answer report to the new worksheet

obiee.InsertView InsertServer, InsertAnswer, ReportingTable, prompts, Default_Format, SameSheet

'--------------------------------------

'Starts page prompt loop

'This loop fills the page prompt according to j variable derived earlier

For j = 0 To LoopColCount

InfoWorkbook.Activate

PromptSheet.Activate

PromptSheet.Range("A2").Activate

'Stores the current prompt value

PromptValue = ActiveCell.Offset(i, j).Value

ReportWorkbook.Activate

ActiveSheet.Range("A1").Activate

'Inserts the prompt value to Worksheet Row 1 cells so that

'end users would know which prompt values was chosen

ActiveCell.Offset(0, j).Value = PromptValue

ActiveSheet.Range("A2").Activate

'Stores the prompt value to page prompt

Pages(j) = PromptValue

'Modify the Sheetname so that it is using the prompt values

CurrentSheet.Name = CurrentSheet.Name & " " & PromptValue

Next j

'--------------------------------------

'After page prompt loop ends, the page prompt is all applied

'The below submits/apply the query to OBIEE

'Retrieves the results

obiee.EditPagePrompts Empty, Pages

'After retrieval, the columns should auto fit as a simple format

ActiveSheet.Cells.EntireColumn.AutoFit

'The sheetname was initialized with N + i

'It will be removed here

RemoveWSInit = Len(CurrentSheet.Name) - Len(i) - 2

CurrentSheet.Name = Right(CurrentSheet.Name, RemoveWSInit)

'--------------------------------------

'Goes back to TOC worksheet

'Inserts the hyperlink to the newly generated report

TOCSheet.Activate

ActiveSheet.Range("A2").Activate

HypTemp = "[" & TempWorkbook & "]'" & CurrentSheet.Name & "'!A1"

ActiveCell.Offset(i, 0).FormulaR1C1 = "=HYPERLINK(""" & HypTemp & """,""" & CurrentSheet.Name & """)"

'Autofit Columns for TOC

ActiveSheet.Cells.EntireColumn.AutoFit

Next i

'--------------------------------------

'Deletes the PromptSheet without askings Yes or No

Application.DisplayAlerts = False

InfoWorkbook.Activate

PromptSheet.Activate

PromptSheet.Delete

Application.DisplayAlerts = True

InfoSheet.Activate

Application.ScreenUpdating = True

'--------------------------------------

'Message Box and tells user that the report has been generated.

MsgBox (TempWorkbook & " Generated")

End Sub

Congratulations on finishing your Smart View BI VBA! Easy peasy, right? Since this is not an Excel VBA tutorial, I will not go through individual coding, but the comments in the coding are self-explanatory. In the following, I will explain individual OBIEE VBA extension functions that you can use to modify the script.

Oracle Smart View BI Extension Functions

In our example, you might have already noticed that I said lines 12 and 13 are compulsory. Actually, we are declaring IBIReport as a variable and using SmartViewOBIEEAutomation class to use the extensions. So if OBIEE extension functions are needed, you must declare this.

There are 10 OBIEE functions; I will provide the representative feature from the Smart View User Interface.

  1. InsertView (Official Oracle Documentation Reference)

    This is the most frequently used OBIEE function. When used, it provides the same context as the insert button found in the Smart View panel.

    From our previous example, insert view is the function used to insert Answer tables. In fact, we can use the same function to insert the compound view. In the Smart View panel, choose Compound View 1 and click Insert All Views.

          **![image012.jpg](https://objectstorage.us-phoenix-1.oraclecloud.com/p/BqK85Rn1zA5MP0vYiqbAdPgs7Z6OmMxw8SD3WCFVm5kY8uReidZ1KPIKkgJ1hCkG/n/axciphqpnohg/b/forums-legacy/o/uploads/jive_attachments/0/3/2/03282154egami.jpeg)  
          Figure 12**  
    

    In the MultiSheet Report example, Table View was used. In the Smart View panel, this would be choosing Table 1 and clicking Insert.

          **![image013.jpg](https://objectstorage.us-phoenix-1.oraclecloud.com/p/BqK85Rn1zA5MP0vYiqbAdPgs7Z6OmMxw8SD3WCFVm5kY8uReidZ1KPIKkgJ1hCkG/n/axciphqpnohg/b/forums-legacy/o/uploads/jive_attachments/1/3/2/13282154egami.jpeg)  
          Figure 13**  
    

    The result would be the same as the VBA obiee.InsertView function.

    image014.png

    Figure 14

    Other features of this function can be viewed in the official document. Such features include Insert As Excel Table, Insert As Excel Pivot and Insert Chart.

    With the use of VBA, a report prompt can also be made use intensively through InsertView function.

  2. EditPrompts (Official Oracle Documentation Reference)

    This function modifies Report Prompt values. In Answers, Report Prompts are set in the Prompts tab.
    image015.png

    Figure 15

    In Smart View, you can Edit Prompts value through the Oracle BI EE ribbon.

          **![image016.png](https://objectstorage.us-phoenix-1.oraclecloud.com/p/BqK85Rn1zA5MP0vYiqbAdPgs7Z6OmMxw8SD3WCFVm5kY8uReidZ1KPIKkgJ1hCkG/n/axciphqpnohg/b/forums-legacy/o/uploads/jive_attachments/4/3/2/43282154egami.png)  
          Figure 16**         
    
  3. EditPagePrompts (Official Oracle Documentation Reference)

    The EditPagePrompts function, used previously in the MultiSheet Reporting example, is used to modify Table View Table Prompts or Pivot View Page Prompts. However, currently Chart View Chart Prompts are not supported.
    image017.png

    Figure 17

  4. GetPagePrompts (Official Oracle Documentation Reference)

    The GetPagePrompts feature returns Objects that are present in the Table Prompt or Page Prompt. It will return the column name in an array. If we insert the Untitled Table View 1 into a new sheet and create a command button, the following code can be used to return the column names:

    Private Sub cmdGetPagePrompts_Click()

    Dim obiee As IBIReport

    Set obiee = New Smart ViewOBIEEAutomation

    Dim dims() As String

    Dim pageSelections() As String

    obiee.GetPagePrompts Empty, dims, pageSelections

    MsgBox (dims(0))

    MsgBox (dims(1))

    End Sub

    image018.png

    **Figure 18

    **

  5. DeleteView (Official Oracle Documentation Reference)

    Delete the View from the worksheet. The selected view will be deleted using this function: obiee.DeleteView Empty.

    However, you must first select the View, which means using Sheets.Range("A1").Select before performing the function.

    This is the same as the Delete button on the Oracle BI EE ribbon.

    image019.png
    Figure 19

  6. AnalysisProperties (Official Oracle Documentation Reference)

    Viewing the properties of the analysis can be done through the Smart View panel.
    image020.png

    Figure 20

    Using VBA functions, AnalysisProperties can be used to check and display properties.

  7. DirProperties (Official Oracle Documentation Reference)

    Similar to Analysis Properties, DirProperties is used to check and display Folder Properties.
    image021.png

    Figure 21

  8. InvokeMenu (Official Oracle Documentation Reference)

    InvokeMenu calls the object directly in the ribbon. Options to call are:

    | Menu | ID |
    | View Designer | ViewDesigner |
    | Publish View | PublishView |
    | Refresh | Refresh |
    | Edit Prompts | EditPrompts |
    | Edit Page Prompts | EditPagePrompts |
    | Copy | CopyView |
    | Paste | PasteView |
    | Delete | DeleteView |
    | Mask Data | MaskView |
    | Mask Document Data | MaskDocumentView |

    obiee.InvokeMenu "MaskDocumentView" can be made useful to Mask All Document Data before sharing reports in a shared folder. The majority of the features may not be the most useful since, apart from View Designer, Publish View, Refresh, Mask Data and Mask Document Data, they seem repetitive to others.

  9. CopyView (Official Oracle Documentation Reference)

    An existing View can be copied to another workbook or worksheet when this function is used. Similar to DeleteView, the selected View will be copied.
    image022.png

    Figure 22

  10. PasteView (Official Oracle Documentation Reference)

    The PasteView feature follows that of the CopyView feature since, after copying the selected view, PasteView will be used in another worksheet.
    image023.png

    Figure 23

Conclusion

All in all, Smart View OBIEE VBA creates a whole new way of Excel BI reporting, putting together the power of Excel and governed data discovery. In the end, learning VBA is much easier than learning any other languages. Strictly speaking, with the help of Excel Macro Recording, it doesn't take any effort to learn the skills. The Smart View BI Extensions let everyone have the freedom to manipulate their own personal dashboard and reporting system. Enjoy your road to success with Smart View OBIEE!

About the Author

With 10 years of experience in the BI field, Steve Yeung is a Project and Business Development Manager with Elufa Systems Limited, based in Hong Kong. He is also a Committee Member for Hong Kong Computer Society Business Intelligence & Big Data and speaks publicly on Oracle BI solutions. He holds Certified Specialist for Oracle Essbase and Oracle BIEE as well as Certified KPI Professional. Steve is also an instructor for Oracle University Oracle BI courses including OBIEE 11g and Essbase. His personal blog can be found on www.mondaybi.com.


This article represents the expertise, findings, and opinion of the author. It has been published by Oracle in this space as part of a larger effort to encourage the exchange of such information within this Community, and to promote evaluation and commentary by peers. This article has not been reviewed by the relevant Oracle product team for compliance with Oracle's standards and practices, and its publication should not be interpreted as an endorsement by Oracle of the statements expressed therein.


Comments