Cognos Report Studio

Cognos Report Studio

Cognos Report Studio Introduction This document introduces you to Cognos Report Studio. The Report Studio and Query Studio reporting tools are two parts of the Cognos ReportNet web application that allows you to create ad hoc and managed reports. With Query Studio, you created ad hoc reports. Report Studio lets you create and manage complex reports, define layouts and styles, and much more. It offers more flexibility in calculating and in formatting report data. With Report Studio you can • Author complex reports using your data. • Save and reuse reports. • Run reports in a variety of formats. • Print reports. • Create report templates. • Manage reports. Note: You can open a Query Studio report in Report Studio and edit it as a Report Studio report. However, you cannot open a Report Studio report in Query Studio

Prerequisites To use this training material effectively, you should be familiar with producing ad hoc reports using Query Studio. Some exercises in this class build on skills you acquired in the Query Studio class. You should also be familiar with your department’s data. This training is designed to familiarize you with the Cognos Report Studio reporting tool; it is not designed to familiarize you with the underlying data in UD’s system. The training is based on data in a demonstration database. Important Note This training document was designed originally to be used in a class setting. Therefore, the exercises are meant to be followed in a sequence. If you have difficulty working through an exercise, try the first four exercises to familiarize yourself with the basics.

Training Objectives This class was designed using a desktop computer running the Windows XP operating system and using the Internet Explorer (version 6.0.2) browser. If you use a different type of computer or operating system, some of the screen graphics may appear slightly different from those printed here. However, the function of the screen (page) should remain the same. After you complete the exercises in this training material, you should be able to do the following: • • • •

Open the COGNOS Connection web page. Open Report Studio. Create a report. Choose the package for the report.

Copyright © 2004-2005 University of Delaware

1

Cognos Report Studio

• • • • • • • • • • • • • • • • • •

Choose a template for the report. Add query items to the report. Save the report. Run the report. Recognize the types of reports. Change the appearance of reports. Create crosstab reports. Use charts. Filter, sort, and group data. Add prompts. Perform calculations. Create Drill-Through reports. Create reports from scratch. Add styling and images. Understand how reports are structured. Create a Master-Detail report. Create a Parent-Child report. Create a report template.

Conventions The following conventions are used throughout the exercises in this material:

Item

Convention

Text you should type Text you should replace

Courier, bold, 10 point font Courier, bold, italics,  10 point font

Keyboard key names

Bold, Uppercase text (e.g., the ENTER key)

Copyright © 2004-2005 University of Delaware

2

Cognos Report Studio

Menu and Window titles

Bold, mixed-case letters

Table of Contents The Cognos Connection Web Site

5

Exercise 1—Open the Cognos Connection Web Site. Open Report Studio Exercise 2—Open Report Studio and Begin a New Report. The Report Studio Window

6 7

Report Terminology

8

Add Data to a Report Exercise 3—Add Data to a Report and Save It.

9

Run the Report Exercise 4—Validate and Run the Report.

11

Types of Reports

13

List Reports

13

Grouped List Reports Exercise 5—Create a Grouped List Report and Set the Level Span.

13

Create Repeated Form Frames from a List Exercise 6—Create Repeated Form Frames from a List

16

Crosstab Reports Exercise 7—Create a New Crosstab Report. Exercise 8—Create a Crosstab Report from an Existing Report.

18

Charts

22 Exercise 9—View the Types of Report Studio Charts.

Create a Chart Exercise 10—Create a Column Chart. Exercise 11—Create a Combination Chart.

Manipulate Data in Reports

23

27 27

Filters Exercise 12—Add a Filter. Filter on Details and Summaries Exercise 13—Filter on Details. Exercise 14—Create a Summary Filter.

29

Filter Groups

32

Exercise 15—Filter Groups. Copyright © 2004-2005 University of Delaware

3

Cognos Report Studio

Filter Crosstabs

33

Exercise 16—Filter a Crosstab Report.

Prompts Exercise 17—Add a Prompt. Exercise 18—Use the “Build Prompt Page” Tool.

36

Cascading Prompts Exercise 19—Build a Cascading Prompt. Exercise 20—Create a Single-Value Cascading Prompt. Exercise 21—Create a Multiple-Value Cascading Prompt.

41

Optional Prompts Exercise 22—Add an Optional Prompt.

45

Drill Throughs Exercise 23—Create a Target Report. Exercise 24—Create a Parent Report with a Drill Through.

46

Report Layouts

52

Exercise 25—Create a Report Layout. Exercise 26—Create a Crosstab Report from Scratch.

Advanced Techniques

59

Exercise 27—Change the Organization of a List Report. Exercise 28—Add Styling at a Higher Level. Exercise 29—Add an Image or Annotate a Crosstab Report.

Report Studio Queries

64

Exercise 30—Explore the Report Studio Tabular Model. Exercise 31—Work with the Report Studio Cube Definition.

Master-Detail Reports

70

Exercise 32—Create a Master-Detail Report. Exercise 33—Add a Filter to a Master-Detail Report.

Parent-Child Report

76

Exercise 34—Create a Parent-Child Report.

Create an Invoice

81

Exercise 35—Create a Query for an Invoice. Exercise 36—Build the Invoice Page Header. Exercise 37—Create the Invoice Page Body. Exercise 38—Add Data to the Invoice. Exercise 39—Add a List Footer to the Report.

Create a Template

97

Exercise 40—Create a Report Template. Copyright © 2004-2005 University of Delaware

4

Cognos Report Studio

The Cognos Connection Web site Before you can use Report Studio, you must know how to open the Cognos Connection web site. Exercise 1—Open the Cognos Connection Web Site Note: Report Studio works only on the Internet Explorer web browser on a computer running the Windows operating system. 1. Open the Internet Explorer web browser. 2. To access the Cognos Connection web site, type the following URL in the browser’s Address field and then press ENTER: https://cognos.udel.edu/crn/

3. Type your UDelNet ID in the appropriate field. 4. Type your password in the appropriate field. You should see a window similar to the following:

The Cognos Connection web site gives you access to the ReportNet suite of reporting tools: Query Studio and Report Studio. Query Studio allows you to create ad hoc reports from an existing data source. Report Studio allows you to author complex reports based on your business needs. Reports can contain multiple report objects: lists, charts, and crosstabs as well as images, logos, and other elements. End of Exercise

Copyright © 2004-2005 University of Delaware

5

Cognos Report Studio

Open Report Studio In the following exercise, you will open Report Studio and begin to create a new report. It is also possible to open an existing report (this will be discussed later in this class). Exercise 2—Open Report Studio and Begin a New Report 1. At the top right of the Cognos Connection Web Site window, click the

Report Studio link.

You should see the Select a package window, similar to the following, that contains the packages you can use:

2. Click Go Sales and Retailers. Note: Before you can create a report, a UD Cognos administrator must create a package that contains the data you will work with. You will learn the name of the package that contains UD data from the UD Cognos administrator at a later date. (Anyone can create a report using the Go Sales and Retailers training package.) Report Studio will open and you will see a window similar to the following:

Copyright © 2004-2005 University of Delaware

6

Cognos Report Studio

5. In the Welcome box, click

Create a new report.

This brings up a box with a list of layout objects you can use to create your report:

6. Click List to select a list report and click OK. You will see a window like the one below:

End of Exercise

The Report Studio Window The Report Studio window contains an Insertable Objects pane, a Properties Pane, an Explorer Bar and a Work Area. A toolbar above the panes allows you to carry out many functions by clicking an icon; there is also a menu bar at the top of the window with a variety of Report Studio commands. Following is a description of the areas of the Report Studio window: Insertable Objects pane The Insertable Objects pane at the top left of the Report Studio window contains objects that you add to your report. There are three tabs in this pane: Copyright © 2004-2005 University of Delaware

7

Cognos Report Studio

• • •

On the Model tab are items, such as query items, from the package you are using. On the Query Items tab is a description (tree diagram) of the queries you are creating in your report. On the Toolbox tab are other objects you can add to your report like text, calculations, hyperlinks, images, and more.

Properties Pane The Properties pane at the lower left of the Report Studio window lists properties you can apply to a given item in a report. You will work with the Properties pane later in this class. Explorer Bar The Explorer Bar, the vertical bar at the center of the Report Studio window, contains three buttons:

• • •

The Page Explorer lets you navigate to a specific report page or prompt page. The Query Explorer lets you create or change report queries. The Condition Explorer lets you work with variables.

Work Area Report Studio’s right pane is the Work Area, the space where you create your reports.

Report Terminology Before you begin to use Report Studio, you should understand the terminology associated with its reports. Each report has two aspects: • •

A layout Queries

The report layout defines your report’s formatting and appearance. Based on your needs, your report can contain objects such as lists, crosstabs, and charts. It can be formatted to include such features as color backgrounds, images, dates, and more. You also have control over how the data appears on multiple-page reports.

Copyright © 2004-2005 University of Delaware

8

Cognos Report Studio

The pages in Report Studio contain the objects you include in your report. Each page must have a page body. It can also have a header and a footer. Report queries control the data items in your report. The simplest queries are created by adding report items from the package you are using. However, complex queries can contain filters, calculations, grouping and more. You can change the queries Report Studio creates as well as create custom queries.

Add Data to a Report The query items you add to your report appear on the Model tab of the Insertable Objects pane in the Report Studio window. To insert a query item, you can either double-click the item or click the item and drag it into the report. You can use CTRL-click to insert multiple items; items will appear in the order in which you select them. A bold black bar in the work area indicates where you can place query items. In the following exercise, you will create a report that shows the quantity of each product type your retailers sold and the revenue generated by the sales. To do this, you will first add data to your report and save it. Exercise 3—Add Data to a Report and Save It 1. On the Model tab of your list report, click the plus sign to expand the Orders item. 2. Click Retailer name. 3. Hold down the CTRL key, expand the Products item and click Product type. 4. Continue to hold down the CTRL key and in the Orders item, click Quantity and Revenue. 5. Drag the selected items to the work area and release the mouse button when you see the bold black bar. You will see a window like the one below:

Copyright © 2004-2005 University of Delaware

9

Cognos Report Studio

Notice that no data appears in the report yet. You must save and run the report to see the data.

You can obtain information about your report’s structure and the items it contains by selecting some of the other tabs and features in the window: 6. Click the title of the Retailer name column. This produces a list of properties like the one below in the Properties pane at the lower-left of the Report Studio window:

Note that this is a partial list; scroll down to see the remaining properties available to apply to the Retailer name column. Note: When you select a property, you will see a description of what it does at the very bottom of the pane. If the description is not visible, select Property Descriptions from the View menu to see it. Copyright © 2004-2005 University of Delaware

10

Cognos Report Studio

7. In the Insertable Objects pane, click the Query Items tab to see the structure of the query Report Studio has created from your items:

8. Move the mouse pointer over each of the components of the Explorer Bar in the center of the window to see the type of information each provides. Next, you will save your report. 9. On the File menu, click Save As. You will see a box like the one below:

10. In the Name box, type your initials Retailer Revenue. Click Save. End of Exercise

Run the Report After you create a report in Report Studio, you must run it to see the actual data. Report Studio offers a tool to validate the report before you run it to make sure it contains no errors. This will be important when you develop complex reports with features such as calculations.

Copyright © 2004-2005 University of Delaware

11

Cognos Report Studio

In the next exercise, you will validate the Retailer Revenue report and then run it to see the data it contains. Exercise 4—Validate and Run the Report 1. In the Retailer Revenue report, click the Validate

toolbar button.

You will receive a message that the report is being validated, followed by a box indicating whether the report specification is valid. Click OK to close the box. 2. On the toolbar, click the Run

button’s down-arrow and select Run Report (HTML).

Your results will appear in a separate Report Viewer window like the one below. Note that the window below shows a partial list; scroll down to see the remaining data.

End of Exercise

Options for Running Reports In the previous exercise, you will have noticed five options for running your report:

Run Report (HTML)

Produces a report in HTML format. If you run your report in HTML, you can click the Run Options command and type in the Maximum Rows box a number for the maximum number of rows you want to see on each report page.

Run Report (PDF)

Use this option when you want to print your report or

Copyright © 2004-2005 University of Delaware

12

Cognos Report Studio

save a permanent copy. Note: Adobe Acrobat Reader must be installed on your system for you to run your report in PDF format. If you run your report in PDF, you can click the Run Options command to select the output format (PDF), the paper size, paper orientation, and language for the report. Run Report (XLS)

Use this option to produce a report in Microsoft Excel. For limitations associated with this option, see “Producing Reports in Microsoft Excel Format” in the Report Studio Help.

Run Report (CSV)

Use the CSV (comma separated values) option if you want to save your results to be opened in an application like Microsoft Excel.

Run Report (XML)

Use the XML (Extensible Markup Language) option to see an XML representation of the queries, prompts, layouts, and styles in your report.

Note: You cannot run a report in XML or CSV format if it contains more than one query unless the other queries are used as prompts (prompts will be discussed later in this class). View Tabular Data on the Run menu allows you to see only the tabular data (for example, to check that your calculations are correct) before you select one of the Run options.

Types of Reports You can create three types of reports in Report Studio: •

List Report A list report is a representation of your data in rows and columns and is useful for showing detailed database information. The report you created for Retailer Revenue beginning in Exercise 2 is a list report. You can manipulate list report data in various ways to obtain the information you need.

•

Crosstab Report You can create a crosstab report to show information in summary form. The report still has rows and columns, but the intersection of the rows and columns shows a summary of information.

•

Charts You can create a chart to show your information in graphical form. There are several types of graphs you can use in Report Studio. These will be explained later in this class.

Report Studio allows you to combine multiple types of reports in one report.

List Reports Grouped List Reports Copyright © 2004-2005 University of Delaware

13

Cognos Report Studio

In the previous exercises, you created a list report. You may have noticed duplicate values in the Retailer Revenue report you just created. For example, each retailer’s name appears multiple times, once for each product type the retailer sold. You can manipulate the report to sort the data and to hide duplicate values through a process called grouping. Report Studio also allows you to control when values are displayed through a process called setting the level span. In the following exercise, you will create a new list report. You will then group two of the report items and set the level span so that you see certain data when the data changes in a related column. Exercise 5—Create a Grouped List Report and Set the Level Span 1. On the Report Studio toolbar, click the New button to create a new report. If you are asked if you want to save the previous report, click Yes. 2. In the New box, select List and click OK. 3. On the Model tab, expand the Countries item by clicking the plus sign. 4. Click Country. 5. Hold down the CTRL key, expand the Products item, click Product type and Product name. 6. Drag the selected items to the work area and release the mouse button when you see the bold black bar. You will see a window like the one below:

7. On the toolbar, click the Run

button’s down-arrow and select Run Report (HTML).

Notice that there are many duplicate values in the report. You can eliminate duplicates by grouping values. Copyright © 2004-2005 University of Delaware

14

Cognos Report Studio

8. In the Report Studio window, click the title of the Country column; hold down the SHIFT key and click the title of the Product type column to select both columns. 9. On the Report Studio toolbar, click the Group/Ungroup

button.

Grouping adds a symbol to the selected columns to indicate that each column is grouped. Your report columns now look like the ones below:

10. Run the report again to see the effect of grouping the columns. Your results will appear in a separate Report Viewer window like the one below. Note that the window below shows a partial list; scroll down to see the remaining data.

Now each product type appears only once for each country. Suppose you decide that, since each country sells many product types, you would like to have the country name appear each time the product type changes. Report Studio lets you set the level span to do this. 11. In the Report Studio window, click the title of the Country column. Now the properties you can apply to the column appear in the Properties pane:

Copyright © 2004-2005 University of Delaware

15

Cognos Report Studio

12. In the Properties pane, under the Data heading, double-click Level Span. You will see a box like the one below:

13. In the Choose Level box, click Product type and then click OK. Compare your report columns now with the grouped columns in step 8 above. 14. Run the report. Your results should look like those below. Notice that now, each time the product type changes, the country name appears beside it.

15. Close the Report Viewer window. 16. On the Report Studio window File menu, click Save As. Copyright © 2004-2005 University of Delaware

16

Cognos Report Studio

17. In the Save As Name box, type your initials Grouped Country. Click Save. End of Exercise

Create Repeated Form Frames from a List You can use a list report to create repeated form frames for data. For example, you might want to create labels listing each of your retailers with a contact name and city. To do this, you first create a list report. In the following exercise, you will create a list report and convert it to a repeater (creates a single form frame). You will then set properties for the frame to create multiple frames on each page. Exercise 6—Create Repeated Form Frames from a List 1. On the Report Studio toolbar, click the New button to create a new report. If you are asked if you want to save the previous report, click Yes. 2. In the New box, select List and click OK. 3. On the Model tab, expand the Retailers item by clicking the plus sign. 4. Click Retailer name. Hold down the CTRL key and click Contact last name, Contact first name, and City. 5. Drag the selected items to the work area and release the mouse button when you see the bold black bar. 6. On the Structure menu, click Convert List to Repeater. Your report will now contain one form frame and look like the one below:

7. To set the number of form frames to appear on each report page, click anywhere in the background of the frame you have created to bring up its Properties pane:

Copyright © 2004-2005 University of Delaware

17

Cognos Report Studio

8. At the top center of the Properties pane, click the Ancestor box, select Repeater.

button and, from the pop-up

9. Under General in the Properties pane, highlight the number beside Across and type 3. Then highlight the number beside Down and type 3. 10. Run the report. You should see a Report Viewer window like the one below with 9 frames per page. Scroll down to see the remaining pages.

11. Close the Report Viewer window. 12. On the Report Studio File menu, click Save As. 13. In the Save As Name box, type your initials Level Span List. Click Save. End of Exercise

Crosstab Reports A crosstab report shows information in summary form. The report still has rows and columns, but the intersection of the rows and columns shows a Copyright © 2004-2005 University of Delaware

18

Cognos Report Studio

summary of quantitative information. You can create crosstab reports in two ways: • •

Create a new Report Studio report, selecting Crosstab from the New box. Open an existing List report and use the Pivot button to change it into a crosstab.

Crosstab reports can contain nested data to make it easy to compare or increase the information you see. In the following exercise, you will create a new crosstab report. Exercise 7—Create a New Crosstab Report 1. On the Report Studio toolbar, click the New button to create a new report. If you are asked if you want to save the previous report, click Yes.

2. In the New box, select Crosstab the one below:

and click OK. You will see a window work area like

3. On the Model tab, expand the Countries item by clicking on the plus sign. 4. Click Sales territory to select it and drag the selection to the Columns section of the work area. When you see the bold black bar above Columns, release the mouse button. 5. Expand the Product item by clicking on the plus sign. Click Product type to select it and drag the selection to the Rows section of the work area. When you see the bold black bar to the left of Rows, release the mouse button. 6. Expand the Orders item by clicking on the plus sign. Click Quantity to select it and drag the selection to the Measures section of the work area. When you see the Measures area flash black, release the mouse button. 7. Run the report. You will see a window like the one below. The intersection of the rows and columns shows the number of product types sold in each sales territory.

Copyright © 2004-2005 University of Delaware

19

Cognos Report Studio

Suppose you want your crosstab report to show how many of each individual product were sold in each sales territory. You can nest information by dragging additional items into the report rows or columns. 8. In the Orders item, click Product name to select it and drag the selection to the right of Product type in the Rows section of the work area. When you see the bold black bar, release the mouse button. 9. Run the report and compare the results with those in step 7 above. Next you will add an additional measure (quantitative data) to the crosstab. When you do this, each measure is shown as a column. 10. In the Orders item, click Revenue to select it and drag the selection to the right of one of the Quantity items in the Measures section of the work area. When you see the bold black bar, release the mouse button. 11. Run the report. Your Report Viewer window should now look like the one below. Scroll down to see the rest of the information.

At times, a crosstab report may be easier to read if you swap the rows and columns. To do Copyright © 2004-2005 University of Delaware

20

Cognos Report Studio

this: 12. In the Report Studio window, click the Sales territory column title. On the toolbar, click the Swap Rows and Columns

button.

13. Run the report and compare the look of the report with that in step 12 above. Close the Report Viewer window 14. On the Report Studio window File menu, click Save As. 15. In the Save As Name box, type your initials Nested Crosstab. Click Save. Note: You will not perform all these steps for every crosstab report. The steps you select depend upon your individual needs. End of Exercise

Note: If you want to create an Excel-like crosstab, you must be sure that the data item you want Report Studio to recognize as a fact (quantitative data) is dropped into the report as a measure. Otherwise, it will not be treated correctly. In the previous exercise, you created a new crosstab report using a Report Studio template. You can also create a crosstab report using an existing list report. You can open an existing report from within Report Studio or by clicking the Public Folders tab in the Cognos Connection Welcome window, selecting Go Sales and Retailers, and searching for your report. Once you have located the report you want to edit, click the More link at the far right of the line it is on and select Edit with Report Studio. Note: You can open a Query Studio report in Report Studio and edit it as a Report Studio report. However, you cannot open a Report Studio report in Query Studio In the following exercise, you will create a crosstab report based on the Grouped Country report you created earlier. You will open the report in Report Studio and make a few changes before you create the crosstab. Exercise 8—Create a Crosstab Report from an Existing Report 1. On the Report Studio menu, click Open to open an existing report. If you are asked if you want to save the previous report, click Yes. 2. In the Open box, scroll down to the report your initials Grouped Country. Click the report to select it and click Open. 3. To add a measure to the report (every crosstab must have at least one measure), expand the Orders item and double-click Revenue. A Revenue column appears after the last report column. Note that double-clicking is an alternate way to add items to a report. Suppose that to make the report more succinct, you decide to remove the Product name column before you create the crosstab. You can remove Report Studio information either Copyright © 2004-2005 University of Delaware

21

Cognos Report Studio

permanently by clicking the Delete button.

button or temporarily by clicking the Cut

4. Click the title of the Product name column to select it, then click the Cut

button.

The Product Name column is removed. However, it still remains available on the Query Items tab of the Insertable Objects pane. 5. Click the Query Items tab on the Insertable Objects pane to see the structure of the query Report Studio is building. Note that the Product name item is still in the query. If you wanted to add it back into your report, you would drag it to the position you wanted it to occupy. 6. Click the title of the Country column (the item you want to appear in the columns section of your crosstab). 7. On the toolbar, click the Pivot List to Crosstab button then run the report. Your Report Viewer window will look like the one below. Scroll across and down to see the rest of the page.

Charts Charts allow you to present your information graphically. Report Studio offers 9 types of charts with multiple configurations for each type. To see the chart types, you can open the New report box in Report Studio and select Chart. Besides being able to create many different types of charts based on Report Studio templates, you can create custom charts that combine chart types. You can also alter the appearance of a chart by changing chart properties to include background images and foreground and background colors. You can also change chart labels and titles. For combination charts, you can control how the data items will appear. In the following exercise, you will view the types of charts available in Report Studio and learn how to choose the one most suited to your purposes.

Copyright © 2004-2005 University of Delaware

22

Cognos Report Studio

Exercise 9—View the Types of Report Studio Charts 1. On the Report Studio toolbar, click the New button to create a new report. If you are asked if you want to save the previous report, click No. 2. In the New box, select Chart and click OK. You will see a window like the one below:

3. Click some of the chart types in the Chart grouping pane. For each item you select, you will see all the configurations available for that particular chart type in the Chart type pane. 4. Click some of the chart configurations in the Chart type pane. When you select a configuration, you will see the type of chart configuration immediately under Chart type. At the same time, you will see an explanation of how the chart will appear and what it is used for. End of Exercise

Create a Chart When you create a chart in Report Studio, you add data to three drop zones. The numbers in the zone boxes show the order in which the items will be added if you double-click them. Alternatively, you can drag items into the zones where you want them to appear. The zones are: •

Measures:

The numbers (quantitative data) that will be plotted on the Y axis.

•

Categories:

The values that appear on the X axis against which each data series is plotted.

•

Series: A group of related data points. The data series appear in the chart’s legend, with each series having a different color or pattern. “Series” is an optional drop zone when you create a chart.

In the following exercise, you will create a column chart and change some of its properties. The chart will show the quantity of products sold by each order method in 2000, 2001 and 2002. Copyright © 2004-2005 University of Delaware

23

Cognos Report Studio

Exercise 10—Create a Column Chart 1. In the Insert Chart box that you opened in the previous exercise, click the icon for the Column chart and click OK. You will see a work area pane like the one below:

Note the three drop zones and the numbers indicating the order in which data will be placed if you double-click the items. Also note the corresponding numbers on the chart itself that indicate where the data will appear. 2. On the Model tab, expand the Orders item. Click Quantity to select it and drag the selection to the Measures drop zone of the chart. 3. Click Order year to select it and drag the selection to the Categories drop zone of the chart. 4. Click Order method to select it and drag the selection to the Series drop zone of the chart. 5. Run the report to see the chart you have created. Note that Report Studio has added labels for the axes and legend as well as the date, page number, and time at the bottom of the page. The chart itself will look like the one below:

Copyright © 2004-2005 University of Delaware

24

Cognos Report Studio

6. In the Report Studio window, click the Measures drop zone (it now reads Quantity). Note the properties you can apply to the Quantity item. 7. In the Properties pane, click the Text property then click the box with the ellipses at the end of the Text row. (Ellipses indicate that there are additional options for the property). 8. In the Text box, type Number of products sold and click OK. Run the report again to see the title change on the Y axis. 9. On the File menu, click Save As. 10. In the Save As Name box, type your initials Sales Chart. Click Save. If you decide another chart type would better represent your data, you can change types from within the chart window. 11. Click anywhere in the background (white) area of the chart to highlight it. 12. In the Properties pane, under General, click Chart Type. 13. Click the ellipsis box and, from the Column Chart Grouping, select 100 Percent Stacked Column (last icon on the top row). Click OK. 14. Run the report to see how the chart looks now. End of Exercise

Many charts will require that you manipulate data or the chart properties to produce meaningful information, particularly when you are working with multiple measures. Suppose you want to create a chart that shows Revenue generated for the Quantity of products sold in 2000, 2001, and 2002. To do this, in the next exercise, you will create a combination chart and change its properties. Exercise 11—Create a Combination Chart Copyright © 2004-2005 University of Delaware

25

Cognos Report Studio

1. On the Report Studio toolbar, click the New button to create a new report. If you are asked if you want to save the previous report, click No. 2.

In the New box, select Chart and click OK.

3. In the Chart grouping pane, click Combination. 4. In the Chart type pane, click the Combination icon and click OK. 5. On the Model tab, expand the Orders item, click Quantity and drag it to the Measures drop zone. You will see a Chart Type for Measure box. 6. In the Chart Type for Measure box, click the Column icon and click OK. 7. On the Model tab, under the Orders item, click Revenue and drag it to the Measures drop zone, just under Quantity. When you see a bold, black bar, release the mouse button. You will see a Chart Type for Measure box. 8. In the Chart Type for Measure box, click the Line icon and click OK. The Measures drop zone should now look like the following:

The corresponding numbers (1.1, 1.2) appear on the chart model to show where the data will appear when you run the report. 9. Under the Orders item, click Order year and drag it to the Categories drop zone. 10. Run the report. Your chart will look like the one below. Note that since Quantity and Revenue share the same axis, the chart is not meaningful.

You can remedy this problem by changing the chart properties to add another axis and then Copyright © 2004-2005 University of Delaware

26

Cognos Report Studio

mapping the Revenue item to that axis. 11. In the Report Studio window, click anywhere in the background (white) area of the chart to highlight it. 12. In the Properties pane, click the Axis Type property (under General) then, from the dropdown box at the end of the Axis Type row, select Dual. 13. In the Measures box, click Revenue. 14. In the Properties pane, click the Measure Axis property (under General) then, from the drop-down box at the end of the Axis Type row, select Y2. 15. Run the report to produce a more meaningful chart like the one below. Note the dual axes and their labels.

Now the chart shows clearly the information you wanted to extract from the data. 16. Close the Report Viewer window. 17. On the Report Studio File menu, click Save As. 18. In the Save As Name box, type your initials Combination Chart. Click Save. End of Exercise

Manipulate Data in a Report You can manipulate Report Studio data in many ways to produce a report that shows only the information relevant to your audience’s needs. Some of the changes you can make are: • • • •

Add a filter to produce a subset of results. Sort and group data to produce custom reports. Format data to change the appearance of numbers, currency, dates, and times. Perform calculations using your data.

Copyright © 2004-2005 University of Delaware

27

Cognos Report Studio

Filters To produce a report that shows a subset of your data, you can add a filter to a report item. Suppose you produce a report that shows the product types your company sells along with order dates and the quantity sold. You might want your results to show only quantities over 300, excluding all data with numbers less than that. The following exercise will show you how to create this report. Exercise 12—Add a Filter 1. On the Report Studio toolbar, click the New

button to create a new report.

2. In the New box, select List and click OK. 3. On the Model tab, expand the Products item and click Product type. 4. Hold down the CTRL key, expand the Orders item, click Order date and then Quantity. 5. Drag your selections to the work area. 6. Click the title of the Product type item then click the Group/Ungroup toolbar.

button on the

7. Run the report to view the data then close the Report Viewer window. 8. Click the title of the Quantity item then click the Filters see a Filters window like the one below.

9. To create a filter, click the Add like the one below:

Copyright © 2004-2005 University of Delaware

button on the toolbar. You will

button. You will see a Tabular Model Filter window

28

Cognos Report Studio

10. In the Available Components pane, click the Query Items tab and double-click Quantity. Quantity will now appear in the Expression Definition pane. Note: You can also type the entire filter directly into the Expression Definition pane. If you are typing a year, it must be in the format YYYY-MM-DD. 11. Click in the Expression Definition pane just after Quantity and type >=300.  Note: Alternatively, you can click the Functions tab and select the >= function from the list. 12. Click the Validate button to ensure that there are no mistakes in the expression. You will see the validation results in the Information pane of the Tabular Model Filter window. Then click OK. This returns you to the Filters window. 13. Click OK. 14. Run the report. You will see a Report Viewer window like the one below that now shows only quantities greater than or equal to 300. Page down in the window to see all of the data.

Copyright © 2004-2005 University of Delaware

29

Cognos Report Studio

15. On the Report Studio File menu, click Save As. 16. In the Save As Name box, type your initials Quantity Filtered. Click Save. End of Exercise

Note: To edit a filter, click the Report Studio column that has been filtered, then click the Filter icon. In the Filters box, click the filter and then click the Edit

icon.

Filter on Details and Summaries You can filter reports in several ways. In the following exercise, you will create a report, (in this case, total) the data, and filter first on detail data and then on the summary data. Exercise 13—Filter on Details 1. On the Report Studio toolbar, click the New

button to create a new report.

2. In the New box, select List and click OK. 3. On the Model tab, expand the Products item and click Product type. 4. Hold down the CTRL key, expand the Orders item, click Order Method and then Quantity. Drag the selections to the work area. 5. Click the title of the Product type item then click the Group/Ungroup toolbar to group Product type.

6. Click the title of the Quantity item and, on the toolbar, click the Aggregate down-arrow. Click Total.

button on the

button’s

7. Run the report and notice the data in the aggregate report. A summary has been added for each Product type group. Page down to see all of the data:

Copyright © 2004-2005 University of Delaware

30

Cognos Report Studio

8. Click the title of the Quantity column and, on the toolbar, click the Filters 9. In the Filters window, click the Add

button.

button.

10. Click in the Expression Definition pane and type [Quantity]>100. 11. Click the Validate button to ensure that there are no mistakes in the expression. You will see the validation results in the Information pane of the Tabular Model Filter window. Then click OK. This returns you to the Filters window. Click OK. 12. Run the report. You will see a Report Viewer window like the one below that now shows only quantities greater than 100. Page down in the window to see all of the data.

13. In the Report Studio window, click the Filters button then click the filter you created. Note that in the Application box, Before Aggregation is selected:

This means that you filtered on the details in the report—the data in the rows before the aggregation was applied. Cancel the Filters window. Copyright © 2004-2005 University of Delaware

31

Cognos Report Studio

14. On the Report Studio File menu, click Save As. 15. In the Save As Name box, type your initials Filtered Details. Click Save. In the following exercise, you will add a filter to the report you just created to produce a summary filter Exercise 14—Create a Summary Filter 1. In the Filtered Details report, click the Filters button on the toolbar. 2. Click the filter you created earlier and then, in the Usage box, click Disabled. 3. Click the Add

button.

4. Click in the Expression Definition pane and type [Quantity]>5000. 5. Click the Validate button to ensure that there are no mistakes in the expression. You will see the validation results in the Information pane of the Tabular Model Filter window. Click OK. This returns you to the Filters window. 6. In the Filters window Application box, click After aggregation and click OK. 7. Run the report. The summary filter is being applied to the data after the aggregation:

End of Exercise

Filter Groups You can filter information at a higher level by using the Group Filters tab in the Filters window. Group filters are applied at the cube level (this will be discussed later in this class). When you apply a group filter to a report item like Product type, product types that do not fit your specification are filtered out when you run the report. In the following exercise, you will apply a group filter to the Product type report item to eliminate from your report product types where the quantity sold was less than 100,000. Copyright © 2004-2005 University of Delaware

32

Cognos Report Studio

Exercise 15—Filter Groups 1. In the Filtered Details report, click the Filters button on the toolbar. 2. In the Filters window, click the Group Filters tab and then click the Add

button.

3. In the Expression Definition pane, type [Quantity]>100000. 4. Click the Validate Filters window.

button then click OK. This returns you to Group Filters tab in the

5. Click the ellipsis box under Level and, in the Choose level box, click Product type:

6. Click OK. You will see Product type in the Level box in the Filters window. Click OK. 7. Run the report. Notice that all product types with quantities less than 100,000, for example, “First Aid” and “Tools,” do not appear in the report. 8. On the Report Studio File menu, click Save As. 9. In the Save As Name box, type your initials Filtered Groups. Click Save. End of Exercise

Filter Crosstabs You can filter crosstab reports using all the methods we have discussed for filtering list reports. In the following exercise, you will create a crosstab report and filter it in several ways to see subsets of the data you are using. Exercise 16—Filter a Crosstab Report 1. On the Report Studio window toolbar, click the New

button to create a new report.

2. In the New box, select Crosstab and click OK. 3. On the Model tab, expand the Countries item and drag Sales territory into the Columns section of the work area.

Copyright © 2004-2005 University of Delaware

33

Cognos Report Studio

4. Expand the Products item and drag Product type to the Rows section of the work area then drag Product name to the Rows section of the work area. Note: You must drag each item separately. 5. Expand the Orders item and drag Revenue to the Measures section of the work area. 6. Run the report to see the data available to you. Suppose you want to filter your report to see only data for Climbing Accessories and Cooking Gear sold in Central Europe, Northern Europe, and Southern Europe. To do this: 7. Click the Filters button on the Report Studio window toolbar. In the Filters window, click the Add button and create the following filter: [Product type] in (‘Climbing Accessories’,’Cooking Gear’) and [Sales  territory] in (‘Central Europe’,’Northern Europe’,’Southern Europe’)

Note: Each item within the parentheses must be enclosed in single quotes, and items are separated by commas. Also, there can be no spaces between items in parentheses. 8. Click the Validate button then click OK. This returns you to the Filters window. Click OK. Run the report. You should see a report like the one below, showing only the items defined in your filter:

Next you will add a second filter to further refine your data. This time, you will filter out any product whose Revenue is less than $700. 9. Click the Filters button on the Report Studio window toolbar. In the Filters window, click the Add button and create the following filter: [Revenue]>700

10. Click the Validate button then click OK. This returns you to the Filters window. Click OK, and then run the report. You should see a report like the one below: Copyright © 2004-2005 University of Delaware

34

Cognos Report Studio

Notice the difference in the revenue, where products with sales of less than $700 have been removed from the report. Both of the crosstab filters you’ve created have filtered on report details before aggregation. Also, you may have noticed that your report contains two filters. Report Studio has used an “AND” operator to “AND” the filters together to create one filter. You can include multiple filters (both before and after aggregation) in any report. You will now add a third filter to your crosstab report, this time after aggregation. 11. Click the Filters button on the Report Studio window toolbar. In the Filters window, click the Add button and create the following filter: [Revenue]>50000

12. Click the Validate

button then click OK. This returns you to the Filters window.

13. In the Filters window Application box, click After aggregation and click OK. 14. Run the report. You should see a report like the one below:

Now products with revenue of less than $50,000 have been eliminated from the report.

Copyright © 2004-2005 University of Delaware

35

Cognos Report Studio

You can also add totals to crosstab reports. 15. In the Report Studio window, click the title of the Sales territory item and, on the toolbar, click the Aggregate

button’s down-arrow. Click Total.

16. Repeat the Total operation for Product type and Product name. 17. Run the report to see the new summary information that has been added:

Finally, you can add a group filter to your crosstab report. 18. Click the Filters button on the Report Studio window toolbar. In the Filters window, click the Group Filters tab. 19. Click the Add button and create the following filter: [Revenue]>500000

20. Click the Validate Filters window.

button then click OK. This returns you to the Group Filters tab in the

21. Click the ellipsis box under Level and, in the Choose level box, click Product name and click OK. Click OK again to close the Filters window. 22. Run the report. The Report Viewer window will look like the one below:

23. On the Report Studio window File menu, click Save As. 24. In the Save As Name box, type your initials Filtered Crosstab. Click Save.

Copyright © 2004-2005 University of Delaware

36

Cognos Report Studio

Note: You will apply only the filters you need in your reports. The varying types of filters used in this exercise illustrate the possibilities for applying filters and demonstrate that multiple filters can be used in a single report. End of Exercise

Prompts You can give people the ability to customize your report information by adding prompts. Prompts have 3 elements: • • •

Parameters—the choices you give people Prompt controls—the interface where people make their choices. In Report Studio, people choose the information they want in the Report Viewer window. Parameter values—the results of the choices people make.

In Report Studio, you can build prompts in a number of ways. The simplest way is to have Report Studio build the prompt for you. Anytime you add a parameter to a Report Studio filter, Report Studio will prompt the user for information. Beyond that, Report Studio offers a Build Prompt Page tool that creates a default prompt page based on the report items you select. You can customize this report or create your own custom prompt pages. In the following exercise, you will create a report that shows return dates and return quantities for product types. You will then add a prompt that allows a user to select specific product types and date ranges to see only the information that interests them. Exercise 17—Add a Prompt 1. On the Report Studio window toolbar, click the New

button to create a new report.

2. In the New box, select List and click OK. 3. On the Model tab, expand the Products item and drag Product type and Product name into the work area. 4. Expand the Orders item and then expand the Returned items folder within the Orders item. Drag Return date and Return quantity to the right of the two items in the work area (release the mouse button when you see the bold black bar). 5. Click the title of the Product type column then, on the toolbar, click the Group/Ungroup button to remove duplicate values. 6. Click the title of the Return date column then, on the toolbar, click the Sort button’s downarrow and select Sort Ascending. Note: Steps 5 and 6 simply provide some order (removing duplicate values and sorting) to your report before you work with it further.

Copyright © 2004-2005 University of Delaware

37

Cognos Report Studio

7. Run the report to see the available data:

Page down to see the rest of the data. Note that the return dates are in ascending order. 8. Click the Filters button on the Report Studio window toolbar. In the Filters window, click the Add button and create the following filter (this filter contains a parameter named PDate): [Return date]>?PDate?

9. Click the Validate Studio window.

button then click OK. Click OK a second time to return to the Report

10. Run the report. You will notice that the Report Viewer window requires you to choose a date and a time to see the data in your report:

11. Click the left arrow beside the year (2004) bar until the year reads 2002. Change the calendar to June 23 by clicking the month and day. Leave the time as is and click OK (you may have to scroll down to see the OK button). Now your report looks like the one below with only information for returns after June 23, 2002 included. Page down to see the rest of the data.

Copyright © 2004-2005 University of Delaware

38

Cognos Report Studio

12. Remove the filter you’ve created in this report (click the Filters button on the Report Studio window toolbar, highlight the filter then click the

to remove it. Click OK).

End of Exercise

The previous exercise demonstrated the simplest form of a Report Studio prompt—adding a filter with a parameter. This will automatically generate a prompt page in the Report Viewer. In the next exercise, you will use the “Build Prompt Page” tool to create a more complex prompt. Once again, Report Studio will build the prompt for you.

Exercise 18—Use the “Build Prompt Page” tool 1. In the Report Studio window, click the title of the Product type column; hold down the CTRL key and click the title of the Return date column to select both columns. 2. On the toolbar, click the Build Prompt Page button. Based on the report items you selected, Report Studio builds a prompt page that looks like the one below:

Copyright © 2004-2005 University of Delaware

39

Cognos Report Studio

. The prompt page includes buttons at the bottom (Cancel, Back, Next, Finish) that your users will click after they select values for their reports. 3. Run the report. The Report Viewer window will look like the one below (the bottom buttons are not shown here):

4. From the Product type box, select Climbing Accessories; hold down the SHIFT key and select Cooking Gear. 5. In the Return date area, under From, type (or select from the calendar) Jun 23 2001. 6. In the Return date area, under To, type (or select from the calendar) Jun 23 2002. 7. Click Finish. The report will run and will show only products you selected that were returned between June 23, 2001 and June 23, 2002. 8. On the Report Studio window File menu, click Save As.

Copyright © 2004-2005 University of Delaware

40

Cognos Report Studio

9. In the Save As Name box, type your initials Simple Prompt. Click Save. 10. Close the Report Viewer window. 11. In the Report Studio window, the Prompt Page should still be showing. Click inside the Product type prompt box. The Properties pane at the lower left of the window shows the properties available for customizing the Product type prompt. Click inside the Return date box to see its properties as well. You can also see the filters that Report Studio has generated for your Prompt Page by using the Page Explorer bar at the center of the Report Studio window. 12. Click the Page Explorer bar. You will see a panel with the structure of your report:

Note that it contains folders for Report Pages and Prompt Pages. You can move from page to page within the Page Explorer. 13. Click Page 1 under Report Pages. You will again see the page with the items you added to your report. 14. Click the Filters button on the toolbar. You will see the filters Report Studio has created for the prompts. (Hint: to see the entire filter, select it and click the Edit [pencil] button). Click OK to close the Filters window. End of Exercise

Cascading Prompts When you create a Cascading Prompt, your report uses values in a previous prompt to filter values in a succeeding prompt. For example, if your report contains a cascading prompt for Product Type, Product Name, and Description, when you select an item in the Product type prompt box, only the product names for that product type will appear in the next (Product name) box. Likewise, when you select an item in the Product name prompt box, the description for that product will appear in the next (Description) box. In the next exercise, you will create a cascading prompt for Product Type, Product Name, and Copyright © 2004-2005 University of Delaware

41

Cognos Report Studio

Description. Your report will show a product type, the name of a particular product, its description, and cost. Exercise 19—Build a Cascading Prompt 1. On the Report Studio toolbar, click the New button to create a new report. If you are asked if you want to save the previous report, click Yes. 2. In the New box, select List and click OK. 3. On the Model tab, expand the Products item. Click Product type. Hold down the CTRL key and click Product name and Description. 4. Continue to hold down the CTRL key, expand the Orders item and click Unit cost. Drag your selections to the work area. 5. Click the title of the Product type column then, on the toolbar, click the Group/Ungroup button to remove duplicate values. 6. Click the title of the Product type column then hold down the SHIFT key and click the titles of the Product name and Description columns. 7. On the toolbar, click the Build Prompt Page button. Based on the report items you selected, Report Studio will build a prompt page that looks like the one below:

This time, the prompt page contains three text boxes, based on the types of items you selected. You will next create the cascading prompt. 8. Starting at the bottom of the prompt page, click inside the Description prompt box. 9. In the Properties pane, under General, click Cascade Source.

Copyright © 2004-2005 University of Delaware

42

Cognos Report Studio

10. Click the Ellipsis button and, in the Cascade Source box that appears, use the pull-down arrow to select Product name. Click OK. 11. Next, click inside the Product name prompt box. 12. In the Properties pane, under General, click Cascade Source. 13. Click the Ellipsis button and, in the Cascade Source box that appears, use the pull-down arrow to select Product type. Click OK. Because there are two ways to treat cascading prompt reports, you will now save the report you have created as two separate files so that you can try each way. 14. On the Report Studio window File menu, click Save As. 15. In the Save As Name box, type your initials Cascading Prompt Multiple. Click Save. 16. On the Report Studio window File menu, click Save As. 17. In the Save As Name box, type your initials Cascading Prompt Single. Click Save. In the following exercise, you will change the properties in your Cascading Prompt Single report so that the user can get information for one product at a time. This report should still be open on your screen. Note: When you create a cascading prompt report with your own data, you will be using either a single-value or a multiple-value cascading prompt (these terms will become clear as you go through the following exercises). In that case, you will combine the steps in exercises 19 and 20 (single) or exercises 19 and 21 (multiple) to produce the report. The steps are separated here so that we can use the same report items for each exercise and so that you can compare the two types of cascading reports. Exercise 20—Create a Single-Value Cascading Prompt 1. In the Cascading Prompt Single report, click inside the Product type prompt box. 2. In the Properties pane, under General, click Multi-Select then, using the pull-down arrow, select No. 3. Again, under General, click Auto-Submit then, using the pull-down arrow, select Yes. 4. Next click inside the Product name prompt box. 5. In the Properties pane, under General, click Multi-Select then, using the pull-down arrow, select No. 6. Again, under General, click Auto-Submit then, using the pull-down arrow, select Yes. 7. Run the report. In the Report Viewer window, you will only see values in the Product type prompt box. When you click a product type, names of specific product types appear in the Copyright © 2004-2005 University of Delaware 43

Cognos Report Studio

Product name box and so on. This is the cascading nature of the prompt. 8. In the Product type prompt box, click Binoculars. 9. When the values for Binoculars appear in the Product name prompt box, click Seeker 35. 10. In the Description prompt box, click the product description for Seeker 35 and click the Finish button. Your report will look like the one below:

11. Close the Report Viewer window. 12. On the Report Studio window toolbar, click the Save

button to save the report.

End of Exercise

You can also create a cascading prompt that will reprompt you to select the next set of values once you have selected one set. You will create a multiple-value prompt in the following exercise. Exercise 21—Create a Multiple-Value Cascading Prompt 1. On the Report Studio window File menu, click Open. 2. In the Open box, scroll down and open the your initials Cascading Prompt Multiple report. You will insert prompt buttons into your report that will let you move from prompt to prompt. 3. Click the Page Explorer tab in the center of the Report Studio window. 4. Click Prompt Page 1 to open the prompt page. 5. In the Insertable Objects pane, click the Toolbox tab. Scroll down and click Prompt Button. 6. Drag the Prompt Button to the right of the Product type prompt box. 7. Again on the Toolbox tab, click Prompt Button. 8. Drag the Prompt Button to the right of the Product name prompt box. Your prompt page should look like the one below:

Copyright © 2004-2005 University of Delaware

44

Cognos Report Studio

9. Click the first Prompt Button (it is the box that currently says “Next”). In the Properties pane, under General, click Type then, using the pull-down arrow, select Reprompt. 10. Repeat step 9 for the second Prompt Button. 11. Run the report. 12. In the Product type prompt box, click Binoculars; hold down the SHIFT key and click Climbing Accessories. Click the Reprompt button. The values for these items will appear in the Product name prompt box. 13. In the Product name prompt box, click Firefly Charger, hold down the CRTL key and click Firefly Climbing Lamp, Firefly Rechargeable Battery, Seeker 35, Seeker 50, and Seeker Extreme. Click the Reprompt button. Descriptions for all the products you selected appear in the Description prompt box. 14. Below the Description prompt box, click Select All then scroll down (or use the downarrow) and click the Finish button. (You may need to scroll to the right to see the Select All link and scroll back to the left to see the Finish button.) You will see a report like the one below with a description of each of the products you have selected:

15. Close the Report Viewer window. Copyright © 2004-2005 University of Delaware

45

Cognos Report Studio

16. On the Report Studio window toolbar, click the Save

button to save the report.

End of Exercise

Optional Prompts With an optional prompt, the user can choose whether or not to select specific information from prompt boxes you include in a report. The next exercise demonstrates the use of optional prompts. Exercise 22—Add an Optional Prompt 1. On the Report Studio toolbar, click the New

button to create a new report.

2. In the New box, select List and click OK. 3. On the Model tab, expand the Orders item. Click Product name. Hold down the CTRL key, expand the Returned items folder within the Orders item and click Return date. Drag the selected items into the work area. 4. Run the report to see the available data. 5. In the Report Studio window, click the title of the Return date column and on the toolbar, click the Build Prompt Page button. Based on the report item you selected, Report Studio will build a prompt page. 6. Click the Page Explorer tab in the center of the Report Studio window. 7. Under Report Pages, click Page 1 to open the report page. 8. On the toolbar, click the Filters button and select the filter Report Studio has created. 9. In the Filters window Usage box, click Optional then click OK. 10. Run the report. The Report Viewer shows a prompt box that looks like the one below:

Copyright © 2004-2005 University of Delaware

46

Cognos Report Studio

In the Return date prompt box From area, you can select either a specific date and time or Earliest date and in the To area, you can select either a specific date and time or Latest date. 11. In the Return date prompt box From area, click the radio button under From and then change the date to Jun 28 2001 (leave the time as is). 12. In the Return date prompt box To area, select Latest date. 13. Click Finish to run the report. The report shows only products returned between June 28, 2001 and the latest date in the report data. 14. Close the Report Viewer window. 15. On the Report Studio window File menu, click Save As. 16. In the Save As Name box, type your initials Optional Prompt. Click Save. End of Exercise

Drill Throughs Report Studio allows you to drill through from one report to another report that contains related information. Suppose one of your reports shows product information (product type, name, introduction date, quantity, and revenue), and you know that another report lists the retailers who sold those products between 2000 and 2002, their sales targets and actual revenue. You can create a drill-through report so that when someone clicks on a product name in the first report, they will see the report with the retailer information for that product. To create a drill through, you must have a parent report and a target (child) report. In the target report, you create a filter with a parameter that relates to a column in the parent report. The following exercises should make this clear. You will first create the target report.

Exercise 23—Create a Target Report 1. On the Report Studio toolbar, click the New

button to create a new report.

2. In the New box, select List and click OK. 3. On the Model tab, expand the Sales targets item. Click Retailer name. Hold down the CTRL key and click Product name, Sales year, Sales target, and Actual revenue. Drag the selected items into the work area. 4. Click the title of the Retailer name column then, on the toolbar, click the Group/Ungroup button to remove duplicate values. Copyright © 2004-2005 University of Delaware

47

Cognos Report Studio

5. Click the title of the Sales target item, hold down the CTRL key and click the title of the Actual revenue item. On the toolbar, click the Aggregate Total.

button’s down-arrow. Click

Your report columns should look like the ones below:

You will next create a filter with a parameter to make the report the target for another report. 6. On the toolbar, click the Filters

button. In the Filters window, click the Add

button.

7. In the Tabular Model Filter window’s Available Components pane, click the Model tab, expand the Sales Targets item, and double-click Product name. Your window should now look like the one below:

8. In the Expression Definition pane, following [Product name] type =  ?Prodname?

Note: Leave a space before and after the equal sign. Also, note that the parameter name you choose (Prodname, in this case) can be any meaningful name. Now your window should look like the one below:

Copyright © 2004-2005 University of Delaware

48

Cognos Report Studio

9. Click the Validate button. When the Information pane reads No errors, click OK. Click OK a second time to return to the Report Studio window. Now that you’ve done this, the report will use Prodname as a parameter and will give you results based on Product name. 10. Give the report the title Drill Target. To do this, double-click just above the report columns on the words that say Double click to edit text. In the text box that appears, type Drill  Target and click OK. Note: This is the way to add a title to any Report Studio report. 11. On the Report Studio window File menu, click Save As. 12. In the Save As Name box, type your initials Drill Target. Click Save. End of Exercise

Next, you will create the parent report with a drill through. The parent report will provide a column (Product name) that will drill through to the target report you just created. Exercise 24—Create a Parent Report with a Drill Through 1. On the Report Studio toolbar, click the New

button to create a new report.

2. In the New box, select List and click OK. 3. On the Model tab, expand the Products item. Click Product type. Hold down the CTRL key and click Product name and Introduction date. Continue to hold down the CTRL key, expand the Orders item and click Quantity and Revenue. Drag the selected items into the work area.

Copyright © 2004-2005 University of Delaware

49

Cognos Report Studio

4. Click the title of the Product type column then, on the toolbar, click the Group/Ungroup button to remove duplicate values. 5. Click the title of the Quantity item, hold down the CTRL key and click the title of the Revenue item. On the toolbar, click the Aggregate

button’s down-arrow. Click Total.

6. Give the report the title, Drill Parent. To do this, double-click just above the report columns on the words that say Double click to edit text. In the text box that appears, type Drill  Parent and click OK. Next, you will use the Product name column as the drill through to get to the target report. 7. Select the data portion of the Product name column (i.e., do not select the column title). Your work area will look like the one below:

8. In the Properties pane, under Data, double-click Drill Throughs. You will see a window like the one below:

9. Click the Add

button. The Drill Throughs window now looks like the one below:

Copyright © 2004-2005 University of Delaware

50

Cognos Report Studio

10. In the Properties pane of the Drill Throughs window, click the ellipsis button under Target Report. 11. From the Open box, select your initials Drill Target and click Open. You will see a box telling you that a parameter has been added. Click OK. The parameter (Prodname) that you created in the target report now appears below Drill Through in the left pane. 12. Under the Target Report area, click the check box next to Open in new window. The window with the parameter added should look like the one below:

13. Click the parameter you have just added. The window changes to look like the one below:

Copyright © 2004-2005 University of Delaware

51

Cognos Report Studio

Notice under Target parameter in the Properties pane, the parameter you created in the Drill Target report is displayed. 14. The Source query item should read Product name. If it does not, select Product name using the pull-down arrow. 15. Click OK. In the Report Studio window, notice that the Product name values are now underlined links with a plus sign to the left of each value. 16. Run the report. The Report Viewer columns will look like the ones below.

Page down to see the rest of the data. 17. Under Product name, click Seeker Extreme. The report runs and a new Report Viewer window shows the Target Report with more details about the retailers who sold this product.

Copyright © 2004-2005 University of Delaware

52

Cognos Report Studio

Page down to see the rest of the data. 18. Go back to the first Report Viewer window (the Drill Parent window) and click another product name. Compare the results with those you received when you clicked Seeker Extreme. 19. Close all Report Viewer windows. 20. On the Report Studio window File menu, click Save As. 21. In the Save As Name box, type your initials Drill Parent. Click Save. End of Exercise

Report Layouts Report Studio allows you to lay out your own reports using one or any combination of report formats (list, chart, crosstab, etc.) that the software offers. Before you add any type of query to a blank report, you must set up the layout. This requires that you first decide what components your report will contain and what format best suits your data. A report has three components: •

Page header The page header is optional. It contains information, such as a title or logo, that you want to appear at the top of every page of your report.

•

Page body The page body is required. It contains the data derived from your queries.

•

Page footer The page footer is optional. It contains information, such as a page number or date, that you want to appear at the bottom of every page of your report.

You will design your report using tables, much as you would in a word processing or web page development package. Report Studio offers many table features you are familiar with such as including text, changing text attributes (color, font, font size, etc.), changing background color, adding images, nesting objects, and more.

Copyright © 2004-2005 University of Delaware

53

Cognos Report Studio

In the following exercise, you will become familiar with using Report Studio’s table feature to create a report that combines several types of report formats. Exercise 25—Create a Report Layout 1. On the Report Studio toolbar, click the New

button to create a new report.

2. In the New box, select Blank and click OK. You will see a work area with the following at its center: “This page uses flow layout. Objects will be arranged top-to-bottom as in a word processing document. Drop objects here to add them to this page.” 3. On the Report Studio View menu, verify that Boundary Lines is checked. If it is not, click to check it. 4. To add a table object to the work area, on the Insertable Objects’ Toolbox tab, click Table and drag it to the work area. You will see a box like the one below:

5. Set the number of columns to 2 and the number of rows to 1. Make sure that Maximize width is checked. Click OK. You will see a table with one row and two columns at the very top of the work area. In the left table cell, you will place a list report frame. To do this: 6. On the Toolbox tab, click List and drag it into the left table cell. You will see a Create box like the one below:

Copyright © 2004-2005 University of Delaware

54

Cognos Report Studio

7. In the Based on area, make sure New query is selected. Click OK. You will see the familiar list report frame in the left table cell. Next, you will insert a chart frame into the right table cell. 8. On the Toolbox tab, click Chart and drag it into the right table cell (make sure you place it in the table cell which is narrow and at the far right of the work area—see below): ↓↓

Again you will see a Create box (Create – Chart). 9. In the Based on area, make sure New query is selected. Click OK. You will see an Insert Chart box. 10. In the Chart grouping pane, click Column then in the Chart type pane, click Column and click OK. Now a chart report frame appears to the right of the list report. Finally, you will add a crosstab frame just below the table. 11. On the Toolbox tab, click Crosstab and drag it into the work area, just below the table. Again you will see a Create box (Create – Crosstab). In the Based on area, make sure New query is selected. Click OK. You will see a Crosstab report frame below the table (You may recall that the original work area message said that objects are placed top-tobottom. The table and the crosstab are two separate objects.) At this point, your report should look like the one below:

Copyright © 2004-2005 University of Delaware

55

Cognos Report Studio

Next, you will add query items to each report frame. 12. On the Insertable Objects’ Model tab, expand the Countries item and click Country. Hold down the CTRL key, expand the Products item, and click Product line and Product name; expand the Orders item and click Quantity. Drag your selections to the List report. 13. Click the title of the Country column and SHIFT-Click the title of the Product line column then, on the toolbar, click the Group/Ungroup

button to remove duplicate values.

Next, you will add query items to the chart. 14. Again on the Model tab under Orders, click Quantity and drag it to the Measures drop zone of the Chart. 15. Click Order year and drag it to the Categories drop zone of the chart. 16. Click Order method and drag it to the Series drop zone of the chart. The chart is now complete. 17. Under the Model tab Countries item, click Sales territory and drag it to the Columns section of the Crosstab report. 18. Under Products, click Product type and drag it to the Rows section. Under Orders, click Revenue and drag it to the Measures section. 19. Run the report. The report should look like the one below:

Copyright © 2004-2005 University of Delaware

56

Cognos Report Studio

Scroll down (or use the down-arrow) to see the rest of the information on the report page. To see all the information in the list report, page down. To see the bottom portion of the crosstab report, click the Bottom link. 20. Close the Report Viewer window. Note: The queries in this report are not linked in any way. You will see linked queries later in the class. You will now add a page header to your report. 21. On the Report Studio toolbar, click the Page Header & Footer button. Check the box next to Header and click OK. You will see the page header area at the top of the work area. You will next drop two blocks into the page header. A block is a rectangular container into which you can place other objects (text, images, etc.). 22. On the Insertable Objects Toolbox tab, click the Block object and drag it into the page header area. 23. Click the Block object again and drag it slowly until the arrow is beside the first block in the header area. When you see the black flashing line beside the first block (the block icon you are dragging will look like it is in the work area), release the mouse button. (Be sure not to drop the second block into the first one.) You will see the second block below the first one. The report work area should look like the one below:

Copyright © 2004-2005 University of Delaware

57

Cognos Report Studio

24. On the Toolbox tab, click Text Item and drag it into the top Block. In the Text box that appears, type Layout Report. Click OK. 25. Select the words Layout Report and then on the toolbar, click the Font Font box, click Foreground Color.

button. In the

26. In the Foreground Color box, click Green and click OK. Click OK again. The words Layout Report should still be selected; if they are not, click to select them. 27. Center the words Layout Report. To do this, on the toolbar, click the down-arrow next to the Container Alignment selection).

button and click the Align Top Center button (the middle

28. On the Toolbox tab, click Text Item and drag it into the second Block. In the Text box that appears, type List, Graph, and Crosstab Reports. 29. Follow steps 25-27 to change the color and alignment of List, Graph, and Crosstab Reports. 30. Run the report to see the header. Close the Report Viewer window. 31. On the Report Studio window File menu, click Save As. 32. In the Save As Name box, type your initials Layout Report. Click Save. End of Exercise

In the previous exercise, you became familiar with the parts of a Report Studio layout and learned to create a report using the layout elements. In the following exercise, you will use this knowledge to create a Crosstab report from scratch. Once again, you will set up the layout before you add queries. Exercise 26—Create a Crosstab Report from Scratch Copyright © 2004-2005 University of Delaware

58

Cognos Report Studio

1. On the Report Studio toolbar, click the New

button to create a new report.

2. In the New box, select Blank and click OK. 3. On the Report Studio menu, select Structure and then click Page Header & Footer. In the Page Header & Footer box, click Page Header and Page Footer to add them to your report. Click OK. Your report now has its three components: header, body, and footer areas. 4. On the Toolbox tab, click Text Item and drag it into the Page Header. In the Text box that appears, type Revenue by Order Method. Click OK. 5. Center the words Revenue by Order Method by clicking the down-arrow next to the Container Alignment selection).

button and clicking the Align Top Center button (the middle

Next, you will add a crosstab frame to the body of your report. 6. On the Toolbox tab, click Crosstab and drag it into the Page Body (the center part of the work area). 7. You will see a Create box (Create – Crosstab). In the Based on area, make sure New query is selected. Click OK. Now you will see a Crosstab report frame in the work area. 8. On the Report Studio Model tab, expand the Orders item, click Order method and drag it to the Columns section of the Crosstab report. Then click Revenue and drag it to the Measures section. 9. Expand the Products item, click Product type and drag it to the Rows section. Next, you will add footer information to the report. 10. Click the Page Footer area to select it. 11. On the Report Studio menu, select Table and click Insert Table. In the Insert Table box, set the number of columns to 3 and the number of rows to 1. Make sure that Maximize width is checked. Click OK. You will see a table with one row and three columns in the page footer. You will first insert a Date object into the left column. 12. On the Toolbox tab, click the Date object and drag it to the left column of the footer table. 13. On the Toolbox tab, click the Page Number object and drag it to the middle column of the footer table. Click Page Number to select it and then center it as in step 5 above. 14. On the Toolbox tab, click the Time object and drag it to the right column of the footer table. Click Time to select it. Click the down-arrow next to the Container Alignment Copyright © 2004-2005 University of Delaware

button 59

Cognos Report Studio

and click the Align Top Right button (the last selection in the top row). 15. Run the report. You will see a window like the one below:

You may want to change the way some of your report information appears. For example, the title is almost unnoticeable at the top of the crosstab report. You can add more space inside the page header to separate the title from the contents of the report. 16. In the Report Studio window, click inside the Page Header area. 17. In the Properties pane, under Box, double-click Padding. You will see a window like the one below:

In the box at the bottom of the window, type 20. Leave px (pixels) as the measurement. Click OK. You will see an increase in the bottom padding of the page header. 18. In the page header, click the words Revenue by Order Method then click the Font button. In the Font box, select 16 for the Size and Bold for the Weight. Click OK. Note: Report Studio refers to changing attributes like font size, color, weight, etc. as Styling. 19. Run the report again and compare the results with those in step 15 above.

Copyright © 2004-2005 University of Delaware

60

Cognos Report Studio

20. On the Report Studio window File menu, click Save As. 21. In the Save As Name box, type your initials Crosstab from Scratch. Click Save. End of Exercise

Advanced Techniques In Report Studio, you can work with reports so they look exactly as you want them to. You can add information to columns, move data from one column to another, add images, background color, and more. You will see some of these techniques in the following exercises. In the next exercise, you will create a list report that shows your product names, their introduction dates, descriptions, production cost and the revenue they have produced. You will change the report to consolidate all the product information in one column. Exercise 27—Change the Organization of a List Report 1. On the Report Studio toolbar, click the New

button to create a new report.

2. In the New box, select List and click OK. 3. On the Model tab, expand the Products item. Click Product name. Hold down the CTRL key and click Introduction date and Description. Continue to hold down the CTRL key, expand the Orders item and click Production Cost and Revenue. Drag the selected items into the work area. 4. On the Report Studio toolbar, click the Unlock

button.

Note: To insert an item into another object (like a list column), you must first unlock the item. The Unlock button is a toggle button—pressing it again will lock the columns. 5.

On the Insertable Objects Toolbox tab, click Block and drag it into the Product name column, just after the data item. (You should see a small blinking cursor immediately after the Product name item’s final angle bracket. This is where you drop the block.) Repeat this process to add a second block. Your Product name column should look like the one below:

6. Click and drag the Introduction date data item (not the column title) into the top block. Copyright © 2004-2005 University of Delaware

61

Cognos Report Studio

7. Click and drag the Description data item (not the column title) into the lower block. Your work area should look like the one below:

8. Click one of the empty Introduction date cells (not the title cell) and click the Delete button. The whole column disappears. Repeat this process for the Description column. 9. Now your work area looks like the one below:

10. Click the Production cost data item (). Then click the Container Alignment button and click the Align Middle Center button (the middle selection in the second row of choices). Repeat this process for the Revenue data item. 11. Select the title of the Product name column then click the Delete button. To add a new column title, you must first drag a text item into the title area. 12. On the Toolbox tab, click the Text Item and drag it into blank title area (the area from which you deleted “Product name”). In the Text box that appears, type Our Products and click OK. 13. Run the Report. The Report Viewer window should look like the one below:

Copyright © 2004-2005 University of Delaware

62

Cognos Report Studio

14. On the Report Studio window File menu, click Save As. 15. In the Save As Name box, type your initials Advanced Techniques. Click Save. End of Exercise

Another advanced technique allows you to add styling at a higher level than that of individual report items, column titles, etc. When you use this technique, the styling applies to all items of a given type (list, list or crosstab column, table, page, and so on). Moreover, when you add styling at a higher level, any new items added to your report will automatically have the style of the rest of the report. Exercise 28—Add Styling at a Higher Level 1. On the Report Studio menu, click Open to open an existing report (in this case, the first report you created for this class). If you are asked if you want to save the previous report, click Yes. 2. In the Open box, scroll down to the report your initials Retailer Revenue. Click the report to select it and click Open. 3. Click the Retailer name data item (not the column title) to select it. At the top of the Properties pane, you will see an up-arrow beside the words “List Column.” This up-arrow is called the Ancestor button. To add styling at a higher level, you must work with the ancestor (parent) of a particular item. 4. Click the Ancestor

Copyright © 2004-2005 University of Delaware

button. You will see a box like the one below:

63

Cognos Report Studio

5. Click List Columns (second item from the bottom). Notice that the words beside the Ancestor button now read List Columns and all the list columns are selected. 6. In the Properties pane, under Color & Background, double-click Background Color. Click Silver and Click OK. The background of all the list columns changes to silver. 7. Click any of the column titles. Click the Ancestor button and, from the box that appears, click List Column Titles (second item from the bottom). 8. In the Properties pane, under Color & Background, double-click Background Color. Click Aqua and click OK. The background of all the list column titles changes to aqua. 9. Click the Retailer name column title; hold down the CTRL key and click the Product type column title. Click the Group/Ungroup

button to remove duplicate values.

10. Run the report to see that the styling applies to all the columns. Next you will see the effect of adding a new list item to your report. 11. In the Report Studio Insertable Objects pane, under Orders, click Order method and drag it between the Product type and Quantity columns. When you see the flashing black bar, release the mouse button. 12. Run the report again. The new item shares the styling of the other columns because styling was applied at a higher level. You can use this method to change any styling feature (color, alignment, etc.) for any report. 13. On the Report Studio window toolbar, click the Save

button to save the report.

End of Exercise

Report Studio also allows you to add images and annotations to your reports. To illustrate this process, you will use the Crosstab from Scratch report you created earlier. Exercise 29—Add an Image or Annotate a Crosstab Report 1. On the Report Studio File menu, click Open to open an existing report. 2. In the Open box, scroll down to the report your initials Crosstab from Scratch. Click the report to select it and click Open. At the top left of your report (at the intersection of Order method and Product type) is a blank white space. You can insert objects into this cell. 3. Click inside the white space then in the Properties pane under Color & Background, double-click Background Image. 4. In the Background Image box for the Image URL, type Copyright © 2004-2005 University of Delaware

64

Cognos Report Studio

..\samples\images\cube.gif

Click OK. You will see a security box regarding nonsecure items. Click Yes. 5. Run the report. The Report Viewer window should look like the one below:

6. In the Report Studio window Properties pane, double-click Background Image. Highlight the URL you typed previously, delete it, and click OK. The space from which you deleted the image should remain highlighted. 7. From the Insertable Objects pane Toolbox tab, click and drag a Text Item into the highlighted space. In the Text box that appears, type Product Revenue

Click OK. 8. Run the report. The Report Viewer window should look like the one below:

You won’t be saving this report. End of Exercise

Report Studio Queries In order to work with Report Studio queries, you must first understand how the program handles data and structures its queries. Report Studio begins with a tabular model—an object you use to select query items for a report. The tabular model defines a simple list of items. A tabular model data item is a column in your database. The second aspect of the query is a cube definition. The cube consists of dimensions, facts, and filters. Dimensions group aspects of your information and contain hierarchical levels. For instance, when you group data in a report, that data is placed into a hierarchy of levels. You are, in effect, creating a dimension. Facts in the cube are references to data items in the tabular model. Filters eliminate groups of rows from the tabular structure before aggregates are computed. Report Studio uses the cube elements to create dimensions and to calculate the aggregate values for levels you have defined. It calculates the values that have been fed in from the tabular model. This should become clearer as you work through the following exercises. Exercise 30—Explore the Report Studio Tabular Model Copyright © 2004-2005 University of Delaware

65

Cognos Report Studio

1. On the Report Studio toolbar, click the New button to create a new report. If you are asked if you want to save the previous report, click No. 2. In the New box, select List and click OK. 3. On the Model tab, expand the Products item. Click Product type. Hold down the CTRL key and click Product name. Continue to hold down the CTRL key, expand the Orders item, and click Order method and Revenue. Drag the selected items into the list frame. 4. Run the report. Notice that each row of the report has been totaled against the product type, product name, and order method.

5. In the Report Studio window, move the mouse pointer over the Query Explorer bar the center of the page. You will see the query you have created in the report:

at

6. Click Query1 (the link beside the cube). You will see a page like the one below:

The page has 4 frames. The bottom frame, the Tabular Data frame, contains the Tabular Model. The top 3 frames (Dimensions, Facts, and Figures) comprise the cube definition. 7. Double-click the Tabular Model icon in the Tabular Data frame. Now you see a page with three frames: Data Items, Filters, and Children. The Tabular Model is a list of items: Copyright © 2004-2005 University of Delaware

66

Cognos Report Studio

If you examine the list, you will notice that Revenue has an Aggregate symbol over its icon, while the other three items are grouped. The creator of the package for this model specified that Revenue should be totaled. Because there were no such specifications for the other three items, they are grouped by default. It is the Auto Group & Summarize property in the Tabular Model that determines whether the queries in the tabular model are grouped and aggregated. If property is set to No, you will get only details in your report. 8. In the Properties pane for the Tabular Model, double-click Auto Group & Summarize to change the setting to No. 9. Run the report. Since grouping and aggregation have been turned off, you are looking at individual detail rows from the database:

10. In the Report Studio window, move your mouse pointer over the Page Explorer bar the center of the page.

at

11. Click Page 1 to return to your report page. 12. Click the title of the Revenue column then, on the toolbar, click the Aggregate down-arrow. Click Total.

button’s

Now the work area shows a single total at the end of the list. 13. Run the report. In the Report Viewer window, click the Bottom link to go to the bottom of the report. Notice that there is a single total for the detail lines in the report—there is still no grouping because Auto Group & Summarize is set to No. 14. On the Report Studio toolbar, click the Undo Copyright © 2004-2005 University of Delaware

button to remove the total from Revenue. 67

Cognos Report Studio

15. Click the title of the Product type column. Hold down the CTRL key and click the titles of the Product name and Order method columns. Then, on the toolbar, click the Group/Ungroup

button.

Your work area now looks like the one below:

16. Run the report. The Report Studio window looks like the one below:

This looks something like a grouped report, but since Auto Group & Summarize is set to No, we still see data for the detail rows in the report. Page down a few pages to see how the report is structured. Click the Bottom link to go to the bottom of the report. You will see that there is no aggregation. 17. Click the title of the Revenue column then click the Aggregate Click Total.

button’s down-arrow.

18. Run the report. In the Report Viewer window, click the Bottom link to go to the bottom of the report. Now there are group totals and the total for the whole report, but they are still against the unaggregated data (detail rows). 19. In the Report Studio window, move the mouse pointer over the Query Explorer bar (as in step 5 above). Click the Tabular Model link. 20. In the Properties pane for the Tabular Model, double-click Auto Group & Summarize to change the setting to Yes. 21. Run the report. Now the groups are summarized because Auto Group & Summarize is set to Yes:

Copyright © 2004-2005 University of Delaware

68

Cognos Report Studio

If you page down a few pages and then go to the bottom of the report, you will see that the rows are summarized and each level is totaled. 22. On the Report Studio file menu, click Save As. 23. In the Save As Name box, type your initials Tabular Model. Click Save. Now that you have seen how the structure of the Tabular Model is determined, you will look at the cube definition. Remember that values from the Tabular Model are fed into the cube to produce the report. Exercise 31—Work with the Report Studio Cube Definition 1. On the Report Studio toolbar, click the New

button to create a new report.

2. In the New box, select List and click OK. 3. On the Model tab, expand the Products item. Click Product type. Hold down the CTRL key and click Product name. Continue to hold down the CTRL key, expand the Orders item, and click Order method and Quantity. Drag the selected items into the list frame. 4. Run the report. Notice that each row of the report has been totaled against the product type, product name, and order method.

5. In the Report Studio window, move the mouse pointer over the Query Explorer bar the center of the page.

at

6. Click Query1 (the link beside the cube). You will see the query you have created in the report. The three columns at the top of the page show the cube definition:

Since you have not grouped any items, there are no dimensions in the report.

Copyright © 2004-2005 University of Delaware

69

Cognos Report Studio

7. In the Report Studio window, move your mouse pointer over the Page Explorer bar the center of the page.

at

8. Click Page 1 to return to your report page. 9. Click the title of the Product type column. Hold down the CTRL key and click the titles of the Product name and Order method columns. Then, on the toolbar, click the Group/Ungroup

button.

10. Run the report. The results should be familiar to you as those of a grouped report. 11. Repeat steps 5 and 6 above to again see the cube definition. Because you have grouped some list items, your definition now looks like the one below:

The grouped items now appear in the Dimensions area. The report has one dimension, indicated by the icon. There are three hierarchical levels (Product type, Product name, and Order method), indicated by the icons. By default, the Dimension and the first level share the same name. Within each level are categories, indicated by a Key . 12. To see the Keys, click the plus sign beside each of the levels. You cube definition now looks like the one below:

The categories, represented by the Keys, contain the values from the database. 13. Repeat steps 7 and 8 above to return to the Report Studio report page. 14. Click the title of the Quantity column then click the Aggregate Click Total.

button’s down-arrow.

15. Run the report. In the Report Viewer window, click the Bottom link. At its bottom, the report should look like the one below:

Copyright © 2004-2005 University of Delaware

70

Cognos Report Studio

Notice that each group now has a total and there is a summary total for the entire report. 16. On the Report Studio window File menu, click Save As. 17. In the Save As Name box, type your initials Cube Definition. Click Save. End of Exercise

Master-Detail Reports In the following exercise, you will create a master report that contains information on orders for your company, including order numbers, the sales representative who placed the order, and the order date. You will then create a report inside the master report that includes details for each order number, including the order number, product name, quantity purchased and revenue. You will link the two reports to create a single report. Exercise 32—Create a Master-Detail Report 18. On the Report Studio toolbar, click the New

button to create a new report.

19. In the New box, select Blank and click OK. 20. On the Report Studio View menu, verify that Boundary Lines is checked. If it is not, click to check it. 21. On the Insertable Objects pane Toolbox tab, click List and drag it into the work area. You will see a Create - List box like the one below:

Copyright © 2004-2005 University of Delaware

71

Cognos Report Studio

22. In the Based on area, make sure New query is selected. Click OK. 23. On the Model tab, expand the Orders item. Click Order date. Hold down the CTRL key, expand the Sales reps item and click Staff name. Continue to hold down the CTRL key, and in the Orders item, click Order number. Drag the selected items into the work area. You will next create a second query inside the first one. 24. On the Report Studio toolbar, click the Unlock

button.

25. On the Toolbox tab, click List. Drag it to the Order number column and drop it just after the first Order number data item () when you see the small flashing black line. 26. You will see a Create box (Create – List) for the second query. In the Based on area, make sure New query is selected. Click OK. Your work area should look like the one below:

You will next add items to the list you have just created. 27. On the Model tab, expand the Orders item. Click Order number. Hold down the CTRL key and click Product name, Quantity and Revenue. Drag the selected items into the Copyright © 2004-2005 University of Delaware

72

Cognos Report Studio

second list frame. Your work area should look like the one below:

You will now link the two reports in a master-detail relationship. 28. From the Report Studio Data menu, click Master/Detail Relationships. You will see a box like the one below:

You will first define the Master Query for your report. 29. Make sure Query1 is showing in the Master query pull-down box. Then click the Add button. You will see a box like the one below:

30. In the Add box, click the radio button next to Filtered detail query and click OK. You will next see a Choose Detail Query box like the one below: Copyright © 2004-2005 University of Delaware

73

Cognos Report Studio

31. Highlight Query2 and click OK. You will see a Master Detail Link Filter box like the one below:

32. Under Facts in Query1, click Order number and drag it into the Expression Definition pane. 33. In the Expression Definition pane, type an equal sign after [Order number]. 34. Under Query2, click Order number and drag it into the Expression Definition pane following the equal sign. Your expression should look like the one below: [Query1].[Order number]=[Query2].[Order number] 35. Click the Validate

button. When the Information pane reads No errors, click OK.

36. You will again see the Master Detail Relationships box that now shows the Master-Detail relationship you have defined. Click OK. Run the report. The Report Viewer window should look like the one below:

Copyright © 2004-2005 University of Delaware

74

Cognos Report Studio

The Master-Detail report shows the order information combined with details about each order number. 37. On the Report Studio window File menu, click Save As. 38. In the Save As Name box, type your initials Order Master­Detail. Click Save. You can clean up the report by eliminating the repetition of the order number. To do this: 39. In the Detail report, click the background (i.e., not on any of the words) of the Order number column and click the Cut

button.

40. Run the report. The Report Viewer window should look like the one below:

Now the Order number is listed once with the details underneath. Here is why this works: You may remember that if you cut an item in Report Studio, it remains in the Query (Query2). Thus, the data—although hidden—is still available to maintain the link between the Master and Detail queries. Even if you save and reopen the report, the item remains in the query and you can drag it back into the report at any time. Copyright © 2004-2005 University of Delaware

75

Cognos Report Studio

You can also add filters to Master-Detail reports. Suppose, for example, that you wanted to eliminate all the product names for types of binoculars from your report. You can create a filter so that you will see all product names except those for binoculars. In the next exercise, you will add a filter to a Master-Detail report. Make sure that the Order Master-Detail report is still open. Exercise 33—Add a Filter to a Master-Detail Report 1. On the Report Studio File menu, click Save As. 2. In the Save As Name box, type your initials Order Master­Detail Filtered.  Click Save. You will add the filter to the Detail report (Query2).

3. Move your mouse pointer over the Query Explorer bar at the center of the Report Studio page. You will see the queries you have created in report:

4. Click Query2 (the link beside the cube). You will see the cube definition for your page. 5. On the Insertable Objects pane Toolbox tab, click the Filters item and drag it to the Filters section of the cube definition. This opens a Group Filter box in which you can add an expression for the filter. You will create a filter to eliminate binoculars from your report. 6. Type the following in the Expression Definition area: [Product name] not in ('Seeker 35','Seeker 50','Seeker  Extreme','Seeker Mini') Note: Each item within the parentheses must be enclosed in single quotes and items are separated by commas. Also, there can be no spaces between items in parentheses. Your window should look like the one below:

Copyright © 2004-2005 University of Delaware

76

Cognos Report Studio

7. Click the Validate button to validate the expression. When the Information area reads “No Errors,” click OK. 8. Run the report. You will not see any instances of the binocular names you have filtered out. 9. On the Report Studio menu, click Save to save the report. End of Exercise

Parent-Child Reports In the following exercise, you will create a parent report that contains information on orders for your company, including order numbers, retailer names, product names, quantity, and unit price. You will then create a child report that includes details for staff names, retailer names, product names, actual revenue, and sales target. You will join the queries to produce a report that shows orders in quantities greater than 10. The report will also show that information for a particular sales person, including the revenue generated by the sale. Exercise 34—Create a Parent-Child Report 1. On the Report Studio toolbar, click the New

button to create a new report.

2. In the New box, select Blank and click OK. 3. On the Report Studio View menu, verify that Boundary Lines is checked. If it is not, click to check it. 4. On the Insertable Objects pane Toolbox tab, click List and drag it into the work area. You will see a Create - List box like the one below:

Copyright © 2004-2005 University of Delaware

77

Cognos Report Studio

5. In the Based on area, make sure New query is selected. Click OK. 6. On the Model tab, expand the Orders item. Click Order number. Hold down the CTRL key and click Retailer name, Product name, Quantity and Unit price. Drag the selected items for the Parent query into the work area. You will next create the Child query.

7. Move your mouse pointer over the Query Explorer bar at the center of the Report Studio page. You will see the query you have created in the report:

8. Click Tabular Model. 9. On the Insertable Objects pane Toolbox tab, click Tabular Model and drag it into the Children area at the bottom of the work area. Note that Report Studio names this Tabular Model1. 10. Double-click the Tabular Model1 icon to open the work area for the Child query. 11. On the Model tab, expand the Sales targets item. Click Staff name. Hold down the CTRL key and click Retailer name, Product name, Actual Revenue and Sales target. Drag the selected items into the Data Items area of Tabular Model1. The work area should look like the one below:

Copyright © 2004-2005 University of Delaware

78

Cognos Report Studio

You will next drag the items you want to filter to the Filters area at the right side of the window and set the criteria on which you want to filter. 12. From the Tabular Model1 Data Items list, click Staff name and drag it to the Filters area at the right side of the window. This opens the Tabular Model Filter window. In the Expression Definition pane, after [Staff name], type = ’Bart Scott’ You will see a box like the one below:

Copyright © 2004-2005 University of Delaware

79

Cognos Report Studio

Click OK. 13. Move your mouse pointer over the Query Explorer bar at the center of the Report Studio page. In the Query Explorer box, click Tabular Model. You will see the data items you added for the Parent Query. You will next drag the items you want to filter to the Filters area at the right side of the window and set the criteria on which you want to filter. 14. From the Tabular Model Data Items list, click Quantity and drag it to the Filters area at the right side of the window. This opens the Tabular Model Filter window. In the Expression Definition pane, after [Quantity], type >10 and click OK. Next you will add a filter for the item from the Parent Query (Tabular Model) that you want to join to the Child Query (Tabular Model1). 15. From the Tabular Model Data Items list, click Product name and drag it to the Filters area at the right side of the window. This opens the Tabular Model Filter window. 16. In the Expression Definition pane, after [Product name], type = 17. Still in the Tabular Model Filter window, click the Query Items tab at the bottom of the left pane. Your window will look like the one below.

18. Under Tabular Model1, double-click Product name. The expression in the Expression Definition pane should look like the one below: [Product name]=[Tabular model1].[Product name] Copyright © 2004-2005 University of Delaware

80

Cognos Report Studio

Click OK. 19. On the Insertable Objects pane Toolbox tab, click Query Items. Drag all the items under Tabular Model1 to the Data Items section of the work area. 20. Move your mouse pointer over the Query Explorer bar at the center of the Report Studio page. In the Query Explorer box, click Query1. 21. On the Insertable Objects pane Toolbox tab, click Query Items. Drag all the items that are not already in the Facts section of the work area to that section. Your work area will look like the one below:

22. Move your mouse pointer over the Query Explorer bar at the center of the Report Studio page. In the Query Explorer box, click Query1 again. 23. In the Properties pane at the lower left of the window, scroll down and click Cross Product Allowed. From the pull-down menu, select Allow. 24. Click the Page Explorer bar in the middle of the Report Studio window and click Page1. 25. On the Insertable Objects pane Query Items tab, double-click each item that is not already in the work area. Your work area will look like the one below:

Copyright © 2004-2005 University of Delaware

81

Cognos Report Studio

26. On the Report Studio window File menu, click Save As. 27. In the Save As Name box, type your initials Parent­Child. Click Save. 28. You can clean up the report by eliminating the repetition of the Product Name. To do this: 29. In the Detail report, click the background (i.e., not on any of the words) of the Product name1 column and click the Cut

button.

30. Run the report. The Report Viewer window should look like the one below:

Close the Report Viewer window. 31. On the Report Studio window File menu, click Save. End of Exercise

Create an Invoice You will next create an invoice using the data from GO Sales and Retailers. The purpose of this exercise is to show you how to have the greatest control over your reports by defining the query and controlling how the report pages will be handled. The invoice incorporates such features as calculations, number formatting, styling on high levels and more. You will become aware of Report Studio best practices as you move through the exercise. When you finish the exercise, your invoice should look like the one below:

Copyright © 2004-2005 University of Delaware

82

Cognos Report Studio

If you look at the example above, you notice that there is information at the top (in the page header) that will appear on every invoice. The rest of the information appears in the page body. There is no page footer. Before you construct your invoice, you will define a query and then associate your page with a query item (Order number). The purpose of doing this is to have a new invoice page begin each time there in a new invoice (Order) number. Exercise 35—Create a Query for an Invoice 1. On the Report Studio toolbar, click the New

button to create a new report.

2. In the New box, select Blank and click OK. To create the query for your page: 3. Click the Query Explorer bar in the middle of the Report Studio window and click Queries:

4. On the Insertable Objects Toolbox tab, click the Query item and drag it to the work area.

5. Double-click the Query

Copyright © 2004-2005 University of Delaware

icon to open it. You will see a query page with no data.

83

Cognos Report Studio

6. On the Insertable Objects Toolbox tab, click the Tabular Model object and drag it into the Tabular Data frame at the bottom of the query page. 7. Double-click the Tabular Model item You will next add a data item from the GO Sales and Retailers package model directly into the query. 8. On the Insertable Objects Model tab, under Orders, click Order number and drag it into the Data Items frame. 9. Click the Query Explorer bar in the middle of the Report Studio window and click Query1. You will see the query page with the tabular model you added and the empty cube definition frames at the top. 10. On the Insertable Objects Query Items tab, click Order number and drag it into the Dimensions frame. Note: Make sure that you drag the Query Items Order number item and not the Order number from the Model tab. Report Studio creates a Dimension that looks like the following (click the plus signs to see the entire dimension):

You will next associate your report page with the Order number query item. After you have done this, Report Studio will begin a new invoice page each time the order number changes. 11. Click the Page Explorer bar in the middle of the Report Studio window and click Report Pages (the link next to the folder).

You will see a Pages box like the one below:

Copyright © 2004-2005 University of Delaware

84

Cognos Report Studio

12. Click Page1 to highlight it and then click the Page Breaks box. You will see a box like the one below:

button at the bottom of the

13. Using the pull-down arrow under Break pages using this query, select Query1. Then click the radio button next to Page breaks by grouping level (recommended) and check the box next to Order number. The Page Breaks box should now look like the one below:

Copyright © 2004-2005 University of Delaware

85

Cognos Report Studio

14. Click OK. This will return you to the Pages box which now shows Page 1 associated with the Order number item:

15. Click OK. 16. On the Report Studio window File menu, click Save As. 17. In the Save As Name box, type your initials Invoice. Click Save. End of Exercise

Now that you have structured the report query, in the next exercise, you will build the page header for your report. This is the recommended procedure for building reports if you start with a blank page. First define the query and then build the page starting with the page header, progressing to the page body, and ending with the page footer (if there is to be one). You should still be in the your initials Invoice file. Exercise 36—Build the Invoice Page Header 1. Click the Page Explorer bar in the middle of the Report Studio window and click Page1. You will see a blank work area. Copyright © 2004-2005 University of Delaware

86

Cognos Report Studio

If you look at the sample Invoice on page 75, you notice that the page header contains invoice information, the date, a logo, address and phone number. 2. From the Report Studio Structure menu, select Page Header & Footer. 3. In the Page Header & Footer box, check the box next to Header and click OK. You will see the page header frame at the top of the work area. Report Studio Best Practice: When you create a report, first identify any horizontal bands that will extend the width of your report. Horizontal bands suggest that you should use a block object to contain your information. If there are no horizontal bands, look for vertical bands. Vertical bands suggest a table object as a container for your information.

Look at the sample invoice. There is no information that extends across the entire header. There are two vertical bands of information (the invoice information and the University information), so you will add a table object to the header. 4. On the Insertable Objects Toolbox tab, click the Table object and drag it into the page header. In the Insert Table box, set up the table to be 2 columns and 1 row. Click the radio button next to Maximize width and click OK. You will see the table frame inside the page header area. Look at the sample invoice. Each table cell contains bands of horizontal information (information that extends across the cell). To add the information, you will first add blocks to contain it. The invoice information on the left has 3 lines (bands) and requires 3 blocks. The information on the right requires 4 blocks. 5. On the Insertable Objects Toolbox tab, click the Block object and drag it into the left table cell. Repeat this process to add two more blocks (when you drag the second and third blocks into the cell, wait till you see the small flashing line at the end of the previous block to release the mouse button). 6. On the Insertable Objects Toolbox tab, click the Text Item object and drag it into the top block. In the Text box that appears, type INVOICE. Click OK. 7. Click the word INVOICE to select it and, in the Properties pane, double-click Font. For the Size select 36 pt and for the Weight, select Bold. Click OK. 8. On the Insertable Objects Toolbox tab, click the Text Item object and drag it into the second block. In the Text box that appears, type Invoice No.: . (Be sure to include the space after the colon.) Click OK. 9. On the Insertable Objects Query tab, click the Order number Key:

Copyright © 2004-2005 University of Delaware

87

Cognos Report Studio

Drag it beside the Invoice No.: text you just added in the second block. 10. On the Insertable Objects Toolbox tab, click the Text Item object and drag it into the third block. In the Text box that appears, type Date: . (Be sure to include the space after the colon.) Click OK. 11. On the Insertable Objects Toolbox tab, click the Date object and drag it beside the Date: text you just added to the third block. The left page header cell should now look like the one below:

12. On the Report Studio File menu, click Save to save your report. 13. On the Insertable Objects Toolbox tab, click the Block object and drag it into the right table cell. Repeat this process to add three more blocks (when you drag the second, third, and fourth blocks into the cell, wait till you see the small flashing line at the end of the previous block to release the mouse button). 14. On the Insertable Objects Toolbox tab, click the Image object and drag it into the top block. Select the Image object and, in the Properties pane, double-click URL. In the box that appears, type ..\samples\images\logo.jpg

Click OK. 15. On the Insertable Objects Toolbox tab, click the Text Item object and drag it into the second block. In the Text box that appears, type University of Delaware. Click OK. 16. On the Insertable Objects Toolbox tab, click the Text Item object and drag it into the third block. In the Text box that appears, type Newark, DE 19716. Click OK. 17. On the Insertable Objects Toolbox tab, click the Text Item object and drag it into the fourth block. In the Text box that appears, type (302)831­2791. Click OK. 18. Select any block in the right table cell. On the Properties pane, click the Ancestor and click Table Cell.

Copyright © 2004-2005 University of Delaware

button

88

Cognos Report Studio

19. In the Table Cell Properties pane, click Horizontal Alignment and, from the pull-down menu, select Right. The right table cell should look like the one below:

You will next set the font style for your entire invoice report. 20. Select any of the text items you added to the page header. On the Properties pane, click the Ancestor button and click Page. 21. In the Page Properties pane, double-click Font and, for Family, select Arial and click OK. Report Studio Best Practice: Apply styling at the highest possible level in your report. This way, you set such things as font family, weight, color, etc. once and the setting will apply to all like items in your report. 22. Click the background area of the left table cell. On the Properties pane, click the Ancestor button and click Page Header. 23. In the Page Header Properties pane, double-click Border. For Color, select Blue, for Style, select Solid line, and for Width, select 1pt. Click the bottom border on the diagram and click OK. 24. In the Page Header Properties pane, double-click Padding. Set the bottom padding to 10px and click OK. 25. Save your report and run it to see the page header you have created. Page down in the report. You will notice that the invoice number changes as you go through the pages. The next step is to create the body of the invoice. If you look at the sample invoice on page 75, you will notice that there are two horizontal bands in the body—the sales information rows and the product information rows. While the information at the bottom of the invoice (No Returns Without Receipt, etc.) looks like a page footer, it is not. It is part of the second band of information. Exercise 37—Create the Invoice Page Body 1. On the your initials Invoice report’s Insertable Objects Toolbox tab, click the Block object and drag in into the page body work area. Repeat this process to add a second block (when you drag the second block into the work area, wait till you see the small flashing line at the end of the previous block to release the mouse button). Note on the sample invoice on page 75 that the first block has 3 vertical bands; this means that you should insert a table into the first block. Copyright © 2004-2005 University of Delaware

89

Cognos Report Studio

2. On the Insertable Objects Toolbox tab, click the Table object and drag in into the top block. 3. In the Insert Table box, type 3 for the number of columns and 2 for the number of rows. Click the box beside Maximize Width and click OK. 4. On the Insertable Objects Toolbox tab, click the Text Item object and drag in into the topleft table cell. In the Text box that appears, type Sales Person and click OK. 5. Repeat step 4 for the top-middle block. In the Text box that appears, type Order Method and click OK. 6. Repeat step 4 for the top-right block. In the Text box that appears, type Shipping Date and click OK. 7. Click the background of any table cell to select it. On the Properties pane, click the Ancestor button and click Table. 8. In the Table Properties pane, click Horizontal Alignment. Using the pull-down arrow, select Center to center the text in the table cells. Next, you will add a border to the table that includes vertical lines between the cells. 9. Click the background of the top-left table cell. CTRL-click the backgrounds of all the other cells in the table. 10. In the Table Cell Properties pane, double-click Border. Click all four borders on the diagram and set the borders to black and solid line; set the width to 1px (use the pull-down arrow under the Width box to select px). Click OK. You will next add a background color to the top table row. 11. Click the background of any table cell in the top row to select it. On the Properties pane, click the Ancestor button and click Table Row. 12. Double-click Background Color, click Silver, and click OK. 13. Save the invoice report. In the next exercise, you will add data from the package model to your invoice report. Exercise 38—Add Data to the Invoice 1. On the Insertable Objects Model tab, expand the Sales reps item, click Staff name, and drag it into the bottom-left table cell. (Note that since you set horizontal alignment on the table level, the Staff name text is automatically centered.) 2. Expand the Orders item, click Order method, and drag it into the bottom-middle table cell. 3. On the Insertable Objects Toolbox tab, click the Date object and drag it into the bottomright table cell.

Copyright © 2004-2005 University of Delaware

90

Cognos Report Studio

4. Save the invoice report. Your page body table should look like the one below:

5. Run the report. Page down to see the invoice numbers and staff names change. Notice that your table is nearly hiding the blue border line you added to your page header. To add more space above the table: 6. Click the background of the page body. In the Page Body Properties pane, click Padding. Set the top padding to 10px and click OK. Now the invoice header and page are better defined. Next you will add the product information for your invoice. 7. Click the bottom block in the page body. In the Block Properties pane, click Padding. Set the top padding to 20px (use the pull-down arrow under the Width box to select px) and click OK. 8. On the Insertable Objects Toolbox tab, click the List object and drag it into the bottom block. Note: You will associate the list with the query you have already defined so that the data items you add to the list will become part of the tabular model for that query. (Hint: after you have associated your list with the query and added the data in step 10 below, click on the Query Explorer tab and then click Query1 to see how the data is being handled for the whole report.) Remember that all the data in your page is associated finally with the Order number dimension so that each time the Order number changes, Report Studio begins a new page that includes all the data for that Order number. 9. In the Create - List box, click the radio button next to Existing Query. Using the pull-down button, select Query1. Uncheck the box next to Auto populate. The box should look like the one below:

Click OK.

Copyright © 2004-2005 University of Delaware

91

Cognos Report Studio

10. On the Insertable Objects Model tab, expand the Products item and, within Products, expand the Product Codes item. Click Product number. Hold down the CTRL key and, under Products, click Product name and Description. Continue to hold down the CTRL key, expand the Orders item, and click Unit price and Revenue. Drag the selections to the list frame you added. 11. Click one of the list column titles. On the Properties pane, click the Ancestor click List Column Titles.

button and

12. In the List Column Titles Properties pane, double-click Background Color. Click Gray and click OK. 13. Click the Ancestor button and click List. In the List pane, double-click Font and set the Size to 8pt. Click OK. To make the invoice look like the sample one on page 75, you will combine the Product name and Description in one column. 14. On the Report Studio toolbar, click the Unlock

button.

15. On the Insertable Objects Toolbox tab, click the Block object and drag it into the Description data item cell. When you see a small blinking line after , release the mouse button. Repeat the process to add a second Block object below the first. Your list should look like the one below:

16. Click the data item and drag it into the first block you added. Then click the data item and drag it into the second block. The list should look like the one below:

17. On the Report Studio toolbar, click the Lock

button.

18. Click one of the blank cells in the Product name column and click the Delete

Copyright © 2004-2005 University of Delaware

button.

92

Cognos Report Studio

19. Click the Revenue column title. In the List Column Title Properties pane, double-click Text and, in the Text box that appears, type Price. 20. Save and run your invoice report. End of Exercise

For the final part of creating the Invoice, you will add the material for the bottom portion of the report. Exercise 39—Add a List Footer to the Report 1. In the your initials Invoice report, click the title of the Price column and on the toolbar, click the Aggregate button’s down-arrow. Click Total. Doing this produces a list group footer () and an overall footer (Summary). Note: The list group footer is Order number because that is the only dimension you have defined for this report—there is no other data grouping. You can see this by clicking the Query Explorer tab and examining Query1. 2. On the toolbar, click the List Headers & Footers button. In the List Headers & Footers box, uncheck Overall Footer to remove the overall footer from your report. Click OK. Your list should look like the one below:

Notice that the text for the cells with is light gray. 3. Click the background of the one of the cells with the data item. 4. In the List Cell Properties pane, under Miscellaneous, click Class. Using the pull-down arrow, select Default. This removes the gray color and makes the text the default black. 5. Make sure the cells are still selected. From the Report Studio Structure menu, select Insert List Row Cells Below. In the Insert Rows Below box, type 3 for the number of rows. Click OK. Your list should look like the one below:

Copyright © 2004-2005 University of Delaware

93

Cognos Report Studio

6. Click to select the first cell below the cell. SHIFT-click to select the next cell in that row. Your list should look like the one below:

7. From the Report Studio Structure menu, select Merge List Row Cells. 8. Repeat steps 6 and 7 for the next two rows of cells that you added to the list. 9. Click the data item in the List Group Footer and click the Delete button. Note: The Order number data you need for this Invoice appears in the Page Header. You can delete the Order number from the list group footer and replace it with text (if you choose) to add information to the Invoice. 10. Select the cell from which you deleted and from the Report Studio Structure menu, select Split List Row Cell. 11. In the same row, click to select the first cell and SHIFT-click to select the next cell. From the Report Studio Structure menu, select Merge List Row Cells. Your list should look like the one below: Copyright © 2004-2005 University of Delaware

94

Cognos Report Studio

In the next steps, you will add text, styling, and alignment to produce an invoice like the sample one on page 75. When you create your own reports, you will undoubtedly make different choices for the items you add. Here you are exploring the process for making a report look the way you want it to. 12. On the Insertable Objects Toolbox tab, click the Block object and drag it to the merged cell you just created. 13. On the Insertable Objects Toolbox tab, click the Text Item object and drag it into the block. In the Text box that appears, type Please Make Check Payable to University of  Delaware. Click OK. 14. Select the text you added and, in the Text Item Properties pane, double-click Font. Set the Weight to Bold and click OK. 15. On the Insertable Objects Toolbox tab, click the Block object and drag it to the remaining empty cell in the row you have been working with. 16. On the Insertable Objects Toolbox tab, click the Text Item object and drag it into the block. In the Text box that appears, type Subtotal. Click OK. Repeat step 14 to make Subtotal bold. 17. In the next row, click the first cell. On the Insertable Objects Toolbox tab, click the Block object and drag it to the selected cell. 18. On the Insertable Objects Toolbox tab, click the Text Item object and drag it into the block. In the Text box that appears, type No Returns Without Receipt. Click OK. Repeat step 14 to make the text bold. 19. Repeat steps 17 and 18 to add the text Tax to the second cell in the row. Repeat step 14 to make the text bold. 20. In the next row, repeat steps 17 and 18 to add the text Shipping to the second cell. Repeat step 14 to make the text bold.

Copyright © 2004-2005 University of Delaware

95

Cognos Report Studio

21. In the last row, repeat steps 17 and 18 to add the text Total to the second cell. Repeat step 14 to make the text bold. 22. Click the cell with the word Subtotal then CTRL-click the cells with the words Tax, Shipping, and Total. On the Properties pane, click Horizontal Alignment and click Right. Your list should look like the one below:

Next, you will add a row at the bottom of the footer that will contain the text Classes Begin August 31. 23. Click the last cell of the last list row (just after the cell with the word Total). From the Report Studio Structure menu, select Insert List Row Cells Below. In the Insert Rows Below box, type 1 for the number of rows. 24. Click the first cell in the added row and SHIFT-click to select all the cells in the row. From the Report Studio Structure menu, select Merge List Row Cells. 25. With the merged cell selected, click Horizontal Alignment in the List Cell Properties pane. Using the pull-down arrow, select Center. Then click Font and set the Weight to Bold. 26. On the Insertable Objects Toolbox pane, click the Block object and drag it to the bottom row. 27. On the Insertable Objects Toolbox tab, click the Text Item object and drag it into the block. In the Text box that appears, type Classes Begin August 31. Click OK. The text takes on the styling you applied at the list cell level. Next you will add calculations to provide data for the Tax, Shipping, and Total items you added earlier. 28. On the Insertable Objects Toolbox pane, click the Calculation object and drag it to the cell to the right of Tax. In the Create Calculation box, for the Name type Tax then click the Group icon and click OK:

Copyright © 2004-2005 University of Delaware

96

Cognos Report Studio

This opens a Cube Data Item window like the one below:

29. In the Expression Definition pane, type [Revenue] * .06

Click the Validate

button then click OK.

30. Click the Tax calculation you just added and in the Text Item Properties pane under Cube Data Item, click Aggregate Function. Using the pull-down arrow, click Aggregate. Then, under Data, double-click Data Format. Using the pull-down arrow for the Format type box, select Currency. From the resulting Properties list, select Currency Symbol and click OK. 31. On the Insertable Objects Toolbox pane, click the Calculation object and drag it to the cell to the right of Shipping. In the Create Calculation box, for the Name type Shipping  then click the Group icon and click OK. 32. In the Expression Definition pane, type [Revenue] * .01

Copyright © 2004-2005 University of Delaware

97

Cognos Report Studio

Click the Validate

button then click OK.

33. Click the Shipping calculation <Shipping> you just added and, in the Text Item Properties pane under Cube Data Item, click Aggregate Function and, using the pull-down arrow, click Aggregate. Then, under Data, double-click Data Format. Using the pull-down arrow for the Format type box, select Currency. From the resulting Properties list, select Currency Symbol and click OK. 34. On the Insertable Objects Toolbox pane, click the Calculation object and drag it to the cell to the right of Total. In the Create Calculation box, for the Name type Total then click the Group icon and click OK. 35. In the Expression Definition pane, type [Revenue] + [Tax] + [Shipping]

Click the Validate

button then click OK.

36. Click the Total calculation you just added and, in the Text Item Properties pane under Cube Data Item, click Aggregate Function and, using the pull-down arrow, click Aggregate. Then, under Data, double-click Data Format. Using the pull-down arrow for the Format type box, select Currency. From the resulting Properties list, select Currency Symbol and click OK. 37. Click the background of the Tax calculation cell then CTRL-click the background of the Shipping and Total calculation cells. In the List Cell Properties pane, click Horizontal Alignment and, using the pull-down arrow, click Right. Your list should look like the one below:

38. Save and run the invoice report. Just a few border lines and the Invoice will be complete. 39. Click any cell or block in your report. On the Properties pane, click the Ancestor and click Page.

button

40. In the Page Properties pane, double-click Border. Set the Color to Blue, the Style to Solid Line and the Width to 1pt. Click all four borders on the diagram and click OK. Copyright © 2004-2005 University of Delaware

98

Cognos Report Studio

41. Click the cell with the text Please Make Check Payable to University of Delaware. On the Properties pane, click the Ancestor button and click List Row Cells. 42. In the List Row Cells Properties pane, double-click Border. Set the Color to Black, the Style to Solid Line and the Width to 1.5pt. (Either type 1.5 in the Width box or select 1½ from the list.) Click the bottom border on the diagram and click OK. 43. Save and run the invoice report. Your report should look like the sample one on page 75. If you page down, you will see that a new invoice begins on each new page. End of Exercise

Create a Template You can create a report template to be used for reports specific to your department. To do this, start with a blank report and add the components you want every report to have but do not add data from the model to the report. Save the report. When you want to use the template, open it, select Save As from the File menu, and name the new report. In the next exercise, you will create a report template. When you finish, your template will look like the one below:

Copyright © 2004-2005 University of Delaware

99

Cognos Report Studio

Exercise 40—Create a Report Template 1. On the Report Studio toolbar, click the New

button to create a new report.

2. In the New box, select Blank and click OK. 3. On the Report Studio menu, click the Page Header & Footer next to Header and Footer and click OK.

button. Click the boxes

4. Click inside the Page Header area and in the Properties pane, double-click Border. Set the Color to Blue, the Style to Solid Line and the Width to 2px (use the pull-down arrow under the Width box to select px). Click the bottom border and click OK. 5. On the Insertable Objects Toolbox tab, click the Table item and drag it into the Page Header area. In the Insert Table box, type 2 for the Number of columns and 2 for the Number of rows. Make sure the Maximize width box is checked. 6. On the Insertable Objects Toolbox tab, click the Text Item and drag it into the top-left table cell. In the Text box, type Double­click to add text. Click OK. 7. Highlight the text you just added and in the Text Item Properties pane, double-click Font. Set the Family to Arial, the Size to 14pt and the Weight to Bold. Click OK. 8. On the Insertable Objects Toolbox tab, click the Date item and drag it into the bottom left table cell. 9. Highlight the Date item and in the Text Item Properties pane, double-click Font. Set the Family to Arial and the Size to 12pt. Click OK. 10. On the Insertable Objects Toolbox tab, click the Image item and drag it into the top-right table cell. 11. Click the Image item you just added and on the Image Properties pane, double-click URL. Set the Image URL to ..\samples\images\logo.jpg and click OK. 12. Click the background of the top-right table cell and in the Table Cell Properties pane, click Horizontal Alignment. Using the pull-down arrow, select Right for the alignment. 13. On the Insertable Objects Toolbox tab, click the Text Item and drag it into the bottom-right table cell. In the Text box, type Double­click to add Department name. Click OK. 14. Highlight the text you just added and in the Text Item Properties pane, double-click Font. Set the Family to Arial, the Size to 12pt and the Weight to Bold. Click OK. Next, doubleclick Foreground Color and click Blue. Click OK. 15. Click the background of the bottom-right table cell and in the Table Cell Properties pane, click Horizontal Alignment. Using the pull-down arrow, select Right for the alignment. This completes the Page Header. Next, you will add a list frame to the Page Body.

Copyright © 2004-2005 University of Delaware

100

Cognos Report Studio

16. On the Insertable Objects Toolbox tab, click the List item and drag it into the page body. You will see a Create – List box like the one below:

17. In the Based on area, make sure New query is selected. Click OK. Next, you will create the Page Footer. 18. Click inside the Page Footer area and in the Properties pane, double-click Border. Set the Color to Blue, the Style to Solid Line and the Width to 2px (use the pull-down arrow under the Width box to select px). Click the top border and click OK. 19. On the Insertable Objects Toolbox tab, click the Table item and drag it into the Page Footer area. In the Insert Table box, type 2 for the Number of columns and 2 for the Number of rows. Make sure the Maximize width box is checked. 20. On the Insertable Objects Toolbox tab, click the Text Item and drag it into the top-left table cell. In the Text box, type University of Delaware. Click OK. 21. Highlight the text you just added and in the Text Item Properties pane, double-click Font. Set the Family to Arial, the Size to 14pt and the Weight to Bold. Click OK. 22. On the Insertable Objects Toolbox tab, click the Text Item and drag it into the top-right table cell. In the Text box, type Newark, DE 19716. Click OK. 23. Highlight the text you just added and in the Text Item Properties pane, double-click Font. Set the Family to Arial and the Size to 14pt. 24. Click the background of the top-right table cell and in the Table Cell Properties pane, click Horizontal Alignment. Using the pull-down arrow, select Right for the alignment. 25. Click the bottom-left table cell then SHIFT-click the bottom right table cell. From the Report Studio Table menu, select Merge Cells. 26. On the Insertable Objects Toolbox tab, click the Page Number item and drag it into the bottom table cell. 27. Highlight the Page Number item and in the Text Item Properties pane, double-click Font. Set the Family to Arial and the Size to 10pt. Click OK.

Copyright © 2004-2005 University of Delaware

101

Cognos Report Studio

28. Click the background of the bottom table cell and in the Table Cell Properties pane, click Horizontal Alignment. Using the pull-down arrow, select Center for the alignment. 29. Click the background of any area in your report template. On the Properties pane, click the Ancestor button and click Page. 30. In the Page Properties pane, double-click Border. Set Color to Blue, Style to Solid Line and Weight to 1px. Click all 4 borders on the diagram and click OK. 31. On the Report Studio window File menu, click Save As. 32. In the Save As Name box, type your initials Template. Click Save. End of Exercise

You have now created a template that can be used to create other reports. When you want to use the template, open it and choose Save As from the Report Studio File menu. Give the report a new name to preserve the template.

Copyright © 2004-2005 University of Delaware

102