University of Sheffield
Sheffield uReports –
Writing Simple Reports Zahir Chaudhary June 2007
Sheffield Web Reports – Writing Simple Reports
Table of Contents 1. 2. 3. 4. 5. 6. 7. 8. 9.
Page Logging on ………………………………………………………………….…..3 Creating a Report…….…………………………………..………………….…..4 Filtering records……………………………………………………………….…5 Sorting records …………………………………………………………………..6 Grouping, Summaries, Formatting, Saving reports..…………….……………7 Saving reports, dates, Operators, And/Or, NOT operator……………….…...8 Prompts and Prompt Pages...........…………………………………………11-13 Creating calculations…………………………………………………………...14 Further Information, troubleshooting, If Then Else statements…………….15
_________________________________________________________________________________________ 2
Sheffield Web Reports – Writing Simple Reports
Logging on to Sheffield Web Reports Start your Web browser. Turn off the „Pop-Up blocker‟ from the‟ Tools‟ menu. Ensure your pop-up blockers are off. Or in the address bar, type the URL: http://cognosapplive.shef.ac.uk/cognos8/ for Training purposes.
From the Welcome page Click on the Launch icon. 1. 2. 3. 4.
Open Report Studio by clicking on it. Click the Packages folder and select the Training package. ( Click Allow Access – if message pops up). Click on Create a new report or template, click List icon. Click OK.
The screen below now appears
The Report Studio user interface has two panes, an explorer bar, and a work area to help you create reports.
Insertable Objects Pane
Explorer bar
Data Items tab Properties Pane
Explore the screen (user interface) 1. 2.
Explore the different panes of Report Studio. Place your cursor over the Page Explorer bar and have a look.
_________________________________________________________________________________________ 3
Sheffield Web Reports – Writing Simple Reports 3.
Place cursor over the Query Explorer bar and have a look.
Insert data in the Report 1.
Insert data in the List Report zone: In the Insertable Objects pane, on the source tab , expand Training, then expand the Applicants table. Double-click Applicant_No to insert it in the Columns zone. Double-click each of the following data items Applicant_No Surname Forename Email_Address Sex_Code Date_of_Birth Nationality_Description Disability_Code
2. 3.
Click the run report button to run your report and view the results. The report output appears in Cognos Viewer.
Items inserted in the report appear on the data items tab. To return to „Report Studio’, close the Cognos Viewer Web browser.
Other ways to select data items are to drag each item, or right-click each item and click Insert. A flashing black 3 bar line indicates where you can drop an item.
Insert another column in the report: 1. 2. 3.
In the Insertable Objects pane, expand Applicants. Insert Middle_Names after the Surname column. Run the report.
Note: To remove the data item from the report but keep it on the data items tab, click the Cut button. Note: If you want to remove a data item from the report, select it and click the Delete button .
_________________________________________________________________________________________ 4
Sheffield Web Reports – Writing Simple Reports
Filtering Records
The = operator Create a filter to return all applicants named Abbot. 1.
In the list, click the Surname column heading.
2.
On the toolbar, click the Filter button
3.
Click the Detail Filters tab.
4.
Click the Add button
5.
Insert Surname (from the Data Items tab in the Available Components pane) by double-clicking it. The ‘Surname’ appears in the „Expression Definition’ box.
6.
Define the following filter:[Surname]='Abbot'. Note the name must have single quotes round it.
7.
Click the Validate button
8.
Click OK twice and run your report. The report appears in „Cognos Viewer’. View the results and close „Cognos Viewer‟.
.
.
to validate your expression.
The wildcard operator % 1.
Edit the filter, click the Filter icon again
2.
Select the existing filter, click the Edit icon
.
and amend it to [Surname] like 'Abbot%', the surname
must have single quotes round it. (NB using the like operator, the wildcard is %). Validate 3.
Click OK twice.
4.
Run the report to view the results in „Cognos Viewer’.
your filter.
The ‘In’ operator 1.
Click the yellow Filter icon again, select the existing filter and click the Edit icon
.
Amend it to [Surname] in ('Flynn','Chowdry','Abbott') and Validate the filter – this is the same as entering ([Surname]=Flynn or [Surname]=Chowdry or [Surname]=Abbott). 2.
Click OK twice and Run the report.
3.
View the results and close „Cognos Viewer’.
_________________________________________________________________________________________ 5
Sheffield Web Reports – Writing Simple Reports
Filtering Records
The ‘Between’ operator 1.
Click on the yellow Filter icon again.
2.
Click to select the existing Surname filter from the list, click the Delete icon
3.
Click the Add button
4.
Select the Functions tab and expand the ‘Operators’ folder, scroll down and double-click the ‘Between’ operator to select it.
to delete it.
and add the Date-Of-Birth from the ‘Data Items’ tab.
Define the following filter: [Date_Of_Birth] between 1975-01-01 and 1975-12-31.
Dates: are entered in the format 2001-03-31 and do NOT have quotes round them. 5.
Validate the filter and Click OK twice.
6.
Run the report and view the results. (NOTE. Only applicants born in 1975 will be returned).
7.
Close ‘Cognos Viewer’.
Sorting Records 1.
In the list, click the Surname field from the list of columns.
2.
On the toolbar, click the Sort button
3.
Click Sort Ascending.
4.
Run the report and view the results.
5.
Close ‘Cognos Viewer’.
.
(NOTE. To remove a sort order, click the column, click
and click Don’t Sort).
_________________________________________________________________________________________ 6
Sheffield Web Reports – Writing Simple Reports
Grouping Records 1.
Click the column you want to group on – Nationality_Description. NOTE. You can click either the column heading or one of the column cells.
2.
Click the Group/Ungroup icon
3.
Change the order of columns in a report to rearrange information.
.
i). Click the column Nationality_Description to reorder in the report. (You can click the column heading or a column) ii). Drag the column to the front of the list. A flashing black 3 bar indicates where you can drop the column. 4.
Run the report and close ‘Cognos Viewer’. NOTE. To perform multiple groupings at once, use Ctrl+click or Shift+click.
Summaries 1.
Click the ‘Applicant_Number’ field from the list of columns. This is the column to which you want to add a summary.
2.
Click the Aggregrate icon
3.
Click the Count icon from the drop-down list of summary options
4.
Run the report and view the results. A summary appears in the report for each Nationality group and an overall total appears in the footer.
. .
Format fields 1.
Select the „Date_of_Birth’ field from the list of columns.
2.
From the „Data‟ menu, click „Default Data Formats’
3.
In the „Data Format‟ box, from „format type‟ select Date.
4.
In the Properties pane, select ‘Date Style’ and choose ‘Short’ from the drop-down list.
5.
Run the report and view the results, close „Cognos Viewer‟.
6.
Select the ‘Applicant_Number’ column.
7.
In the „Data Format’ box, from „format type‟ select Number from the available list.
8.
In the Properties pane, select ‘Use Thousands Separator’ and choose ‘No’ from the drop-down list.
9.
Run the report. Close Cognos Viewer.
10. For the report title, place your cursor where it says „Double click to edit text’ and double-click. In the text box that appears type: „Applicants by Nationality report‟ and click OK. _________________________________________________________________________________________ 7
Sheffield Web Reports – Writing Simple Reports
Saving the Report 1. 2. 3.
Click the Save icon . From the File menu, click Save (Save it in the MyFolders tab). Name the report „Applicants by Nationality’ and click OK. Or Save As to save a copy of the report under another name.
Comparison Operators: There are a number of operators, which allow the selection of a range of records. For example: < Less than Greater than >= Greater than or equal to Not equal to
[Date_Of_Birth] > 1975-01-01 in the filter will return all applicants born from 02-01-1975 onwards (excluding those born in 01-01-1975). [Date_Of_Birth] >= 1975-01-01 in the filter, returns all those born from 01-01-1975 onwards. Similarly [Date_Of_Birth] < 1975-01-01 will return applicants born prior to 1975.
Combining Criteria, and/or
1. Find all Female applicants from China. 2. Find the records for applicants who are Male and from Turkey OR Female and from China.
The NOT operator Enter NATIONALITY_DESCRIPTION Not in (‘Belgium’) into the filter. This time you will get all applicants who are not Belgian. Entering NATIONALITY_DESCRIPTION ‘Belgium’ will give the same result as above.
Find all Applicants with a disability (use the DISABILITY_CODE field, applicable values are: 0 = no disability; 1 = dyslexia; 2 = sight impaired; 3 = hearing impaired; 4 = mobility difficulty; 5 = personal care required; 6 = mental health difficulty; 7 = unseen disability; 8 = two or more disabilities; 9 = other). _________________________________________________________________________________________ 8
Sheffield Web Reports – Writing Simple Reports
Selecting fields (Columns) from more than one table 1. 2. 3.
From the File menu, click New and select the List icon.
4.
Double-click to add each of the following data items to your report:
Locate and expand the „Stu_Registration_Details’ table
Session_Year, Owning_Organisation_Code, Programme_Code, Programme_Period_Code, Registration_Status, Active_Flag Registration_Number, Title, Initials, Surname, 5.
Scroll to the ‘Student_Unit_Choices’ and expand this table.
6.
7.
Double-click to add each of the following data items: Unit_Code Unit_Name Owned_by_Description On the toolbar, click the Filter button .
8.
Click the Detail Filters tab.
9.
Click the Add button
.
10. Insert Active_Flag (from the Data Items tab) by „double-clicking‟ it. 11. Add the following individual Filters: Insert [Active_Flag], type =, click the Select Value button , choose Y from the list and click ‘Insert’. ( The filter should be [Active_Flag] =’Y’). Click OK Insert [Registration_Status], type in, click the ‘Select Multiple Values’ button In the Values box, click FR (the value you want to insert in the filter).
.
Click the right arrow button to add this to the Selected values box. Also add PR and TEMP values. Click Insert. The filter should be [Registration_Status] in (‘FR’,’PR’,’TEMP’) Add this filter [Session_Year] = ‘2004’ Add this filter [Programme_Code] starts with ‘GEO’ (This is the same filter as [Programme_Code] like „GEO%‟ 12. Click the Validate button the right to see all the fields.
to validate your expression. Click OK twice and run your report, scroll to
13. Locate and expand the ‘Unit_Results’ table and add the following Data Items to the report Latest_Grade, Latest_Outcome and Calendar_Occurence_Code 14. Run the report and view the output. Close Cognos Viewer. 15. Add a Filter - [Calendar_Occurence_Code] like '%04' (NB using the like operator, the wildcard is %), Validate
your filter.
16. Click OK twice and Run the report to view the results, scroll to the right to see all the fields. Close Cognos Viewer. _________________________________________________________________________________________ 9
Sheffield Web Reports – Writing Simple Reports
Hiding, Grouping, Renaming fields
1.
To Hide columns use the Cut icon , select the column ‘Registration_Status’ and click Cut, this removes the data item from the report but keeps it in the Data Items tab.
2.
Delete the „Title‟ column - use the Delete icon Data Items tab.
3.
Hide the „Active_Flag‟ and „Session_Year‟ columns too.
4.
Click to highlight the „Programme_Code’ column and then Control-click to also select „Programme_Period_Code’, drag both columns to the start of the list.
5.
Group the Owning_Organisation_Code, Programme_Code and Programme_Period_Code dat items using the Group/Ungroup icon
, this removes the data from both the report and the
.
6.
Select the „Owning_Organisation_Code‟ column (to rename it).
7.
In the Properties pane, under the Data Item group, type the new name you want in the Label property. Name it as Dept.
8.
Rename the following fields:
9.
Run the report and view the results, close Cognos Viewer.
„Registration_Number‟ „Programme_Period_Code‟ „Owned_by_Description‟ „Calendar_Occurence_Code‟ „Programme_Code‟
RegNo Period Dept Name Semester Programme
10. Try experimenting with the Sorting to get the records in the best sequence. 11. Run report, view the results and close Cognos Viewer. 12. Group the columns to get the best results. 13. Save your report.
Headers and Footers 1. Click the „Headers & Footers‟ icon
.
2. Select „List Headers & Footers….‟ 3. Tick the ‘Owning_Organisation_Code (header)’.
_________________________________________________________________________________________ 10
Sheffield Web Reports – Writing Simple Reports
Using Prompts Prompt Page Report Columns Programme Code
Reg No
Surname
Forename
Dept
Prompt Entry box AAPU01 AAPU02
BIBU07 LAWR03 PHYR07.
Using prompts in a ‘Prompt Page’ to find required data. Entering a value e.g. BIBU07 in the prompt for Programme Code would find those corresponding value/s in the Report. NOTE. In a filter a prompt must have question marks (?) around it. eg Programme_Code like ‘%’+ ?prompt? A prompt is also known as a parameter or input box.
Create a Prompt (parameter) and Prompt Page 1. 2. 3.
Create a new list report. Select Programme_Instances from the list of tables. Add the following columns: Programme_Code Short_Title Main_Attainment Owned_by_Code Start_Date
4.
Create a filter and in the Expression Definition box, create the following: [Programme_Code] starts with ?ProgrammePrompt? NOTE A question mark must be placed before and after the prompt name. This prompt will be used in the Prompt Page later on. Validate the filter.
_________________________________________________________________________________________ 11
Sheffield Web Reports – Writing Simple Reports
Create a Prompt Page Create a Prompt page for the „Programme_Code’ field to allow users to filter data in the report and retrieve data only for the „Programme_Code’ that they select:
Query Explorer tab
1.
Pause the pointer over the page explorer button
and click Prompt Pages. (See above diagram)
2.
In the „Insertable Objects’ pane, on the Toolbox tab automatically named „PromptPage1‟
3.
Double click the Page you just created.
4.
In the „Insertable Objects’ pane, on the Toolbox tab page. The Prompt Wizard dialogue box appears.
5.
Click „Use existing parameter’ and select the prompt that you created earlier in your report filter. Select ProgrammePrompt from the drop-down list.
6.
Click Finish. At the bottom of the page delete both the „‟ buttons. Run the report.
7.
A Text box prompt appears (Enter eg AAPU01) and click Finish. View the results.
, drag Page to the Prompt Pages box. This is
, drag the Text Box Prompt to the prompt
_________________________________________________________________________________________ 12
Sheffield Web Reports – Writing Simple Reports
Editing the current prompt and adding Date prompts 1.
Pause the pointer over the page explorer button
and click Report Pages and select Page1.
2.
Click the Filter icon , edit the filter, change it from: [Programme_Code] starts with ?ProgrammePrompt? to [Programme_Code] like ‘%’ + ?ProgrammePrompt?
3.
Validate the filter and click OK.
4.
Add another Filter.
5.
In the ‘Available Components’ double-click „Start_Date’ from the „Data Items’ tab. Then from the „Functions‟ tab, open the „Operators‟ folder and choose the „Between‟ operator. Create the filter as follows: [Start_Date] between ?EnterDate1? and ?EnterDate2? ‘Validate‟ the filter and click OK twice.
6.
From the ‘Page Explorer’ button, click Prompt Pages and select Prompt Page1.
7.
In the „Insertable Objects‟ pane, click the Toolbox tab , drag the Text Item onto the prompt page and drop it in front of the prompt box created earlier. The Text dialogue box appears. Type in the text: “Enter Programme Code or part of code and wildcard”.
8.
From the Toolbox tab, drag a Block onto the page below the first prompt. (This is to make the layout of the Prompt Page more presentable, by separating the first and second prompt which we will add in the next step).
9.
Drag a Text Item and place it below the Block. In the Text dialogue box type the following: “Enter Date From”
10. From the Toolbox tab, drag the Date Prompt
next to the text inserted.
The Prompt Wizard dialogue box appears. 11. Click „Use existing parameter’ and select the Date prompt (which you created in your filter earlier) „EnterDate1’ from the drop-down list. 12. Drag a Text Item and place it to the right of the Date prompt you inserted. In the Text dialogue box type the following: “Enter Date To”. 13. Insert another Date prompt to the right of this text item, from the Prompt Wizard box, click „Use existing parameter‟ and select the Date prompt that you created earlier „EnterDate2’ from the list. 14. Run the report and view the prompts that appear. Press Cancel. 15. Select both Date prompts and in the „Properties‟ pane under General, click „Select UI‟ and from the drop-down list choose „Edit box’. 16. Run the report, enter NURU11 for the Programme Code prompt, 01/01/1996 for the first Date prompt and 01/05/2000 for the second Date prompt (You can type-in the date values or use the calendar icon). View your results. Re-run your report entering the values NUR%, 01/01/1996 and 01/05/2000 respectively, view your results and close Cognos Viewer. 17. Save your report (in MyFolders). _________________________________________________________________________________________ 13
Sheffield Web Reports – Writing Simple Reports
Create a Calculation Create a calculated column that counts students by programme 1.
Create a new List report (using the „Training‟ package).
2.
Select Stu_Registration_Details from the list of tables.
3.
Add the following columns:
4.
Add a Filter, from the Data Items tab, double-click Active_Flag and create the filter [Active_Flag] =’Y’. From the Functions tab, open the Operators folder and double-click the „and‟ operator, then from the „Data Items’ tab, double-click Owning_Organisation_Code, again from the Functions tab and the Operators folder, double-click the starts with operator and then type ‘G’.
Programme_Code Active_Flag Owning_Organistion_Code Registration_Number
The filter should now read [Active_Flag]=’Y’ and [Owning_Organisation_Code] starts with ‘G’, this returns all departments starting with „G’ e.g. GER or GEO. Validate and click OK twice. 5.
Run your report, view results and close Cognos Viewer.
6.
Cut (NOT Delete!) the Active_Flag and Registration_Number columns from the report, this hides them from the output.
7.
From the toolbox tab
8.
In the Name box that appears, type a name for the calculation. Type the following: “Number of Students” and click OK.
9.
In the Available Components box, define the calculation. On the Functions tab open the Summaries folder and double-click on count (
, select Query Calculation and drag it into your list report.
Count, this now appears in the Expression definition box on the right as
10. From the data items tab, double-click ‘Registration_Number’ and type the closing bracket ) to complete the expression. The expression should now read: count([Registration_Number]) 11. Validate the expression and click OK. 12. Run the report and view the results. Close Cognos Viewer. 13. Drag the Owning_Organisation_Code to the left of the report and then Group it. Run the report and view results. Close Cognos Viewer.
_________________________________________________________________________________________ 14
Sheffield Web Reports – Writing Simple Reports
Query Explorer
This allows you to see all the fields in your query. 1.
Place your cursor over the Query Explorer button , under Queries double-click Query1. This shows all the data items in your query (even Active_Flag and Student_Registration_Number which you removed from the report view).
2.
Place your cursor over Page Explorer and click Page1 to return to the report view.
Further Information and Help This guide is not intended to cover every single feature of „Writing Simple Reports’. If you need further information or help email
[email protected] or call the CiCS Help Desk on 21111. There is also extensive help and documentation in Web Reports that can be accessed by clicking the Help command.
Troubleshooting common errors Filter issues: [Programme_Code]=LAWU01 is incorrect as the quotes round LAWUO1 are missing. [Programme_Code]=‘LAWU01' is now correct. [Surname] in („Smith‟, Joe, „Mohammed‟, „Vilbert‟) is incorrect as the quotes around Joe are missing. [Surname] in („Smith‟,’Joe’,‟Mohammed‟,‟Vilbert‟) is now correct. Creating filters using ‘operators’ such as „like’ or ‘=’ will return the correct data when written directly in the filter.
All the following filters will return data [Programme_Code] starts with „AAPU‟ or Programme_Code] = „PHYT03‟ [Programme_Code] like „AAPU%‟
(Note: No wildcard is required) (Note: The % wildcard is required)
However, please note that sometimes when using prompts with alphanumeric fields (i.e. mixed data combining numbers and text like PHYT03), no data will be returned. The following examples will return NO data: The „Like‟ or ‘=’ operators used on their own in a page prompt may not return any data for certain fields eg . [Field] like ?prompt? might not return data. To rectify this add the Percent (%) symbol eg. [Field] like ‘%’ + ?prompt? this will return data.
_________________________________________________________________________________________ 15
Sheffield Web Reports – Writing Simple Reports
All the following filters will return NO data [Programme_Code] like ?Programmeprompt? or [Programme_Code] = ?Programmeprompt? (Note prompts must have Question (?) marks round them eg ?prompt?) To correct this, add the following ‘%’ + into the prompt. The prompt should now be [Programme_Code] like ‘%’ + ?Programmeprompt? or [Programme_Code] = ‘%’+ ?Programmeprompt?
Using the IF THEN ELSE expression 1. 2.
In your Report Page, drag „Query Calculation’ from the Tool Box and drop it into the list report. Name the column and create your Expression. If Then Else statements are written as follows: IF ( [Sex_Code]='F' ) THEN ( 'Female' ) ELSE ( 'Male') Using a NESTED, IF THEN ELSE expression IF ( [Mode_Of_Attendance] = 'PT' ) THEN ( 'Part time' ) ELSE IF ([Mode_Of_Attendance] = 'FT' ) THEN ( 'Full time' ) ELSE IF ([Mode_Of_Attendance] = 'AB' )then ( 'Abroad' ) ELSE (' ') There must be a space between the quotation marks. Having no space will return an error !.
Combining AND/OR ([Sex_Code]='F' and [Nationality_Description] = „Cyprus‟) Or ([Sex_Code]='M' and [Nationality_Description] = „Turkey‟)
Other useful criteria syntax To return the current date (i.e. today‟s date) use the function {sysdate}. Note the curly brackets. In a filter this would be written as eg Start_Date < {sysdate} and End_Date >= {sysdate}
_________________________________________________________________________________________ 16
