OBIEE SmartView Part 4 – Beyond Answers with VBA

Oracle Business Intelligence

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.

OBIEE-SmartView-Part-4-019

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

OBIEE-SmartView-Part-4-018

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.

OBIEE-SmartView-Part-4-017

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

OBIEE-SmartView-Part-4-004

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.

OBIEE-SmartView-Part-4-016

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

OBIEE-SmartView-Part-4-015

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

OBIEE-SmartView-Part-4-014

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

OBIEE-SmartView-Part-4-012

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

OBIEE-SmartView-Part-4-011

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

OBIEE-SmartView-Part-4-010

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

OBIEE-SmartView-Part-4-009

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.

OBIEE-SmartView-Part-4-008

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 = ""

OBIEE-SmartView-Part-4-007

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

OBIEE-SmartView-Part-4-001

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

OBIEE-SmartView-Part-4-006

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.

OBIEE-SmartView-Part-4-005

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

OBIEE-SmartView-Part-4-003

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

OBIEE-SmartView-Part-4-002

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.

OBIEE-SmartView-Part-4-ExtraMile-003

 

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

OBIEE-SmartView-Part-4-ExtraMile-002

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

OBIEE-SmartView-Part-4-ExtraMile-001

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

Advertisements

Author: Steve Yeung

Being in the EPM & BI field for more than 8 years, it's about time I contribute to newcomers! As a founder of MondayBI.com I wish to give you all the help I can. Feel free to give any suggestions or questions. Hope you will all enjoy this blog! William Wong Essbase Certified Specialist OBIEE Certified Specialist

One thought on “OBIEE SmartView Part 4 – Beyond Answers with VBA”

  1. Hello Steve,
    Could you please help me with the automatic connection in all worksheets in an excel file using Smartview? The old essbase had this option. I just connected in one sheet and automatically I would be connected in the whole Excel file.As I saw, Smartview does not work this way. I made a macro, but it does not work. I have no error on it, but it just does not work. It worked for 1 day and then just all of a sudden it stopped.I am desperate. I can not connect manually in every sheet otherwise I will go crazy. I am working with very large databases.

    (Previous to writing this macro I imported the .bas file to declare all functions as safe)

    Public Sub Connect()

    Dim wsSheet As Worksheet

    For Each wsSheet In ActiveWorkbook.Worksheets
    x = HypConnect(wsSheet.Name, “user”, “password”, “Connection”)
    Next wsSheet

    End Sub

    Thank you so much,

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s