[searchandfilter taxonomies="search"]

Application Engine Development Tips

Application Engine can be fairly handy in a PeopleSoft developer’s toolchest. Aside from all of the useful batch processing things that it can do, it can also be useful for providing ways of running PeopleCode against a system directly from within Application Designer. This can be used for things like testing some PeopleCode or providing some developer productivity utilities. David Bain and I used to do some presentations on developer productivity for PeopleSoft developers, and taking advantage of Application Engine was one of the tips that we used to always mention.

An example utility is the version control work that we’ve been doing for our products at Grey Sparling. When you have a project that needs to be checked into the source code control system, the project needs to be split up from one large file into a multitude of separate files (the exact number of objects that you have in your project). We have an App Engine program that does this for us, but we needed a way to specify the exact project name to the program. In regular App Engine programs running on a server, you’d have some page for entering run control parameters and the program would look at these. But when you run the App Engine program directly from within Application Designer, you don’t have those facilities available to you.

So what we end up doing is a couple of things. First, we take advantage of the COM integration in PeopleCode and use that to have Internet Explorer provide a prompt with the list of projects in the database.

When you have an Application Engine program open in Application Designer, you can press the traffic light icon or select Edit->Run Program from the menu. You’ll get a prompt like

this.

I always turn on the save to log checkbox, and then press Enter or click OK. Once the program starts, Internet Explorer pops up the list of projects in the database and lets you select one. The project name that you select is then used by the rest of the Application Engine program to do it’s work.

Here’s what the code looks like:

SCROLL BOX

The IEPrompt function takes a title and a label and an array of choices and returns back the selected choice. We use a hidden form field as a flag for when the user has made their choice since we can’t actually catch COM events from within PeopleCode. The HTML that we generate from PeopleCode is not super fancy, but it gets the job done.

The other functions in the code are to assemble the list of projects from the database into a PeopleCode array and then to put the entire thing together.

How about if you wanted to supply the parameter yourself without getting prompted? Maybe you, as the developer, want to run this AE program as part of a bigger script. The answer is to just pass the parameter on the command line and use a little PeopleCode to parse out the values.

In order to do that, I ported over this C# based command line parser from The Code Project. It was easiest to port by utilizing Java from PeopleCode.

SCROLL BOX

The C# regular expression classes are fairly similar to what is available in Java. The only minor headache was that PeopleTools was having problems looking up one of the Java methods used (which we’ve seen before in previous blog entries), but that was fairly straightforward to get around. The workaround is to repeatedly compile one of the regular expressions instead of just once, but in this particular usage scenario, the overhead of that is so negligible that we don’t care.

As a side note : native regular expression support was added the 1.4 version Java Runtime Environment, so if you’re on an older version then you’d need to look at some extra libraries for adding regular expression support, or this code won’t work.

Labels: PeopleSoft

Put the Appsian Security Platform to the Test

Schedule Your Demonstration and see how the Appsian Security Platform can be tailored to your organization’s unique objectives

Deeper dive explanation of Siebel Analytics by Mark Rittman

In my previous blog entry on nVision and Siebel Analytics, I shamelessly embedded a couple of screenshots from Mark’s Blog.

This morning, he posted an in-depth explanation of the platform and how it works. For those who want a look into the crystal ball showing the foundation of application reporting in Fusion applications, this posting is a must-read.

Labels: Fusion

Put the Appsian Security Platform to the Test

Schedule Your Demonstration and see how the Appsian Security Platform can be tailored to your organization’s unique objectives

PeopleSoft Single Signon

(Sept. 20 update: since writing this we have created a Desktop Single Signon snap-on product that works with PeopleSoft Enterprise. Here’s the announcement and here is the product page).

Single signon is widely desired, yet not widely understood. As usual with PeopleSoft, there isn’t one simple answer, but the good news is that it’s not that hard to get what you want. The biggest challenges are political rather than technical.

So, let’s start by listing a few of the different common definitions of single signon. What most people mean (and want) is that a user signs on once in the morning and is then granted access to all other applications based on that signon. No additional login screens, etc.

Another common definition is that there is only one place for a user to authenticate with. No need to remember 17 different passwords from systems that have different rules about how often to change the password and how long it has to be. The drawback here is that the user still has to authenticate for each system that they access. I’ll refer to this style as “single password”.

Note that I use the word “authenticate” rather than saying “fill in their username and password”. Although most environments are based on username and passwords, the best run environments go beyond just username/password in order to validate the user (think SecureID token).

One interesting wrinkle to all of this is somewhat PeopleSoft specific. PeopleSoft supports a notion of single signon between PeopleSoft instances.

If you have PeopleSoft HR, Financials, CRM, and EPM, then you actually have four different environments, not just four different product lines in one environment. There are some advantages to this loosely coupled model, but unified administration wasn’t one of them. We actually made some progress at this at PeopleSoft towards the end, but it still never got to be as simple as administering one large system.

Given those four separate environments, PeopleSoft supported single signon between them. If a user logged into, say, Financials and then followed a link to the HR system they would not have to signon again. You do need to configure each system to trust each other (you don’t want someone with access to a demo CRM system to be able to access your production Financials), but that is not difficult at all. PeopleBooks has good information on how to do this.

Note that the word LDAP has not yet been mentioned. LDAP is just a common place for storing user information (such as their password, their email address, etc.). By itself, it doesn’t provide single signon. It only simplifies getting single signon working by having a standards-based common location for storing user credentials.

We made some big bets on LDAP support in PeopleSoft 8. When that came out back in 2000, there weren’t really too many enterprise application vendors that supported LDAP. Of course, all of our customers in Higher Education had been telling us to do this for years (especially the University of Michigan). We even had fantasies about dropping our internal authentication support and using LDAP as the out of the box authentication mechanism for PeopleTools 9.

One problem that we had though was that when our field was trying to explain to other customers how this stuff worked, that the concept of single signon and LDAP got confused. Even to the point where the single signon section in PeopleBooks had to be changed to explain that they are not the same thing.

So, out of the box, you can get support for “single password” from the desktop level if your desktop signon uses a backend that supports LDAP (such as Microsoft Active Directory). The first time that the user accesses PeopleSoft they get prompted for their password again, but then (via the PeopleSoft single signon) the user can access all of your PeopleSoft systems.

If you want to go beyond this and have desktop level single signon, then you’ll need to do some customization. A common way of doing this is to have a Windows server running IIS that acts as a proxy server to PeopleSoft. You setup IIS to use NTLM authentication for the proxy link, which will cause Internet Explorer to send in the user’s desktop signon information. Then you create a little bit of signon PeopleCode that will check the custom HTTP header that IIS will attach to the request with the user’s domain and login ID.

If you do this make ABSOLUTELY sure that you validate requests with this header come through the IIS server. Otherwise you’ve just opened up access to your entire PeopleSoft system to anyone that knows how to create an HTTP request with a custom header (which is painfully easy). This is because the IIS server just passes back the domain and username, but does not cryptographically secure it.

The nice thing is that this is not just limited to Internet Explorer. Recent versions of Mozilla based browsers (Mozilla 1.6+, Netscape 7.2+, Firefox 0.8+) also have support for Microsoft’s NTLM protocol. If the user is on a different platform than Windows, then their desktop signon won’t be passed along, but at least they won’t be locked out.

If you want to do this type of Windows desktop single signon, but don’t want/can’t have an IIS proxy server, then you’ll want to look at using jCIFS for that.

How about if you don’t want to use the Windows login as the basis for desktop single signon. Is that even possible with PeopleSoft applications?

Sure. It takes a little bit more work, but it’s possible. You’d have to install something locally on the client machine that get the user’s credentials once, and then passes that along to somewhere where the PeopleSoft server can validate it. Either by passing it along in the browser headers or some other way. If you’re interested in this, take a look at Steve Friedl’s Illustrated Guide to SSH Forwarding. Using SSH as a mechanism for desktop single signon for PeopleSoft applications strikes me as an interesting idea.

Well, there’s more to be said on this topic, but this has been sitting in the queue for too long, so I’m publishing what I’ve got. Please comment if you’re interested in hearing more (as well as what you’d like to hear).

Labels: Products, Security

Put the Appsian Security Platform to the Test

Schedule Your Demonstration and see how the Appsian Security Platform can be tailored to your organization’s unique objectives

Java and PeopleCode Tips and Tricks – Part 1

Since Java is the language of choice for the Oracle Fusion applications, I thought it would be nice to have some posts that show some good tips and tricks related to using Java within PeopleSoft today. As I mentioned in the previous post there are a few quirks in the way that Java and PeopleCode work together. Well, as Bill Cosby once said, “I told you that story so that I could tell you this one” 🙂

The quirk that we’ll cover today has to do with the way that the mapping between PeopleCode and Java datatypes works. I have a few quirks that I’ve known about for awhile, but I got bitten by this one just recently while working on a follow on to a previous blog entry about version control and PeopleTools. The idea was to show an example of using the JavaSVN java library from PeopleCode to be able to place application data under version control (application setup/configuration data, not something like Ledger entries).

The library works great directly from within Java code, but I hit some strange behaviour when trying it from PeopleCode, and it turned out that the fault is in the way that PeopleCode was passing Null into the Java side.

To simplify things, imagine that you have the following Java class that exposes these static methods.

package com.greysparling.demo;
public class JavaPeopleCode1 {

public static boolean IsObjectNull(Object test1) { if (test1 == null) { return true; } else { return false; } }

public static boolean IsStringNull(String test2) { if (test2 == null) { return true; } else { return false; } }

}

You’d think that these methods would each return True when you pass a Null from PeopleCode into them and False otherwise. When we call these from a short AppEngine program (side note: using AE is a great way to test out these sorts of quick test things) we see otherwise.

Local String &sClassName, &sPeopleCodeString;
Local JavaObject &demo;
&sClassName = “com.greysparling.demo.JavaPeopleCode1”; &demo = GetJavaClass(&sClassName); &sPeopleCodeString = “Testing”;

Warning (&demo.IsObjectNull(&sPeopleCodeString)); Warning (&demo.IsObjectNull( Null));

Warning (&demo.IsStringNull(&sPeopleCodeString)); Warning (&demo.IsStringNull( Null));

Here’s the output that we get from running this (miscellaneous junk from the log has been trimmed out).

False
True
False
False

Notice that last one? We would have expected that to return True since we’re passing Null. It turns out that any object type that PeopleCode has a direct mapping for (such as a PeopleCode string with java.lang.String), you can’t pass null. Or more accurately, if you pass Null from the PeopleCode side, you won’t actually get null on the Java side.

Annoying eh?

The workaround for this is to create some additional Java glue code and call that from the PeopleCode side.

Labels: PeopleCode

Put the Appsian Security Platform to the Test

Schedule Your Demonstration and see how the Appsian Security Platform can be tailored to your organization’s unique objectives

Dynamic PeopleSoft Security Based on Login

(update : see ERP Firewall for PeopleSoft)

A few years back I had the opportunity to help out with an interesting customer requirement for dynamic security based on where the user is logging in from. Kirk Chan (another ex-PSFT person) and I got to spend quite a bit of time working with the customer on this. In the end, it was quite successful. In fact, they even presented on this at one of the PeopleSoft Connect user conferences.

The customer was rolling out PeopleSoft eBenefits to tens of thousands employees, a large number of whom didn’t have access to the corporate network except over the public internet. The challenge was that they didn’t want to take any chance that, for example, the VP of HR logs in from a kiosk machine somewhere to do his or her benefits, gets their password swiped, and then someone has access to all of the HR system. So anyone logging in over the internet was to only get the needed privileges for accessing the eBenefits pages, and nothing else. If they logged in from the internal network, then they would receive their usual access level.

Making matters more complicated was that they were about 30 days from their code freeze cut-off date for what would be rolled out. The benefits open enrollment period wasn’t something that can be changed just for dealing with code changes. If this couldn’t be implemented, then they would have to go back to doing things the old way (where most employees would fill in their info on paper). That would be pretty expensive.

Another complication was that their IT group was definitely in the “show me” mood. Later on, we were able to speculate why they didn’t have much confidence in their software vendors at that moment 🙂

So, how’d it work?

The key to this was some custom signon PeopleCode, a couple of extra tables for storing some additional data and a couple of modifications to the one of the delivered security maintenance pages. We got lucky in that they had an ex-PSFT person onsite doing some consulting who helped with the actual implementation of the code for them (Hi Maverick!). What I’m about to describe is actually simpler than what they did because of some additional requirements of theirs (users being able to opt out completely from their account being exposed over the Internet) that I won’t go into in any detail here.

(update: see this blog entry for more things to consider in looking at this customization)

What the signon PeopleCode did was to check which servers they were coming through (this is accessible in the %Request object) and then populate the PSROLEUSER table for them based on that.

The PSROLEUSER table is the table that PeopleTools uses to know what roles a user has. All of the delivered dynamic role stuff (sourcing roles via Query, or LDAP or a custom PeopleCode program) all populate this table.

This is important to understand – if there isn’t an entry in this table for a given user/role combination, then the user does NOT have that role. At runtime this is the only place that is checked for roles. The Internet Architecture does not run any queries or access LDAP servers to determine the roles, they always come from the PSROLEUSER table. You can test this out by deleting rows from this table in a demo system while you are logged in. You will lose the role(s) immediately. Unfortunately the internal PeopleTools code for handling roles that change in mid-session is really bad from a usability perspective – it’s not graceful at all).

Once we know which server the user is coming through, the signon PeopleCode would flush the existing list of roles for that user, and then re-populate it from a shadow table that was created. The shadow table was essentially a clone of PSROLEUSER, but it served to act as the “master” list of the overall set of roles that a user had. The signon PeopleCode would filter this list based on what roles were deemed appropriate for the server that the user was logging in through.

In order to keep this shadow table up to date, the delivered page for maintaining user/role combinations had to be updated to store it’s changes into the shadow table instead of PSROLEUSER. The delivered programs that source role info from Query and/or LDAP also needed small changes to write into the shadow table and not PSROLEUSER.

In a nutshell this work splits out the notion of overall roles and current roles. With the delivered PeopleSoft configuration, these two are tightly linked together. Fixing this was one of the things that was likely for PeopleTools 9.

(note: there are some other ways to do this without changing the delivered objects, but they get a little tricky and the customer didn’t care about the slight bit of extra maintenance in exchange for this functionality).

This particular problem was very focused on the notion of dynamic security based upon whether someone was logging in from inside the firewall or not, but it would be easy to extend it to other scenarios, such as the time someone was logging in. An example might be a student intern in the HR department only gets HR system access during their assigned intern hours, but they’re given access to the Student Admin pages 24/7.

Another scenario would be how the user was authenticated. If someone logs in using a SecurID token, you might grant them a higher level of access than if they only used a username and password.

There’s another scenario where this technique could be applied, but it’s probably worth it’s own blog post because there’s a couple of different ways of solving it.

Labels: PeopleCode, Security

Put the Appsian Security Platform to the Test

Schedule Your Demonstration and see how the Appsian Security Platform can be tailored to your organization’s unique objectives

Follow-on to styles and nVision

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.

Labels: nVision, Tree_Manager

Put the Appsian Security Platform to the Test

Schedule Your Demonstration and see how the Appsian Security Platform can be tailored to your organization’s unique objectives

Using Styles and nVision

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.

Put the Appsian Security Platform to the Test

Schedule Your Demonstration and see how the Appsian Security Platform can be tailored to your organization’s unique objectives

Materialized Views and Row Level Security

While consulting at the same customer in NYC, we were looking at performance issues related to implementing row level security.

As with many customers who implement row level security with PeopleSoft products, there is a relatively complex join between a set of tables that map users to the data values they have access to, and finally to the data table (i.e. Ledger table in this circumstance). Because the data table has a lot of values, and because the joins to it are complex, performance is often a significant issue.

Thinking back to PeopleTools 9

One of the features planned for PeopleTools 9 was definitional row level security. We planned to provide a centralized means of defining the rules that drove how sets of data values mapped to sets of users (including support for trees). Because we were trying to minimize the administrative effort required by organizations who were implementing this type of security, the logic required to evaluate a rule could be relatively time consuming if it were executed every time a user tried to access data.

Therefore, we decided that a good trade-off was to create a security staging table that was optimized for joining to the data table. This staging table would identify all the individual values a user or user group had access to, allowing an equality join to be done to the data table. The security staging table would be populated periodically by evaulating the rules that drove the mapping of the users to the data values.

Applying the design concept here This customer had already created a set of tables and UI for administering those rules. However, in order to identify which rows of data a user should see in the ledger table, a relatively complex set of joins and unions needed to occur. As we looked at the potential performance implications of this, we recognized that evaluating the rule at the same time data is selected from the table would cause serious issues.

Because this customer was running on Oracle, we were able to use the database to create the staged table using a materialized view. Depending on the complexity of the SQL needed to get the appropriate data, the view can either be updated incrementally, or not. In this circumstance, the SQL required two unions (the first select got the data for the first chartfield, the secon select got data for an alternative chartfield, and the third got data for blank values). This meant that the refresh of the materialized view needed to occur on a pre-defined schedule or be triggered. In this circumstance, the customer created a trigger on each of the tables used in the view (OPRCLASS, the Custom mapping table, and each of the chartfields referenced).

One important note was that the materialized view did NOT include the join to the data table (i.e. LEDGER table). The Ledger reporting view was used instead. This is important, because of the following factors:

The LEDGER table changes frequently, which means that the refresh would have to be run each time data in the ledger table is modified.
Materializing the join between the ledger table and the security table would physically store the combination of each user and each row of data that user has access to (in other words, much of the ledger table would be replicated for each user… although disk is cheap, it’s not that cheap.)

The Grey Sparling approach to Data Security

Although this is the method used in PeopleSoft to support row level security out of the box, it does have significant drawbacks. This is why we developed our own reporting security product.

Keep in mind that the PeopleSoft approach to row level security is to hide the security rule from the application and to filter on OPRID, OPRCLASS, or ROWSECCLASS when it finds it in the record definition. This means the following:

The criteria used to filter the data is hidden from the report. This is a big no-no from an auditing and compliance perspective, because you cannot easily figure out whether the data was missing in a report because security was applied, or whether the data simply didn’t exist.
The filtering of the data is convoluted. In other words, instead of filtering the data directly against the data table, multiple joins are required to do the filtering.
It is not trivial to apply the security rules to multiple data tables in the application. In each circumstance, either the record definition must be modified (for PS/Query), or a view joining the data table to the security rules must be created (for PS/nVision or PS/Query).

Our solution is to provide a place where the security rule can be defined in a manner optimal for the administrator, and then apply the rule directly to the report in the reporting tool. This means that the performance from row level security is better, it is easy to see how the data in the report had been filtered, and the security is applied consistently across different ledgers or tables without requiring app designer access or DBA access to create views. The secret sauce to this is how we apply the rule to the report without allowing the person running the it to be able to modify it. If you want to find out how we do that, feel free to contact me to get an NDA, I’ll add you to the Grey Sparling SPAM engine (just kidding), and we’ll talk…

Of course, I’m sure many of you are thinking “Well, if this is so great, why didn’t this customer buy the product instead of using this technique?” The answer comes down to timing. If our product existed before they did the analysis, design, development, and testing, they may have used it. Because we were looking primarily at the performance implications of using something that was about ready to be moved to production, it made more sense to look at performance as a stand-alone item (instead of revisiting the decisions already made). It’s entirely possible that there may be a future posting about how this customer purchased and implemented the Grey Sparling Report Compliance, Security and Distribution Extender.

Labels: nVision, Security

Put the Appsian Security Platform to the Test

Schedule Your Demonstration and see how the Appsian Security Platform can be tailored to your organization’s unique objectives

Filtering nVision for date and quarter

This week, one of our blog readers asked us for advice on filtering his/her nVision reports with respect to dates.

All of you nVision experts are already thinking “timespan”, and you’d be right. However, based on the needs of this customer, the answer wasn’t quite as simple as that.

So, why do I need to span my time?

First, some background for those who aren’t quite sure about timespans. Timespans were created for General Ledger, to allow reports and other processing to more intelligently react to changes in time. Because time is managed in General Ledger by accounting period, Timespans is all about how to pick the accounting periods to use in a report or process.

Taking from the concept introduced by effective dating in other areas of PeopleSoft (where you can look at the state of a system as of any point in time and determine what was current), the people who designed timespans realized that it would be valuable to use as of dates for picking data in reports (to allow you to see what a report looked like at a point in time… for example, run the profit and loss as of 3 months ago).

Therefore, timespans are evaluated based on as of date (just as effective dates are evaluated based on an as of date). When supplying an as of date, you can then determine what period or periods can be used. It also allows you to define which part of the date is evaluated when picking the starting period or ending period in a range. This allows the creation of timespans, such as year to date, current period, current period last year.

Because timespans were built for general ledger for the sole purpose of evaluating accounting periods, timespans are tied to the calendar (which is what defines what accounting periods are), and Ledgers in General Ledger have a calendar as a property of them.

Sounds straightforward, so what are the issues?

Very good question. The issues that customers hit are related to the fact that the creators of timespans didn’t envision other uses for them.

They are bound to General Ledger. Unless you are reporting against a Ledger, there is no way of finding a calendar to use, which means that the tool does not know how to evaluate a timespan.
They are bound to Accounting Periods. There are many circumstances where you want to filter on dates that aren’t accounting periods.
Month and year are the only two levels of granularity in which you can create timespans in a detail ledger. This means that quarterly timespans are problematic.

Quarter To Date Processing

So, tell me why again that quarter to date processing is problematic?

The reason is that timespans are built by determining how to pick a starting period/year and an ending period/year. Here are two examples that work well:

YTD has a starting period of 1 (or 0 if balance sheet accounts) / and year of the as-of-date, and an ending period of period of the as-of-date / and year of the as-of-date.

Current period has a starting period of the current period of the as-of-date / current year of the as-of-date and an ending period of the current period of the as-of-date / current year of the as-of-date.

Now, current quarter is much more complex to calculate, because the beginning period is 1 if the as of date is <= march 31, or 4 if the as-of-date is between April 1 and June 30, etc.

So, how do you deal with it? There are two ways I know of:

The summary ledger method.
The nVision method.

Summary Ledger method

So, let’s start with the summary ledger method. At first, this method may seem overkill. I mean, isn’t summary ledgers there for performance? Why would there be functionality there to help me?

Well, the answer to this question gets to how year to date balances are calculated in GL (which is how you always report balance sheet accounts). Instead of storing balances for each period in balance sheet accounts, PeopleSoft stores the incremental period changes. This allows the posting process to use the same logic for any type of account, but means that the reporting tool must sum all the periods to get a balance. As you can imagine, this can be pretty expensive to get a december balance. Now, imagine that you’re a bank with 365 periods (i.e. daily balances). Now you’re really talking expensive SQL.

Therefore, one of the features in summary ledgers is to roll groups of periods into summary calendars for storing. This is where the magic comes in. Simply create a summary calendar with 3 detail periods rolling into 1 summary period, and current period in the summary calender = current quarter. Also, Quarter to Date becomes the same timespan as year to date on the detail calendar. Finally, if you nPlode within the summary calendar, you can show a quarterly breakout through the current quarter.

WHOOOHOOO!!!

So, you say you don’t want to build summary ledgers?

Well, we can help you too. This is where nVision comes in. You begin by creating a timespan for each quarter (Q1, Q2, Q3, Q4), each with the specific periods in them (Q1 = period 1 – period 3 of current year, etc.). When you build the nVision report, you include columns for each of the quarters. Then, if you want to hide the columns you don’t want, you use an instance hook macro to determine which quarter you’re currently in (most likely useing the %ASD% variable), and then hiding them.

For those who missed the instancehook posting, here it is: https://appsian.wpengine.com/embedding-macros-into-nvision-reports

Filtering against non-ledger data sources

Adding timespan support to other data sources was to be a big feature in release 9 (ah, but the other Larry said it was not to be so). On the drawing board was a feature called “data objects”, which would have allowed us to map calendars to either date fields or to period fields. Timespans would have come along for the ride, and we would have been able to leverage them in searches, pages, batch programs, as well as reports. Being able to pick timespan names to filter dates for searches would have dramatically improved usability for call center alone (show me all cases for this product issue for the current day, month, quarter, year). God, it would have been beautiful!

Unfortunately, the best way to filter dates for non-ledger reporting (i.e. query-based nVision reporting), is to build it into the query. This is one of the reasons why we felt that this was an important feature in our nVision PeopleSoft Solutions Extender product.

Because you can apply other criteria and labeling outside of the data source in nVision (and have them applied across the other axis), there are a few options for deploying this way.

Option 1

The first, and simplest to deploy is to create a query for each data source for each timeframe you want to report against. In most circumstances, you’ll want to have a table that is used to track the current date (and this table will be joined to your data table for the date filtering you need). If you are using Financials, your business unit table has this field already in it. If you need period processing similar to that done with calendars in GL, you may want to add a from and to date to this table and use PeopleCode to set the values to bound your periods.

So, if you want to have a current period, current quarter, and year to date column on a report, you will probably want to create a query that gives you each and put each query in a different column. You can then apply your row nPlosion and criteria as well as your worksheet criteria to generate the other pieces of the report.

Option 2

Option 2 extends the part of option 1 that has a table for administering the current date. Because nVision allows you to apply criteria outside of the query, it is possible to use a view to pro vide a generated field with values that can be filtered on. This, however, is dependent on your database having the ability to create a view with a union in it (sorry, DB2 customers).

Basically, what you would do is create a view with a select for each timespan you want to expose (PER, QTD, YTD). In essence, instead of having multiple queries in option 1, you would have a view with the same number of selects with a column that identifies which select it is.

In nVision, you would specify criteria for that pseudo-field as row, column, or worksheet criteria. Because you are only ever getting data from one of the selects in the union, performance should not be as bad as you might think.

One final note, is that you will also have to create a valid value table for this new field, so that nVision will allow you to pick the values you need.

Labels: nVision

Put the Appsian Security Platform to the Test

Schedule Your Demonstration and see how the Appsian Security Platform can be tailored to your organization’s unique objectives

Request a Demo

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.

Necessary

Always Enabled

Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.

Cookie	Duration	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

Functional

Performance

Analytics

Others

Platforms

Features

Register for an Upcoming Webinar

SAP

PeopleSoft

Appsian360

Review

Webinars

Persona

Register for an Upcoming Webinar

Customers

Video Testimonials

Featured Case Studies

Why Appsian

Management Team

Careers

Press Releases

Partners

Contact Us

Customers

Partners

Home / Blog / Application Engine Development Tips

Application Engine Development Tips

Related Articles.

Put the Appsian Security Platform to the Test

Home / Blog / Deeper dive explanation of Siebel Analytics by Mark Rittman

Deeper dive explanation of Siebel Analytics by Mark Rittman

Related Articles.

Put the Appsian Security Platform to the Test

Home / Blog / PeopleSoft Single Signon

PeopleSoft Single Signon

Related Articles.

Put the Appsian Security Platform to the Test

Home / Blog / Java and PeopleCode Tips and Tricks – Part 1

Java and PeopleCode Tips and Tricks – Part 1

Related Articles.

Put the Appsian Security Platform to the Test

Home / Blog / Dynamic PeopleSoft Security Based on Login

Dynamic PeopleSoft Security Based on Login

Related Articles.

Put the Appsian Security Platform to the Test

Home / Blog / Follow-on to styles and nVision

Follow-on to styles and nVision

Related Articles.

Put the Appsian Security Platform to the Test

Home / Blog / Using Styles and nVision

Using Styles and nVision

Related Articles.

Put the Appsian Security Platform to the Test

Home / Blog / Materialized Views and Row Level Security

Materialized Views and Row Level Security

Thinking back to PeopleTools 9

Related Articles.

Put the Appsian Security Platform to the Test

Home / Blog / Filtering nVision for date and quarter

Filtering nVision for date and quarter

So, why do I need to span my time?

Sounds straightforward, so what are the issues?

So, tell me why again that quarter to date processing is problematic?

So, how do you deal with it? There are two ways I know of:

So, you say you don’t want to build summary ledgers?

Option 1

Option 2

Related Articles.

Put the Appsian Security Platform to the Test

US Office

India Office

Start your free demo

Register
for an Upcoming
Webinar

Register
for an Upcoming
Webinar