Advanced Report Authoring, Sample of Dissertations

10 pages, 4791 words

professional expertise distilled

P U B L I S H I N G

IBM Cognos 8 Report Studio Cookbook

Abhishek Sanghani

Chapter No.2 ” Advanced ReportAuthoring”

Buy IBM Cognos 8 Report Studio Cookbook [RAW] with IBM Cognos 8 Planning ebook and get 50% both. Add both the ebooks to the shopping cart individually and then enter cog825ebk in the Code’ field and click ‘Add Promotion’ during checkout. Your discount will be applied. This offer is valid till 30th June 2010. Grab your copy now!!!

In this package, you will find:

A Biography of the author of the book A preview chapter from the book, Chapter NO.2 ” Advanced Report Authoringe ” A synopsis of the book’s content Information on where to buy this book

About the Author

Abhishek Sanghani was born in India and attended Mumbai University where he

majored in Computer Engineering. He began his career in 2004 as a Business Intelligence and Cognos Consultant, and has worked with leading IT and Finance Services companies since then. He pursued Finance Management degree along with his work in the field of Cognos and BI, successfully progressing and winning awards and certifications year after year. Presently, he is working in the United Kingdom, utilizing his skills of Cognos, SQL, BI and Data Warehousing. In his free time, he writes technical blogs and also provides trainings/seminars on demand. This book is his first attempt in technical authoring. Mail ID: [email protected] Blog: http://biandcognos.blogspot.com/

1 page, 429 words

Johnny Tremaine Essay Book Part Author

PLOT OUTLINE By: Ivan Tarnawsky This book starts in the pre-revolutionary time. At the beginning of the book, Johnny T remain, is working as an apprentice to Mr. Lapham. Mr. Lapham is a blacksmith. Johnny's parents died in a fire several years before, and this is why he lived with the Laphams. He worked there with enthusiasm for several years until he hurt his arm, scolding it in hot metal. After ...

I would like to thank the entire PACKT Publishing team for helping this endeavor. On the personal front, I am thankful to my loving wife Dolly for all the support, and my friend and work mate Amma Manso for all the valuable advice.

For More Information: www.PacktPub.com/ ibm-cognos-8-report-studio-cookbook/book

IBM Cognos 8 Report Studio Cookbook

Cognos Report Studio is widely used for creating and managing business reports in medium to large companies. It is simple enough for any business analyst, power user, or developer to pick up and start developing basic reports. However, when it comes to developing more sophisticated, fully functional business reports for wider audiences, report authors will need guidance. This book helps you to understand and use all the features provided by Report Studio to generate impressive deliverables. It will take you from being a beginner to a professional report author. It bridges the gap between the basic training provided by manuals or trainers and the practical techniques learned over years of practice.

What This Book Covers

Chapter 1, Report Authoring Basic Concepts, introduces you to some fundamental components and features that you will be using in most of the reports. This is meant to bring all readers on to the same page before moving on to advanced topics. It covers filters, sorting, aggregations, formatting, conditional formatting, and so on. Chapter 2, Advanced Report Authoring, shows advanced techniques required to create more sophisticated report solutions that meet demanding business requirements. It covers cascaded prompts, master-detail queries, conditional block, defining drill links, and overriding the drill links. The most distinguishing recipe in this chapter is “Writing back to the database.” Chapter 3, Tips and Tricks: Java Scripts, shows how to manipulate the default selection, titles, visibility, and so on when the prompt page loads. It explains how to add programmability like validating the prompt selection before submitting the values to the report engine. A favorite recipe in this chapter is “Generating a bar chart using JavaScript”. These recipes open a whole new avenue for you to progress on. Chapter 4, Tips and Tricks: Report Page, shows some techniques to break boundaries and provides some features in reports that are not readily available in the Studio. It also talks about showing images dynamically (traffic lights), handling missing images, dynamic links to external website (for example, Google Maps), alternating drill links, showing tooltips on report, minimum column width and merged cells in Excel output. Chapter 5, Xml Editing, shows you how to edit the report outside the Studio by directly editing the XML specifications. The recipes show you how to save time and quickly change references to old items, copy-paste the drill parameter mappings, and introduce

4 pages, 1840 words

The Research paper on Ibm Eclipse Case Study

The creation of an independent IBM software group (SWG) was strategically important for the following reasons: a) A category of software named “middleware” was emerging in the mid-‘90s. At this point SWG abetted IBM to emerge in the middleware market. b) SWG was also strategically important for IBM to help focus its software activities and prepare for a new age in distributed networked computing. ...

For More Information: www.PacktPub.com/ ibm-cognos-8-report-studio-cookbook/book

you to important XML tags. The most intriguing recipe in this chapter is “A hidden gem in XML—row level formatting”. Chapter 6, Writing Printable Reports, gives you tips and shows you the options available within the Studio to make reports printable as business reports need to be printed and this part is often ignored during technical specification and development Chapter 7, Working with Dimensional Models, When reports are written against a dimensional data source (or dimensionally modeled relational schema), a whole new style of report writing is needed. You can use dimensional functions, slicers, and others. Also, filtering and zero suppression are done differently. This chapter talks about such options (as dimensional data sources are becoming popular again).

Chapter 8, Macros, shows you that even though macros are often considered a Framework Modeler’s tool, they can be used within Report Studio as well. These recipes will show you some very useful macros around security, string manipulation, and prompting. Chapter 9, Using Report Studio Better, shows you the studio options and development practices to get the best out of Report Studio. It will include the understanding of Studio options, setting time-outs, capturing the real query fired on database, handling slow report validation, customizing classes, and so on. Chapter 10, Some More Useful Recipes, is an assorted platter of useful recipes, meant to show more work-arounds, tricks, and techniques. A highlight recipe is–”changing style sheets at run time depending on the user”. Chapter 11, Best Practices, shows you how to achieve code commenting, version controlling, regression testing, and so on. It will also show you some useful practices you should cultivate as standard during development. Appendix, Recommendations and References, covers topics that are very useful for a Cognos report developer such as version controlling, Cognos mash-up service, and Cognos Go Office.

3 pages, 1285 words

The Essay on Rewarding Excellent Employees

REWARDING EXCELLENT EMPLOYEES Modern business world is highly competitive in every aspect. Today even employers have to find new original ways to reward outstanding employees, because only decent incentives will keep the level of their performance high and attract new talented people to work. And if you think that salaries and extra payments are original and highly motivating, you are, ...

For More Information: www.PacktPub.com/ ibm-cognos-8-report-studio-cookbook/book

Advanced Report Authoring

In this chapter, we will cover the following: Adding cascaded prompts Nested reports: deﬁning master detail queries Writing back to the database Conditional formatting Show negative numbers in red and brackets New conditional styling with version 8.3 onwards Conditional block—many reports in one Drill link from crosstab intersections Overriding crosstab intersection expression and drill

Introduction

Now as you have implemented the recipes in Chapter 1, or read them through, I am conﬁdent that we are on the same page about fundamental techniques of report authoring. You now know how ﬁltering, sorting, and aggregations 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. Based on this understanding, we will now move on to some advanced topics; including cascaded prompts, nested reports, and conditional blocks. We will also examine some techniques around drill-through links. These will enable you to create professional reports as required in current industrial environments.

3 pages, 1465 words

The Essay on Hiring Disabled Employees

Hiring Disabled Employees Approximately 54 million noninstitutionalized Americans have physical, intellectual, or psychiatric disabilities. Of these cases, 26 million are classified as having a severe disability. Severe disabilities include Alzheimer's disease, autism, mental retardation, and long-term use of a cane, crutches, walker, or wheelchair. Historically, individuals with disabilities have ...

For More Information: www.PacktPub.com/ ibm-cognos-8-report-studio-cookbook/book

Advanced Report Authoring

Adding a cascaded prompt

Business owners want to see sales made by employees. They also want the facility to limit the report to 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 Employee by region query subject) and Quantity (from Sales fact).

Deﬁne appropriate grouping and sorting for Employee name and ensure that aggregations for Quantity are correctly set.

How to do it…

1. We will start by creating detailed ﬁlters on the report query. Select the list report and open the ﬁlters dialog by clicking the Filters button. 2. Add three detailed ﬁlters as follows:

a. [Employee name]=?Employee? b. [Sales (query)].[Employee by region].[Country]=?Country? c. [Sales (query)].[Employee by region].[Region]=?Region?

3. Deﬁne all ﬁlters as Optional.

For More Information: www.PacktPub.com/ ibm-cognos-8-report-studio-cookbook/book

5 pages, 2024 words

The Term Paper on Studio Production

With academic reference, research and analyse what your studio role involves both technically and creatively from pre production to production of a studio programme. And with reference to your team’s studio programme, discuss your role and how you contributed. Introduction The purpose of the “Studio Production” module was to produce a live studio programme in a team, starting from the pre- ...

Chapter 2

4. Now create a new prompt page. We will start by adding a prompt for Region. 5. 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. 6. Click the Finish button. 7. 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.

8. Similarly, add third and last value prompt for employee. Choose Employee as a parameter, Employees as the query name, and Country for the Cascading source. 9. Select the Region prompt and set its Auto-Submit property to Yes. Do the same for the Countries prompt. 10. Run the report to test it.

How it works…

In our case, users may run the report for the whole company, select 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 ﬁlters and kept them all optional.

For More Information: www.PacktPub.com/ ibm-cognos-8-report-studio-cookbook/book

Advanced Report Authoring Even if it was mandatory for the users to select an employee, we would have kept ﬁlters for country and region. The reason is that one employee might have done sales for different countries/regions. By keeping those ﬁlters, we would assure that report fetches data for that employee, for the selected Region/Country only.

Cascaded source

When we set the Cascaded source property, Report Studio ensures two things. Firstly, the prompt is disabled until the cascaded source is satisﬁed. Secondly, when re-prompted and the cascade source is populated, the prompt values are ﬁltered accordingly. In our case, the countries prompt remains disabled until a valid value for region is submitted. Similarly, employee list is disabled until a valid value is submitted for countries.

4 pages, 1812 words

The Term Paper on Product life cycle and its stages

In today’s world, where market is unpredictable, strategies play crucial role in defending a firm’s product position. “The main reason why companies must continually develop new products is because products have life cycle”, (Bittel, 1980). Just as operation managers must be prepared to develop new products, they must also be prepared to develop strategies for both new and ...

There’s more…

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 dependant prompt to be correctly ﬁltered 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. This action can also be performed by a Reprompt button. In that case, auto-submit is not required. This will be covered in detail in the next recipe.

More info

Up to version 8.2, when a prompt value is submitted by an auto-submit action, the whole page refreshes. You can see the progress bar in the browser become active and the page takes a few moments to reappear. With version 8.3, a new technology is implemented in Cognos. The auto-submit action does not reload the whole page. Instead, only affected prompts are refreshed. This action is much quicker and gives a seamless experience to users.

Getting ready

Create a simple list report based on the Sales (query) namespace. Pull Product | Product line, Product | Product name and Sales fact | Unit cost in the list.

How to do it…

1. 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. 2. Now we will add a nested Chart object to show the sales trend for each product. 3. Drag a new Chart object from the Insertable Objects pane onto the report as a column.

4. Choose an appropriate chart type. In this recipe, we will choose “Line with Markers”.

For More Information: www.PacktPub.com/ ibm-cognos-8-report-studio-cookbook/book

Advanced Report Authoring 5. From Source pane, drag Quantity from Sales fact onto the chart as the Default measure. Drag Month key from time dimension under Category and Product name from product dimension as the Series.

Please note that we are using the month key here in order to show the monthly ﬁgures in correct order. You can later on use a category label to show month names. Directly pulling the month name results in alphabetic sorting, and hence in an incorrect trend.

6. Now click anywhere on the chart and choose Data | Master Detail Relationship from the menu bar. 7. Create a new link and connect Product name items from both the queries.

For More Information: www.PacktPub.com/ ibm-cognos-8-report-studio-cookbook/book

Chapter 2

8. Click the OK button to come back on the Report Page. Now select the Y1 Axis of chart by clicking on it. 9. Change its Use Same Range for All Instances property to No.

10. Now click on the Chart and click the Filter

button from toolbar.

11. Deﬁne a detailed ﬁlter on Current Year from time dimension as required. In this recipe, I will hard code it to 2004. So, the ﬁlter is deﬁned as: [Sales (query)]. [Time dimension].[Current year]=2004. Though in practical cases, you would have to ﬁlter for year, rather than hard-coding. 12. Run the report to test it. 13. Update the chart properties (size, marker, color, and so on) for better presentation.

For More Information: www.PacktPub.com/ ibm-cognos-8-report-studio-cookbook/book

Advanced Report Authoring

How it works…

Cognos Report Studio allows one report object to be nested within another list report. In the previous recipe of creating sections, we saw that the Report Studio automatically creates nesting for us. In this recipe, we manually created nesting for ﬁner controls.

Master Detail relationship

We need to deﬁne this relationship in the following cases: 1. When outer and inner report objects use different queries. 2. For any nesting other than ‘List within List’. In order to generate the report, Cognos ﬁrst ﬁres the Master query on the database to retrieve the records. Then for each record, it ﬁres the Detail query with the ﬁltering as deﬁned in Master-Detail relationship. Hence, the Detail query is executed multiple times, each time with different ﬁltering. As it has to retrieve very small amount of information at a time, a page of output is returned very quickly. This is particularly useful when the report is accessed interactively.

There’s more…

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 2004. Hence, the chart query needs to be ﬁltered on year. However, the outer query (list of product lines and products) does not need this ﬁltering. As you can see in the report output, there are some rows with no corresponding graph. For example, Personal Accessories | Auto Pilot. This means there was no selling of this product in the year 2004. If we had used the same query for the list and the chart, this row would have been ﬁltered out resulting in loss of information (Product name and Unit cost) to the users.

See also

With version 8.4 of Cognos Report Studio, a new feature called ‘Microchart’ is introduced. This type of chart is particularly useful for such in-line spark chart kind of representation. This version also allows you to quickly insert a chart within a crosstab by right-clicking on row titles (not one-click action for a list report though).

I would highly recommend exploring and experimenting around these features.

For More Information: www.PacktPub.com/ ibm-cognos-8-report-studio-cookbook/book

Chapter 2

Writing back to the database

This is perhaps the most frequently requested functionality by business users—writing some notes or comments back to database, for a particular entry on the report. 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 ﬁgures by products. They want to then write some comments for the products from the same interface. The comments need to be stored in database for future retrieval and updating. You would 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 Modelling, but they are expected to have sufﬁcient knowledge of how it works. There is often a Framework Manager Specialist or modeller in the team who controls the overall schema, implements the business rules, and deﬁnes hierarchies in the model.

Getting ready

Create a simple list report with Product key, Product name, and Sales quantity columns. Create appropriate sorting, aggregations, and prompts.

How to do it…

1. 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 here. In this recipe, I am using a simple table created in a MS SQL Server 2008 database using the SQL Server Management Studio. The table is deﬁned as follows:

CREATE TABLE [gosalesdw].[ProductComments]( [ProductID] [int] NOT NULL, [Comment] [varchar](255) NULL, CONSTRAINT [PK_ProductComments] PRIMARY KEY CLUSTERED ( [ProductID] ASC

For More Information: www.PacktPub.com/ ibm-cognos-8-report-studio-cookbook/book

Advanced Report Authoring

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_ DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

2. After creating the above table 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.

CREATE PROCEDURE [dbo].[InsertComment] @ProductID int, @Comments VARCHAR(255) AS BEGIN IF ((select count(*) from gosalesdw.ProductComments where ProductID = @ProductID) = 0) INSERT INTO gosalesdw.ProductComments VALUES (@ProductID,@ Comments) ELSE UPDATE gosalesdw.ProductComments SET Comment = @Comments WHERE ProductID = @ProductID END Select ProductID,Comment from gosalesdw.ProductComments GO

3. Please ensure that the user account used to access the database from Cognos, has been given EXECUTE permission on above Stored Procedure. On SQL Server, you can do that using GRANT PERMISSION command. 4. Now open your Framework Model and import this Stored Procedure as a Stored Proc Query Subject. You need to conﬁgure the input parameters as Prompts. This is shown in the following screenshot:

For More Information: www.PacktPub.com/ ibm-cognos-8-report-studio-cookbook/book

Chapter 2

As you can see in the picture, @ProductID and @Comments are the Stored Procedure parameters. They have ‘in’ mode which means they accept input. For their value, we are deﬁning prompts as ?ProductKey? and ?Comments? respectively. 5. Verify the model and publish it. 6. Now, we will create a new report which users will use to insert the Comments about product. For that start with a new list report. 7. Use the InsertComment stored proc Query Subject for this report. Drag Product ID and Comment columns on this report.

8. Create a prompt page for this report. Insert a Text Value type of prompt and connect it to the existing parameter called Comment. 9. Save this report as drill report. We will call it as ‘2.5 Writing Back to Database – Drill’ in this recipe. 10. Now re-open the ﬁrst report. Drag a Text Item as a new column on the report and deﬁne text as Insert Comment.

For More Information: www.PacktPub.com/ ibm-cognos-8-report-studio-cookbook/book

Advanced Report Authoring 11. Create a drill-through from this text column by clicking on the drill-through icon. Set ‘2.5 Writing Back to Database – Drill’ as drill target. Check the option of Open in New Window. 12. Edit the parameter for this drill by clicking the edit button. 13. Map the ProductKey parameter to the Product key data item.

14. Run the report to test it.

How it works…

Cognos Report Studio on its own cannot perform data manipulation on a database. It cannot ﬁre DML statements and hence can’t write back to database. However, Cognos allows reports to execute the Stored Procedures and show the result output on report page. 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 Proc on database. We can use this opportunity to perform some DML operations, for example, inserting or updating rows in tables. When we import a Stored Proc into Framework Model, it allows us to deﬁne an expression for every input parameter. In step 3 of this recipe, we deﬁned the parameter value to be prompts. The prompt parameters, namely ProductKey and Comments then become visible in the report.

For More Information: www.PacktPub.com/ ibm-cognos-8-report-studio-cookbook/book

Chapter 2

Once we have imported the Stored Proc in Framework Model, mapped the input parameter to prompts and published package, we are ready to start with reports. We created a report (drill report) to use the stored proc and hence allow users to insert the comments. In this report, we created a text prompt and linked it to ‘Comments’ parameter. The Product Key is passed from main report. This way we achieve the write-back to the database. After inserting/updating the row, Stored Proc returns all the records from the comments table. We show those records in a simple list report to users.

There’s more…

This recipe is a very basic example to demonstrate the capability. You can build upon this idea and perform more sophisticated operations on database.

Adding conditional formatting

The business wants to see company sales ﬁgure by years and quarters. They want to highlight the entries where sales are below 5,000,000. 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.

Getting ready

Create a simple list report with Current year and Current quarter columns from the Sales | Time Dimension query subject. Drag Quantity from Sales Fact. Group by current year and sort by current quarter.

For More Information: www.PacktPub.com/ ibm-cognos-8-report-studio-cookbook/book

Advanced Report Authoring

How to do it…

1. Go to Condition Explorer and click on Variables.

2. Drag a new string variable from Insertable Objects pane. Deﬁne the expression as: [Query1].[Current quarter]. 3. Change the name of variable to Convert_to_words.

For More Information: www.PacktPub.com/ ibm-cognos-8-report-studio-cookbook/book

Chapter 2

4. Add four values for the variable; the numbers 1 to 4.

5. Now add a Boolean variable and deﬁne it as: [Query1].[Quantity]

In this package, you will find:

IBM Cognos 8 Report Studio Cookbook

What This Book Covers

Advanced Report Authoring

Introduction

Chapter 2

How it works…

Cascaded source

More info

See also

Getting ready

How to do it…

Similar Papers