MISCELLANEOUS TOOLTIPS FROM OUR USER GROUP MEETING ON 2/12/09

The following are miscellenous tips discussed in our Cognos User Group Meeting on 2/12/09. For the sake of time, only the text has been uploaded at this point. The different tips and tricks below will be separated out with graphics at a later date.

No wrap for column values
 
For a specific column only:  In Properties pane,  go to List Column Body Ancestor.  Under Font and Text, White Space, select “No Wrap”.

For all columns:  In Properties pane, go to Page Body Ancestor.  Under General, White Space, select “No Wrap”. 

To mask an unformatted phone number.

We had to create a data item expression in order to add a mask for the contact phone number in the Book Awards Report. 

Expression notes:

Substring grabs a piece of text at a starting point and counts the number indicated.

+  use this character to join (concatenate) one string with another. 

To add a space or a hyphen, put the space or hyphen in between single quotes ‘ ‘ or ‘-‘

IF
(char_length([CONTACT_PHONE_NUMBER_COMBINED])<11)

THEN
('('+substring([CONTACT_PHONE_NUMBER_COMBINED],1,3)+') '

+substring([CONTACT_PHONE_NUMBER_COMBINED],4,3)+'-'

+substring([CONTACT_PHONE_NUMBER_COMBINED],7,4))

ELSE
(
'('+substring([CONTACT_PHONE_NUMBER_COMBINED],1,3)+') '

+substring([CONTACT_PHONE_NUMBER_COMBINED],4,3)+'-'

+substring([CONTACT_PHONE_NUMBER_COMBINED],7,4)+'  '

+substring([CONTACT_PHONE_NUMBER_COMBINED],11)

)

CASE Statement for Ethnic Groups

Clay created the following CASE statement to get Ethnic Groups in the Reader Rating Report:

CASE

WHEN [Admission Application].[Person Detail].[PRIMARY_ETHNICITY] LIKE 'A%' AND [Admission Application].[Person Detail].[CITIZENSHIP_TYPE] in ('Y','P') THEN 'Asian Am'

WHEN [Admission Application].[Person Detail].[PRIMARY_ETHNICITY] LIKE 'N%' AND [Admission Application].[Person Detail].[CITIZENSHIP_TYPE] in ('Y','P') THEN 'Native Am'

WHEN [Admission Application].[Person Detail].[PRIMARY_ETHNICITY] LIKE 'B%' AND [Admission Application].[Person Detail].[CITIZENSHIP_TYPE] in ('Y','P') THEN 'African Am'

WHEN [Admission Application].[Person Detail].[PRIMARY_ETHNICITY] LIKE 'H%' AND [Admission Application].[Person Detail].[CITIZENSHIP_TYPE] in ('Y','P') THEN 'Latin Am'

WHEN [Admission Application].[Person Detail].[PRIMARY_ETHNICITY] = 'MR' AND [Admission Application].[Person Detail].[CITIZENSHIP_TYPE] in ('Y','P') THEN 'Multirac'

WHEN [Admission Application].[Person Detail].[CITIZENSHIP_TYPE] = 'N' THEN 'Interntl'

WHEN [Admission Application].[Person Detail].[PRIMARY_ETHNICITY] IN ('OT','WH','UN') AND [Admission Application].[Person Detail].[CITIZENSHIP_TYPE] in ('Y','P') THEN 'Other'

END

 

To hide all column titles.

Select List Ancestor, Properties Pane, General, Column Titles, select Hide.

Change column titles back from hidden to unhidden.

If you’ve hidden the column titles, you must first go to View, Visual Aids, and select “Show Hidden Objects”  Then go to Select List Ancestor, Properties Pane, General, Column Titles, select at Start of List.

To keep report from printing columns on a second page.

List Ancestor, Properties Pane, General, Pagination, uncheck “Allow Horizontal Pagination”.

To create Headers and Footers

List Headers and Footers, Select the Region and Club Header, then delete the headings in the report.

To print landscape in PDF format

Go to File, PDF Page Setup, select landscape, and set page size as letter.

To create page breaks (using Book Club report as a sample)

Go to Page Explorer
Select Reports Folder
Drag new Page set Object
Associate new page to query
Define Grouping and Sorting then drop in Groups Folder
Take page 1 and drag onto detail pages folder
Should now have one page per club.
Back to Page Set, sorting and Grouping.
Expand sort list, drag School Region into sort list folder.
On report layout, click Name, then sort Ascending.

To repeat header on each page. 

Click on header in page layout.  Select the List Header Ancestor.  Go to Properties Pane, General, Pagination, select Repeat every Page.

To view properties of a field (called "query item" in Cognos).  

While in Expression Builder, select the field from the list under packages.  Right click on the item and select properties.  You can see what the data type is for example. 

To add values to a filter by clicking. 

Use the steps above (To view properties of a field), then click “Select Mutliple Values.

To copy address format from one report to another. 

Unlock report.  Click any field in the address.  Select the Table Ancestor, then copy and paste into new report.  Lock report when complete.

Dynamic Column Headers

For example, if you want the column headers to reflect what was selected in a prompt (Five Year Feeder Schools by Club report).  Click on the column, make sure List Column Title Ancestor is selected.  In Properties, Text Source, Source Type, change it from Data Item Label to Report Expression.  Then create the Report Expression on the line below.  For example [Query1].[SEL_TERM]

Then Go to List Ancestor, Properties Pane, click the “Properties” Property, then select the SEL_TERM data items that will be used for column headings. 

Format the column headers as number type.

Go to List Ancestor, Column Titles, Column Titles, change to “At Start of Details”

If null value, print a value in Column Titles.

This is new in 8.3.  For example, in the Reader Rating report, click on the column title, then click on the Format icon.  The data is text, the Missing Value Characters is “NT.

To remove borders around column titles.

 Go to List Column Title Ancestor, Borders, select No Border.  Then go to Background Color and select Transparent.

Time Block Report

For each days item, created coalesce function to force it to return a non-null value.

Expression notes:  coalesce (if null, return some value)

coalesce([Schedule Offering].[Meeting Time].[MONDAY_IND],'')+
coalesce([Schedule Offering].[Meeting Time].[TUESDAY_IND],'')+
coalesce([Schedule Offering].[Meeting Time].[WEDNESDAY_IND],'')+

IF
([Schedule Offering].[Meeting Time].[THURSDAY_IND] is not null)
THEN
('Th')
ELSE
('')

+
coalesce([Schedule Offering].[Meeting Time].[FRIDAY_IND],'')

 

Block Days

 

coalesce([Schedule Offering].[Meeting Time].[MONDAY_IND],'')+
coalesce([Schedule Offering].[Meeting Time].[TUESDAY_IND],'')+
coalesce([Schedule Offering].[Meeting Time].[WEDNESDAY_IND],'')+
coalesce([Schedule Offering].[Meeting Time].[THURSDAY_IND],'')+
coalesce([Schedule Offering].[Meeting Time].[FRIDAY_IND],'')

 

To convert from military time

to_char(to_date(to_char({sysdate},'MM/DD/YYYY')||' '||[BEGIN_TIME],'MM/DD/YYYY HH24MI'),'HH:MI AM')

||'-'||

to_char(to_date(to_char({sysdate},'MM/DD/YYYY')||' '||[END_TIME],'MM/DD/YYYY HH24MI'),'HH:MI AM')

Define Number of Blocks in a Repeater Table

Click the Repeater, select the Repeater Table Ancestor.  In the properties pane, under general, enter the number across and down.

Report Width and Formatting

To modify report width on page, ancestor to List or xTab level,  Size & Overflow property, choose % of page to fill.

To separate consecutive list boxes, go to List level, Box properties, and adjust Margin OR put in a border with a thick white line.

To adjust width of a field, unlock, click the field name in the report cell, go to Positioning properties, Size & Overflow, and choose % for width or height.  This was used in Status of Reader Ratings report to line up the series of crosstab objects.  This is also where the Visible property is found.

A Reminder

Delete removes field from query and layout

Cut removes field from layout only – is safer

Lock for coarse movements – ie moving a field to column

UnLock for fine adjustments – ie drop text within a column (to find Drop Zone)

Why are the Years or PIDMS totaling on my report?

If you’re wondering why your PIDM or Calendar Year is totaling, the answer lies in the Aggregate Function property.  When you insert an object into a report that is a number data type, the default aggregate function is total.  Change it to None.