PeopleSoft Crazy

I have completed my first complex multi level drill down report in Reporting Services. I should say that I am throughly delighted.. It took me 4 working days to finish the entire thing. This is a report that has to give the details like Sales, Direct Costs, Operating Expenses, Gross Profit, Income Tax, Net profit etc. all in a single page with drill down across months for different business units.

The Report will have a drilldown both at the row level as well as the column level. The row level drill down consists of a primart reporting head. For eg. in our case, it will be Operating Expenses. The second drill down will consists of following heads – Human Resource Costs, Employee Costs, Facility Costs etc. The third drill down under Human Resource Costs (for eg.) will consist of heads like Salaries, Bonus, Relocation, Business Travel etc. The fourth level drill down under the head Salaries will consist of all the GL account codes.

The column drill down will have all months at the first level. The second drill down level will consist of different business units under each month.

So that sums up the requirement. I have no clue how easy or difficult it is to do in nVision. But as far as Reporting Services is concerned, this is how I proceeded..

I first created a tree that represents the four level drill downs at the rowlevel. I wrote a query that ported the relevant data with the necessary structure from Oracle(PeopleSoft Database) to local report server database(SQL Server). The structure had the following columns Parent Node, Middle Node, Child Node and the accounts under the child node along with the posted transaction amount in PS_LEDGER table. I must admit this was pretty messy. I didn’t use any Oracle’s “connect by” and “prior” duo to get the hierarchy. I think, if I had used them, that would have increased the speed of the query by several times.

Anyways once I have ported the data, I wrote a dataset query in SQL Server that represents the order as required in the report. For eg, the Sales have to appear first in the drill down, followed by Direct Costs followed by Operating Expenses. Now if you simply run the report without ordering, it will display heads sorted by alphabetical order. This means, Direct Costs will be displayed first, followed by Operating Expenses and Sales. This is evidently wrong as we need to first show what’s the total sales of the company is for a particular month, for the operating expenses and direct costs to be deducted from it.

This part was bit tricky although not that challenging. Once you take care of the ordering, it is now time to show those uncomfortable “Gross Profit” which is simply total sales – total direct costs – total operating expenses. Now displaying this was tricky as this has to be calculated from the calculated fields and that too on the fly… I had more of these kind of heads in my requirement. The other ones are Income before tax, Net Profit. All of these are to be calculated impromptu from the other calculated fields.. My dataset query turned out to be one huge ugly monster. I will post both SQL Query and the dataset query tomorrow. They are really slouchy and difficult to maintain. I need to improvise on this. I will post them tomorrow. Any suggestion to improve them will be greatly appreciated.

Once I am done with the dataset queries with some prompts here and there, the report is ready to be deployed. Thats it. Pretty easy!! Isn’t it? I will try to emulate this in n-vision and estimate the time taken to do the same requirement. This will give us an idea of when to go for nVision reports and when to go for reports prepared using reporting services.

I plan to devote this week to nVision. The reason being that I have to produce an income statement report in Finance. The report looks demanding to me and it’s also a drill down report. There are two options for me : Using PeopleSoft nVision to do the report or using Microsoft Reporting Services to do the report. I initially thought I can do the report in reporting services. But I thought it will be rather better to look into the features of nVision and then decide whether to go with nVision or with Reporting Services. The first thing that I wanted to know is how will I know what is the SQL that is being used to generate the data in the nVision layout.

Well ,here is my first novice step of knowing nVision:

Go to the nVision report layout. In the tool bar, you will notice something called “nVision”. Click that.

Now you will notice a variety of options. One of them will be “Options”. Click that. A dialog box will open. One of the options will be “Show Report SQL”. Check this.

Now when you run the report using “Report Request”, every SQL that is used will be displayed one by one as the report is run and the data is being fetched.

I will be posting more of my basics findings in the subsequent postings. Also do remember that I got to tell you about “Add Attachment” feature. I will be visiting on this topic soon.

I am doing a R&D on how “Add Attachment” works. I think it behaves differently across HCM and other streams. I will post my findings soon. Meanwhile, I was checking on different types of free reporting tools available for download in the net. I came across a reporting tool called LogiXML. It is pretty simple to use. Preparing drill down reports is piece of cake.  I think you can also use N-Vision for the same. But I feel producing drill down reports takes lesser time than N-vision.

The major hurdle is incorporating security to the data. The security feature is available only in the advanced versions. But then again, the advanced versions come with a cost. You will have to buy the product to incorporate security.

The other product I checked out was Microsoft Reporting Services, SQL Server 2005. This one is certainly worth it if your organisation has a licenced version. Otherwise you can also download the free version which is SQL Server Express Edition. Preparing drill down reports, incorporating security, formatting, parameterizing reports and deploying it takes so less time that you will be tempted to bring most of your MIS reports in PeopleSoft into Reporting Services.

It took me hardly a day to prepare a complex drill down report which will easily take me a week to do the same in PeopleSoft if it has to be done in one single page. I was able to bring security based on the network ID. I was able to bring into the system logic, that identifies the user who is logged into the network and filters data based on the user. This is fantastic.

I was also able to produce graphical reports, crosstab(matrix) reports with drill down and without it. All this requires no coding. Everything is drag and drop. Ofcourse for all those code buffs out there, there is a facility to write your own code using extensions that Microsoft provides.

The best part is I was able to schedule a job that takes in the transaction data from PeopleSoft(Oracle database) and port it into my local reporting database(MS SQL Server 2005) using DTS available along with the SQL Server 2005 as SSIS(SQL Server Integration Services). Then the report will be produced based on the mini-datamart that I had created in the SQL Server. Even, the report processing can be scheduled and made to run at a particular time. We can also automatically send the report via email to a set of users in the email distribution list. This feature is called Subscription. The report can be sent in excel, PDF, web archive, TIFF etc. This is a cool feature. When I got the email automatically sent by the server, I was thrilled.

I can also do a data driven subscription. This means, I can dynamically generate the content that will appear to the users as comments, or I can dynamically generate the list of users to whom this email has to be sent.

Whatever I have said is just tip of the iceberg. There is so much you can do with this tool. Check out whether you can satisfy your organisation needs with this tool. I am sure you can if you have too many reports in PeopleSoft. The best part is that the productivity of the developer is increased manifold.

Hope this post is useful.