IBM COGNOS 10 REPORT STUDIO PRACTICAL EXAMPLES PDF
IBM Cognos 10 Report Studio: practical examples / Roger Johnson, Filip Draskovic. p. cm. ISBN (pbk.) 1. Business intelligence— Computer. [DOWNLOAD $PDF$] IBM Cognos 10 Report Studio: Practical Examples FULL- PAGE Click button below to download or read this book. IBM COGNOS 10 REPORT STUDIO: PRACTICAL EXAMPLES. PDF. By seeing this page, you have done the ideal staring point. This is your begin to choose the .
|Language:||English, Spanish, Portuguese|
|Genre:||Fiction & Literature|
|ePub File Size:||26.75 MB|
|PDF File Size:||10.12 MB|
|Distribution:||Free* [*Regsitration Required]|
IBM Cognos 10 Report Studio: Practical Examples will help you find the answers to specific questions based on your data and your business. Introduction To Cognos Analytics And Report Navigation report navigation training ibm cognos analytics 11 applicable for former ibm cognos 10 report. tutorial step by step – either way you'll end up with some highly impressive reports. [PDF] Ibm. Cognos 10 Report Studio Practical Examples.
After learning the basics, report authors in the enterprise need to apply the technology to reports in their actual, complex work environment.
This book provides that advanced know how. Using practical examples based on years of teaching experiences as IBM Cognos instructors, the authors provide you with examples of typical advanced reporting designs and complex queries in reports.
The reporting solutions in this book can be directly used in a variety of real-world scenarios to provide answers to your business problems today. The complexity of the queries and the application of design principles go well beyond basic course content or introductory books.
IBM Cognos 10 Report Studio: Practical Examples will help you find the answers to specific questions based on your data and your business model. The final chapter has been dedicated to showing those features that are unique to the latest version of this powerful reporting solution. Practical Examples will help you find the answers to specific questions based on your data and your business model.
The final chapter has been dedicated to showing those features that are unique to the latest version of this powerful reporting solution. Roger Johnson is a learning consultant on IBM Cognos technologies delivering a wide variety of courses focusing on the needs of his learners. His education experience has been honed over years of work in training software professionals, college students, and many types of technology users.
After he started his career as a computer programmer, a co-worker said, "Hey, you do community theater productions. You would make a good trainer.
Over the next 20 years, he never moved too far from either technology or education. As a learner, he has master's degrees in Systems Management and Education. Currently, he is researching the end-user adoption of technology as his doctoral dissertation at Capella University.
IBM Cognos 10 Report Studio Fundamentals
Filip Draskovic has spent his professional career, which covers the past 11 years, living and breathing IBM Cognos. For the first 8 years of his career, he had been a Cognos consultant and developed his skills applying Cognos Business Intelligence and Planning solutions in multiple industries.
Following his desire to constantly gain new experiences and knowledge, he is currently filling the role of a Cognos client technical professional. You can find him today in Toronto's financial district.
At home, with his wife, he is enjoying raising their son and daughter. Blogs My Blogs. Public Blogs. We need to show monthly sales and the yearly total sales. The year should be shown in the Year total row and not as a separate column.
Add a total for the Month and Order method type then deine appropriate sorting if required. In this recipe, we want to hide the year from the crosstab and show it only in the report as a year total. To do this, perform the following steps: First, let's identify the issue. If you run the report as it is, you will notice that the year is shown to the left of the months. This consumes one extra column. Also, the yearly total doesn't have a user friendly title as shown in the following screenshot: We will start by updating the title for the yearly total row.
Run the report and check that the yearly total is shown with the appropriate year as shown in the following screenshot: Now we need to get rid of the year column on the left edge. For that, click on the Unlock button in the Report Studio toolbar.
The icon should change to an open lock unlocked. Select the empty crosstab node left after deleting the text. Change its Padding to 0 pixels in all directions. Run the report and you will see the following screenshot: As you can see the year column on the left is now successfully hidden. However, in this case, that was not possible. It will look like the following screenshot: As you can see, the cells have shifted to the left leaving the titles out of sync.
This is most often the problem when Report Studio creates some merged cells in our case, for the aggregations.
The solution to this is to format the column in such a way that it is hidden in the report as we have seen in this recipe. This solution works best in HTML output. The Excel output still has a column on the left with no data in it. You might need to deine the background color and bordering as well so as to blend the empty column with either the page background on the left or the month column on the right.
However, the display values in the prompts need to be textual and user friendly. In this recipe, we will create a prompt and examine the differences between using the display value and the use value. Open Page Explorer and click on the Prompt Pages folder.
Drag a new page from Toolbox under Prompt Pages. Double-click on the newly created prompt page to open it for editing. From the toolbox, drag Value Prompt to the prompt page.
This will open a wizard. Set the prompt name to ProductLine and then click on Next as shown in the following screenshot: Keep the Create a parameterized ilter option checked. Click on Next as shown in the following screenshot: Keep the Create new query option checked. Give the query name as promptProductLine. Click on the Finish button.
When you drag a prompt object from Toolbox, Report Studio launches the prompt wizard. In the irst step, you choose the parameter to be connected to the prompt.
It might be an existing parameter deined in the query ilter or framework model or a new one. In this recipe, we chose to create a new one. Then, you are asked whether you want to create a ilter. If there is already a ilter deined, you can uncheck this option. In our example, we are choosing this option and creating a ilter on Product line code. Please note that we have chosen the numerical key column here. Filtering on a numerical key column is a standard practice in data warehousing as it improves the performance of the query and uses the index.
In the next step, Report Studio asks where you want to create a new query for the prompt. This is the query that will be ired on the database to retrieve prompt values. Here we have the option to choose a different column for the display value.
In our recipe, we chose Product line as the display value. Product line is the textual or descriptive column that is user friendly. It has one-to-one mapping with the Product line code. For example, Camping Equipment has a product line code of Hence, when we run the report, we see that the prompt is populated by Product line names, which makes sense to the users.
Whereas if you examine the actual query ired on the database, you will see that iltering happens on the key column; that is, Product line code. Enter any dummy number and examine the query generated for the report. You will see that the Product line code key column is being iltered for the value you entered.
So, now you know how the prompt display values and use values work. If you ever need to capture the prompt value selected by the user in expressions which you will often need for conditional styling or drill-throughs , you can use the following two functions: This function returns the textual value which represents the display value of the prompt.
In our example, it will be the product line that was selected by the user. This function returns the numeric value which represents the use value of the prompt. In our example, it will be the Product line code for the product line selected by the user. Now you know how iltering, sorting, and aggregation work.
You also know how to apply data formatting, create sections, and hide columns. You are also now aware of how to add new prompts and select appropriate options in the prompt wizard. We will also examine some techniques around drill-through links.
These will enable you to create professional reports as required in the current industrial environment. Adding cascaded prompts Business owners want to see sales made by employees.
They also want the facility to limit the report to a certain region, country, or employee. When they select a region, they would like the country pick-list to automatically reduce to the countries falling in that region. Similarly, the employee pick-list should also reduce when they pick a country.
Getting ready Create a simple list report with Employee name from the Employee by region query subject and Quantity from Sales fact. Deine appropriate grouping and sorting for Employee name and ensure that aggregations for Quantity are correctly set.
In this recipe we will build three prompts for Region, Country, and Employee. We will create a relationship between these prompts by making them cascading prompts. So if you change the value selected in one prompt, the data in the other prompts should change accordingly. We will start by creating detailed ilters on the report query. Select the list report and open the ilters dialog by clicking on the Filters button and then click on Edit Filters. Add three detailed ilters as follows: Deine all ilters as Optional as shown in the following screenshot: Now create a new prompt page.
We will start by adding a prompt for Region. Drag a new value prompt. In the prompt wizard, choose the existing parameter Region for it.
Choose to create a new query called Regions for this parameter. Now add another value prompt. Choose the existing parameter Country for this, and create a new query called Countries. On the same page, choose Region under the Cascading source as shown in the following screenshot: Similarly, add the third and last value prompt for employee.
Choose Employee as a parameter, Employees as the query name, and Country for the Cascading source. Select the Region prompt and set its Auto-Submit property to Yes. Do the same for the Countries prompt. In our case, users may run the report for the whole company, select a particular region, select a region and country combination, or go all the way down to employees.
We want to allow them to submit the selections at any stage. That is why we created three ilters and kept them all as optional. Even if it was mandatory for the users to select an employee, we would have kept ilters for country and region.
Cascaded source When we set the cascaded source property, Report Studio ensures two things. Firstly, the prompt is disabled until the cascaded source is satisied. Secondly, when re-prompted and the cascade source is populated, the prompt values are iltered accordingly.
In our case, the Countries prompt remains disabled until a valid value for region is submitted. Similarly, the employee list is disabled until a valid value is submitted for countries. In step 9, we set the Auto-Submit property to Yes for the prompts.
Auto-Submit When the Auto-Submit property is set to Yes, the prompt value is automatically submitted when the user selects one. This enables the dependent prompt to be correctly iltered and enabled. In our recipe, Auto-Submit for Region is set to Yes. Hence, when you select a region, the value is automatically submitted and the Country prompt is enabled with the correct values populated.
Send Email Notification
In that case, Auto-Submit is not required. See also f The Prompts — display value versus use value recipe in Chapter 1, Report Authoring Basic Concepts Creating a nested report — deining the master-detail relationship Users want to see product lines, products, and corresponding unit costs.
For every product, they also want to see the trend of sales over the last year. We need to produce a list report with the required information and nest a line chart within it to show the sales trend.
Getting ready Create a simple list report based on the Sales query namespace. To complete this recipe, we need to create a relationship between the list report created and a chart report that will be embedded into it. We can do this by deining a master-detail link between them.
We already have a list report that shows the product lines, products, and corresponding unit costs. Please make sure that appropriate sorting and aggregations are applied to the columns. Now we will add a nested Chart object to show the sales trend for each product.
Drag a new Chart object from the Toolbox pane into the report as a column as shown in the following screenshot: Choose an appropriate chart type. In this recipe, we will choose Line with Markers. From the source pane, drag Quantity from Sales fact into the chart as the Default measure y-axis. Drag Month key from the Time dimension under Categories x-axis and Product from the Products dimension under Series as shown in the following screenshot: Later on you can use a category label to show month names.
Directly pulling the month name results in alphabetic sorting is an incorrect trend. Create a new link and connect Product items from both the queries as shown in the following screenshot: Click on the OK button to come back on the report page. Now select the Y1 Axis of the chart by clicking on it. Now click on the chart and click on the Filter button from the toolbar and then click on Edit Filters. Deine a detailed ilter on Year from the Time dimension as required.
In this recipe, we will hard code it to So, the ilter is deined as [Sales query ]. Though in practical cases, you would have to ilter for year, rather than hard-coding. Update the chart properties size, marker, color, and so on for better presentation as shown in the following screenshot: Cognos Report Studio allows one report object to be nested within another list report.
In the previous recipe, we saw that the Report Studio automatically creates nesting for us. In this recipe, we manually created nesting for iner control. Then for each record, it ires the detail query with the iltering as deined in the master-detail relationship.
Hence, the detail query is executed multiple times, each time with different iltering. As it has to retrieve very small amounts of information at a time, a page of output is returned very quickly. This is particularly useful when the report is accessed interactively. This will highlight the data containers that have master-detail relationships with an icon like this: By double-clicking on the icon, you can quickly open the relationship for viewing and editing.
By using separate queries for the outer and inner report object in nesting, we can have more control on what information is retrieved. In this example, we want to show a sales trend chart only for one year—we hard coded it to Hence, the chart query needs to be iltered on year.
However, the outer query the list of product lines and products does not need this iltering. As you can see in the report output, there are some rows with no corresponding graph. This means there was no selling of this product in the year If we had used the same query for the list and the chart, this row would have been iltered out resulting in loss of information product and unit cost to the users.
Though there is no direct functionality provided in Cognos Report Studio for this, it is still possible to achieve it by putting together multiple tools. This recipe will show you how to do that.
The business wants to see sales igures by products. They then want to write some comments for the products from the same interface.
The comments need to be stored in the database for future retrieval and updating. You will need access on the backend database and Framework Manager for this recipe. As we are only concentrating on Report Studio in this book, we will not cover the Framework Manager options in depth. The power users and Report Studio developers need not be masters in Framework Modeling, but they are expected to have suficient knowledge of how it works.
Mark as Duplicate
There is often a Framework Manager Specialist or modeler in the team who controls the overall schema, implements the business rules, and deines hierarchies in the model. Create appropriate sorting, aggregation, and prompts.
To complete this recipe, we will use a stored procedure to do the write back action to the database. This is illustrated in the following steps: We will start by creating a table in the database to store the comments entered by users. For that, open your database client and create a table similar to the one shown later. The table is deined as follows: After creating the table in step 1 in the backend, we will now write a stored procedure that will accept product key and comments.
It will enter this information in the table and then return all the product keys and corresponding comments back as shown in the following code: ProductComments GO 3. You need to conigure the input parameters as Prompts. This is shown in the following screenshot: As you can see in the previous screenshot, ProductID and Comments are the stored procedure parameters. They have in mode which means they accept input. For the values we will be deining prompts so that we can use them inside the reports.
For the parameter ProductID, click on the Value button. A new pop-up window will appear that will help you to set the value as you can see in the following screenshot: Click on Insert Macro and deine the macro as prompt '? Repeat the same for the Comments parameter and deine another macro for the? Verify the model and publish it.
Now, we will create a new report which users will use to insert the comments about the product. For that start with a new list report. Use the InsertComment stored procedure query subject for this report. Drag Product ID and Comment columns on this report as shown in the following screenshot: Create a prompt page for this report. Insert a Text Value type of prompt and connect it to the existing parameter called Comment.
Save this report as drill report. We will call it as 2. Now reopen the irst report. Drag a Text Item as a new column on the report and deine the text as Insert Comment as shown in the following screenshot: Create a drill-through from this text column by clicking on the Drill-through icon. Set Writing Back to Database — Drill as drill target.
Check the option of Open in New Window. Edit the parameter for this drill by clicking on the Edit button. Map the ProductKey parameter to the Product key data item as shown in the following screenshot: Cognos Report Studio on its own cannot perform data manipulation on a database.
It cannot ire DML statements and hence can't write back to the database. For this, we need to import the Stored Procedure as query subject within Framework Manager.
When a report that uses this query subject is run, Cognos executes the Stored Procedure on the database. We can use this opportunity to perform some DML operations, for example, inserting or updating rows in tables. When we import a Stored Procedure into Framework Model, it allows us to deine an expression for every input parameter.
In step 3 of this recipe, we deined the parameter value to be prompts. The prompt parameters, namely ProductKey and Comments then become visible in the report.
Once we have imported the Stored Procedure in Framework Model, mapped the input parameter to prompts and published the package, we are ready to start with reports. We created a report drill report to use the Stored Procedure and hence allow users to insert the comments. In this report, we created a text prompt and linked it to the Comments parameter. The Product Key is passed from the main report. This way we achieve the write-back to the database. We show those records in a simple list report to users.
This recipe is a very basic example to demonstrate the capability. You can build upon this idea and perform more sophisticated operations on the database. Adding conditional formatting The business wants to see company sales igure by years and quarters. They want to highlight the entries where sales are below 5,, We will assume that database provides us the Quarter number and we need to convert that to words. We will use conditional formatting for that.
Also, where sales is below 5 million, the cell will be shown in red using another conditional variable. Drag Quantity from Sales fact. Group by Year and sort by Quarter. Conditional formatting is used to enhance the report visualization.
Here is how we can deine conditional formatting in the created report: Go to Condition Explorer and click on Variables as shown in the following screenshot: Drag a new string variable from the Toolbox pane. Deine the expression as [Query1]. Add four values for the variable; the numbers 1 to 4.
Now add a Boolean variable and deine it as [Query1].
Go to the report page and select the Quarter numeric column. Run the report to test the output as shown in the following screenshot: Here we are deining conditional variables to trap the speciic conditions and perform required actions on corresponding rows. There are three types of conditional variables: String, Boolean, and the report language variable. The String variable The String type of variable allows you to deine different possible values that the expression can be evaluated into.
You only need to deine the values for which you need to deine speciic style or text. The rest are taken care of by the Other condition. The Boolean variable This variable is useful when the expression only evaluates into true or false and you need to format the entries accordingly.
You don't need to deine any expression for this type of variable. You simply need to choose the languages for which you want to perform certain actions like display titles in the corresponding language, or show the respective country lag in header. Here, we have used one variable of String type and one of Boolean type. There are some other important style variables to check out.
The style variable property By assigning a variable to this property, we can control the styling aspect of the object which includes font, colors, data format, visibility, and so on.
We can provide static text or a report expression. We can also choose to show value or label of another data item in the selected object. In this example, we used this property to display the appropriate quarter name. Please note that it was possible to achieve the same result by putting a CASE statement in the data expression.
However, the purpose here is to highlight the function of text source variable. Formatting negative values Business owners need to see the sales igures by month and their month-on-month difference. If the difference is negative fall in sales then it needs to be shown in red and values need to be in brackets. Group Year and sort Month numeric in ascending order. Showing negative values in red and between brackets is a familiar practice for analysts in tools like Excel.
Here are the steps to create the exact same formatting in your reports: Add a new query calculation to the list. Deine the expression as running- difference [Quantity]. Call this item as Running Difference. Open the Data Format properties for this calculation from the Property list. Set the Format type as Number and the Negative Sign Symbol as brackets as shown in the following screenshot: Now go to Condition Explorer and create a new condition variable of Boolean type.
Ibm cognos 10 report studio practical examples pdf download Free Download
Deine the condition as [Query1]. Now go back to the report page and select the Running Difference column. Select the Running Difference column from the list and open its Font properties. Set the font foreground color to red as shown in the following screenshot: Click on the OK button. Double-click on the green bar to come out of condition. Run the report to test it as shown in the following screenshot: One purpose of this recipe is to introduce you to the powerful aggregation functions provided by Cognos.
Running Difference The Running Difference function returns difference between value in current row and previous row. You can also control the scope and level of aggregation. In this example, we leave the scope and level of aggregation to default. There are other such functions provided in Report Studio for example, Running-Maximum, Running-Count, Running-Total, and so on which are useful in real life scenarios.
It is easy to use and gives enough power for the business users to do their analysis. It readily allows you to display negative numbers in red and brackets, which is a popular choice in the inance world. However, under the Data Format options of Report Studio, you can only choose to display the negative numbers in brackets. You cannot specify to show them in different colors. Hence, we have to create a conditional variable here and deine the foreground color accordingly.
Playing with conditional styles Assume that the following report needs to be formatted such that quantities below 1. Also, we need the negative values for Running Difference month-on-month to be shown in red and in brackets as shown in the following screenshot: As shown in the previous recipe, this would have needed us to deine two conditional variables.
Then attach each to the corresponding column as Style variable to deine the styles. With one more such numeric column, the author had to deine one more variable and repeat the exercise. Let's see how the new conditional styling feature solves this problem. Getting ready Write a new report similar to the one shown in the previous screenshot.
In this recipe, we are going to examine more ways to enhance the reports look and feel. To do this: Select the Quantity column on the report page. Open the new Conditional Styles dialog box from the Properties pane. Alternatively, you can also click on the Conditional Styles button from the toolbar. Create a new Conditional Style as shown in the following screenshot: Choose Quantity to base the conditions on. Deine three values 0, 1. This will look like the following screenshot: Also, choose corresponding styles for each range as shown in the previous screenshot.
Give appropriate names, like Quantity colors in this case. Similarly, deine the negative values for the Running Difference column to be shown in red as shown in the following screenshot: Run the report to test it as shown in the following diagram: With this feature, we can now deine styling for any column without explicitly deining the conditional variable. The styling can be based on the values on the column itself or some other column.
Also, deining actual formatting font, color, border, and so on for different conditions is now done within one dialog box.
This is more author-friendly than traversing through the conditional variable pane and choosing each condition. The previous example deines very basic value-based range or classiication. You can also choose the Advanced Conditional Style option under this property, which allows you to deine an expression and have better control over conditions than just classifying the values into ranges.
Users want a report on sales igures.
Quarantine this entry
They want the facility to split the numbers by product lines, periods, or retailer region, any one at a time. For convenience purposes, they don't want three different reports, instead they are looking for one report with the facility to choose between the report types. Getting ready Create a report with three list objects. Deine the list columns as follows: Make sure that all objects use different queries as shown in the following screenshot: Conditional blocks are very powerful tool that you can make your report very ixable.
In this recipe, we will create three different reports, but using the conditional blocks we will be able to show them in the same report. The procedure to do this is as follows: We will start by creating a prompt for the report type.
Go to Page Explorer and add a prompt page. Drag a new value prompt object on to the prompt page. Deine parameter name as paramReportType. Do not deine any iltering, use value, or display value in the prompt wizard. Select the value prompt and open Static Choices from its properties. Deine three static choices as shown in the following screenshot: Now go to Condition Explorer and create a new String variable. Deine it as ParamValue 'paramReportType'. Change the name of the variable to ReportType.
Now go to the report page. Add a new conditional block from the Toolbox pane. Select the conditional block and open the Block Variable dialog from the properties. Select the ReportType variable from the dropdown and then click on the OK button as shown in the following screenshot: Now choose BP as the current block from properties. Select the irst list object that shows sales quantity by products.
Drag this list into the conditional block. Please note that you need to use the Ancestor button to select the whole list before dragging it in. Change the current block property of the conditional block to BT. Drag the Sales by Time list into the block. Repeat the same for BR and the last list object. On the report page header, select the Double click to edit text item.
Change its Source type property to Report expression. We saw how to deine conditional variables and use them as style variables in the Adding conditional formatting recipe.
In this recipe, we are checking how conditional variables can be used with the conditional blocks. A conditional block is a useful component that allows you to show certain objects in a certain condition. So, each query subject will have only the required columns.There is a way to reconstruct the report, and this is how to do it: Map the month parameter with the Month data item as shown in the following screenshot: The information we are trying to show in this report can also be achieved by normal grouping on order number.
It cannot ire DML statements and hence can't write back to the database. Getting ready Create a new drill-through target that accepts Month and Product line as parameters. In this recipe, we will create three different reports, but using the conditional blocks we will be able to show them in the same report.