Today, I was helping a customer configure our Desktop Single Signon product for use with nVision. Because this was a brand-new machine where nVision had never been run, he kept encountering the error PS/nVision is not configured properly on this workstation.
He was amazed when I gave him the solution: drop into a windows command shell and type
Magically, nVision started to work.
The error message is generated from the code that uses COM to initiate a conversation between Excel and the PeopleTools bindaries. You see, because nVision is part Excel and part PeopleTools, there’s a delicate dance that has to occur at startup between the two. COM facilitates the communications between the two.
Sometimes if the COM objects aren’t initialized properly (they’re supposed to do this as part of running workstation configuration… PSCFG.EXE, but sometimes it doesn’t work, especially when importing the settings from a file). Running nVision with the /register flag will force this to occur (if you want to unregister the COM objects, you type
Labels: excel, nVision
- Create a view against your valid value table
- Create a new table and populate it manually
- Use PeopleSoft’s summary ledger and summary tree functionality
SELECT 'CONS' , A.DEPTID , A.EFFDT , A.EFF_STATUS , A.DESCR , A.DESCRSHORT , A.COMPANY , A.SETID_LOCATION , A.LOCATION , A.TAX_LOCATION_CD , A.MANAGER_ID , A.MANAGER_POSN , A.BUDGET_YR_END_DT , A.BUDGET_LVL , A.GL_EXPENSE , A.SYNCID , A.SYNCDTTM FROM PS_DEPT_TBL A WHERE A.SETID = ( SELECT MAX(B.SETID) FROM PS_DEPT_TBL B WHERE B.DEPTID = A.DEPTID)Populating a new table Let’s move on to discuss how to populate a separate table with data. This gives you a bit more control over the process of doing this than a view would provide (such as rules for picking values that may be duplicated across setids). There are two ways of accomplishing this: the first is to write an application engine program you run periodically to move the data (and the SQL statement above could be the starting point for doing %InsertSelect). Another option is to add SavePostChg Peoplecode to the record you’re doing this for. Let’s focus on DEPT_TBL as an example for this again. I’ve created a new record definition called DEPT_CONS_TBL, which has a subset of the fields in the DEPT table I want to use for reporting. I, then added SavePostChange peoplecode to the SETID field on the DEPT_TBL record. This means that every time data is saved into the table, my PeopleCode will be invoked to update my new table.
/* Grey Sparling Solutions - Create SuperSetid value */ /* */
rem Check to see if current value currently exists in Super Setid Record; Local Record &SuperSetidRec; Local Record &CurrentRec; Local string &SuperSetidVal = "CONS";
&CurrentRec = GetRecord(Record.DEPT_TBL);
&SuperSetidRec = CreateRecord(Record.DEPT_CONS_TBL); &SuperSetidRec.GetField(Field.SETID).Value = &SuperSetidVal; &SuperSetidRec.GetField(Field.DEPTID).Value = DEPT_TBL.DEPTID;
If &SuperSetidRec.SelectByKeyEffDt(DEPT_TBL.EFFDT) Then
rem Update existing value; &CurrentRec.CopyChangedFieldsTo(&SuperSetidRec); &SuperSetidRec.Update();
- rem insert new value;
&SuperSetidRec = CreateRecord(Record.DEPT_CONS_TBL);
&SuperSetidRec.GetField(Field.SETID).Value = &SuperSetidVal;
&SuperSetidRec.GetField(Field.DEPTID).Value = DEPT_TBL.DEPTID;
&SaveRtn = &SuperSetidRec.Save();
So, you know that there’s opportunities for improving your end-users’ ability to get meaningful information out of PeopleSoft, but it seems very daunting. We’ve had several discussions with organizations in the past 2 weeks, where they see the opportunity, but don’t know where to get started.
This is a bit of a different spin on the following blog entry.
Baby Step 1 – Ad-hoc Queries
The first step is to start leveraging PS/Query as a means for getting data out. Many organizations have already been taking advantage of PS/Query (I know of several situations where organizations have tens of thousands of queries).
If you’re not using Query, then I suggest your first step to be to centrally develop a small number of queries to provide answers to common questions and then secure them with object security to be read-only. PeopleTools 8.44 added the ability to run a read-only query, but not save it (which means your end-users can use them without being able to change or break them).
When you create these queries, you will want to try to make them as useful as possible to a wide range of users. This generally means:
- You will want to use prompts in the queries to allow end-users to use the same query to answer multiple questions. One neat trick on the prompt side is to put wild-card criteria in it (in other words, make it so that the criteria does something like this: WHERE ….. AND ( A.DEPTID = :1 OR ‘*’ = :1)
- You will want the query to include fields to answer as many questions as possible related to the prompts and data source as possible.
Baby Step 2 – Formatting the Queries
Now that you’re using PS/Query, the first thing your users will want to do is to change them, either by re-sorting the data, by applying additional filters, or merely by making the output look better.
Many of your more savvy users will run the Query to Excel and do this themselves. The first thing they will do is put an auto-filter on the data, so that they can use the query results as a data source for doing additional querying (using the auto-filter). They may also resize columns and apply other formatting to the data.
Unfortunately, this can be very cumbersome to do manually every time your end-users run a query. Therefore, they will very quickly ask whether there is a way to automate this. There are two options available to you:
- A tabular nVision report with an InstanceHook macro in it to format the data. This option was discussed in the following posting. The nice thing about a tabular nVision report is that it’s essentially a Query that is specifically designed and formatted, so it’s relatively simple to do.
- Our Excel Add-in product, which will take the results of any query and automatically format and add auto-filters to it. The nice thing about this approach is that any query your end-users run will automatically be usable in this way (whereas somebody has to build an nVision report and write the macro code to apply auto-filters in nVision).
Baby Step 3 – Linking Queries
As your users start getting used to running queries and formatting them, they’ll start wanting to include more and more information in the query results. This is a natural part of the process, because once they learn one thing, users will want to see information related to it.
When your organization reaches this milestone, you will want to be very careful. Many organizations start extending the queries by joining related data into their queries. This can lead to “Kitchen Sink” queries and can cause two issues:
- The query results start getting too large to understand. Now, the users have to start wading through the columns to find the data they’re interested in.
- The queries become difficult to develop and maintain. This is because as you join in disparate data sources, the SQL gets more complex, and you often start introducing cartesian products in your results that you have to find and troubleshoot.
- Performance issues start to crop up. As the SQL gets more complex, the database has to do more work.
The best approach for this is to find ways to link queries together. Often, this is called drilling. There are 3 main ways of linking your queries together:
- Modify the query to have hyperlinks in the result set. This is covered in the powerpoint and code attached following blog entry. This means you will have to pre-think and code this into the query.
- Believe it or not, our Excel Add-in product will allow you to drill from any Query you’re viewing in Excel, allowing you to link one query to another without having to write code or dirty up the output with hard-coded links to other queries.
- Utilize drilling in nVision. I will discuss this further in the next topic, because the core functionality of drilling in nVision is not exposed for query-style output (so we haven’t yet taken that baby-step in this blog entry).
Baby Step 4 – Aggregating information
Okay, now your end-users are getting information and drilling into related information. However, now they want to do some comparisons. How are they doing related to their budget? Has the average customer satisfaction been going up or down over time? Instead of seeing lists of information, now they want to start aggregating it.
The first and easiest step of this is to simply put subtotals into their queries (which can either be done manually by you or automatically with our Excel Add-in product). This will allow you to see counts and sums of your results broken out for each field you sort on.
The second step is to leverage PivotTables in Excel, which will allow you to do the analysis in a cross-tab format. Again, your users can do this using the Excel menus or you can automate this in a tabular nVision report and and InstanceHook macro (again the example is covered for pivot tables here).
Baby Step 5 – Aggregating and Comparing across different items
Although Subtotals and Pivot Tables on top of queries provides a lot of value to your users, you will quickly reach the point where your users will want more. As we discussed in Baby Step 3, your queries will have a targeted set of data (which means that the data available for your pivot tables is limited). What your users will want to do is to do comparisons between different items (such as comparing customer opportunities with customer satisfaction ratings).
The common thread for doing this type of anlaysis is that there’s a set of “attributes” (often called dimensions) that you want to compare “data” (often called metrics, KPIs, or facts) against. There are a couple of options available to you here.
- Matrix reports in nVision. nVision allows you to take different queries (or Ledgers) and organize the data by common attributes, such as trees, chartfields, or timespans. Because you already have experience with Queries (and because nVision is included with PeopleSoft), this is often a natural step to take. The following blog entry has examples of nVision matrix reports for different subject areas.
- A BI tool, such as OBIEE, Hyperion Essbase, Cognos PowerPlay, or Microstrategy. These tools require additional development effort to model the relationships and often to extract the data from PeopleSoft. However, if your organization has already standardized on one of these tools (and especially if you have a Dat a Warehousing group), you may be forced to go in this direction.
Although there are other aspects we could look at, I believe that this is the best place to start (especially since many other aspects are more difficult to accomplish in baby steps). Another benefit of this approach is that your learnings can be applied to alternative technologies (i.e. even if you end up building a data warehouse, building a set of nVision reports helps you identify the data you want, how to aggregate them, and what facts your users want prior to building your first ETL map).
Labels: excel, nVision, Query, Reporting
For those who are familiar with our demo and posting that discusses how to drill from a report into a page (blog posting here), you may or may not notice a limitation in what was presented. The example showed drilling from a financial report to the journal entry where the number came from. Unfortunately, drilling to the journal is just not granular enough to tell you exactly where the number came from (journals can have hundreds of lines, and a number in a financial report is governed by the chartfield values that are used).
This means that what you really want to do is to drill to items that are at scroll level 1 or greater in the page. Because the standard URLs to PeopleSoft pages are driven by the search records for those pages, you need to be able to (1) pass parameters to identify what values you want to navigate to, and (2) write code to do the navigation.
Sounds interesting, so how do you do it?
Well, the first part was answered in this posting on how to add parameters to your PeopleSoft pages.
The second part can be acoomplished in multiple ways (depending on the following):
- Whether the page or scroll items are read only.
- Whether the data to be navigated to is chunked by application code or by PeopleTools.
Using the SetCursorPos PeopleCode Function
The first approach we will discuss is using the SetCursorPos function. This works by iterating through the data in the component buffer until you find the row you want to be on, and setting the focus (or cursor position) to a field on that row. Because you can’t navigate to fields that are grayed out (or are read only), this only works when that occurs. Also, since you are navigating through what’s already in the component buffer, if the only loads a subset of the data at a time into the component buffer, then you may be navigating through a small part of the data you want to search. Navigating to a posted journal entry in PeopleTools is a perfect example of where both of these conditions would prevent this from occurring.
Here is an example of code you would use for a page with updatable data where the component buffer contains the full data set you want to search
Local Rowset &rsJrnlLines = GetLevel0().GetRow(1).GetRowset(Scroll.JRNL_LN);
Local number &j;
For &j = 1 To &rsJrnlLines.ActiveRowCount
Local Row &rowTest = &rsJrnlLines.GetRow(&j);End-If;
If &rowTest.GetRecord(Record.JRNL_LN).GetField(Field.ACCOUNT).Value = &sAcctNum Then
Adding a navigation element to the grid
If all the items in the scroll are read-only (or grayed out), then another option is to put a push button or other element in the grid that isn’t grayed out to set focus to. It’s actually as simple as that. You add the item, and then set the cursor position to it. Of course, this gets into customizing the page itself, which can be an issue at upgrade time.
Leverage selection code written into the page
This approach can be used very effectively in inquiry pages or even pages where there search logic is used written by application developers to populate the scroll. The journal line page is a great example of this. There’s a link in the Financials 8.9 journal entry page that allows you to enter search criteria for your journal lines. This page actually displays fields in the JRNL_PANELS_WRK record, which is in the componenet buffer for the page. By merrely setting the values of chartifelds in this work record and calling the adjust_line_scroll function, you can use parameters to restrict the set of journal lines displayed in the page (ultimately drilling to those values).
Here is the code to do that.
Declare Function adjust_line_scroll PeopleCode FUNCLIB_GL.JOURNAL_LINE FieldFormula;
/* Code to drill to row with account number passed in as a parameter */
Local string &sAcctNum = %Request.GetParameter("ACCOUNT");
If All(&sAcctNum) Then
- JRNL_HEADER.JRNL_HDR_STATUS = “P”Or
JRNL_HEADER.JRNL_HDR_STATUS = “U”Then
/* Journal is read only */
JRNL_PANELS_WRK.ACCOUNT = &sAcctNum;
If JRNL_HEADER.JRNL_HDR_STATUS = "D" Or
One last item of note: if there is already Page Activate PeopleCode, you will probably want to put yours at the end for the navigation (this ensures that all other logic has already been executed). The JOURNAL_ENTRY2_IE page is an example of this.
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).
Okay. I see, so why are you even covering it then?
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).
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:
- Open up Excel and navigate to the Visual Basic Editor
- Open up the object browser
- Use the menu Tools –> References menu
- 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
- Click OK to add it. If you get an error, re-browse to PSNVD.DLL and re-select the PS/nVision Type Library
- 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).
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.
Although I put together a previous posting on Drilling and nVision, I only included a single nVision drill layout to illustrate this. I already put together comprehensive packages to show:
- Reporting in HCM (Orig Posting, Demo, and Code)
- Reporing in Student Admin (Orig Posting, Demo, and Code)
Code for Financials Drilling to Pages and Queries (among other things).
I decided to put together a package for Reporting in Financials (Orig Posting and Code). As with the other packages, the code includes a working example with our nVision drilling product.
Labels: Drilling, nVision, Query
Over the past 24 hours, I’ve had lots of folks wanting to learn more about the HCM reporting examples in yesterday’s post. I decided to record a flash demo that shows how one would use the queries as well as the nVision reports (and drills).
In order to simplify the navigation in the demo, I did use the nVision Drilling Snap-on (which is separately licensable, but is not required to use the queries and nVision objects in the project). However, it does make it much easier to find and use them together.
Click here to watch the HCM Reporting in action…
Labels: Drilling, HCM, nVision, PeopleSoft, Query, Tree_Manager
After reviewing my last posting, I realized that although I covered the specifics of style definitions and nVision, I missed a critical set of information. You see, there are limitations in Excel as well as nVision that can easily be worked around if you understand the following:
The posting and formatting process nVision follows
Because nVision uses excel for storing results and for formatting, a basic understanding of the steps that are followed, and excel behavior will give you some insight as to the hooks available for formatting outside of the nVision stylesheet functionality discussed in the previous posting.
So, in a nutshell, here are the steps followed in nVision in running a report once it has retrieved the data to be included:
Step 1: Insert rows or columns in the worksheet
It inserts the appropriate number of rows or columns for the results of the nPlosion. When it does this, it calls the excel function to do this. This is important to know, because the standard excel behavior for an insert occurs. You may be thinking “yea… so…?”
So… This means you can leverage some of the cool things that excel does on an insert with respect to data ranges and formatting:
- If you insert a row or column in the middle of a range (either a named range, or a range that is used in a formula, chart, etc), the references in the range are automatically updated. This means that you can bind a chart to nPloded data without having to write a macro!
- If you insert a row or column in the middle of two cells that have the same formatting, the new cell in the new row or column is given the formatting as those around it. This means that if you apply conditional formatting to a cell in the row or column with the nPlosion specified as well as the one above it, all nPloded rows or columns will also have the conditional formatting applied (even though styles in Excel don’t support conditional formatting).
In other words, by paying attention to where nVision inserts rows or columns, you have additional control over the behavior of the results.
Step 2: Paste the data into the worksheet
This is done using some bulk APIs available in excel. Because it only pastes the values (and formulas for formula nPlosion), it does not override the formatting.
Step 3: Apply the styles
I’m not sure whether this is done prior to pasting the data, but it actually doesn’t matter because the paste data step doesn’t affect the formatting of it. The important point is that the default behavior of excel for inserting a row is applied prior to the step of applying the appropriate nVision styles .
One additional point is that excel styles are not all or nothing from a formatting perspective. In other words, you can define a style to only include the cell color and nothing else. This gives you a lot of flexibility, and also allows you to retain formatting in the original layout as needed. Because of this, you can think of the formatting as being applied in layers (where you can override part of what comes from a lower layter).
Putting this into practice
Although there are a couple of examples above as to how to put it into practice, I believe the following example does a pretty good job of putting it all together.
Imagine that you have a report that shows payroll costs, FTEs, and Percent over/under budget as three different colums. It nPlodes the total amount for a general manager into the department detail. Notice that the number formatting for each column should be different: currency for column 1, a number with no decimals for column 2, and percentage for column 3.
Because the nVision stylesheets do not support multiple amount styles for row nPlosion, the number formatting needs to come from the report layout, where the columns are created. Therefore, the report developer would need to do the following:
- Apply the number formatting for the different columns as needed in the nVision layout(one for dollars, one for percentage, etc.)
- Modify the amount styles (the ones that end in “A”) to exclude number formatting. You may want to save off a new stylesheet workbook for use in this type of report in the future.
- When you run the reports, the number formatting won’t be applied from the styles, while the other formatting, such as font size, border, and pattern will be applied.
So, you’ve created this really cool nVision report that gets all the data you want, but you can’t for the life of you figure out how to get it to format the way you want. You’ve gone through all the documentation, and have reached the point where you’re wondering where you can get a lamb to sacrifice to the gods of nVision. In order to save countless sheep out there, I’ve tried to describe how it works and what you should do.
In the beginning….
Originally, formatting in nVision was pretty straightforward. You would format the cells you wanted, and the formatting of the contents cells were used for any rows or columns inserted through nPlosion. Because the options for nPlosion were limited, there wasn’t much need to deal with it.
Along came tree nPlosion…
Then, we added the ability to nPlode multiple levels in a tree. This was great, because there was less hard-coding needed in reports, but it added an issue of how to format the different levels different ways. We needed to create an infrastructure to support this.
This infrastructure was created to allow formatting to be applied to rows or columns that nVision inserts due to the nPlosion feature (which shows supporting detail dynamically). It pulls together multiple elements in an effort to simplify the effort for applying these formatting rules.
- It understands the inherent structure of an nVision report, where there are
- Headings that precede a set of children rows or columns and label them.
- Labels that are used to describe rows or columns.
- Numeric amounts.
- Base formatting for rows or columns inserted, where the cells are not populated by nVision.
- It understands that results are generated at multiple levels of granularity, and that the different levels need specialized formatting rules.
- It leverages the formatting functionality delivered in excel, and allows the rules to be applied in combination with each other using Excel styles:
- Number formatting
- Indentation and other alignment features
- Font formatting, including size, name, and emphasis
- Outline formatting, such as that used to designate a total line.
- Background color.
In addition, an understanding of the way that nVision inserts rows and applies formatting allows customers additional flexibility in applying formatting, such as conditional formatting (which is not supported in style definitions in excel). Conditional formatting allows the data in the report to control the format (such as highlighting a row of data that falls outside an acceptable threshold).
It’s important to note that nVision styles are not applied to rows or columns that existed in the layout prior to running the report. They are only applied to rows inserted due to nPlosion.
More on nVision Stylesheets
In order to support defining different formatting for rows, columns, different levels of aggregation, and different types of fields in a report, nVision has implicit knowledge of specific style names in excel that have different meanings. In other words, when it comes time to apply styles to an nVision report, it looks for excels styles with specific names and applies them. This means that there are different style names for these different attributes.
Therefore, Excel styles that nVision utilizes has the following naming convention:
R (for row), or C (for Column)
Second and Third Characters:
The level number (00 = detail, 01 = first level, 02 = second level)
Fourth and Fifth Characters:
B (for Base), A (for Amount), L (for label), H (for heading)
This means that R01H is the formatting to be applied to the heading row for a row nPloded at the first level of the hierarchy.
In order to simplify the maintenance of these styles, spreadsheets are delivered with the styles populated and displayed. Customers can maintain the styles within these worksheets and then click a button to apply (or copy) the styles to a given nVision layout. After the style names have been copied to the layout, they can be modified directly in the layout without affecting other reports.
Labels: nVision, Tree_Manager