How To Create a Crystal Report for PeopleSoft

In the following example, I have blocked out the first 3 characters of the name for custom objects.  If you see a name beginning with an underscore '_' or a blank space then you should know to add your own identifier for that custom object.

First, create a PeopleSoft Query as shown:

Any prompts the user must enter for criteria are set in the query. The Run Control page you create collects those prompt values from the user. So, act like you are creating a query for the end user and not a Crystal. All the Crystal does is acts as a nice dressing for a query plus it can do some advance formulas that you may not be able to accomplish with the query alone. But think of the query doing all the work and Crystal taking all the credit.

So, create your query with the necessary prompts and save it.

Next, you will open Crystal and select FILE > NEW and select radio button for “As Blank Report” from the following popup window and click OK:

  
Next in the Data Explorer window that should popup, select your PeopleTools ODBC via a double click: 

And then you should get the familiar 2-tier login:

You should now be able to see queries under your PeopleTools ODBC, if not…select the OPTIONS button on your Data Explorer screen and make sure Stored Procedures is checked. Click ok, and then close/reopen your PeopleTools ODBC. Select your query and click ok. After this just drag and drop the fields you need for the report. Save the Crystal on the application server \CRW\ENG or \CRW folder with an 8 character length name. NOTE: YOU DO NOT NEED TO CREATE PROMPTS IN CRYSTAL. THE PEOPLESOFT QUERY DOES ALL THE WORK.

Next, create your run control record for storing data and a run control page for collecting data as you would with an Application Engine or for an SQR.

Now, if you have an existing Crystal report that was created to hit the database directly (used outside of PeopleSoft), and you need to add it to PeopleSoft…do the following.

1. Examine the way the Crystal is querying the database by selecting within the Crystal Report DATABASE > SHOW SQL QUERY 
2. Create a PeopleSoft Query based on the Crystal’s SQL query and add the prompt values that the Crystal may be asking and replicate that functionality on the PeopleSoft Query. You “may” have to remove the prompting from the Crystal itself.

Now, you will need to point the Crystal to the new PeopleSoft Query through the PeopleTools ODBC by selecting DATABASE > SET LOCATION > SET LOCATION (button). 

Once connected via the PeopleTools ODBC, use the Data Explorer to find and select your new query. The Crystal may ask to do an update of the report and go so far as to ask you to map existing fields in the Crystal Report to those in the query. This is mostly GUI and requires so coding.

Now, I will focus on setting up Crystal so it is accessible 4-tier (web browser) and assume your run control page and component are already setup and accessible 4-tier.

Now, navigate PEOPLETOOLS > PROCESS MONITOR > PROCESSES and select the Add New Value tab. Select Crystal Report and enter the 8 character report name as seen below:

Once you click add, you need only worry about the first 3 tabs: “Process & Definitions” (descriptions), “Process Definition Options” (security), and “Override Options” (passing query parameters).

Examples:

This last example is where the query parameters are passed.
 

In this example the appended line is as follows:

-ORIENTL :_run_cmpfrms.date_sel_dt :_run_cmpfrms.date_sel_dt2 :_run_cmpfrms.deptid
:_run_cmpfrms.supervisor_id :_run_cmpfrms.emplid :_run_cmpfrms.location

The portion “-ORIENTL” means orient for landscape. “-ORIENTP” would be for portrait. The rest of the line is in the format “space” and “:” and “no space” and “record.field” until all parameters are in the line. NOTE: THE PARAMETERS ENTERED IN THIS LINE MUST BE IN THE SAME ORDER THE QUERY PROMPTS APPEAR WHEN YOU RUN THE QUERY.

Save this process definition and you should be good to go. You can add a process definition to a project via application designer and migrate it to another environment.

SHARE

peoplesoft

• 
• 
• 
• 
•