Query and Crystal
"How to" Notes

• First, identify the name of the panel where the field appears.



1. Access the panel in PeopleSoft.
2. Click on VIEW in the menu.
3. Select PANEL NAME from the drop-down list.
4. Note the name of the panel as it appears at the bottom right, next to the action.


• Run the ULPNLFIELDS query.



1. Select GO, PEOPLETOOLS, QUERY.
2. Select FILE, OPEN.
3. Click on the down arrow next to the NAME box.
4. Select the query ULPNLFIELDS List fields on a panel.
5. Click the RUN QUERY icon.
6. Type the name of the panel in the prompt box and click OK.
7. The query results will show the name of the field and the table where it can be found to assist you in creating your queries.

For more information, contact SherryRoss.
Posted 09/28/99

Go to top

• Required Software

• Preliminary Preparations

1. Create the Data Source

• Create the Query

• Run Query to Excel

2. Mail Merge in Word

• Select the Data Source

• Set Up the Mailing Label

• Add Merge Fields

• Perform a Selective Merge

• Perform a Record Sort

• Perform the Merge

3. Print Mailing Labels

• Resources

• PeopleSoft SAPROD75
• Microsoft Word 97, 2000
• Microsoft Excel 97, 2000

Running the query to Excel will cause spaces between columns, which interferes with correct printing of labels. The configuration must be set to eliminate the space between columns.

1. Load PeopleSoft, click on Edit from the menu, Preferences, Configuration.
2. Click on the nVision tab at the top of the configuration panel.



3. Check that Spreadsheet is set to Excel.
4. Type a zero (0) in Space Between Query Columns box.
5. Click Apply, then OK.

Create the Query

A Public query has been created to serve as a mailing label template. The Public query can be saved to a Private query, which may be modified to reflect specific requirements of the label.

1. Access the Public AD_MAILLABEL query in SAPROD75.
2. Click on File, Save As from the Query menu.
3. Click on the arrow to change Owner to Private.



4. Change the Query Name and enter a Description.
5. Click OK. The Private query will now be the active window. The name of the query appears in the Application bar at the top left of the window. It should read PRIVATE.QUERY.name of query.
6. Modify and save the Private query.

PeopleSoft provides the ability to send queries to a predefined Microsoft Excel spreadsheet layout.

1. Click on the Run to Excel icon on the Query toolbar.



2. A blank Excel worksheet will appear while the program is running.
3. Check the data to make sure it contains the information you require and save the Excel worksheet.

A merge is an operation that takes the information from a data source and merges it with a main document, in this case to create a set of labels. Word organizes the process of merging the main document with the data source and leads you through it in a step-by-step manner using the Mail Merge Helper.

1. Load Word.
2. Select Tools, Mail Merge from the menu to open the Mail Merge Helper dialog box.



3. Under Step 1, Main Document, click the Create button and select Mailing Labels.
4. When the dialog box pops up requesting a document window, select the active window.

The data source for these mailing labels is the query from PeopleSoft that was run to Excel.

1. From Mail Merge Helper, under Step 2, Data Source, click the Get Data button and select Open Data Source.



Note: The data source may be an Excel attachment to a GroupWise e-mail message. The data originated as a query from PeopleSoft.
• Open the GroupWise message with the Excel attachment.
• Double click on the attachment. This will open an Excel spreadsheet with the label data.
• Save the spreadsheet in the directory of your choice.
2. From the Open Data Source dialog box, select the Excel file (.xls) that contains your data. After selecting the data source, choose Entire Spreadsheet from the Microsoft Excel dialog box to include the entire spreadsheet in the merge.



3. Click OK.

The main document must be defined as mailing labels.

1. Click on the Set Up Main Document button to complete document setup.





2. The Label Options dialog box appears. Under Printer Information, select the type of printer you’ll be using - Dot Matirix or Laser and Inkjet. From the Tray drop-down list, select the tray or bin where you put the labels for printing.
3. From the Label Products drop-down list, select the brand or type of labels you use. The Product Number listing will differ depending on what you select. Choose the Product Number you use, or the nearest in size. If the size label you want to use isn’t listed, click New Label and fill in the dimensions.





4. Click OK.
5. The Create Labels dialog box opens.

Place the field codes on the label where you want them to appear.

1. Click the Insert Merge Field button and select each merge field code you want to insert in the label. Remember to add any required punctuation, such as the comma after the City field, spaces between the First and Last Name and to press the enter key to place the Address field on the next line.



2. You can also add lines of text, for example "To the parents of", that will print on each label.

The data source file may be a large mailing list, but you only want to send a letter to a select group of members of the list, such as people in a particular state or those who meet particular conditions.

To set up the conditions of a selective merge, use the Query Options feature of the mail merge process. Access Query Options from the Mail Merge Helper prior to doing the merge by clicking the Query Options button, or if you have clicked Merge and opened the Merge dialog box, click the Query Options button from there.

1. Select the Filter Records tab to set conditions to select only certain records for your merge. The information entered in the Field, Comparison,and Compare To boxes is called the filter criteria because it filters out any records that don’t meet that criteria.



2. In the Field box, select the Field you want to use to set the conditions. For example, if you want to send mail to only those people in a particular state, choose the State field.
3. From the Comparison box, select the type of comparison you want to make (Equal To, Not Equal To, Greater Than, Less Than, Is Blank, and so on.)
4. In the Compare To box, enter the exact value to which you want to compare the field information. In the State example, enter the code for the State.
5. If you want to evaluate more than one condition, fill out the criteria boxes in the second line. In the first box of the second line, select And for all the records that match all of the filter criteria. Select Or for records that match any of the filter criteria.

Mailing labels may be sorted according to most fields in the data source, regardless of whether they appear on the label. The merged labels will only include those records that meet the conditions specified.

1. Click the Sort Records tab on the Query Options dialog box.
2. From the Sort By drop-down list, select the desired sort field.
3. Click OK to close the Query Options dialog box.

1. Under records to be merged, select All or From. If you chose From, enter the record numbers in the From box and the To box that you want to merge. If you only want to merge specific records or records of a certain type, click Query Options and set conditions on the merge. (See Perform a Selective Merge in this document.)





2. Under When Merging Records, select Don’t Print Blank Lines when Data Fields Are Empty to prevent blank lines in the label if there was no entry in a particular field.
3. Click Merge. A running account of the records that are being merged appears at the bottom left of the Word screen. The merged labels will appear in a new document.
4. To change the font and font size of the print on the labels, click anywhere in the document, select Edit, Select All, and change the font and font size in the boxes on the document toolbar.

1. Select File, Print from the Word menu.
2. Select the printer destination by clicking on the drop-down arrow on the Printer Name bar.
3. In the Page Range area, select from the following options:
• For a test print of a single page, choose Current Page to print the page where the cursor is currently located.
• Choose Pages to print specified pages, and then enter the page numbers in the text box. Since Word identifies each label as a page, entering 1-30 will print the first page of the label file (30 labels). Entering 1-35 will print pages one and two of the file, since Word will print whole pages only, the second page being labels 31-35.

4. To print multiple copies, in the Number of Copies box, enter the desired number of copies and verify that the Collate option is selected.
5. Click OK.

Please e-mail Sherry Ross for assistance or call 5139.

Additional guides are available:

• Introduction to PeopleSoft Applications
• Introduction to PeopleSoft Query
• Crystal Reporting Basics

For more information, contact SherryRoss.
Posted 10/25/99

Go to top

This document is a guide to running a predefined query to the grid control, Excel or Crystal. A predefined query can be run from a PeopleSoft panel in the Windows environment or from PeopleSoft Query.

Run a predefined query from a PeopleSoft panel in the Windows environment:

1. Click View on the Menu bar.
2. Select Navigator Display.
3. Select Query.
4. Right click on the desired query to access output options.
5. Left click on Run (to Grid), Run to Excel, or Run to Crystal.

1. Select File, Open to access the Open Query dialog box.
2. Click on the arrow next to the Name box to view predefined queries.
3. Double click on the desired query.
4. Select the appropriate "Run" icon (grid control, Excel or Crystal).

For more information, contact SherryRoss.
Posted 01/19/00

Go to top

When making a copy of a Public query for personal use as a Private query, please rename the query. Do not use the same name as the Public query.

PROBLEM: If you have two queries (a Public and Private query) with the same name, the data returned when running the query to Excel or Crystal is always from the Private query.

EXPLANATION: When running a Public query to either Excel or Crystal, if a Private query exists with the same name, the data from the Private query will be returned. The Excel or Crystal program cannot determine the difference between a Private query and a Public query with the same name and since the Private query is listed first, it will be the first query the program finds with that name.

RESOLUTION: Rename the Private query if you make a copy of a Public query. Do not use the same name as the Public query.

For more information, contact SherryRoss.
Posted 03/22/00

Go to top