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.
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.
This document assumes that you have basic knowledge of Smart View and have the following installed:
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:
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.
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.
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.
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:
In column B, ranges B2:B8, we will fill in these variables. Example as follows:
You may also create additional instructions or format your worksheet. Here is an example:
Before we continue, there are two important steps to be done.
We should still be in Design Mode. To check, go to the developers ribbon and see if Design Mode is highlighted.
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.
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.
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!
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.