×
Tips and Techniques

Filtering nVision for date and quarter

By Larry Grey • November 11, 2005

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:

  1. The summary ledger method.
  2. 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:

Stay Updated

One Reply to “Filtering nVision for date and quarter”

  1. Hello

    I am trying to build one Query-Based Report Like this.
    Can you please Post a sample Query that is Built along the lines of Option 2 or Option 1.
    Or you can also email it to me at rajanala@gmail.com

    Thanks
    Ranjeet Rajanala

Comments are closed.

Request a Demo

Start your free demo

"Learn how you can reduce risk with rapid threat protection, audit response and access control. All from a single, comprehensive platform"

Trusted by hundreds of leading brands