Reporting against multiple Setids in PeopleSoft

By Larry Grey • April 9, 2008

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:
  • 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
View 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: 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); &CurrentRec.CopyFieldsTo(&SuperSetidRec); &SuperSetidRec.GetField(Field.SETID).Value = &SuperSetidVal; &SuperSetidRec.GetField(Field.DEPTID).Value = DEPT_TBL.DEPTID; &SaveRtn = &SuperSetidRec.Save();
End-If; 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. Code Here is a project with the code referenced above. Labels: nVision, Reporting

Stay Updated

Tips and Techniques

Baby steps with Reporting against PeopleSoft

By Larry Grey • March 13, 2008

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

Stay Updated