Using the PS/nVision Visual Basic Interface

You can develop powerful Visual Basic applications with the PS/nVision Visual Basic programming interface. The objects and related methods referenced in this section provide the ability to call PS/nVision features programmatically for common reporting and analysis tasks. This chapter describes:

Note. Object methods are listed in order based on their functionality.

Click to jump to top of pageClick to jump to parent topicUsing Designer Object Methods

Creates an instance of PS/nVision.

The following is an example.

Dim objnVision As Object
Dim objDesignerCmd As Object
Dim strAppName As String
strAppName = "PSnvision.nvsdesigner"
Set objnVision = CreateObject(strAppName)
Set objDesignerCmd = objnVision.DesignerCmd

After you finish with the Designer object, clear the PS/nVision object by setting it equal to nothing, as in the following example: 

Set objnVision = Nothing

Click to jump to top of pageClick to jump to parent topicDesigner Object Methods

The following shows details of designer object methods.

Click to jump to top of pageClick to jump to parent topicConnect

Syntax

Connect([in, optional] BSTR startMacro)

Description

This method enables the user to sign on to the PeopleTools application.

Example

‘ Straight Connection
objDesignerCmd.Connect
‘ Connection and run macro
objDesignerCmd.Connect("MacroName")

Click to jump to top of pageClick to jump to parent topicDisconnect

Description

This method enables the user to sign off from the PeopleTools application.

Example

objDesignerCmd.Disconnect

Click to jump to top of pageClick to jump to parent topicResetEnvironment

Description

This method resets the working directory and other environment settings needed for PS/nVision to process reports.

Example

objDesignerCmd.ResetEnvironment

Click to jump to top of pageClick to jump to parent topicStartMenu

Description

This method causes the designer to register its menu within the grid.

Example

objDesignerCmd.StartMenu

Click to jump to top of pageClick to jump to parent topicOnWindow

Description

This method refreshes the designer window, including PS/nVision menus.

Example

objDesignerCmd.OnWindow

Click to jump to top of pageClick to jump to parent topicOpenFile

Syntax

OpenFile([in] BSTR filename, [in, optional] BOOL
 updatelinks, [in, optional] BOOL readonly)

Description

This method opens a new or existing PS/nVision report layout.

Parameters

There are three arguments associated with this method: the file name (with full path) and two optional arguments: update links and read only.

Example

ObjDesignerCmd.OpenFile("c:\user\BALANCE.xls", TRUE, FALSE)

Click to jump to top of pageClick to jump to parent topicOpenLayout

Description

This method opens an existing layout.

Example

objDesignerCmd.OpenLayout

Click to jump to top of pageClick to jump to parent topicNewLayout

Description

This method creates a new layout.

Example

objDesignerCmd.NewLayout

Click to jump to top of pageClick to jump to parent topicSaveAsLayout

Description

This method saves the worksheet as a layout.

Example

objDesignerCmd.SaveAsLayout

Click to jump to top of pageClick to jump to parent topicDefineLayout

Description

This method invokes the PS/nVision Layout Definition dialog box.

Example

objDesignerCmd.DefineLayout

Click to jump to top of pageClick to jump to parent topicLayoutOptions

Description

This method invokes the PS/nVision Layout Options dialog box.

Example

objDesignerCmd.LayoutOptions

Click to jump to top of pageClick to jump to parent topicReportRequest

Description

This method opens an existing report request.

Example

objDesignerCmd.ReportRequest

Click to jump to top of pageClick to jump to parent topicRunCurrent

Description

This method runs a defined or previously opened report request.

Example

objDesignerCmd.RunCurrent

Click to jump to top of pageClick to jump to parent topicRunReport

Syntax

RunReport([in] BSTR BusUnit,
 [in] BSTR RptRqst)

Description

This runs a specific report.

Parameters

There are two arguments required for this request: business unit and report request name. The report request must be in uppercase letters.

Example

objDesignerCmd.RunReport("M04", "BALANCE")

Click to jump to top of pageClick to jump to parent topicOptions

Description

This to opens the Options dialog box.

Example

objDesignerCmd.Options

Click to jump to top of pageClick to jump to parent topicPerfOpts

Description

Same as LayoutOptions with just one selection.

Example

objDesignerCmd.PerfOpts

Click to jump to top of pageClick to jump to parent topicOpenScope

Description

This method displays the Open Scope dialog box and then the Scope Definition dialog box, assuming the user chooses a scope.

Example

objDesignerCmd.OpenScope

Click to jump to top of pageClick to jump to parent topicScopeDefn

Description

This method invokes the Scope Definition dialog box.

Example

objDesignerCmd.ScopeDefn

Click to jump to top of pageClick to jump to parent topicDrillDown

Description

Initiate DrillDown sequence.

Example

objDesignerCmd.DrillDown

Click to jump to top of pageClick to jump to parent topicDrillLayout

Description

Performs a DrillDown using the specified child layout.

Example

objDesignerCmd.DrillLayout

Click to jump to top of pageClick to jump to parent topicAutoDrill

Description

If a default DrillDown layout is defined (as DefaultDrill) in the active instance, drills down using that layout.

For ledger reports, the default drill string may include a D, which indicates that any summary ledgers should be translated to detail. If no default layout is defined, for a query-based instance, drill down to that query in DrillQuery (QueryLink) mode. For a ledger-based instance, have the user select the DrillDown layout, as in DrillDown.

Example

objDesignerCmd.AutoDrill

Click to jump to top of pageClick to jump to parent topicReDrill

Description

Drills the selected cell using the most recently selected DrillDown layout.

Example

objDesignerCmd.ReDrill

Click to jump to top of pageClick to jump to parent topicDrillQuery

Description

Performs a DrillDown using the specified query.

Example

objDesignerCmd.DrillQuery

Click to jump to top of pageClick to jump to parent topicJumpBack

Description

Implements commands to activate the parent instance of the current child DrillDown.

Example

objDesignerCmd.JumpBack

Click to jump to top of pageClick to jump to parent topicRunQuery

Description

Runs the specified query with no inherited criteria. Not used for DrillDown.

Example

objDesignerCmd.RunQuery

Click to jump to top of pageClick to jump to parent topicUsing Session Object Methods

This object retrieves information about the current session.

The following is an example.

Dim objnVision As Object
Dim objSessionCmd As Object
Dim strAppName As String
Dim strDatabaseType As String
strAppName = "PSnvision.nvsdesigner"
Set objnVision = CreateObject(strAppName)
Set SessionCmd = objnVision.SessionCmd

After finishing with the session object, clear the PS/nVision object by setting it equal to nothing, as in the following example:

Set objnVision = Nothing

Click to jump to top of pageClick to jump to parent topicSession Object Methods

The following shows details of the session object methods.

Click to jump to top of pageClick to jump to parent topicDBType

Description

This property retrieves database types for the current session.

Example

strDatabaseType = SessionCmd.DBType

Click to jump to top of pageClick to jump to parent topicDBName

Description

This property retrieves the database’s name for the current session.

Example

strDatabaseName = SessionCmd.DBName

Click to jump to top of pageClick to jump to parent topicToolsRelDB

Description

This property retrieves the tool release database version for the current session.

Example

strToolReleaseDatabase = SessionCmd.ToolsRelDB

Click to jump to top of pageClick to jump to parent topicServerName

Description

This property retrieves the server’s name for the current session. It retrieves nothing if the user is logged on in two-tier.

Example

strServerName = SessionCmd.ServerName

Click to jump to top of pageClick to jump to parent topicOprId

Description

This property retrieves the user’s ID for the current session.

Example

strOperatorId = SessionCmd.OprId

Click to jump to top of pageClick to jump to parent topicTwoTier

Description

This property identifies whether or not the current session is two-tier. If the return value is TRUE (or a value of 1), the current session is two-tier.

Example

bTwoTier = SessionCmd.TwoTier

Click to jump to top of pageClick to jump to parent topicShowWarnings

Description

This property retrieves or sets a ShowWarnings option for the current session.

Example

Retrieve Example:

Dim bShowWarnings As Boolean
bShowWarnings = SessionCmd.ShowWarnings

Set Example:

Dim bShowWarnings as Boolean
bShowWarnings =  True
SessionCmd.ShowWarnings = bShowWarnings

Click to jump to top of pageClick to jump to parent topicAmountSql

Description

The AmountSql property retrieves or sets an Amount SQL option for the current session.

Example

Retrieve Example:

Dim bAmountSql As Boolean
bAmountSql = SessionCmd.AmountSql

Set Example:

Dim bAmountSql as Boolean
bAmountSql =  True
SessionCmd.AmountSql = bAmountSql

Click to jump to top of pageClick to jump to parent topicReadConfig

Syntax

ReadConfig([in] BSTR ConfigDir)

Description

This method retrieves the directory paths of a given PS/nVision Configuration Manager setting (as defined in the Windows Registry). If the user has multiple directory paths in the same configuration setting, it retrieves them all. The available configuration settings that can be retrieved are:

Example

Dim strDirPath as String
strDirPath = SessionCmd.ReadConfig("LayoutDir")

Click to jump to top of pageClick to jump to parent topicReadConfigDir

Syntax

ReadConfigDir([in] BSTR ConfigDir,
 [in] WORD nPosition)

Description

This method retrieves one directory path of a given PS/nVision Configuration Manager setting (as defined in the Windows Registry). This differs from the ReadConfig method: it parses out the list of available directories by providing a number representing the position of the directory in the list. For example, if you had two directories, such as c:\user\nVison\layout and c:\user, and you wanted just the c:\user directory, you would pass in the number two. If the user is looking for the second directory and there is only one, then nothing is retrieved. The list of available configuration settings that can be retrieved is as follows:

Example

Dim strDirPath as String
strDirPath = SessionCmd.ReadConfig("LayoutDir", 2)

This retrieves the second directory listed.

Click to jump to top of pageClick to jump to parent topicInstance Hooks

Beginning with Excel 97, Microsoft began using Visual Basic as the programming language to write macros. In earlier versions of Excel, you had to create a macro sheet to use the NvsInstanceHook. Now, whether recording your macro or writing Visual Basic code within the Visual Basic Editor, you can invoke the PS/nVision Instance Hook directly from the module within a Visual Basic project without having to create a macro sheet.

Instance hooks allow user-written macros to be executed by PS/nVision after delivering the data to a report instance but before saving the instance. Instance hooks are supported for both matrix and tabular layout sheets. With an instance hook, you can:

Within an Instance Hook macro, you can call any other Visual Basic methods or functions. But you should avoid calling PS/nVision functions and closing Excel.

To use instance hooks, identify the layout sheets in the layout workbook (.XNV file) to which you want to apply post-delivery processing. For each sheet, define the name NvsInstanceHook to refer to the name of the first cell of an Excel 4 macro sheet or the name of a Visual Basic procedure.

The defined name NvsInstanceHook is sheet-specific. You need to either define the same name on multiple sheets or, the preferred method, enter the sheet name and an exclamation point (!) with the name when defining it. By doing this you can fire different macros for different layout sheets in the same workbook. The resulting name looks like Sheet1!NvsInstanceHook.

To record a macro from within Excel, select Tools, Macro, Record a Macro.

To write a macro from within Excel, select Tools, Macro, Visual Basic Editor.

The following example shows a module that combines a recorded macro with additional code that has been added through the editor.

The macro bolds and right-aligns the nPloded TimeSpan columns, collapses the outline of the nPloded rows and columns, and saves the instance.

After creating the macro module, return to your PS/nVision layout and attach it using NvsInstanceHook.

To attach a macro using NvsInstance Hook:

  1. Select the cell in the layout.

  2. Select Insert, Name, Define.

    For the name, enter the sheet name followed by an exclamation point and the text NvsInstanceHook. For example, Sheet 1!NvsInstanceHook.

  3. Enter the name of the macro in the Refers to field.

    For the name, enter the name of Excel Visual Basic Module, where the macro is located, followed by a period and the name of the Excel VB Macro. For example, Module1.MacroName

  4. Click Add.

  5. Click OK.

Your macro is executed after the PS/nVision Instance is created.