Calculated Members

The member function is used to create a new member in a specified hierarchy. The initial argument of the member function is the expression used to create the member. The next two arguments are the business key and caption values for the new member. The final argument is the hierarchy in which your new member will reside.

A calculated member can serve almost any purpose. One example of using many calculated members is to create a Variance calculation. If you wish to calculate a variance for many different measures you wouldn't use the measures themselves. Instead, you would create a calculated member in another dimension that would form the basis of your variance calculation. Say you wanted to compare the current period values to the prior period values. This would use an expression something like:

([Current Period] - [Prior Period])/[Prior Period]

You could define this as a new member of your time hierarchy using the member function as:

member(([Current Period] - [Prior Period])/[Prior Period], 'CurPriorVar', 'Variance to Prior Period', [Cube].[Time Dimension].[Time Hierarchy])

Drop down lists: Customize label

After creating a simple list in Report Studio you may want to create a drop down parametrized filter for it. First thing you'll notice when run the report is that the default selected text on the drop down is always set to the first field name of the list. I had requests to make this a little more representative, to something like 'Select field...' or 'Choose something...' which would give the user an indication that there were more choices available in the drop down.

To solve this issue, I found 2 techniques:

First is to create a static choice with the right correct display value for this prompt and set the default value to this static choice's value. Obviously this won't prevent the user from seeing the first line when he opens the drop down list.

The second technique would harness the following Javascript code. Drag a new html item into the report and include the following:

<script language="javascript">

var x = document.getElementsByTagName('select');

var RN_SelectClass = "clsSelectControl pv";

var is_Required;

for (var i=0;i 1) {
switch (x[i].options[0].text) {
case 'parameter1': x[i].options[0].text = 'NewLabel1'; break;
case 'parameter2': x[i].options[0].text = 'NewLabel2'; break;
default: x[i].options[0].text;
}
}
}
</script>


Next, change the parameter and NewLabel in the above syntax to suit your required label name conventions.

Freeze rows and columns on Report Studio crosstab

I had a request recently by a business user who wanted to know if Cognos could freeze the header rows and columns of a crosstab (an Excel-like functionality). I found a fairly simple technique on how to accomplish this.

Steps:

1. 1. Add two blocks on a blank report page.
2. 2. Add a table with one cell and one row in the top block (assuming the report contains only one column).
3. 3. Add a list on the lower block, and a data item from the model (order number).
4. 4. Select the page, and associate the Query to the page.
5. 5. Drag a text item in the cell of the table (in the top block).
6. 6. Select the text item, and in the properties under query items, select order number (see step 3) and content as Label.
7. 7. Select the second block containing the list.
8. 8. Under Properties, Positioning, Size and Overflow, set Height = 12 cm and select Use scroll bar when necessary.
9. 9. Select the Order Number column title in the list. Set its Box Type properties to None.

Set default "from date" in a date range prompt

I discovered recently that you can't set the "default" of any Cognos prompts to a dynamic (non-static) value.

Let me explain: In my example, within Report Studio, I needed to change a date range prompt. I needed the "from date" to default to the beginning of the current year (i.e. Jan. 1, 2009), and the "to date" to default to the current date (today's date).

The best (non-Javascript) method of handling this as I discovered, is to create a radio-button prompt with the static choices of "From Start of Year" and "Select From Date". Next, I set these to values of 1 and 0 respectively. Finally, in the filter, I used the following logic:

(?DateType?=1 AND [MyDate] > _make_timestamp(extract('year',currentdate),01,01))

OR

(?DateType?=0 AND [MyDate] > ?FromDate?)

Your Corporate Brand on the C8 UI

Want to modify / customize your C8 headers (and add your company name and logo for example)? Look to make these changes directly in the system.xml (cognos\c8\templates\ps\portal\system.xml).

Steps:

1. 1. Place an image in the folder \c8\webcontent\skins\my_style\branding, where "my_style" is one of the skins/styling that is being used for the portal.
2. 2. Edit the /c8/templates/ps/portal/system.xml file using Notepad or an XML editor.
3. 3. Search for You will notice that this section is commented out. Un-comment out this section and customize as needed.
4. 4. Restart the Cognos 8 service for these changes to take effect.
   

Ensure you make a backup of your original system.xml file. Refer to the section: "Part 8 Customization" in the Cognos 8 Administration and Security Guide.

Reference book: IBM Cognos 8 Business Intelligence

From the editorial review: Develop, deploy, and maintain a complete BI solution across your enterprise. IBM Cognos 8 Business Intelligence: The Official Guide shows you how to take full advantage of the robust service-oriented architecture and simplified role-based user interfaces. Learn how to create, share, and analyze data-rich reports that lead to increased productivity and better business decisions. Featuring real-world insight and expert tips, this is a must-have guide for IBM Cognos 8 Business Intelligence users of all levels, from administrators to end users.

Measure and monitor performance using dashboards and scorecards; Assign user roles and privileges; Provide complete and consistent access to a broad range of data sources via Cognos Connection; Use Cognos 8 Go! to extend BI to Office applications, mobile devices, and search engines; Enable business users to create reports with Query Studio; Develop new insights by exploring data in multiple dimensions and perspectives with Analysis Studio; Build Dimensional Reports with Report Studio; Use Event Studio to identify and deliver mission-critical information; Create, modify, organize, and publish a model from Cognos Framework Manager;
Implement sound security measures; Take advantage of the management tools in Cognos Administration to ensure maximum reliability and availability.

We'll review this book soon. You can purchase this book on Amazon or as an eBook.

Disclaimer: The author of this blog is not affiliated with the author or publisher of the above described book.

YouTube find: MB Dashboards for Cognos 8

A UK company called morgan benjamin (lower case on purpose) has developed a sharp looking dashboarding tool for Cognos 8 business intelligence. From the visuals, it seems to integrate nicely into the Cognos suite and is available for all Cognos 8 releases.



Key features:

• Data driven ticker
• Pop up & Transition facilities
• Zoom Functionality
• Links to other Studios (Report Studio, Analysis Studio, Query Studio)
• Animated KPI’s
• Content and functionality Rich Dashboards created in hours not weeks
• Compatible with all versions of Cognos 8
• Leverages your in house report studio skills
• Employs professionally designed graphical layout
• Multiple templates and colour schemes
• Simple to add corporate identity
• Allows users to add comments to dashboards
• Links seamlessly with existing reports, cubes and data sources

I'm most interested in the ability to add comments or annotations to reports although I have read that this type of functionality will be included when Cognos 8.4 is released.