I am having trouble creating a turn

[CKingery]

I am having trouble creating a turnover report in a cross-tab format.

I would like to pull from PeopleSoft Query a list of all active and
termed individuals and then divide the active by the termed and
display all the data in a cross-tab that displays the count of each
and the calulation. Sounds easy, but it is for 160,000 records and
needs to be in rows by Division and then columns by organization.
Is there anyone out there who has done this or has used and expression to get this data?



I can only pull from PeopleSoft through Query ODBC one query per
report.

Any suggestions??

 

[rekclaw]

I'm not sure what kind of results you are expecting. It seems like you are after summary data rather than detail. A detail report of 160,000 rows would be a little cumbersome. Thus, I'm assuming that you are attempting to pull counts by some category like department id. This of course is not a problem with the query tool. The cross tab report is where life gets ugly. Because you are referencing ODBC it would seem that you are delivering the data to some desktop tool such as Access or Excel. I would use the capabilities of one of these products to do the cross tab work for you. Pivot Tables are a really cool tool in Excel to make very flexible cross tab reports. I really don't think the Query tool in PeopleSoft is capable of this sort of complex reporting (though I haven't seen version 8 of Query yet).

 

[johnmanfreda]

Hi,

The previous post mentions using a pivot table in Excel this is an option , however your summary rows i.e. the number of rows retrieved by your base PS/query cannot exceed the number of rows availible to Excel depending upon versions this can be 65,000 Excel rows. The safer method if you are locked into using PS/Query would be Crystal reports. Its been a while since I have used this but, the template is there in the Crystal report and I have used this in the past. Check your Crystal documentation for the specifics. The version in 7.5x and higher has a privot table format this can be used similar to Excel only it allows unlimited rows to be returned. This should work for you.
A word of caution on querying large quantities of rows in ps/query. There are "default" middleware i.e. tuxedo settings around Ps/query that may effect you if you are using PS in a three tier environment. There is also a default timeout setting that may need to be adjusted i.e. increased so that your PS/Queries aren't timed out by the report server. These can be addressed by your tech support staff.

Also as an aside configuring the PS/Query process to run on its own unix/NT machince can also help your performance.

Good luck,

Let me know what how you solved it and if this information was helpful.

John