Determine Languages installed for your PeopleSoft Application.

You can use this SQL to find out which languages are already installed for your installation.

select * from PSLANGUAGES where installed = 1

Find if the current user has been assigned the named role

You can use this in your peoplecode to determine if the current logged in user has role assigned to him.

&IND = %Roles.Find("MYROLENAME");

IF &IND <> 0 Then

     /* User has this Role */

or 

   /* User does not have this Role */

end-if

You can also use the peoplecode function IsUserInRole("ROLENAME") to find the same.

Export to PDF Fix for Crystal Report 8.5

If you try to run Export to PDF from Crystal Report 8.5 Report designer, it does not work. To resolve this, download this hot fix from businessobject web site and install it. Click here to get list of all downloads available for 8.5

Crystal Report Tip: Know the height and width of a object

In Crystal Report, you can right click on any field or object and select Object Size and Position.

This can help you align two objects horizontally or vertically using same x and y coordinate and also allow you to set the same height and width. Very useful, when you need to delete one field and add another from different source. Just note down the all the values before deleting and set the values after adding the new object.

How to link Sub Report to Main Crystal Report

Sub reports allows to use another query as a data source, different from the main query. You can have as many subreports as you want. Sub reports can not have another sub report inside it. One of the challenge is to link the sub report parameters with main report, so that sub report query is executed for the every row retrieved from the main report. This is easy.

I will explain this using a Sample. Let's say we are designing a report that prints user profile information. The main query is based on psoprdefn and is used in the main report. The Sub report is based on psroleuser and is used in the subreport to print role names for each user selected in the main query.

Click Insert -> Sub Report

Select Create a Sub Report and Type in PSROLEUSER and click on Report Expert.

Click on Database and Select the Query name. Complete the rest of steps by clicking on Next. Once you return to main Insert Subreport dialog box, Select Link tab. You can also change links for sub report later on by right clicking on sub report and selecting Change Subreport links.

Select Oprid from Available Fields and Click on > button to move it to Fields(s) to link To.

Now Click on Subreport Parameter fields to use:

Click on tiny Scroll bar, (Note: Do not use ?Pm-GF_C_PSOPRDEFN.OPRID as your parameter to link to).

Once you see the parameter field for the sub query i.e. user id select it.

Note: Both main query and subquery has a common parameter i.e. oprid or Userid. We need to match the Oprid field from main query and assign it to Parameter field of the sub query and do not use autogenerated Crystal Report parameter. There may be other ways to achieve the same results, but i found this works best.

Once you insert the subreport to main report, Right click on it and select border and colors. Set Line style to none for Left, Right, Top and Bottom. Right click on Subreport and Select Edit SubReport. You may want to Delete Report Header and Report footer in the subreport and arrange the fields and add any additional information you need. Also delete the Print Date special field.

Note: This is based on Crystal Report 8.5

Crystal Report 8.5 and SubReports

If you have a linked sub report in Main Crystal Report, every time you open the main report, it re imports the sub report if it founds the sub report in the same location. This is despite the fact that Re-import Subreports when Opening Reports check box is unchecked.

This seems to me a bug. Similar bug in Crystal 10 has been reported on businessobject support web site.

Clearing the 'Re-import Subreport on Open' check box does not work (c2017466)

This creates a annoying behavior, as Crystal report will remove the links between main report and sub report. Only workaround I have found is to rename the sub reports or move them to other folders, once they are imported in main report. If Crystal Report does not find the sub report in the original location, it does not re import the sub report.

If any body has encountered the similar issues, please post it in the comments section.

Determine Hidden Folders and Content References for Portal Navigation

If you do not see a folder or content Reference (Menu Item) in Left hand side navigation, (Applicable to 8.4x ) then you must check to see if the folder or content reference is not marked as hidden. Other reasons could be security. Here is a SQL to find out all the objects that are hidden.

To find all the folders which are hidden from Portal Navigation.

select * from PSPRSMSYSATTRVL where portal_name = 'EMPLOYEE' and PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and portal_Reftype = 'F'

select a.portal_objname,b.portal_label,b.portal_prntobjname,c.portal_label
from PSPRSMSYSATTRVL a,psprsmdefn b,psprsmdefn c where a.portal_name = 'EMPLOYEE'
and a.PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV'
and b.portal_Reftype = 'F' and a.portal_name = b.portal_name
and a.portal_objname = b.portal_objname
and b.portal_name = c.portal_name
and b.portal_prntobjname = c.portal_objname

To find all the content references which are hidden from Portal Navigation.

select * from PSPRSMSYSATTRVL where portal_name = 'EMPLOYEE' and PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and portal_Reftype = 'C'

select a.portal_objname,b.portal_label,b.portal_prntobjname,c.portal_label
from PSPRSMSYSATTRVL a,psprsmdefn b,psprsmdefn c where a.portal_name = 'EMPLOYEE'
and a.PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV'
and b.portal_Reftype = 'C' and a.portal_name = b.portal_name
and a.portal_objname = b.portal_objname
and b.portal_name = c.portal_name
and b.portal_prntobjname = c.portal_objname

 

Replace EMPLOYEE with Your portal name. Some of the Other names are.

CUSTOMER
DEMOSITE
EMPLOYEE
MOBILE
PORTAL
PS_SITETEMPLATE
SUPPLIER

Portal Content Reference/folder Attributes are stored in

PSPRSMSYSATTR

PSPRSMSYSATTRVL