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.
|1||Fill in above information|
|2||Click on SmartView Ribbon|
|4||Click on Private Connections|
|5||Select the above server or Create new connection|
|6||Expand Server Node|
|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.
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
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.