The best keyboard shortcuts for selecting data in Excel

The first step of selecting cells or a group of cells (called a range) is to position the cursor on a cell that is a corner of the area you want to select. Use the arrow keys to do this.

To select a rectangle area around the active cell, hold down the SHIFT key and press the arrow keys. For example, to select a 2 by 4 rectangle, hold down the SHIFT key and press the RIGHT ARROW key one time and the DOWN ARROW key three times.

Holding down the SHIFT key can be cumbersome, so Excel provides a way to "turn on" selecting with arrow keys. Press F8 to start using the arrow keys to select, and press F8 again when you are finished.

There are other useful keyboard shortcuts to use for selecting data.

PRESS	TO
CTRL+A	Select the entire worksheet
CTRL+SPACEBAR	Select the entire column
SHIFT+SPACEBAR	Select the entire row
F8	Turn on extending a selection by using the arrow keys
CTRL+SHIFT+END	Extend the selection to the last used cell on the worksheet (lower-right corner)
CTRL+SHIFT+HOME	Extend the selection to the beginning of the worksheet
CTRL+SHIFT+arrow key	Extend the selection to the last nonblank cell in the same column or row as the active cell
SHIFT+F8	Add another range of cells to the selection; or use the arrow keys to move to the start of the range you want to add, and then press F8 and the arrow keys to select the next range

A few tips on deploying Secure Enterprise Search with PeopleSoft

Oracle's Secure Enterprise Search is part of PeopleSoft now.  It is provided as part of the Peopltools platform as an appliance, and is used with applications starting with release 9.2.  Secure Enterprise Search is a rich and powerful search product that can enhance search and navigation in PeopleSoft applications.  It also provides useful features like facets and filtering that are common in consumer search engines.

Several questions have arisen about the deployment of SES and how to administer it and insure optimum performance.  People have also asked about what versions are supported on various platforms.  To address the most common of these questions, we are posting this list of tips.

Platform Support
SES 11.1.2.2 does not support some of the platforms supported by PeopleTools, such as Windows 2012 and AIX 7.1. However, PeopleSoft and SES can use different operating system platforms when SES is deployed on a separate machine.

SES 11.2.2.2 will have the required platform support for PT 8.53 in the future. We are planning to certify PT 8.53 once the testing is complete in 8.54 development and all platform support is released for 11.2.2.2.

Architecture
We recommend running SES on a separate machine (from your apps) for two reasons:
1.    SES bundles specific WebLogic, Java, and Oracle DB versions and might need different OS patches at a minimum than PeopleSoft. By having SES run on a different machine, these pre-requisites can be managed better through their lifecycle independenly for PeopleSoft and SES.
2.    SES is resource intensive - it runs it's own WebLogic and Oracle database. By having SES run on its own machine, sufficient resources can be allocated to SES and free the PeopleSoft servers from impacts of SES load patterns.

Sending Batch Emails using Application Engine

From the Application Designer choose
1) File – New and select Application Engine.
2) You will see new definition created for you containing the MAIN section with a step
entitled STEP01.
3) Simply right click your mouse on the step (STEP01) and choose Insert Action.
4) A new action will be inserted and will default to SQL.
5) Change this action to PeopleCode.
6) Save your program
7) Then double click on the PeopleCode action. Now you are ready to insert the code
below. I will fully explain this code.

REM **********************************************;
REM ** Instantiate the record and SQL objects to obtain data *;
REM **********************************************;
&MY_DATA_REC = CreateRecord(Record.EMPLOYEES);
&MY_DATA_SQL = CreateSQL("%selectall(:1) WHERE PAYGROUP = :2");
&MY_DATA_SQL.Execute(&MY_DATA_REC, "OUR_PAYGROUP");
REM **********************************************;
REM ** Loop through all of the data one row at a time for *;
REM ** all rows *;
REM **********************************************;
While &MY_DATA_SQL.Fetch(&MY_DATA_REC);
REM **********************************************;
REM ** Set all necessary email parameters REM **********************************************;
&MAIL_FLAGS = 0;
&MAIL_TO = &MY_DATA_REC.EMAILID.Value;
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "Pay check is Available Online for Viewing";
&NAME = &MY_DATA_REC.NAME.Value;
&MAIL_TEXT_BODY = "Dear " | &NAME | ", Your paycheck is now available for viewing online.";
&MAIL_FILES = "";
&MAIL_TITLES = "";
REM **********************************************;
REM ** Send the email message for the current employee;
REM **********************************************;
&RET_CODE = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT_BODY,
&MAIL_FILES, &MAIL_TITLES);
REM ********************************************************;
REM ** Check the status code to ensure the mail was sent successfully;
REM ********************************************************;
If Not (&RET_CODE = 0) Then
WinMessage("Return status from mail = " | &RET_CODE);
End-If;
End-While;
REM ** Don’t forget to close the SQL stream **;

&MY_DATA_SQL.Close();

SQL Tuning Tips

Inadequate performance can have a significant cost impact on your business. The PeopleSoft application is an extremely powerful and highly configurable product that utilizes many different hardware and database platforms. A poor performing system and application can result in customer dissatisfaction, reduced productivity, and high costs. It is absolutely critical that the system's performance is operating at its peak levels.

Here are some very simple yet powerful SQL tips to remember

Avoid using the following:
 Boolean operators >, <, >=, <=, is null, is not null

 Not in, != 

 Like '%pattern', not exists

 Calculations on unindexed columns or (use union instead)

 Having (use a WHERE clause instead)


Do use the following:
 Enable aliases to prefix all columns

 Place indexed columns higher in the WHERE clause

 Use SQL Joins instead of using sub-queries

 Make the table with the least number of rows the driving table by making it first in the FROM clause

Other important points for SQL Tuning 
Establish a tuning environment that reflects your production database

 Establish performance expectations before you begin

 Always Design and develop with performance in mind

 Create Indexes to support selective WHERE clauses and join conditions

 Use concatenated indexes where appropriate

 Consider indexing more than you think you should, to avoid table lookups

 Pick the best join method

 Nested loops joins are best for indexed joins of subsets

 Hash joins are usually the best choice for "big" joins

 Pick the best join order

 Pick the best "driving" table

 Eliminate rows as early as possible in the join order

 Use bind variables. Bind variables are key to application scalability

 Use Oracle hints where appropriate

 Compare performance between alternative syntax for your SQL statement

 Consider utilizing PL/SQL to overcome difficult SQL tuning issues 

 Consider using third party tools to make the job of SQL tuning easier

Simulating FieldChange Execution in a Transferred Page

 

• Create a function (usually in the FieldFormula event) with the code that is currently in the FieldChange.
  
• Remove the FieldChange code, and instead of it, add a call to the created funtion: 
  
  

  Declare Function funcion PeopleCode workrecord.flag FieldFormula;
  
  function();

  
  
• Add a flag field in a work record, and add this field as hidden in the target page.
  
• In the source page, add the transfer funtion call and send in it an instance of the work record with the flag field value = "Y": 
  
  

  &MYREC = CreateRecord(Record.record); 
  
  record.flag="Y";
  
  &MYREC.Field1.value = record.field1;
  
  &MYREC.Flag.value =record.flag;
  
  Transfer( False, MenuName.menu, BarName.barname, ItemName.nombreitem, Page.page, "U", &MYREC, True);

  
  
• Finally, in the target page activate code, add a if clause that evaluates the flag field, and in case that its equal to 'Y' call the previously declared function: 
  
  

  Declare Function funcion PeopleCode workrecord.flag FieldFormula;
  
  If  workrecord.flag="Y" then
  
  function();
  
  end-if;

• 
  

Manipulating Data on a Grid: Scroll PeopleCode

When you have a grid that is filled with data; data that you don't want. How do you clear it out and then fill it with the data set that you do want? Well, here are the simple steps to do just that.

Lets say we have a Grid on a page and the main record on the grid is called COMPETENCIES. Well we would first want to define a Rowset object to manipulate. The code for this would look as follows: 

Local Rowset &COMPETENCIES;

Next we will want to instantiate the object. Instantiate just means to represent by an instance. If you notice, I am not creating a new Rowset. I am getting the Rowset from the current grid called competencies, using the GetRowSet function. And, I am defining the Grid object as a Rowset.

&COMPETENCIES = &REVIEW(CurrentRowNumber(1)).GetRowset(Scroll.COMPETENCIES);

Once that is complete, I want to remove all rows of data from the Grid. To do that I simply use the Flush method.


&COMPETENCIES.Flush();


Now lets fill the grid with new data that we really want.


&COMPETENCIES.Select(Record.COMPETENCIES,"WHERE EMPLID = :1 AND EVALUATION_ID = :2 AND COMPETENCY IN " | &IN | " AND EFFDT = %DATEIN(:3)", &EMPLID, &EVALUATION_ID, &REVIEW_DT);

What I am doing in the above line of code is selecting the data that I want to use to fill the grid. By selecting the record I am in essence asking to select all the fields that are on the grid from the COMPETENCIES table with the where clause.

Thats it! It's that simple. 

Tuning Your PeopleSoft Apps: Indexes and Temp Tables

Tuning the application can consist of tuning, PeopleCode, SQR code, SQL-intensive code, queries, nVision, and indexes. In This Article, we will focus on Indexing and Temporary Tables

Ineffective Indexing

One of the most common performance problems in the PeopleSoft Application is ineffective indexing against key application tables. As we stated earlier, the PeopleSoft software is delivered with a generic code set that runs on several database platforms. In addition to the code set, the indexes that exist are not specific to any one environment. Because of this, you need to fine-tune your application by selectively finding poor performing applications and determining whether or not the cause is due to ineffective indexing. This can be achieved by tracing the SQL of poor performing pages, application engine programs, COBOL, or sqr programs and finding the long running queries. Once you find the problematic queries that take a significant amount of time to complete, you will need to analyze the indexes that are being used.

Here is an example of how to fine-tune your indexes. The Journal Generator application, within the Financials software, could be a COBOL application (FSPGJGEN) that performs very many selects based on the run control id parameters. In running this process it is determined that it is taking approximately 2 hours to process only 50 Journals.

The first thing to do is to turn on tracing for that specific process and re-running the process in your test environment. Be sure that you always do your tuning in your test environment. You do not want to blindly start adding indexes to your production environment without performing full regression testing. The results can be catastrophic. Once you have the trace file, you can examine it and look for the timings for the long running queries.

After examining the trace file we find the SQL statement that is causing the performance problem. Once you find the SQL statement, you can run it through your RDBMS query tool to determine which indexes are being used. If you are using SQL Server, you will issue the following command: 

SET SHOWPLAN_ALL { ON | OFF }

If you are using Oracle you will utilize the explain plan. Once you execute this command, you can then run your select statement. This returns detailed information about how the statements are executed and provides estimates of the resource requirements for the statements, including the indexes that are being utilized.

The next step is to look at the columns in the where clause of the SQL statement and determine if the indexes being used, if any, contain these columns. If they do not, you can simply create a new index with the missing columns. Once created re-run your query to re-examine the index usage. Simply repeat this process until you achieve the improved performance.

In some cases, certain SQL statements will never even use an Index. This is what is called a full table scan. Full table scans are extremely taxing on the system and cause major performance degradation. If you determine that a SQL query is performing a full table scan, simply create an Index or Indexes with the columns that are contained within the where clause.

Tuning and adding indexes is one of the most overlooked and very simple ways to improve performance. Just remember the following steps.

- Trace 
- Examine the SQL
- Analyze the SQL in your RDBMS tool
- Determine Indexes being used
- Create Indexes with Columns in Where clause
- Re-Analyze the SQL and repeat until you get improved results

Another tip for tuning indexes is to try re-ordering columns within the index. You can sometimes gain huge performance improvements, by simply changing the order of the columns when you create the index. This is a trial and error method that you will have to test. There is no hard and fast rule for which column should be placed in what order.

Temporary Tables

PeopleSoft utilizes temporary tables in many of its application programs, especially application engine programs. These application programs are constantly populated with data and deleted, over and over. Each time a temporary table is populated and deleted, it causes certain databases like Oracle to leave the High Water Mark and produces full table scans. 

For example, an application engine program can insert 200000 rows and then delete them. The next time that application runs, it only inserts 2000 rows, yet a read against that table performs poorly. Additionally, the indexes that exist on these temporary tables are heavily fragmented from all of the deletes. Temporary tables are a common cause of performance problems.

In order to prevent fragmentation and improve performance on most used temporary tables, you should truncate these tables on a regular basis. 

Happy Tuning..