This is another of those posts that I had intended to get completed a while ago, but ended up getting distracted.
One of our loyal blog readers is at an organization with 42 setids and they are trying to do consolidated reporting across them. This is an issue that I’m very familiar with. Many years ago (12 to be exact), I was doing consulting work at Norrell Services, a staffing company based in Atlanta. They also had a large number of business units and setids and needed to perform reporting across them.
How did you accomplish this?
This was done through a technique I call the Super-Setid. This is where you create a completely new setid that contains the union of all values across the different setids. You can then build trees and do other things against that new setid. When doing nVision reporting, you can create a Super Business Unit that is intended to map to the super-setids of your chartfields (and pull in your trees as well). Because the report request in nVision can have a business unit different than the data you’re reporting against, this is possible (which also means that your business unit will be used for setid mapping only). For those who kept track of features in Financials at PeopleSoft, they had tried to accompplish this with a feature called Partial Tableset Sharing, which was targeted to Financials 7.5, but was pulled at the last minute and never released because they were never able to get it to work properly across all the different use cases customers needed.
What’s involved in implementing the Super Setid?
Good question, as always. To implement this, you will want to create a surrogate table for the valid values of each field you want to use a super-set for. This table will be used as the foundation for your trees, for filtering data in your reports, and for getting additional attributes.
To create the surrogate table, there are two main options:
Let’s start by looking at the view. In order for the view to work, your any chartfield or other table you’re creating the superset for must have values that are either unique or consistent across the different setids. In other words, you cannot have department 1000 mean Finance in one set id and have department 1000 mean Manufacturing in another setid. This is usually the case, but in the scenario where an organization does a quick migration of an acquisition into PeopleSoft and they don’t convert chartfield or other values during the migration, this may not be the case.
Let’s use the DEPT_TBL as an example for creating the view. The first step is to open up the record definition for DEPT_TBL, save it under a new name, and remove the fields you don’t plan to use for reporting. Then, you change it to be a view and enter the select criteria for the view (hard-coding the setid to be your super-setid). Here is an example of this select statement:
FROM PS_DEPT_TBL A
WHERE A.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;
Summary Ledger and Summary Tree
The last option we’ll discuss is using summary ledgers and summary trees for accomplishing this. This option requires using a little bit of abstract thinking. Imagine that you are capturing data at a very granular level (i.e. low-level accounts), but you don’t need to perform consolidated reporting against that level. In other words, you can show your values aggregated a bit when doing reporting. If this occurs, then you don’t need to have a single set of valid values at the detail level that works across setids. Instead, you can get a consistent picture by ensuring that your trees have a consistent node structure across your different setids and use those nodes for reporting. You will, in essence, set up your trees to have a common hierarchy, but the sets of values linked in at the leaf level will be different for each setid. The tree will actually be doing your mapping for you.
You will then run PeopleSoft’s summary ledger build process for each business unit, where you aggregate your chartfields to a specific level in these trees (thus storing the values with a consistent set of chartfield values across setids). Viola!
Putting this to use
Okay. Now you’ve handled the data manipulation to get a consistent set of values across your setids. Now, you need to use this in your reporting. How do you do it?
The first thing to do is to build a tree that you can use. If you’re creating the super-setid with a view or table, then you will create a tree structure that uses your new table for the leafs, and build a tree with that structure using your super-setid. You will also want to use that table for valid values in reporting (for value criteria in nVision or for query). If you use the summary ledger option, you will build your report against the summary ledger and report across business units. You would also build a summmary tree as referenced in PeopleBooks.
Here is a project with the code referenced above.