Getting Crystal To Work

Oh Crystal, when will you be decommissioned?

Sometimes Crystal just doesn't want to play ball. Here's what I recently found:

1. If the report was using my local ODBC connection for its Database Connection it didn't work on the Server. Of course.
   
   I was receiving the following error message:
   

   Crystal Reports Print Engine error text: Error in File e:\psoft\s90dev\crw\eng\usqsf3p.rpt:
   Unable to connect: .rpt:
   Unable to connect: incorrect log on parameters. %6 %7 %8 %9 (65,30)
   Message Set Number: 65
   Message Number: 30
   Message Reason: Crystal Reports Print Engine error text: Error in File e:\psoft\s90dev\crw\eng\usqsf3p.rpt:
   Unable to connect: .rpt:
   Unable to connect: incorrect log on parameters. %6 %7 %8 %9 (65,30)
   

   This can be simply corrected by using the following steps:
   
   - Go to 'Database/Set Datasource Location...'
   - Create a new connection.
   
   
   
   - Select both Current Data Source in top pane and new one in bottom, click 'Update'.
   - Verify the database.
   - Save and perhaps even open and close to ...do something.
   
   ... or just never using a local ODBC connection (duh?).
   
   
2. Which user to use for the connection prior to migrating out of DEV and eventually into PROD?
   
   
3. Subreport links.
   
   If found I had to use the following ("sameish" for run control id):
   
   
   

Printing SQR A4 instead of Letter

Bit of a vague post...

When printing directly to Printer we had a Process Type of 'Crystal Check'. In parameters we added:

-PAPERA4

... to the end of the parameter list. This is not documented.

The Registry - Registering Interfaces

For following error message:

"HMCR_FRAMEWORK.ServiceFramework.Utilities.ServiceManager.OnExecute Name:LocateService PCPC:1612 Statement:37 Called from:SCC_PERSON_SOA_CONSUMER.PersonServiceConsumer.SCC_PersCitizenshipConsumer.OnExecute Name:getData Statement:6 Called from:CITIZEN_PASSPORT.GBL.PostBuild Statement:5 not found in the registry (18137,5)

The PeopleCode program executed an Error statement, which has produced this message."

See:

PeopleBooks > PeopleSoft Enterprise HRMS 9.0 Application Fundamentals PeopleBook > Registering Interfaces

Which leads to:

Set Up HRMS > System Administration > HCM Registry > Service Registry

 

Click the 'Refresh Cache' button:

Right Align Field in SQR

How to align right in SQR?

Sometimes the answers are so obvious you kick yourself when you see them:

   Let $total = lpad(To_Char(#total), 15, ' ')

   Print $total (, 40) edit $999,999,999.99 bold

Browser Language Problems

Heard about some problems when browser's default language is not same as Psoft enabled language. Found I couldn't log in when set to Spanish.

Here is the solution
http://docs.google.com/Doc?id=df48r26b_49chqwb3dt

Tricky EFFDT Logic in Query

Sometimes you want to write something like the following in Query
(code snippet for outer join + effdt solution btw):

AND K.PASSWORD (+) = F.PASSWORD
AND
(K.EFFDT IS NULL
OR K.EFFDT =
(SELECT MAX( M.EFFDT)
FROM PS_UQ_CHK_SIG_FILE M
WHERE M.EFFDT <= SYSDATE))

It would appear that Query doesn't let you, but if you do things in the correct order you can force it to do something it was specifically designed to not do. : )

1. Add your subquery
2. Allow the default Effdt Logic to be added by PeopleSoft/Query
3. Add a new expression "max(effdt)" and add as a selected field.
   Note: do not add the alias, just effdt.
4. Correct the Criteria.

Some subqueries don't use effdt logic, the important step is to not add the alias in the expression, until you've finished all the criteria.

Queries in Project and number of Records

select
b.qryname
, b.descr
, b.descrlong
, (select count(*) from PSQRYRECORD where qryname = b.qryname) as Num_Records
from
psprojectitem a
, psqrydefn b
where a.projectname = 'UQXM1210'
and b.qryname = a.objectvalue1
order by 4