nVisionGuru

Enlightened PeopleSoft Reporting

Confined to Quarters

Sep 3

Posted by toma79 in Uncategorized | No Comments

The problem with blogs is they have this date stamp on the posts so it is immediately apparent that the author hasn’t posted anything for, say, 3 months.  Maybe we could call it ‘Quarterly Reporting’.

Coincidentally, when we left off we were talking about dates and problems that might arise in PeopleSoft reporting.  Maybe not problems.  Maybe we should call them “misunderstandings.”  And quarterly reporting produces more than a few misunderstandings.  It’s not that nVision can’t report three-month’s activity; it’s just important to know which three months we’re talking about.

Quarterly reporting is troublesome because – as far as PeopleSoft logic is concerned – quarters are not periods.  They are a set of three periods and how you define the beginning and end of that set has a big impact on the data contained within.

So let’s say you create a TimeSpan that is relative to the report date and contains the current period and three previous periods.  That would be a quarter but it would be a rolling quarter, not one defined by the usual calendar reporting dates.  This also runs into logic problems when you run the report for January or February (or whatever are the first two months in your fiscal calendar) and there are no prior quarters in the defined fiscal year.  nVision doesn’t just know to go back to the last months of the previous fiscal year.

You could also create four absolute quarters in TimeSpans, comprising periods 1-3, 4-6, 7-9, and 10-12.  We could call them QTR1, QTR2 ,etc.  The problem with this approach is cutoff: if you’re running reports for August but you’re halfway through September when the report is run, it will pick up transactions posted for September in QTR3, even if the report date is 08/31.  An absolute TimeSpan is not affected by the report date, remember?

Another solution – and the one I’ve used most – is to make Quarter-to-Date TimeSpans.  These have an absolute beginning period – 1, 4, 7, and 10 – but the end period is relative to report date.  Thus QTD2 begins with period 4 but stretches on to the report date. Thus if I have a report date of 05/31 the TimeSpan will include April and May but not June.  On the other hand, if my report date is 07/31, my QTD2 column will still include April through June, and July as well.

One solution to that quandary in a standard layout is to embed four QTD TimeSpans in hidden columns and use formulas to retrieve the correct quarter’s data based on the report date.  What is appealing about this approach is that if an intermediate Excel or nVision user needs to troubleshoot or modify the layout it’s fairly easy to see what’s going on.

Visual Basic macros in nVision layouts could be used to trigger other criteria for quarterly reporting as well.  Are you using VBA to modify your layouts for quarterly reporting?  Tell us about it in the comments!

Post to Twitter Tweet This Post

Tags: , , ,

A Wrinkle in TimeSpans

May 18

Posted by toma79 in Uncategorized | No Comments

Last post I was pointing out that the Ledger table in PeopleSoft has no date fields, only Fiscal Year and Accounting Period.  But nVision reports don’t even use those fields — they add yet another layer: TimeSpans.  I know, crazy, isn’t it?  But it all makes sense, I swear.

Let’s we’re building a suite of income statements for internal reporting at a company that reports to its investors on a quarterly basis.  That company might be publicly traded but not necessarily – lots of investors like to see quarterly reports.  Let’s say we have three different versions of our Budget/Actual reports – a current period, year-to-date, and quarter-to-date.  Seems perfectly reasonable.

If we were to use Fiscal Years and Accounting Periods to define the time spans those reports we would have to update the report template every period, which kind of makes it not really a template.

To grab time spans in an nVision report layout we use *surprise!* TimeSpans (you can tell it’s a PeopleSoft term because the first and some middle letter are capitalized), which are already defined groupings of Accounting Periods and Fiscal Years.  For the current period and year-to-date reports above we would use the delivered spans “PER” and “YTD”.  QTD is going to be another matter entirely so we’ll address that later.

Definition for TimeSpan QTR1

The illustration at left (which you will need to click to enlarge) shows the setup of a delivered TimeSpan called QTR1.  What we know about the first quarter is that it starts with period 1 of the current year and that it ends with period 3, assuming we are basing it on a monthly calendar.

In other words, the beginning and ending Accounting Periods for QTR1 are Absolute.  No matter what the as-of date for our report QTR1 will always span periods 1 to 3.  The Fiscal Year, on the other hand is Relative; that is, it will be entirely dependent on the as-of date for the report.

Relative TimeSpan references work like this: the current year or period is 0.  Prior is -1 and next is 1.  So if we wanted to use a Fiscal Year two earlier than the year of the as-of date we would put -2 in the Relative values for Fiscal Year.

Combining absolute and relative values lets us create expanding TimeSpans.  YTD is a good example of this, since the beginning Accounting Period is Absolute 1 but the ending Accounting Period would be Relative 0 (i.e., the same period as the as-of date).  Thus the TimeSpan “stretches” as the year goes on.

That might lead us to believe we could easily create a Quarter-To-Date span.  Why this is not so easy will be the topic next time!

Post to Twitter Tweet This Post

Tags: , , , ,

A Funny Thing Happened on the Way to the Ledger

Apr 29

Posted by toma79 in Uncategorized | No Comments

The first place most users attempt to create or modify an nVision report is in General Ledger, usually with reports run against the LEDGER table in PeopleSoft.  Now maybe you’ve done that and you didn’t even notice it.  Because PeopleSoft wrote nVision to go against that table first of all, that’s where it looks first, without being obvious about it.

Now, the LEDGER table is a funny beast – it doesn’t work the way one might think, especially with dates.  Timing is a core principle of accounting and JOURNAL_DATE is a key field for journal tables.  Why doesn’t LEDGER have dates?  Well dates are so tediously detailed and LEDGER just doesn’t have room for all that detail.  LEDGER is all about the Accounting Period, usually a calendar month. Thus JOURNAL_DATEs are translated into Accounting Period + Fiscal Year and the journal amount is added or subtracted into the running total for that Period/Year within any given Business Unit ChartField combination.

This is likely all review but I am going somewhere with it:  That lack of a real date field in LEDGER probably causes more confusion about nVision reporting than any other factor, even among people who already know the stuff above.

For example, you create a report – let’s say an Income Statement.  And it has a YTD (year to date) column and a PER (current period) column.  To make the report relevant you probably would use a variable somewhere in the header reflecting the As-of date for the report.  Reasonably, that variable is often %ASD%, which retrieves the As-of Date, the date entered in the Report Request page by whomever ran the report.

But let’s say a less savvy user, maybe one whose title starts with a capital C, tries running that report and that person wants to see where things stood as of the 15th of the month.  nVision is querying the LEDGER table, which doesn’t know from the 15th or any other day of the month.  That user is going to get the whole month, or whatever is posted so far anyway.  But the header will show the 15th as the As-of date of the report.  Misunderstandings ensue.  Heads may roll.

You could show the user an explanation like the one I gave above and hope they remember but I prefer to head the crisis off by never EVER putting %ASD% in the header of a report.  Instead I use %PED%, or Period-End Date for any and all ledger report headers.  And I make it bold and prominent – so even if somebody forgets and uses the middle of the month as their As-of date, the report correctly states that it is as of the Period End date, that is, the total that is stored in the LEDGER table.

More on date confusion later…

Post to Twitter Tweet This Post

Tags: , ,

Welcome to the new nVisionGuru.com

Apr 29

Posted by toma79 in Uncategorized | No Comments

This is the revival of a domain I didn’t think I’d be using again.  I established the name nvisionguru.com and a small site in the late 90s but found it hard to do much with it all by myself.  When Oracle bought PeopleSoft I believed, as a lot of customers and consultants did, that the applications and tools of the acquired company would soon disappear or languish.  So I let the domain expire and retired the site.

Well, who knew that nVision would continue to thrive into 2010?  Its demise has been overstated more than once.  Even at last week’s Collaborate conference in Las Vegas, Oracle Senior PeopleSoft Product Manager Amira Morcos had to reiterate that “nVision is not going away,” although staffing indicates it’s not being developed much either.  I’m not sure how much that matters in the near term —   PeopleSoft users, especially in the General Ledger department, really love their nVision.  It’s a tool that, at least in its basics, accountants and financial analysts can get their heads around.  ‘Cause, hey – It’s Excel!

Aside from the persistence of nVision as a popular tool, Web 2.0 technology such as WordPress makes it so much easier to post content and share ideas.  And that inspired me to bring back nVisionGuru not as a soapbox for my own ideas (well, okay, partly that) but as a clearinghouse for questions, ideas, best practices and news around nVision and related PeopleSoft technology such as PS/Query, Tree Manager, etc.

I hope you’ll sign up and add your two cents to the conversations because it really won’t be much fun if it’s just me.

Post to Twitter Tweet This Post

Tags: , ,