Another reason to use SmartView for OBIEE? The Power of VBA!!!

OBIEE SmartView Part 1 – Creating your first OBIEE SV Report

OBIEE SmartView Part 2 – Connecting to Essbase

OBIEE SmartView Part 3 – Your own secure mini reporting book

It is required to have the skills from Part 1, Part 2 and Part 3 before you can fully understand what I am doing here. Moreover, you will need some basic skills of VBA. I’m not a VBA or Excel expert, but coding and programming is more or less the same everywhere. There are loads of resources on learning VBA if you google it. So I won’t google it for your and I’m not going through VBA bits and pieces.

So what is this Part 4 all about? Let say you would like to export an Answer report to workbook with multiple sheets based on members 2 different selections. Answer won’t be able to do that and SmartView alone won’t be able to do that. In this post, I will use this as an example as to what you can do with SmartView OBIEE VBA. In short, I’ll call this example, MultiSheet Excel Export.

Starting off with Answers

For this demonstration, we’ll start off with creating a new Answers report using Sample Sales Lite. We need “Time”.”Per Name Year”, “Products”.”Product Type”, “Products”.”Product”, “Base Facts”.”Revenue” in the Selected Columns.

Now go directly to Results and this is what you should see. Delete title view since we don’t need that.

Create a new Table View with just only “Time”.”Per Name Year” and “Products”.”Product Type”. You don’t actually need this in the compound. However, for the multisheet report, keep in mind that we will need this to generate the underlying prompt list choices.

Edit the Table View 1 (the default table in results) and move “Time”.”Per Name Year” and “Products”.”Product Type” to Table Prompts.

Go to Advance and make note of the underlying View Name. In this case, “tableView!1” is the reporting table we need and “tableView!2” is the prompt list.

Save the report in a new folder called Test and just leave it called Untitled for now.

Open a new Excel, Connect to SmartView, browse to Test folder.

Insert Table 1. And by default this will be the first selection in the prompts which is 2008 Accessories. What we want is a combination of this for every sheet.

 

Creating your first OBIEE SmartView VBA

Create a new workbook and fill in the following from A2 onwards.

Server	steveyeung
Domain
Port	7001
Catalog Folder	/shared/test/
Report Name	Untitled
Reporting Table	tableView!1
Prompt Table	tableView!2
Instructions
1	Fill in above information
2	Click on SmartView Ribbon
3	Click Panel
4	Click on Private Connections
5	Select the above server or Create new connection
6	Expand Server Node
7	Sign In
8	Click Run MultiSheet Report

Now go to Developer mode. If you don’t know how to enable this, go here. Click Design Mode. Insert an ActiveX Command Button.

Right click the new command button and edit it’s properties. I’m calling mine cmdRun and Caption as Run MultiSheet Report.

Right click the command button and click view code. In Tools, click References.

Add 2 reference, Oracle Smart View BI Extension and Oracle SmartView RC 1.0 Type Library. Funny how they spell SmartView and Smart View with the space. Anyway, click OK.

A new Private Sub should be there. Paste below that the following:

Dim obiee As IBIReport
Set obiee = New SmartViewOBIEEAutomation
Dim prompts() As BIReportPrompt

Server = Worksheets("Info").Range("B2").Value
Domain = Worksheets("Info").Range("B3").Value
Port = Worksheets("Info").Range("B4").Value
CatalogFolder = Worksheets("Info").Range("B5").Value
ReportName = Worksheets("Info").Range("B6").Value
ReportingTable = Worksheets("Info").Range("B7").Value
PromptTable = Worksheets("Info").Range("B8").Value
InsertServer = "http://" & Server & Domain & ":" & Port & "/analytics/jbips"
InsertAnswer = CatalogFolder & ReportName

If PromptTable = "" Then
MsgBox ("Prompt Table is empty, will exit now")
Exit Sub
End If

Application.DisplayAlerts = False
On Error Resume Next
Worksheets("Prompts").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Sheets.Add.Name = "Prompts"
Worksheets("Prompts").Visible = False
Worksheets("Prompts").Activate
ActiveSheet.Range("A1").Activate
obiee.InsertView InsertServer, InsertAnswer, PromptTable, prompts, Default_Format, SameSheet

Dim PromptSheet As Worksheet
Set PromptSheet = Worksheets("Prompts")
Worksheets("Prompts").Activate
ActiveSheet.Range("A2").Activate

Dim PromptRange As Range
Set PromptRange = Range("A1")
PromptRange = PromptSheet.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Dim LastRow As Integer
LastRow = PromptSheet.UsedRange.Rows.Count
Dim PromptTimes As Integer
PromptTimes = LastRow - 1
Dim LoopRowCount As Integer
LoopRowCount = LastRow - 2
Dim LastColumn As Integer
LastColumn = PromptSheet.UsedRange.Columns.Count
Dim LoopColCount As Integer
LoopColCount = LastColumn - 1
Dim CurrentSheet As Worksheet

'Setting for Number of Page Prompts, Set to 0 means 1 prompt.

ReDim Pages(0 To LoopColCount) As String

For i = 0 To LoopRowCount
Sheets.Add.Name = "NewSheet" & i
Set CurrentSheet = Worksheets("NewSheet" & i)
CurrentSheet.Activate
CurrentSheet.Range("A1").Activate
obiee.InsertView InsertServer, InsertAnswer, ReportingTable, prompts, Default_Format, SameSheet

For j = 0 To LoopColCount

PromptSheet.Activate
PromptSheet.Range("A2").Activate
PromptValue = ActiveCell.Offset(i, j).Value

CurrentSheet.Activate
CurrentSheet.Range("A1").Activate
Pages(j) = PromptValue

Next j
obiee.EditPagePrompts Empty, Pages
Next i

Worksheets("Info").Range("A1").Value = ""

You can now save the report. I’ve called mine MultiSheetReport.xlsm. Remember to choose Save As Type which is Excel Macro-Enabled Workbook.

After you have saved it, login to OBIEE through SmartView. In order for this to work, you must first login.

Now make sure that you Server, Domain, Port is the same as the one in the Panel. Also make sure your catalog path ends with “/”. We saved ours at /shared/test/ and our report name is untitled.

Other fields you should have are the Reporting Table which is tableView!1 and Prompt Table which is tableView!2.

Once all done, make sure you are not in Design Mode anymore and click Run Multisheet Report.

You have generated 32 sheets of reports based on each unique table prompt.

To check if this really worked. Edit the Page Prompts for any sheet.

Conclusion

With VBA option, you can do so much more with Answers. Although VBA functions are very limited, but at least workaround can be done. I suggest to improve these VBA functions. But even before that, we’ll go an extra mile.

What we need is a prompt for login. You can either create a form for it later on or any other way you want. But first, you need to import a module called SmartView.bas. This will be stored in C:/Oracle/smartview/bin depending on where you installed your smartview.

To import, right click on your Book in developer mode.

 

Click Import and go to the directory that has smartview.bas and open.

Once imported, add the following to test it where “steveyeung” is your connection friendly name. If you just want it to prompt users for the username and password, just remove UserName and Password variable. For below, replace with your username and password.

UserName = “admin”
Password = “weblogic”

X = HypUIConnect(Empty, UserName, Password, “steveyeung”)
If X < 0 Then
Exit Sub
End If

Once that is done, you’ll be able to run directly by just clicking Run MultiSheet Report instead of having to going through the panel and connecting stuff. Of course, I don’t recommend storing the username and password. It should be removed for SmartView prompt.

So what else can you do after this. Loads, you can build your formatting using Excel VBA. Do your extra calculations using Excel, print formats, headers and footers, renaming things, an Monthly auto-generating Excel reporting book. So what can’t you do? At the moment, I can’t find a easy way to directly activate the references and users will need to do that separately. We can program this using GUID, but I think it’s alot of work and I don’t recommend doing it that way.

That’s the end of Part 4 and probably the end of the series. Hope you learnt something new.

It took me 15 minutes to write that VBA, so please don’t complain on the syntax. Moreover, I’m not a VBA expert! Feel free to fine tune it or finalize it.

If you want a copy of the xlsm, feel free to contact me.

SmartView 11.1.2.3 VBA Reference