×
UX/Mobile/Responsive

Embedding Macros into nVision reports

By Larry Grey • July 28, 2005

Being the “nVision” guy at PeopleSoft, I’ve presented it each of the last 8 user conferences. There is one part of my presentation that always brings down the house: taking a simple report and embedding an instance hook into it (usually to create a pivot table). I give that part of the presentation and people start throwing undergarments at me… it’s so wierd!

Seriously, though, I’m frequently asked to describe what I did again to people who want to embed macros into nVision reports. Thus, here is my best description of how to do it (without all the pomp and circumstance of a presentation).

Why is this cool?

The reason this is so cool, is that you can work around any limitation in nVision and essentially allow it to do anything you want. For example, there is a limitation in the autmatic formatting in nVision where there can only be a single format for any amount fields (which can be a problem when you are showing dollars, headcount, and a percentage in the report). The macro to change the number formatting is extremely simple to add.

Here are some other examples of things you can do with a macro.

  1. Restructure the report, so that the results are put in multiple files (or worksheets in the same workbook).
  2. Highlight values that need attention.
  3. Send emails based on the data.
  4. Create auto filters, charts, pivot tables, and subtotals.
  5. Hide data or insert new data
  6. Change an instance to a layout and run it (this is a trick used for multiple field nPlosion that will be part of another posting).


First, a little background
For those who are somewhat familiar with nVision, but aren’t completely familiar with how it works, a little background information.

nVision is a reporting tool that lives in Excel. We developed what is essentially an add-in (but technically, isn’t an excel add-in for those excel experts out there). The add-in provides a user interface for prompting against objects in the PeopleSoft application (such as ledgers, calendars, timespans, trees, and valid value tables). This allows the report to be built much more easily. There’s also a set of features for running the report and saving off a report instance that has the data put into it. Finally, there’s a set of functionality to allow iterative drilling into the results to allow different slices to be seen and to allow more detail to be shown.

On the other side is all the functionality that Excel brings to the table. Because nVision uses Excel, in addition to generating output in a format that all accountants know and love, we didn’t have to develop the following features (that a tool like Crystal Reports did have to develop).

  1. Number formatting
  2. Font formatting and background colors
  3. Formula syntax
  4. Extended widgets, such as charts, OLAP controls (pivot tables), and auto filters.
  5. A programming language for more complex rules

Instead, nVision has hooks into excel to leverage all of these features that are inherent to it. This posting is inteded to talk about the programming language hooks, called InstanceHooks (which is a hook to call an Excel VBA program).

How to do it…

Okay, now that the background information is done, let’s talk a little bit about how to embed macros in a report.

There are really two things that you really have to pay attention to when doing this:

  1. Managing the difference between an nVision layout and a report instance.
  2. Utilizing the macro recorder in Excel to write your macro for you.

nVision layout

So, let’s start with a discussion as to how to use layouts and instances in this process. When you develop an nVision report, you are creating a layout (which in other tools is called a template). The layout contains all the rules for accessing data, all the formatting rules, and all the excel functions. It also contains all the macros. This is because in the process of running a report, the layout is first saved as an instance, and then nVision populates the data into it. Then, the InstanceHook gets called to run any macros you want to have automatically run (you can also embed macros that users can invoke in the standard excel way through a shortcut key, or adding a control or menu item linked to the macro… no instance hook needed when the running isn’t done automatically for the user.).

Therefore, in order for something to make it into the final report, it must be present in the layout.

Building the macro

Now that we’ve covered that, let’s move on to the next piece: building the macro. Because most embedded macros work with the data that is extracted (such as an instance hook or auto filter), it is difficult, if not impossible to build the macro directly in the layout. You really need the data there. That is why I recommend that you run the report first and record the macro against a report instance.

So, up until this point, you’ve created your layout to get the data you want, and have run it to see what data it gets. This is where the macro recorder comes in. The excel macro recorder is pretty good at creating the code behind actions that you perform. So, if you are creating a macro to build a pivot table, I definitely recommend using it. You turn on the macro recorder, select the data range you want to build it on (this is an important step, because in nVision the number of rows or columns that contain data can be dynamic… this means that you need to make sure that your macro will select the data set regardless of the number of rows that come back). When you’ve finished the last step in creating the pivot table, you stop the macro recorder.

At this point, you should open the Visual Basic editor to review the code that was generated. Now, you can tweak it if it had hard-coded logic in it (such as selecting a range). One tip for dynamically selecting ranges is to use the Shift+End, then Down sequence. This will create the following code.

Range(Selection, Selection.End(xlDown)).Select

Using Shift+End, then right will allow you to select the columns in a range, and will create the following code.

Range(Selection, Selection.End(xlToRight)).Select

Now, this will only work if all rows and columns are contiguous.

Here is the code that would be generated for creating a pivot table from data.

Sub CreatePivotTable()

‘ Macro3 Macro

‘ Macro recorded 7/28/2005 by Larry Grey

 

      Range(Selection, Selection.End(xlToRight)).Select

Range(Selection, Selection.End(xlDown)).Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _

        “Sheet1!R2C1:R24C4″).CreatePivotTable TableDestination:=””, TableName:= _

“PivotTable2”, DefaultVersion:=xlPivotTableVersion10


      ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

ActiveSheet.Cells(3, 1).Select

With ActiveSheet.PivotTables(“PivotTable2”).PivotFields(“Region”)

        .Orientation = xlRowField

.Position = 1


      End With

With ActiveSheet.PivotTables(“PivotTable2”).PivotFields(“Salesperson”)

        .Orientation = xlRowField

.Position = 2


      End With

ActiveSheet.PivotTables(“PivotTable2”).AddDataField ActiveSheet.PivotTables( _

        “PivotTable2”).PivotFields(“Count”), “Sum of Count”, xlSum

      ActiveSheet.PivotTables(“PivotTable2”).AddDataField ActiveSheet.PivotTables( _
        “PivotTable2”).PivotFields(“Amount”), “Sum of Amount”, xlSum

      Range(“C3”).Select

With ActiveSheet.PivotTables(“PivotTable2”).DataPivotField

        .Orientation = xlColumnField

.Position = 1


    End With
    End Sub

You will now need to modify the first “Selection” reference to be a cell (this was cell B4 on my report). The second “Selection” reference can remain, because the first line of code selects all rows. I also suggest making the subroutine a “Public” subroutine. This will make it easier when you have to create the instance hook (otherwise you ahve to qualify the macro by the module it was put in, which is “Module1” in this circumstance.

Moving the code to the layout and testing

The next step is to take your macro, which is in the report instance (i.e. the one that is the output of running an nVision report), and move it to the layout, so that it will be part of the report to be run. At this point, I suggest you run it and invoke the macro manually to make sure it works correctly.

If there are any problems, you can tweak the code directly in the VB editor in the new instance that you ran or record the macro all over again. Don’t forget to move any changes back to the layout when you’re done.

Turning on the InstanceHook

Okay, now you have created your macro and are sure that it does what you want. The next step is to automate the running of this macro after nVision has returned the data. This is done through a defined name NvsInstanceHook. What you do is you create this defined name in your layout, using the menu items Insert–>Name–>Define…

You then type NvsInstanceHook as the name, and then type in the name of your macro. You’ll need to qualify it by the module if you don’t make it public. Otherwise, you can merely type in the name. If the macro doesn’t get invoked when you test it, you may need to qualify it in quotes (and make sure that it’s a public routine).

Good luck and happy hunting!

Labels:

Stay Updated

3 Replies to “Embedding Macros into nVision reports”

  1. Hi Larry, As you i’m also more into nVisions and macros. But, needless to say you are an expert. I’m currently working on a nvision report which needs multiple field nPlosion, lot many expert told me that you cannot do it. However, i was sure that there should be some way of doing it. And then came across your post under blogger. And believe me this is the only article on the net which speaks about the trick to achieve multiple field nplosion. Now that i know that we can achieve this via a macro but still not sure as to how should we be doing this and how will this work. As you mentioned that this will run as an additional process. How do we do this if we want to run the same thru web. btw, we are currently using PS FS 8.4. Would be great if you can help me out with this. Rgds, Sam

  2. This is a great find for me, a PS developer. I’ve been scratching my head on why columns get hidden automatically for while, and now I know. Thank you very much

  3. Ugh, you did such a great job explaining things thoroughly, until the name defining part. It just ends abruptly without much explanation on how to define the name! I need to know exactly how to define the name (how to name it) for it be be initiated when running the report. Any advice would be appreciated! Kelly

Comments are closed.

Request a Demo