The question behind this posting came from several different customers in the UK earlier in the month. The use case behind this is that customers want some additional control over what comes up in excel when running a query to excel without requiring the user to perform manual steps to accomplish this.

Options

So, there are several options for accomplishing this, each with its own set of pros and cons. Here they are.

• Creating a tabular report in nVision and using the instance hook in nVision to do this.
• Scheduling a query, sending it to a known folder in the process scheduler, and then having a VBA program open each file, format it, and save it.
• Performing the formatting on the client when the excel file is opened.

Using nVision

Pretty much all you need to know is covered in this blog posting.

Although this works relatively well to solve the problem, it has the following issues.

• An nVision tabular report must be created for each query. This requires a developer to get involved (but does provide granular control over the formatting
• nVision runs only on NT, which means that the reports must be scheduled on an NT box dedicated to it.
• The instancehooks must also be added to each report (versus having a global one for all reports

Scheduling the Query and using an App Engine Program

This approach has quite a few moving parts, and will not work if you want to run the queries to window. Here is my recommendation for doing this:

1. Create a VBA program that looks into a directory for an excel file, opens it, performs the processing, and closes it.
2. Create a process definition for it in process scheduler, so that it can be invoked as part of a job (clone the settings for the scheduled query app engine program, so that it can be kicked off from the same page)
3. Create a job with both process definitions in it. This job can be scheduled from the schedule query run control page.

Add the macros to the client instead of the server.

This approach, I believe, is the easiest to put in place and has the most stability. The key to it working is that whenever excel opens up a spreadsheet, it will fire the auto-open macro in any add-ins that are part of the excel environment. Therefore, by creating an excel add-in with an auto-open macro in it, you can cause the macro to run when the query results are opened.

This sounds easy in practice. However, to be successfull, you need to test to see if the excel file came from query (where you do want to process it), or if it didn't (which means you don't want to touch it).

This is where the tricky part comes in. You see, because drilling was always a part of nVision, we stuffed additional metadata into the resulting file that allows us to do all sorts of cool things with it. However, with query, we never thought to do that (and one concern we had was about keeping the file as small as possible for sizing reasons). Now that I'm no longer running the group, I'm thinking that it would have been really nice to have put that feature in (if I had a time machine, I could probably go back and fix that... Does anybody have one I can borrow?)

Okay. So now we all have to workaround an oversight I had when I owned query. What's the best solution? Well, unfortunately, scheduled query and the online query (which is a servlet on the web server) have different formats of files. Here, let me illustrate.

Here's what it looks like when runnning query online (using the servlet). See if you can find the query name somewhere in the picture.

Now, here's what it looks like when running a query through scheduled query. Again, see if you can find the query name somewhere in the picture.

As you can see, the query name is not in the query results, but is in the title bar of the browser (if hosting in excel). This is a property that is not easily accessible in VBA (although we spent a lot of time and effort figuring that out for our product... you'll have to buy it to get that code from us, though ;-).

Our recommendation to you is to have a standard set of text that you include in your query result, such as a prefix in the query description (because the query description is always put in call A1 regardless of whether its run through scheduled query or online). If you want to put somebody else on the hook for making sure it works right all the time (and also get a lot of other cool features that would take a lot of work, such as drilling, subtotalling, and number formatting), we think our add-in would be a great fit.

Plug for Grey Sparling Excel Add-in

Since we wrote this post (and due to requests from our customers), we ended up building this into our nVision add-in and changing the name of the product to be the "Grey Sparling Excel Add-in". The post processing we do is to apply formatting, such as number formatting, sizing of columns, freezing the coumn headings, applying auto-filters, and applying subtotals. This product also allows users to drill from their queries to pages or other pages. Here's the product page for it. Also, here's a flash demo of the product's features.

Labels: excel, Query

This is another blog entry I started a while back, but never completed. As mentioned in yesterday's entry, there was a lot of work done in PeopleTools 8 that didn't get much visibility (because it didn't makes sense with the new paradigm). This feature fits into that category (but it allows you to do some very cool things).

What you can I do with it?

This interface allows you to control nVision in a manner similar to the design UI. For example, you can programmatically populate an nVision report request and run it from VBA without saving it or using the PeopleSoft-delivered dialogs. You can also use it to define criteria in an nVision report and even invoke nVision dialogs.

Cool! Does this mean I can use this with web nVision to automate things?

Sadly, no. You see, this feature puts a VBA interface on top of the nVision designer features. Again, this was all done back when the primary means of running nVision reports was on the client and not on a server. Because we hadn't had the paradigm shift of "no code on the client" yet, people were still putting code on desktops and our initial focus of this release was to improve the client/side functionality.

• New NVSUSER.XLM, which provided a better launching place for end-users for running and managing nVision reports.
• VBA hooks to allow better control of nVision

Unfortunately, when we eliminated code on the client; we also eliminated the foundation for these features (unless customers continue to deploy the client/server code).

Good question. You see, there are still a lot of situations where this level of automation is good:

• Automating parts of the development of nVision reports
• Performing actions around running reports, such as creating and updating report requests and scopes.

You see, you can create some macros to do things such as swap the data source of a report, etc that are utilities for a developer through the hooks. You can also create a new process definition for running nVision with the designer loaded (the existing process definition causes nVision to start with the designer not loaded for performance and stability purposes). When running this way, you can have simple routines that could ensure that report requests are set up appropriately, etc (which is desirable if you're not in a position to use the new security hooks in the report request page added in PeopleTools 8.44).

Getting Started

Unfortunatey, this is one of the few places where PeopleBooks will not help you. Although there is a section in PeopleBooks for them, the documentation is wrong. Therefore, you will want to use the object browser in VBA to see what is available (and because the DLL you browse has a couple of issues with the object browser, you'll need to pull it in twice).

Here are the steps you go through to do this:

1. Open up Excel and navigate to the Visual Basic Editor
2. Open up the object browser


3. Use the menu Tools --> References menu
4. Then browse to your PeopleTools bin directory and open the PSNVD.DLL. You will then see PS/nVision Type Library in the list of the dialog. If you see more than one, pick the one with a reference to NVDUSER.TLB
5. Click OK to add it. If you get an error, re-browse to PSNVD.DLL and re-select the PS/nVision Type Library
6. You should now see PS/nVision in your list of libraries in the object browser

Now that you've done that, you can start looking at the classes, properties, and methods available to you in VBA

nVision Report Requst

The first class of interest is the report request class. Here's a screenshot of it (click on the thumbnail to see a full-sized version of it).

nVision Criteria

Another class of interest is the criteria class (where you can set and change criteria). Here's a screenshot of it (click on the thumbnail to see a full-sized version of it).

Grey Sparling Plans in this area

In our nVision bolt-on, we are in the process of building web services for all these classes. These services allow client-side logic to call the server-side code with logic and prompting without requiring installation on the client. This will allow you to perform this level of automation regardless of the entry point.

Labels: nVision

The inspiration for this posting came while Chris and I were performing a technology review (actually, when we were at dinner with the customer after a long day). Here we were at one of the oldest restaraunts in the USA (which was actually a pub... quite fitting, we thought, since anybody who really knows us also knows that we really like pubs). Anyway, over a couple of beers the customer wanted to know where trees came from, etc. and was fascinated as I spun my tale.

Believe it or not, I started this post almost 2 months ago. However, I felt it important to check my facts with Dana Quitslund (the mastermind behind nVision). It also gave me a great excuse to pick up the phone and catch up with one of my heros (Dana's probably reading this right now, thinking, "Geez, why did he have to go on and say that?"

A Tree is Born

Okay. So, here is where we answer the question: "Which came first, the Ledger or the Tree". Believe it or not, I've had quite a few arguments with folks on the answer to this, because of how well trees are embedded into several of the PeopleSoft application suites. So... Place your bets... The answer is coming up...

Trees were invented for the initial version of General Ledger primarily to support nVision. The vision behind initial vision for Tree Manager came from the following folks:

• Mike Kaiser. He was the person in charge of creating the initial version of Financials, coming from Walker Interactive, where he was instrumental in the architecture of its GL.
• Dana Quitslund. The first developer that Mike Hired for GL. In addition to writing much of the early COBOL in GL, he also created nVision.
• Dan Bulos (AKA "Dan the tree man"). Brother-in-law of long-time PeopleTools developer Linda T.
• John Malatesta. Head of PeopleTools, and architect behind the toolset used by Dave Dufflield's new company, Workday.

PeopleTools 2

So, now you know the players and the reasons behind its creation. I think it makes sense to go on to discuss some the process that went into its creation. You see, even though tree manager was created to support nVision and the first release of financials, it was developed by the PeopleTools group so that all products could take advantage of it (for those interested in the timeline, this was PeopleTools 2, which was the release that Financials 1 shipped on).

Anyway, when John and Dan picked it up, they had the vision that trees should be applicable to HR as well as GL. As Dana put it, John Malatesta wanted to make sure that the initial version of tree manager didn't make HR look bad from a functionality perspective. What did this mean? It meant that winter tree support was included in the first version of tree manager, so that department hierarchies worked properly in HR.

One other concern they had was about performance, which was why Dana Quitslund came up with the tree numbering scheme that allows a single SQL statement to retrieve all of the descendents of a tree node (something that is relatively unique to tree manager).

Therefore, the tools guys developed tree manager in PeopleTools 2, and Dana and Mike used it to create nVision as part of the Financials application (note that in PeopleTools 3, query support was added to nVision, and nVision because a PeopleTool in its own right).

One other interesting note is that the tree selector funcitonality referenced in this blog entry came out of two of the first customers using trees and nVision (actually, I think they were the first because Dana spent a lot of time ironing out the last details of the product). These customers were DB2 shops, which had a quirk in optimizer that affected this design.

After putting together the proof of concept at Andersen Consulting for adding encumberance accounting to GL to create a public sector version of financials, my first involvement in trees and nVision was at one of these initial customers in New York (yes, this is the closest I can get to being involved in the initial creation of it... of course, that also explains why I'm writing a blog entry on it from my bedroom and not sitting on a beach somewhere sipping margaritas).

PeopleTools 3

Tree manager essentially stayed the same in PeopleTools 3. Because the PeopleTools group was project focused, the developers who worked on tree manager in PeopleTools 2 were working on new initiatives in PeopleTools 3 (not the least of which was creating PS/Query and adding support for Crystal Reports).

PeopleTools 5

Between PeopleTools 3 and PeopleTools 5 the PeopleTools group went through a radical shift (for those wondering, there was no PeopleTools 4, even though there was an HRMS 4). Dave Duffield asked John Malatesta and Ken Morris to come up with a radically new toolset for the next generation of business applications (code-named Sonoma). This meant that they were no longer working on PeopleTools.

In the meantime, Baer Tierkel stepped up to become the VP of PeopleTools development and adopted a product-focused organization (in other words, Developers and Development Managers owned their products from release to release, ensuring that there is a consistent investment and strategy for those product). I personally liked the change, because prior to that point, I found it very difficult to get issues looked into and resolved because somebody had to be pulled off of other work to look into it.

So, here we are in PeopleTools 5. Baer Tierkel is running the tools team. Dana Quitslund has a team of folks dedicated to the reporting tools. For the reporting team, there were a couple of key features in PeopleTools 5:

• Adding branching to trees (which allows multiple people to own parts of the tree and also allows them to work on the different parts at the same time).
• Adding Lotus 1-2-3 support (keep in mind that PeopleTools 5 was in 1994-1995, when Excel still hadn't won the spreadsheet wars... Not supporting Lotus was a big issue for a lot of very large PeopleSoft customers).

From a market perspective, the most important PeopleTools 5 feature in the reporting area was 1-2-3 support (there was revenue recognition issues as well as customer satisfaction issues). This was big, important, stuff to a small software company, like PeopleSoft was at the time. Unfortunately, due to the timing of the release and the feature set available in 1-2-3 at the time, we ended up burning through a lot of development cycles without getting the support for that product stable enough to release. A side effect of that was that the branching functionality was more complex than initially anticipated, and it introduced some issues with trees.

PeopleTools 6

After a gruelling release in PeopleTools 5, Dana decided that he preferred being the "nVision guy" and let somebody else deal with all the other things that are part of managing a portfolio of products through the release cycle. His true love was working with customers and figuring out cool ways to solve business problems and really wanted to get back into that. This was how I had the opportunity of a lifetime (and was chosen to do this by one of my biggest heros).

Anyway, here it is. PeopleTools 6. The development team is still focused on cleaning up many of the issues still lingering from PeopleTools 5. For tree manager, that meant agressively focusing on re-working many of the internals of it. There were way too many circumstances where a customer could corrupt their trees (without a good means of fixing them). Therefore PeopleTools 6 was the clean-up release for it.

PeopleTools 7.

For PeopleSoft, PeopleTools 7 was the 3-tier release (no web client yet, but 3-tier access through an application server). We did a lot of work to ensure that the tree internals would run properly with the new infrastructure changes supporting the app server.

Probably the biggest change with trees was adding the tree performance options to them (initially for nVision performance, but later adopted more widely). This was done in PeopleTools 7 and then later backported to PeopleTools 5 and 6.

PeopleTools 7.5.

In PeopleTools 7.5, we re-worked the user interface to use MFC and right-click menus. As part of this, we also did a lot of internal work that ended up improving the stability of tree manager.

PeopleTools 8.

So. This brings us to late 1998. PeopleTools 8. We started scoping the release to make it a brand-new web-based tool, right? WRONG!

PeopleTools 8.0 was initially the EPM release with a lot of other infrastructure things rolled in. Here is what the my scope list looked like in late 1998:

After completing over 80% of what was listed above, we then realized that we needed to move to the web. This initially meant that there was a browser version of the windows client and not the other client tools, such as tree manager, query, and nVision.

PeopleTools 8.14 and 8.4

It took us a while, but we finally created a web version of tree manager. This was a very large and involved project, because we didn't want to make the same mistakes with moving tree manager to the web as we had with PS/Query. This meant that we had to spend a lot more time prototyping different user interfaces (especially with respect to how to take actions on tree nodes). Because AJAX hadn't been invented yet, we were relatively limited in how we could approach the task. We ended up with the icon representation of actions that one uses today.

In addition to re-working the user experience, we also needed to rework the back-end. You see, the original code was written with the user interface code tied pretty tightly to the backend code. This meant that there weren't good services to use when splitting things up between the UI rendering and the backend processing. Because there were a lot of things people wanted to do with trees outside of merely using the user interface, we made the design decision to build a full set of PeopleCode objects to work with trees and then build the user interface in PeopleTools/PeopleCode using those classes/services. Again, this required a lot of development effort.

The end-result is not just the PIA tree manager, but a whole set of new tools to use and work with trees:

• Tree Manager - UI for maintaining trees.
• Tree Viewer - Read-only UI for seeing what's in a tree.
• Tree Viewer Component - Widget that can be embedded into a page to use a tree to pick a value.
• Tree Mover (aka Tree Import and Tree Export). Tool initially created for EPM to allow migration of trees to the warehouse from a source system.
• Tree Audit/Repair Utility. Tool that checks the health of your trees and fixes some common problems.

Fusion

Charles Phillips and Steve Miranda have stated on numerous occasions that Trees will be brought forward from PeopleSoft to Fusion. Although some of the designs details are still being hammered out, it is our understanding that the tree definition itself will be very similar to what you see today (although they seem to be taking my wish list of changes that I never got to do and incorporating them). If you want to talk about this over a beer, I'd be happy to provide my list and we can see in a few years which ones were adopted.

Grey Sparling Tree Enhancements

Since it's on topic, I thought I'd mention where we are with the product we're working on. We're wrapping up development on it and are in the process of packaging it up (which also means creating trial version of it).

Our good friend, Bert Laws, suggested that we finally solve the problem of exporting trees to BI tools. Much of the code we've written for the tree product we've got can be leveraged for this, so we'll be working on that within the next few weeks. If there's a customer trapped on an old version of Cognos or Essbase with Cube Manager (or if you want to get trees into another BI tool and don't want to buy EPM), contact us at info@greysparling.com

Labels: History, Tree_Manager