WEBVTT 00:00:00.000 --> 00:00:04.000 music 00:00:04.000 --> 00:00:08.000 music 00:00:08.000 --> 00:00:10.000 music 00:00:10.000 --> 00:00:13.000 Hi. I'm Mac Chartier from Computing Services. 00:00:13.000 --> 00:00:19.000 In this video, I'll teach you how to build a basic report using Cognos Workspace Advanced. 00:00:19.000 --> 00:00:24.000 We've broken the video into 3 parts so you can better manage your training. 00:00:24.000 --> 00:00:27.000 In this first video, I'll introduce you to the tool. 00:00:27.000 --> 00:00:30.000 In the second video, we'll add fields to the report. 00:00:30.000 --> 00:00:34.000 In the third video, we'll filter the data. 00:00:34.000 --> 00:00:39.000 We'll be building a class roster using course registration data. 00:00:39.000 --> 00:00:44.000 If you don't have access to student information, the same basic principles apply 00:00:44.000 --> 00:00:48.000 to the areas of Cognos. 00:00:48.000 --> 00:00:52.000 We have a workbook available on our website, if you'd like to download it 00:00:52.000 --> 00:00:56.000 for future reference. Let's get started. 00:01:00.000 --> 00:01:04.000 I've already logged into Cognos from the portal, 00:01:04.000 --> 00:01:08.000 and I'm in the Production Data Warehouse. 00:01:08.000 --> 00:01:12.000 I need to launch Cognos Workspace Advanced. 00:01:12.000 --> 00:01:16.000 In the upper right hand corner of the page, there's a launch 00:01:16.000 --> 00:01:20.000 button. If I click on that, it opens a drop-down menu. 00:01:20.000 --> 00:01:24.000 The second item is Cognos Workspace Advanced. 00:01:24.000 --> 00:01:28.000 Click on that, and depending on your browser, it will open Workspace Advanced 00:01:28.000 --> 00:01:32.000 in either a new tab or a new window. 00:01:35.000 --> 00:01:40.000 Here I'm going to click 'Create New' to start a new report. 00:01:40.000 --> 00:01:44.000 I'm going to create a list report. 00:01:44.000 --> 00:01:48.000 I start with the list report 99 percent of the time, which is kind of like an Excel spreadsheet 00:01:48.000 --> 00:01:50.000 with columns and rows. 00:01:50.000 --> 00:01:56.000 Because I don't want to display actual student data on this video, 00:01:56.000 --> 00:01:60.000 I'm going to change the package from production to the Tutorial Data Warehouse. 00:02:00.000 --> 00:02:03.000 I can display test data on the screen. 00:02:03.000 --> 00:02:08.000 After I select 'list', I click 'okay. 00:02:08.000 --> 00:02:12.000 This is Cognos Workspace Advanced. 00:02:12.000 --> 00:02:16.000 Across the top we have a menu bar with drop-down menus. 00:02:16.000 --> 00:02:20.000 Below that we have a toolbar. 00:02:20.000 --> 00:02:24.000 You can hover over the buttons and it will tell you what they are. 00:02:24.000 --> 00:02:28.000 This large area on the left is our canvas. 00:02:28.000 --> 00:02:32.000 That's where we're going to build our report. 00:02:32.000 --> 00:02:36.000 Over to the right are all the fields we have available in the data warehouse, 00:02:36.000 --> 00:02:40.000 to build the report from. 00:02:40.000 --> 00:02:44.000 I'm going to change one thing before we get into the Data Warehouse. 00:02:44.000 --> 00:02:48.000 By default, Cognos Workspace Advanced 00:02:48.000 --> 00:02:51.000 starts in Page Preview Mode. 00:02:51.000 --> 00:02:55.000 When I add a field to the report, it's going to automatically run the report, 00:02:55.000 --> 00:02:57.000 and display the information in that field. 00:02:57.000 --> 00:02:64.000 As I keep adding fields, it can slow down my report development time, because 00:03:04.000 --> 00:03:06.000 it keeps adding more and more data to the report. 00:03:06.000 --> 00:03:12.000 So, what I'm going to do is go to the 'view' dropdown menu and toggle it 00:03:12.000 --> 00:03:14.000 from 'Page Preview' to 'Page Design. 00:03:14.000 --> 00:03:17.000 Feel free to leave yours in Page Preview mode. 00:03:17.000 --> 00:03:24.000 I'm toggling mine just to speed up our report development. 00:03:24.000 --> 00:03:29.000 Over on the right are the fields in the Data Warehouse that we can build a report from. 00:03:29.000 --> 00:03:32.000 They're organized into folders and sub-folders. 00:03:32.000 --> 00:03:37.000 I can get into registration data and there are sub-folders under there. 00:03:37.000 --> 00:03:40.000 If I open the registration information class roster information, 00:03:40.000 --> 00:03:45.000 there are more sub-folders, and I can keep going down and down. 00:03:45.000 --> 00:03:48.000 Then I get the V-number, name information. 00:03:54.000 --> 00:03:57.000 Cognos Workspace Advanced works best 00:03:57.000 --> 00:03:60.000 if we build a report out of one of these low-level yellow folders. 00:04:00.000 --> 00:04:04.000 Such as 'Registration Information - Class Roster. 00:04:04.000 --> 00:04:08.000 If we want to join information between two 00:04:08.000 --> 00:04:12.000 of those low-level yellow folders, say we want to pull up 00:04:12.000 --> 00:04:16.000 a class roster from a math class and then look at the math placement 00:04:16.000 --> 00:04:20.000 test scores for those students, that would be 00:04:20.000 --> 00:04:22.000 creating a separate report and joining the two together. 00:04:22.000 --> 00:04:28.000 That's something we leave to our more advanced report-making tool, Report Studio. 00:04:28.000 --> 00:04:32.000 The way we have the data structured in the Warehouse, 00:04:32.000 --> 00:04:38.000 you'll get good information if you pull a data field from the folder with the asterix. 00:04:39.000 --> 00:04:41.000 In this case, 'Registration Hours. 00:04:41.000 --> 00:04:45.000 We will need to pull something from Registration Hours, like Credit Hours, 00:04:45.000 --> 00:04:47.000 in order to get good information from this folder. 00:04:47.000 --> 00:04:51.000 We'll get to that in Part Two. 00:04:51.000 --> 00:04:56.000 music 00:04:56.000 --> 00:04:62.000 This is part 2 of 3 of building your first report in Cognos Workspace Advanced. 00:05:02.000 --> 00:05:08.000 Let's get into Cognos. We're building a class roster 00:05:08.000 --> 00:05:12.000 out of the Registration Information - Class Roster area 00:05:12.000 --> 00:05:16.000 in the Data Warehouse. 00:05:16.000 --> 00:05:18.000 I'm going to add some fields. 00:05:18.000 --> 00:05:22.000 I'm going to go into the 'Section Information' sub-folder... 00:05:22.000 --> 00:05:28.000 the 'Section Term' sub-folder...the 'Academic Term' sub-folder. 00:05:28.000 --> 00:05:32.000 The first item in that sub-folder is 'Term Code. 00:05:32.000 --> 00:05:36.000 If I double-click Term Code, it adds it to my report. 00:05:41.000 --> 00:05:45.000 I'm going to minimize that Section Terms sub-folder. 00:05:45.000 --> 00:05:48.000 Get into 'Sections. 00:05:48.000 --> 00:05:51.000 And 'Course'. 00:05:51.000 --> 00:05:55.000 I'm going to double-click 'Subject Code' to add it to the report. 00:05:55.000 --> 00:05:60.000 Double-click 'Course Number. 00:06:00.000 --> 00:06:04.000 Double-click 'Course Title. 00:06:04.000 --> 00:06:08.000 I'm going to minimize the course sub-folder. 00:06:08.000 --> 00:06:11.000 Expand the 'Section' sub-folder. 00:06:11.000 --> 00:06:16.000 Then I'm going to add my CRN by double-clicking it. 00:06:22.000 --> 00:06:25.000 Then I'm going to add some student information. 00:06:25.000 --> 00:06:31.000 So I'm going to expand my 'Students' folder, and double-click 'V-number. 00:06:34.000 --> 00:06:40.000 As I mentioned earlier, we need to add something from the folder with the asterix. 00:06:40.000 --> 00:06:44.000 I'm going to go into 'Registration Hours' and double-click 'Credit Hours. 00:06:50.000 --> 00:06:54.000 So far, I've been double-clicking fields on the right to move them to the report. 00:06:55.000 --> 00:06:58.000 But I realized that I forgot to add in the student name 00:06:58.000 --> 00:06:61.000 so I'm going to go back into the 'Students' folder 00:07:01.000 --> 00:07:08.000 and I'm going to click and drag 'Last Name' over to my report. 00:07:08.000 --> 00:07:11.000 Then I'm going to let my mouse hover between 'V-number' and 'Credit Hours. 00:07:11.000 --> 00:07:15.000 You can see how it opens up a column between those two fields. 00:07:15.000 --> 00:07:20.000 If I let go of the left mouse button, it drops 'Last Name' between those two fields. 00:07:22.000 --> 00:07:30.000 I can do the same with 'First Name. 00:07:30.000 --> 00:07:34.000 I'm also going to bring over the 'Confidentiality Indicator. 00:07:36.000 --> 00:07:40.000 Just in case there are students who have elected to keep their info private. 00:07:44.000 --> 00:07:48.000 Those are all the fields I'm going to add to this class roster report. 00:07:48.000 --> 00:07:52.000 I want to save my progress so I don't lose anything. 00:07:52.000 --> 00:07:56.000 I'm going to go to 'File', 'Save as. 00:07:56.000 --> 00:07:60.000 Just like with a Word document or an Excel spreadsheet. 00:08:00.000 --> 00:08:04.000 In the 'Save as' dialogue box, we have two areas. 00:08:04.000 --> 00:08:08.000 We have our public folders, which is where we house the reports to run. 00:08:08.000 --> 00:08:12.000 And we have 'My Folders,' like 'My Documents. 00:08:12.000 --> 00:08:16.000 I'm going to go into 'My Folders. 00:08:16.000 --> 00:08:20.000 I'm going to name my report 'Class Roster. 00:08:20.000 --> 00:08:24.000 And save it. That's all we're going to do for Part Two. 00:08:24.000 --> 00:08:30.000 music 00:08:30.000 --> 00:08:34.000 This is the last part of 'Building Your First Report Using Cognos Workspace Advanced. 00:08:34.000 --> 00:08:38.000 Let's get right into it. 00:08:39.000 --> 00:08:43.000 We've added the fields that we want to display in our class roster report. 00:08:43.000 --> 00:08:49.000 Now we want to filter the information to only look at one course. 00:08:49.000 --> 00:08:52.000 Over on the right where we added the fields from, 00:08:52.000 --> 00:08:56.000 we're going to get back into 'Section Information. 00:08:56.000 --> 00:08:61.000 Section Terms, Academic Term, Term Code. 00:09:01.000 --> 00:09:04.000 Instead of double-clicking on Term Code to add it to report, 00:09:04.000 --> 00:09:09.000 I'm going to right-click on it, which opens up a little pop-up menu. 00:09:09.000 --> 00:09:14.000 Towards the middle of that menu is the option 'Filter for Report. 00:09:16.000 --> 00:09:20.000 If I click on that, it opens up our filter dialogue box. 00:09:20.000 --> 00:09:24.000 Term codes are stored as numbers. 00:09:24.000 --> 00:09:29.000 The current term is 2016-02 for Winter of 2016. 00:09:30.000 --> 00:09:35.000 By default, Cognos wants to do a numeric comparison to that. 00:09:35.000 --> 00:09:38.000 But I want to pick a specific term code, 00:09:38.000 --> 00:09:44.000 so I'm going to change this 'Comparison' drop-down box to 'Specific Values. 00:09:44.000 --> 00:09:47.000 You can see the dialogue changes. 00:09:47.000 --> 00:09:52.000 On the left-hand side here, we have all the terms that are available to filter by. 00:09:54.000 --> 00:09:61.000 I'm going to do a quick search of 2015 to look at my terms from 2015. 00:10:01.000 --> 00:10:05.000 I'm going to select 2015-01 for Fall of 2015. 00:10:05.000 --> 00:10:09.000 I double-click it to move it over to the right-hand column of 'Selected Values. 00:10:09.000 --> 00:10:13.000 You can also 00:10:16.000 --> 00:10:20.000 single-click it and then hit the yellow arrow in between the two boxes 00:10:20.000 --> 00:10:24.000 to move it over to 'Selected Values. 00:10:24.000 --> 00:10:28.000 Then I click 'OK' at the bottom. 00:10:32.000 --> 00:10:35.000 I also want to filter down to a specific course. 00:10:35.000 --> 00:10:40.000 So I go into the 'Course' folder and right-click on 'Subject Code. 00:10:40.000 --> 00:10:45.000 Go 'Filter for Report. 00:10:45.000 --> 00:10:50.000 On the left-hand side again, we have a list of subject codes that we choose from. 00:10:52.000 --> 00:10:55.000 Because I work in IT, I'm going to select 'Computer Science. 00:10:55.000 --> 00:10:59.000 I can use the up and down arrows on the left side to jump between pages 00:10:59.000 --> 00:10:61.000 of subject codes. 00:11:01.000 --> 00:11:05.000 If I go down one, I find 'CS' for 'Computer Science. 00:11:05.000 --> 00:11:12.000 I can move it over to the 'Selected Values' box on the right, and click 'OK. 00:11:12.000 --> 00:11:16.000 I also want to filter by course number. 00:11:16.000 --> 00:11:20.000 Not add it to the report again, which I accidentally did. 00:11:20.000 --> 00:11:24.000 You can see it's over here on the very end of our report. 00:11:24.000 --> 00:11:28.000 I can click the 'Undo' button in the toolbar to get rid of that. 00:11:30.000 --> 00:11:36.000 If I right-click on 'Course Number' and say 'Filter for Report,' 00:11:37.000 --> 00:11:44.000 I want to look at 'Computer Science 123,' so I'll do a search for '123. 00:11:44.000 --> 00:11:48.000 I'm going to move it over to my 'Selected Values' box. 00:11:48.000 --> 00:11:52.000 Then click 'OK. 00:11:55.000 --> 00:11:59.000 Now I want to see what my report is actually going to display for me. 00:11:59.000 --> 00:11:63.000 In my menu bar I'm going to go to 'View,' and change it from 00:12:12.000 --> 00:12:16.000 There we go. There's my class roster. 00:12:16.000 --> 00:12:20.000 You'll notice that I have multiple CRN's listed. 00:12:20.000 --> 00:12:25.000 This is because there's more than one section for Computer Science 123. 00:12:27.000 --> 00:12:30.000 I want to filter just one CRN. 00:12:30.000 --> 00:12:33.000 I'm just going to pick one of these at random. 00:12:33.000 --> 00:12:36.000 I click on this 11037 CRN. 00:12:36.000 --> 00:12:42.000 It highlights that column and that individual CRN field. 00:12:42.000 --> 00:12:48.000 With that 11037 highlighted, in my toolbar up top, there's a 'filter' button. 00:12:48.000 --> 00:12:52.000 If I click on that filter button 00:12:52.000 --> 00:12:56.000 the top option on the list is 'Include 11037. 00:12:56.000 --> 00:12:62.000 If I click that, it applies a filter so we're only looking at that one CRN. 00:13:07.000 --> 00:13:12.000 So now I have a class roster for one specific section of CS123. 00:13:15.000 --> 00:13:20.000 If I select the 'Last Name' field by clicking on one of the names in that column, 00:13:20.000 --> 00:13:22.000 it highlights that column. 00:13:22.000 --> 00:13:28.000 I can go up to my toolbar to this up and down arrow, which is 'Sort. 00:13:28.000 --> 00:13:32.000 And I can sort it alphabetically. 00:13:41.000 --> 00:13:46.000 While I'm in Cognos Workspace Advanced, I'm just previewing the report. 00:13:47.000 --> 00:13:52.000 I can run it like I would run a report in Cognos by clicking the 'Play' button up top. 00:13:52.000 --> 00:13:56.000 You'll notice there's a little drop-down next to that Play button 00:13:56.000 --> 00:13:60.000 I can run the report directly to Excel. 00:14:20.000 --> 00:14:24.000 With the Last Name field highlighted, I can also perform a count. 00:14:24.000 --> 00:14:28.000 If I click this 'Summarize' button on our toolbar, 00:14:28.000 --> 00:14:32.000 and I click on 'Count,' 00:14:32.000 --> 00:14:34.000 it will give us a count at the bottom of the report. 00:14:34.000 --> 00:14:40.000 We have 44 students in this section. 00:14:44.000 --> 00:14:48.000 I'm going to re-save my report, so just click the 'Save' icon in the toolbar. 00:14:48.000 --> 00:14:52.000 So I don't lose any of my changes. 00:15:00.000 --> 00:15:06.000 In our classes, people ask me 'how do I know what this report is filtered by?' 00:15:06.000 --> 00:15:10.000 In the toolbar, we have the 'Filter' button that we used earlier. 00:15:12.000 --> 00:15:15.000 If I click on that, the bottom option is 'Edit Filters. 00:15:15.000 --> 00:15:19.000 If I click on that, it opens up my Filters dialogue box. 00:15:19.000 --> 00:15:23.000 It shows me everything that we filtered by. 00:15:23.000 --> 00:15:26.000 From here, I can change these filters. 00:15:26.000 --> 00:15:32.000 If I double-click on the Term Code filter here, I can pick a different term. 00:15:32.000 --> 00:15:37.000 Same thing with subject or course number. 00:15:49.000 --> 00:15:54.000 So I've saved this report to 'My Folders. 00:15:54.000 --> 00:15:57.000 How do I get to that report after I'm done? 00:15:57.000 --> 00:15:60.000 I'm going to close this window. 00:16:00.000 --> 00:16:04.000 In Cognos, I'm going to go to 'My Folders. 00:16:04.000 --> 00:16:08.000 You can see my class roster report is listed here. 00:16:08.000 --> 00:16:13.000 I can run it just like I would any other report, by clicking on it. 00:16:16.000 --> 00:16:22.000 If I want to open it to make changes to it, on the right are action buttons. 00:16:24.000 --> 00:16:30.000 The third icon - a circle with a red propeller. 00:16:30.000 --> 00:16:33.000 If I hover over it, it says 'Open with Cognos Workspace Advanced. 00:16:33.000 --> 00:16:36.000 Click on that. 00:16:40.000 --> 00:16:44.000 It opens my report in Workspace Advanced so I can make changes to it. 00:16:49.000 --> 00:16:53.000 That's how to build a basic report with Cognos Workspace Advanced. 00:16:53.000 --> 00:16:57.000 If there's anything that you'd like to have covered in future training sessions, 00:16:57.000 --> 00:16:59.000 please let us know. 00:16:59.000 --> 00:16:61.000 Thank you for watching WOU Tech TV. 00:17:01.000 --> 00:17:08.000 music 00:17:08.000 --> 00:17:10.000 music ends