• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

Reporting Studio in IBM Cognos

Report studio or author advanced reports:

  • It is a web based component used to create professional reports or more advanced reports or complete reports
  • Use browser to access report studio
  • The following types of reports can be created by using report studio
    • List reports
    • Cross tab reports
    • Chart report s
    • Repeater table reports
    • Dash boards
    • Active reports
    • Balance reports to create dashboards
    • Map reports ……etc.
  • Folders are used to organize the reports
  • There are two types of folders in cognos
    • Public folder (default)
    • My folder
  • A report which is saved in a public folder can be accessed by any folder
  • A report which is saved in my folder is specific to that particular user

 

Components of reports studios GUI:

Inter table objects pane:

It contains the following there tab

  1. Source tab (default) : to display package items
  2. Data items tab : to display query processed items
  3. Tool Box : to display the predefine objects of report studio GUI

Properties pane:

It is used to set the required properties of the selected item in the work area

It displays the properties of selected item in the work area

Work area:

It is used to create report applications

By default work area contains a report package

Report package contains

  1. Page wader: use page wader to display the titles, logos
  2. Page footer: use page footer to display page number date, time
  3. Page body : use page body to create report

Explorer bar:

It is a vertical bar between insert able objects pane and work area

It contains the following 3 types of explorer

  1. Page explorer : to explore different pages of a report like report page, prompt page
  2. Quarry explorer: it is used to explore the quarries of a report
    1. Use quarry explorer to use set operations like union, join minus, intersection, etc
  3. Condition explorer: it is used to perform conditional formatting

Ancestor button:

It is used to display the data in the form of rows and columns (tabular format)

List report contains:

  1. List header: display column titles
  2. List body : display actual data items

EX: create list report with order year, order month, quantity and revenue

  • Open the browser IE type the following URL http:// localhost/ibm cognos
  • From welcome page select author advanced reports (report studios)
  • Select the package go sets and relevance
  • Select the create name arrrowselect list arrrow click on ok
  • Expand orders query subject drag order year and quantity and revenue and drop on list report in the work

 

Saving a report:  

  • From file menu click on save
  • Click on create name folder
  • Enter the required name arrrowok arrrow double click on the folder
  • Enter the name under the folder arrrow click on save
  • Report created

 

Executing a report by using report studio:

From menu bar, select run menu click on run report time 

 

Aligning the report:

  • Use horizontal alignment and vertical alignment properties to align a report
  • Select page body
  • From properties pane select horizontal alignment arrrow enter
  • From vertical alignmentarrrowmiddle

 

Eliminating duplicate values:

  • Use group option to eliminate duplicates

Note:

Duplicates are because of one too many relationships between the items in the report

  • If there is n items which are having one too many relationship then ‘n-1’ items from top will display duplicates

 

Procedure to eliminate duplicates:

  • Select the order year data item in the list report
  • From structure menu select group/ungroup

Note:

Displaying all records in the single pageoin

  • Select run menu select run option
  • Set rows per page ‘0’ arrrow click on oksognos

 

Setting colors and fonts:

  • Select order year column title cell in the list report
  • From properties window select ancestor Screenshot_3 button
  • Select list columns titles style
  • From properties pane, select font property arrrow click on browse
  • Select required font size, style, family, weight
  • Select for ground color arrrow select required color arrrow ok
  • Click on apply arrrow click on ok

 

Calculating group totals:

Use aggregate functions to perform group calculations

  • Select quantity and revenue in the list report
  • From data menu summaries arrrow “total”

 

Creating sections:

  • Select order year dsa item in the list report
  • From structure menu arrrow  select ‘section’

 

Setting page break:

  • Select order year section in the report
  • From structure menu arrrow select ‘set page break’

 

Merging data cells

  •  Cognos allows the user to combine multiple data cells in to a single data cell
  • Use ‘ block object’ to merge data cells

Eg:

Create a list report with order year, order method, quantity and revenue

  • From tool bar click on ‘unlock’Screenshot_4
  • From insert able object pane arrrow select tool box
  • Drag block objectarrrow drop on order year data item in the
  • Select order method data item, drag and drop the block
  • Select order method data cells from tool bar click on delete
  • Select order year column title from tool bar click on delete
  • Select order year column title from tool bar arrrow delete
  • From tool box, drag a text item, drop on the title column call
  • Enter column name as order_info arrrow ok

 

Data format:

  • Congnos allows t format dates, numbers, currency, time, text_etc
  • Use data format property to format the data

EX:

Create list report with order date, order method, qnty, and grass profit

  • Select order date data item in the list report
  • From properties pane, select data format property
  • Click on browse arrrow format type arrrow date
  • Set the following properties
  • Data style –arrrow full
  • Data ordering arrrowD.M.Y arrrow ok

 

Report filters:

  • A filter which is created at the time of generating the report is called report filter
  • A report filter is specific to that particular report
  • Report filters are two types
    • Detail filters
    • Summary filters

 

Detail filters:

  • Detail filter can be created for attributes as well as measures
  • In case of measures like qty and revenue, detail filters are categorized in to 2 type
  • Before auto aggregation
  • After auto aggregation

“A filter which is applied for each and every detail record that is called a detail filter “

  • Detail filters are two types
    • Static filter 

 

Static filter:

A filter which is called with a static value that can’t be changed at run time it is called a static filter

Syntax:

Quarry item operator operand

Ex: 1 – year = 2009

EX: 2 – country in (‘India’, ‘USA’, ‘UR’)

EX: 3- year not in (2005, 2006)

 

Static filter for attributes:

Create a list report with list report quty, and revenue group on order year total in qty and revenue

  • From data menu, select arrrow filters-arrrow edit filters
  • Click on add Screenshot_5 arrrow advanced arrrow ok
  • From order quantity subject drag order year drop on expression definition window
  • Type= from tool bar select single value, select the required year click on insert (=2006)
  • Click on validate symbolarrrow click on ok
  • Click on add filter arrrow advanced –arrrow ok
  • Define the following condition

 

Detail filter for measure:

  1. Before auto aggregation :

It is a filter condition it is apply before aggregating the detail records than it is called a filter before auto aggregation

Report:

Order year. Order month qty

Year Month Day Qty
2004 1 1 12
1 2 15
1 3 09

 

Data base level:

Filter: qty > 10 (before auto aggregation)

Eg:

Create a list report with product type, predict line, qty

  • Group on product line total on qty
  • From data menu select filters arrrowedit filters click on arrrow add
  • Select advanced arrrow ok
  • Define following condition
  • Qty arrrow 75 (drag qty from order quarry subject)
  • Click on ok arrrow before auto aggregationarrrow ok

 

Filter after auto aggregation:

If a filter condition is applied after aggregating the detail records then it is called a filter auto aggregation

Eg:  create a list report product type, qty, group of product line, total of qty

  • From data menu arrrow select filters arrrow edit filters
  • Click on add select arrrow advanced arrrow ok
  • Define the following condition
  • Qty > 100000 ( drag the qty from orders)
  • Click on ok arrrowselect after auto aggregation
  • Run the report

 

Summery filter:

  • If a filter condition is applied for group summaries then it is called summery filter
  • Use summery filters to filter the data based on group summaries

Note:

To create summery filter first create groups

Eg: create a list report product line, product type, Qty group on product line, qty

  • From data menu select filtersarrrow edit filtersarrrow summery filters arrrow add arrrow advanced arrrow ok
  • Available components drag qty drop on expression definition window
  • Type qty>300000arrrow ok
  • From scope click on browse arrrow product line arrrow okarrrow ok 

 

Parameterized filters or dynamic filters: or prompt:

If a filter condition is created with a parameter then it is called a parameterized filter

  • Parameterized filter allows the user either to select the values (S) or to enter the value (S) at run time
  • In case of parameterized filter, cognos server creates a prompt page at the time of execution to select the value or to enter the value

Syntax to create parameterized filter:

Query item operator? Parameter?

Eg: year =? Param_year? (name, param_year is parameter name which is user defined )

Create a list report order year, order name, qty, and revenue. Group on order year, total on qty and revenue

  • From data menuarrrow select filtersarrrow edit filtersarrrow add
  • Click on advanced arrrowok
  • Define following condition
  • Order year =? Param_order year?
  • Drag order year from orders query subject. Drop on expression definition window –> ok–> optional arrrow ok
  • Run the report
  • Single value means arrrow ‘=’ operator
  • Multiple values means arrrow ‘in’ operator

Eg:

Create a list report with country, product line, order method, qty, and gross profit

  • Group on country, product line total on qty and gross profit
  • From data menu arrrow filterarrrow edit filtersarrrow addarrrow advanced
  • Click on ok
  • Define following condition
  • Country =? Param_country?
  • Click on ok arrrow ok

Note:

Cognos creates a value prompt if the parameterized

  • If creates a text box prompt for numeric items
  • It creates in_range prompt for data items

 

Build prompt page:

It is used to create a prompt page with the selected items in the report

  • By default it creates parameterized filters for the selected items in the report

Eg: Create a list report with country, product line, selected items in the report

  • By defaults it creates parameterized filters for the selected items in the report

Eg: Create a list report with country, product line, order method, qty and revenue

Group on country, product line, total on Qty and revenue

  • Select country product line, data items in the list report
  • From tools menu arrrow select “build prompt page”
  • Apply filters

 

Cascading prompts:  

A prompt which takes the values from author prompt is called a cascading prompt

There are two types of cascading [prompts

  1. Single select, auto submit cascading prompt
  2. Multi selection

Note:    Auto submit works any for single server in case of multi selection, create ’re prompt button’

Ex:

Create a list report with product line, product type, product name, qty, revenue.

  • Group on product line, product type, and total on qty and revenue
  • Select product, product type, product name by using edit button
  • Select product line prompt from properties setarrrow multi selectarrrow no
  • Auto submit arrrow yes, cascading sourcearrrow name
  • Select product type prompt set the following properties
  • Multi select arrrow no, auto submit arrrow yes cascade sourcearrrow click on browsearrrow product line arrrowok
  • Select product name prompt arrrowmulti select arrrow yes
  • Auto submit arrrow no, cascade type arrrow product typearrrow ok

 

Multi selection:

Select product line prompt set the following properties multi select arrrow yes, auto submitarrrow yes, cascade source re prompt line

  • Select product type arrrow multi select arrrow yes
  • Auto submit arrrow no arrrow cascade source-arrrow product type
  • Select product name arrrow multi select arrrow yes
  • Auto submit arrrow no arrrow cascade source arrrow product type

 

Creating a re prompt button:

  • From test box drag prompt button drag beside product line prompt page
  • Select next from properties arrrow select type arrrow re prompt
  • Similarly create a re prompt button for product types prompt

 

Creating customized prompt page with a value prompt:

Value prompt:

It allows the user to select the values at the run time

  • In case of value prompt, cognos creates a prompt query which will fetch all the district values from database and displays in the value prompt
  • Value prompt is very user friendly
  • Value prompt allows the user to create radio buttons, drop down list, check box group, to add user option etc

Create a value prompt for order year by creating the prompt page manually

  • Create a list report with order year, order method, qty and gross profit
  • Group on order year total on qty and revenue
  • Select page explorer bar–> select prompt pages
  • From insert able objects pane drag a page drop on prompt page window
  • Double click on prompt page top open
  • From tool box drag a value prompt drop on prompt page
  • Create a new parameterarrrow enter the name new “param_year”
  • Click on next arrrowfrom package item arrrow order year arrrow ok
  • Operator = arrrow next arrrow
  • From tool box drag a text item drop just before value prompt in the prompt page
  • Enter the text as order yeararrrow ok

 

Creating radio buttons or check boxes group in a value prompt: 

  • Select value prompt from properties pane select “select ui” propertyarrrow radio button group

 

Displaying images: (logo)

  • Use image object to download the images to run time
  • From tool box drag image object drop on the title area
  • Select image object from properties select arrrow name arrrow”company name”
  • From properties, select the “url” property arrrow server URL (image URL path)arrrowok

 

Re creating an already existing download image:

  • Use layout component reference object to refer an existing object
  • Open report page from tool box, drag layout component reference object, drop in the title area
  • Select image arrrow ok

 

Value prompt with user options:

User “static” choice property to add user options to a value prompt

Eg: create a list report with order year, order method and qty, group on order total on qty

  • Add a prompt page to the report
  • Open the prompt page from tool box drag a value prompt
  • Create a parameter (param_year) and click on next
  • Package item (order_year)
  • Operator (=) arrrow click on next and finish
  • Drag the text item from toll box and next select order year Screenshot_6
  • Create radio button to the data body, select “UI” property and select radio ”button group”
  • Select value prompt from properties select “static choices” and click on browse then click on add
  • Use arrrowall, display arrrow all, arrrow ok
  • From page explorer select report page
  • From data menu select filters and edit filters
  • Double click on the filters
  • Define the following expression in the expression definition window

If (? Param_year? /=’all’) than ([do sales_goretailers],[orders],[order year]

Else

[go sales_go retails].[orders].[order_year] = ?param_year?]

  • Click on ok
  • Run the report

 

Text box prompt:

It allows the user to enter the value at run time

  • In case of text box cognos does not create a prompt query

Eg: Create a list report order year, order method, qty, revenue group on order year total on qty and revenue

 

Add a prompt page:

  • Open a prompt page from tool box drag a text box prompt drag on prompt page
  • Create a name parameter (param_year) arrrow select order_year.
  • Operator =arrrow finish
  • From tool box drag a text item, drop before text box prompt
  • Enter the order yeararrrow ok

Eg: Create a prompt page with 2 prompts (1 for order year, and one for country) and display the prompts 1 bellow another with messages

  • Create text box prompt for order year and value prompt for country

Procedure:

  • Create a list report with order year, country, qty, revenue group on order year total on qty and revenue

 

Add a prompt page to the report:

  • Open the prompt page
  • From tool box drag a table object, drop on prompt page
  • Drag a text item drop in (1,1) cells
  • Enter order year : arrrow click on ok
  • From tool box, drag text box prompt drop on (1,2) cell
  • Enter the parameter name (param_year)
  • Package item arrrow order year
  • Operator arrrow in arrrow click on finish
  • Drag a text item drop on (2,1) cell enter the text as select the countryarrrow ok
  • From tool box drag a value prompt drop in (2,2) cell
  • Enter the parameter (param_country)arrrow next arrrow
  • Package item arrrowcountry
  • Operatorarrrowin
  • Parameter arrrow param_country arrrownext arrrow finish

 

Add check box group in the value prompt:  

  • Select the value prompà properties arrrow select UI arrrow check group arrrow ok
  • Display the value in red color
  • Use font properly to select color

 

Select and search prompt:

It allows the order to search for the required values to select the values at run time

Eg:

Create a list report with country, product line, order method, qty, and gross profit

  • Create a page and open the prompt page
  • Enter the parameter arrrow param_countryarrrow nextarrrow
  • Package item arrrow country
  • Operator arrrow in arrrow next arrrow finish
  • Drag a text item just before prompt page and enter the name “enter the word to search for”
  • Run the report

 

Inline prompt:

A prompt which is created in the report page itself is called inline prompt

Eg: Create a list report with order year, order method, qty, and gross profit group on order year total on quantity and gross profit

  • From tool box drag a value prompt drop bellow prompt in the report page
  • Enter parameter arrrow param_year
  • Package item arrrow year
  • Drag text item enter the value sheet order year
  • Select UIarrrow check box group for promp

 

Cascading prompts:    

Create a report cascading prompts build prompt page option (use customized prompt page)

Ex: Create a list report with product line, product type, qty, revenue, product name

Group on product time, product type, total on qty on qty and revenue

  • Add a prompt page to the report and open the prompt page
  • From tool box drag a table object drop on prompt page
  • Enter number of columns 2 number of rows 3
  • Select show borders arrrow ok
  • Drag a text item drop in (1,1) cell
  • Enter the text name arrrow select the product line arrrow ok
  • Drag a value prompt, drop in (1,2) cell
  • Parameter name arrrowpram_product line arrrow next
  • Package item arrrow product line
  • Operator =arrrow next arrrow finish
  • Drag a text item drop in (2,1) cell
  • “ select product type “ (text name)arrrow ok
  • Drag a value prompt drop in (2,2) cell
  • Enter the parameter arrrow param_product type arrrow next
  • Package item arrrow product type
  • Operator arrrow = arrrow next –arrrow nextarrrow
  • Cascade source arrrow param_product line arrrow finish
  • Drag a text item, drop in (3,1) cell
  • Enter “select product name” arrrow ok
  • Drag a value prompt, drop in (3,2) cell
  • Enter the parameter arrrow param_product name arrrow next
  • Package item arrrow product name
  • Operator arrrow in arrrownext arrrow next
  • Cascade source arrrow param_product type arrrow finish
  • Select product line prompt arrrow from propertyarrrowauto submitarrrowyes
  • Select product type promptarrrow from property arrrow auto submitarrrow yes

 

Conditional formatting

It is a process of formatting the report based on given condition or expression

  • Conditional formatting provides the user quick way of analysis
  • Use condition explorer to perform conditional formatting
  • Condition explorer contains the predefined variables which are used to perform conditional formatting
  • There are three types of variables in cognos
    • String variable
    • Boolean variable
    • Report language variable

 

Variable:  

It is a temporary memory location used to hold the nature values of an expression temporarily during execution

String variable:

Desired to gain proficiency on Cognos? Explore the blog post on Cognos training online to become a pro in Cognos.

Conditional formatting – string variable:

Use string variable to hold the return values(s) of an expression which is defined with user defined values temporarily at the time of execution

Ex:

If (order year = 2004) then (1)

Else

If (order year = 2005) then (2)

Else

If (order year =2006) then (3)

 

String variable holds the value wither 1 or 2 or 3 at here, (1, 2, 3) are user defined values

Here (1, 2, 3) are user defined values.

Ex: Create a list report with order_year, order_method, qty group on order year, total on qty.

  • Select condition explorer from explorer bar.
  • Select variables from insert able object pane drag string variable, drop on variables window.
  • Define the following expression in expression definition wind
  • If ([query]. [Order year]=2004) then (1)
  • else
  • If ([query]. [Order year]=2005) then (2)
  • else
  • If ([query]. [Order year]=2006) then (3)
  • From variables arrrow select string1 arrrow properties arrrow name arrrow string_year
  • From values window click on add Screenshot_8
  • Values = 1 arrrow ok
  • Similarly add the values 2 and 3
  • Select page explorer arrrow select report page1
  • Select order year in the list report.
  • From ansister select list columns body style.
  • From properties pane arrrow select style variable arrrow click on browsearrrowfrom drop down list arrrowselect string_year.
  • Select condition explorer, select the value1 from string_year.
  • Set the required color and font by using font properties.
  • Select condition explorer, select the value2 from string_year.
  • Set the required color and font by using font property.
  • Select condition explorer, select the value3 from string_year.
  • Set the required color and font by using font property.
  • Double click on explorer bar to turn off.
  • Run the report.

Conditional formatting – Boolean variables:

Boolean variable is used to hold the return values of a Boolean expression temporarily at the time of execution.

  • It can hold the values either ‘yes’ or ‘no’.

Ex for Boolean expression:

  • Qty > 10000

Ex: Create a list report to display the records which are having more than 1 lakh in green color and display the records which are having Qty below 1 lakh in red color.

Procedure:

  • Create a list report with order year, order method, qty group on order year total on qty.
  • Select condition explorer, select variables.
  • Drag Boolean variable drop on variable window.
  • Define the following expression.
  • [Query1]. [Query] > 100000
  • Click on validatearrrow click on ok
  • Open report page from page explorer
  • Select page1
  • Select qty data item in the list report.
  • From properties pane, select style variable
  • Click on browse from dropdown list arrrow Boolean 1 arrrow ok
  • Select condition explorer, select the value ‘Yes’
  • Set the required green color by using font property.
  • From condition explorer, select the value ‘No’
  • Set red color by using font property.
  • Double click on explorer bar to turn off
  • Run the report.

 

Conditional formatting – Report language variable:

Use report language variable to hold the language names temporarily during execution.

  • Display the report in red color when report is executed in Chinese language. Display the report in green color when report is executed in Dutch language. Display the report in maroon color when report is executed in English language (default)
  • Create a list report with country, product line, order method, qty and revenue
  • Group on country and product line total on qty and revenue.
  • Select condition explorer —arrrowselect variables.
  • Drag report language variable drop on variables window.
  • Select the languages Chinese and Dutch and English.
  • Open report page from page explorer.
  • Select any item in the list.
  • From assister arrrowselect list.
  • From properties arrrow select style variable.
  • Click on browse arrrow from variables arrrow report languages.
  • From condition explorer select the value ‘Chinese’.
  • Set the required color and font by using font property.
  • Similarly set the color and font for the remaining languages.
  • Double click on explorer bar to turn off.

 

Executing a report the required language:

  • From run menu arrrow run options
  • Language arrrowChinesearrrow ok
  • Run the report.

 

Drill through:

It is a process of navigating from one report to another report (or) navigating from one place to another place in the report by creating ‘hyperlinks’.

  • Use drill through property to create hyperlinks.
  • Which allows to perform drill through

Navigating from report to another report (or) Navigating from master to detail:

Master report(or)summary report

 

Screenshot_15

 

Procedure:

Master report:

Create a list report with country, qty, revenue save the report with the name Couuntry_Master.

 

Detail report:

  • Create a list report with country, city, staff name, product line, qty and revenue. Group on country, city, staff name.
  • Total on qty and revenue.
  • From data menu arrrow select filters arrrowedit filters
  • Select advanced arrrow click on ok
  • Add the following condition.
  • Country =? Param_country?
  • Click onarrrow ok
  • Save the report with the name country_details.

 

Creating hyperlinks for country data item in the master report:

  • Open country_master report.
  • From file menuarrrow open arrrowcountry_master arrrowopen
  • Select country data item in the list report.
  • From properties pane arrrow select arrrow drill through definition.
  • Click on browse arrrow click on add Screenshot_8
  • From report click on browse arrrowCountry _Details.
  • Click on open
  • From parameter click on edit (symbol)
  • Enter the following information.
  • Name – Param – country
  • Type – string
  • Required – Screenshot_2 Yes
  • Multi select – none
  • Method – data item value
  • Value – country
  • Property to pass arrrow default
  • Click on ok arrrowok
  • Save the report and execute the report.

 

Staff – details Report:

  • Create a list report with staff name, city, address, work phone, fax and email.

Add a parameterized filter for staff name with the following condition.

Staff name? Param staff name?

  • Save the report with the name staff.details

 

Creating hyperlinks for staff name in country details report:

  • Open country – details report.
  • Select staff name data item in the country – details list report.
  • Right click arrrow drill through definition.
  • Click on add Screenshot_8
  • Report arrrow staff – details arrrow open
  • Click on edit (symbol)
  • Method – pass data item value.
  • Value – staff name
  • Click on ok arrrowok
  • Save the report and execute the report.

 

Creating hyperlink for text item:

  • Open country – detail report.
  • From tool box, drag text item drop in the title area.
  • Enter the name as ‘Back’
  • Click on ok
  • Select text item back arrrowright click drill through definition arrrow click on add.
  • From report arrrow country – master arrrow open arrrow ok
  • Save the report.

 

Book Marks:

It is used to navigate from one place to another place within the report.

  • Create a list report with country qty and revenue.
  • From toolbox, drag a list object drop below first in the report page.
  • Drag country, city, product line, order method, qty and revenue drop on second list (below)
  • Group on country, city, product line total on qty and revenue in the second list.
  • Select country item in the second list from “structure” menu click on “section”.

 

Note: Unlock the report to place a book marks in the report page.

  • From tool bar click on unlock.
  • From tool box, drag a book mark drop beside country section in the report page.
  • Select book mark from properties arrrow source typearrrow data item value.
  • Data item valuearrrow country
  • Select country data item in the first list.
  • Right click, select drill through definition.
  • Click on add Screenshot_8
  • Select book mark
  • Source type arrrow data item value.
  • Data item arrrow countryarrrow ok
  • From tool box drag a book mark drop in the title area.
  • From tool box drag a text item, drop beside country book mark.
  • Enter the text as ‘Top’ arrrow ok arrrow create hyperlink
  • Select title book mark (Top) from properties source type ‘Text’
  • Label arrrowTop arrrow ok arrrow “Create hyperlink” for top text item.
  • Save the report with the name book mark – report.

 

Executing the report in the background by using IBM Cognos connection:

  • From welcome page select “My home”
  • Browse to location of the report.
  • Select gosales and retails arrrow batch 8am – reportsarrrow book mark report.
  • From condition select run with options Screenshot_9 for book mark report.
  • Format arrrow HTML
  • From delivery arrrowselect save the report.
  • Click on run arrrow ok
  • Select refresh button to refresh IBM Cognos connection page to view the output.
  • From action arrrow select “View the output versions”
  • Select the HTML

 

Master detail relationship:

It is used to create a relationship between master data and detail data based on common item.

  • There should be a common item between master layout and detail layout to create master detail relationship.
  • Master detail relationship is used to filter the detail data based on master.

 

Note: Select any item in the detail layout to enable master detail relationship option.

EX: Create a list report with order year, qty and revenue from tool box drag a list object drop beside revenue in the first list report.

  • Drag order year, country, product line, qty and revenue drop in the Inner list (detail list)
  • Group on order year, country in the inner list.
  • Total a qty and revenue.
  • Select order year in the inner list.
  • From data menu arrrow select ‘Master detail relation ships’
  • Select ‘New link’ [New link]
  • Select order year from query 1 and query 2
  • Since order year is common item in this query
  • Click on ok arrrow run the report.

 

Report level calculations (or) report calculations:

  • Report studio allows the user to device new calculations objects from already existing objects at the time generating the report.
  • These calculations are called as report level calculations
  • Report calculations are non reusable
  • There are 2 types of report calculations
  1. Query calculations
  2. Layout calculations

 

Query calculations:

If a calculation is derived by defining an expression at query level is called a query calculation.

  • Query calculation object will be display separate column in the report.
  • Use already existing item data source to device query calculation objects.
  • Use query calculation object from tool box to perform query calculations.

Ex: Derive a new query calculation with the name plane – revenue by multiplying qty and unit sale price.

  • Create a list report with order year, order method, qty and unit sale price.
  • From tool box drag query calculation object drop beside unit sale price in the list report.

Name arrrow plane – revenue arrrow ok

Define the following expression in the expression definition window.

[Go sales_go retailers]. [Orders]. [Quantity] * [go sales_go retails]. [Orders]. [Unit sale price]

Click on valid date arrrow click on ok

 

Calculating plane – revenue in terms of rupees:

  • Drag a query calculation object drop beside plane_revenue in the list report.
  • Name arrrow plane_rev_in_rs arrrow ok
  • Select data items symbol.
  • Drag plane_revenue drop on expression definition.
  • Define following definition.
  • [plane_revenue] * 60

Converting dollar $ into Indian rupees Screenshot_10:

  • Use data format property to format currency.
  • Select plane_rev_rs in the list report.
  • From properties pane select data formats.
  • Click on browse.
  • Format type arrrow currency
  • Set the following properties
  • Currency –> RP(INR) – India rupee
  • Currency symbol arrrow Rs
  • Currency symbol position arrrowbeginning
  • No of decimal places arrrow 0

 

Err: Derive a new query calculation object with the name staff_name by concatenating first name and last name.

  • Don’t display first name and last name in the report.
  • Create a list report with order year, order method, revenue.
  • From toolbox drag query calculation object drop in between order method and revenue.
  • Name arrrow staff – namearrrow ok
  • Define the following expression.
  • First name (double click) ll “ll last name.
  • ll arrrow means (concat operator)
  • Select concat operator from functions tab, operator’s folder.

 

Ex: Derive new calculation objects with the names qty_in_2004, qty_in_2005 and variance country wise and display the countries (or) highlight the countries which are having the variances less than ‘0’

  • Create a list report with only country from tool box drag a query calculation object ——————
  • Name arrrow qty_in_2004 arrrow ok
  • Define following the expression.
  • If ([order year] = 2004) then ([Quantity])

else

(0)

  • Click on validate arrrow click on ok
  • Similarly calculate qty_in_2005
  • By defining following expression
  • If ([order year] = 2005) then ([Quantity])

else

(0)

  • Click on validate –arrrow click on ok
  • Drag query calculation object drop beside qty_in_2005 in the list report.
  • Name arrrow variance arrrow ok
  • Define the following expression.
  • [Qty_in_2004] – [Qty_in_2005]
  • Qty 2004, Qty 2005 drag from data item tab.

 

Highlighting countries which are having variance Less than ‘0’:

  • Select conditional explorer arrrow variables.
  • Drag Boolean variable drop on variable window.
  • Define the following expression.
  • [Quantity].[Variance] < 0 arrrow ok
  • From page explorer select report page.
  • Select variance in the list report.
  • From ansister arrrow list columns body style.
  • From property arrrow style variable arrrow click on browse.
  • Variable –arrrow Boolean 1 arrrowok
  • From conditional explorer select the value ‘Yes’
  • Set the red color by using font property.
  • Turn off explorer bar by double click explorer bar
  • Run the report.

 

Ex: Display top 5 countries revenue wise

  • Create a list report with country, revenue from toolbox drag a query calculation object. Drop beside revenue in the report.
  • Enter the name ‘Rankarrrow ok
  • Select functions tab expand ‘summaries’ functions
  • Drag rank function drop on expression definition window.
  • Drag the revenue drop on expression window beside rank function.
  • Rank (revenue)
  • From data menu select filters, edit filters.
  • Click on add Screenshot_8
  • Select advancedarrrow ok
  • Select data items tab drag the rank drop on expression window.
  • [Rank] < 6
  • Validate arrrow ok
  • Select ‘after auto aggregation’ arrrow ok

 

Layout calculations:

It is a process of calculating the layouts like displaying current dates as title, current time as title at run time.

  • Use layout calculation object to perform layout calculations.

Ex 1: Display the selected country name as a title at run time.

  • Create a list report with country, product line, order method, qty and revenue.
  • Click on qty and revenue.
  • Add a parameterized filter for country with the following condition.
  • Country =? Param-country?
  • From toolbox drag layout calculation object, drop in the title area.
  • Select parameters tab drag the parameter (param_country) drop on expression definition window.
  • Param display value (param_country) arrrow ok

 

Displaying report execution data (current data) as a title:

  • From toolbox drag layout calculation object, drop in the title area.
  • Select functions tab, expand report functions.
  • Drag the function “as of date” drop on expression definition window arrrow ok

 

Displaying both countries, current data one below in title area in runtime:

  • From toolbox drag a table object drop in title.
  • Enter 2/2 columns (2) rows (2) arrrowDrag a text item “country name”
  • Drag a text item (2,1) cell “current date”
  • Drag a layout calculation drop in (1,2) cell
  • Select param_country, drag the expression definition windows.
  • Drag a layout calculation drop ou (2,2) cell
  • Drag “as of date” function drop on expression definition window.

 

Crosstab Report:

It is a best report for compare to analysis crosstab report displays the data in the form of rows and columns and measures.

Every crosstab report contains

  • Row Zone
  • Column Zone
  • Measure Zone

Note: Intersection of row and column is measure symbolical representation of a cross tab

 

Screenshot_16

 

 

Types of crosstab:

1.    Basic cross tab (or) simple crosstab

 

Screenshot_17

 

2.    Nested cross tab

  1. Nested on rows
  2. Nested on columns

 

    1. Nested on rows

     

 

Screenshot_18

 

  1.  Nested on columns

Screenshot_19

 

3.     Union crosstab (or) Discontinuous crosstab

  1. Union on rows
  2. Union on columns

 

  1. Union on rows

Screenshot_20

 

b.  Union on columns

Screenshot_21

 

4.   Asymmetric crosstab

Combination of both nested and union

Screenshot_22

 

Screenshot_23

 

Ex: Create a simple crosstab report writer order flag on rows, Order method on columns and sequence on measures.

  • Open report studio with go sets and retailers package
  • Then select crosstab arrrow ok
  • Drag order year, drop on columns
  • Drag order method, drop on columns
  • Drag columns, drop on measures
  • Select the measure zone, select the aggregate function total.
  • Run the report.

Note: Displaying the grand total just below columns heading in cross tab.

  • Select total in a cross tab drag and drop below revenue (where the big line is blinking)

 

Nested crosstab:

  • Create a crosstab with order year, order month on rows, order method on columns, grass profit on measures.
  • From file menu select new select crosstab arrrow ok
  • Drag order year, drop on rows.
  • Drag order month, drop beside order year on rows.
  • Drag order method, drop on columns.
  • Drag revenue, drop on measures.
  • Select measures zone and select aggregate function total

 

Creating a space between 2 data items in a crosstab:

  • From toolbox drag a crosstab space object, drop in between order year, order month items in the crosstab.
  • Use different filters, prompt pages (name / yourself)

 

Union crosstab:

  • Create a new crosstab report drag order year, drop on rows.
  • Drag order month, drop below order year on rows.
  • Drag product line, drop on columns.
  • Drag revenue, drop on measures.
  • Create a space between order year and order month.

 

Chart Reports:

  • Use chart reports to display the data in the form of graph.
  • Chart allows the user to represents the data in a pictorial way or a graphical way.
  • The following of types charts.
  1. Column chart
  2. Bar chart
  3. Line chart
  4. Pie chart
  5. Gauge chart ….etc
  • Column chart and pie chart are most frequently used charts in real time.
  • Create a column chart with order year on categories (X-axis), order method on series and requires on measures (Y-axis)
  • From file menu select new select chart arrrow column , chart arrrowok
  • Drag order year drop on categories (X-axis).
  • Drag order method drop on measure (Y-axis)
  • Run the report.

 

Hiding the values in a column chart:

  • Select column chart from properties select tooltip property arrrow hide, yes means(show)

 

To increase (or) decrease a chart:

  • Select chart report from property, select size and overflow arrrow click on browse. Height 6 in, width 5 in. arrrow ok

Pie chart:

  • From file menu select new arrrow chart arrrowok
  • Select pie donut arrrow select the chart arrrow ok
  • Drag order year, drop on categories (pies)
  • Drag order month, drop on series (pies slices)
  • Drag revenue, drop on default measure

 

Displaying the measure value per each and every pie outside with lines:

Select pie chart from properties.select values arrrow outside slices with lines

 

Displaying the values internal of percentage:

Select pie chart from properties, select value representation arrrow present

 

Sharing the same information between multiple layouts:

(List, crosstab, chart):

Sharing the information between the layouts improves the performance.

  • Create a list report with order year, order method send qty.
  • From toolbar drag a crosstab, drop list layout in the report page.
  • Select anywhere in the crosstab.
  • From properties select arrrow query arrrow Query
  • Select data items tab (Icon)
  • Drag order year from query, drop on crosstab rows.
  • Drag order method, drop on measures in crosstab.
  • From tool box drag a chart drop below crosstab in the report page.
  • Select chart from properties select.
  • Query arrrow query 1
  • Select data items tab (Icon)
  • Drag order year from query 1 drop on categories.
  • Drag order method, drop on series.
  • Drag qty drop on default measures in pie chart.

 

Conditional Blocks:

  • Use conditional blocks the required layout based in the condition satisfied.
  • Use block variable incase of conditional blocks.

Ex: Create a list report with order year, order method, and qty. group on order year total on qty.

  • From tool box drag a crosstab drop below list.
  • Drag order year drop on rows.
  • Drag product line drop on columns.
  • Drag grass profit drop on measure.
  • From tool box drag a chart drop below crosstab
  • Select column chart click on ok.
  • Drag order year drop on categories.
  • Drag order month drop on series.
  • Drag revenue drop on measures.

 

Creating a value prompt with user defined values:

(List, crosstab, chart):

  • From page explorer select prompt page.
  • Drag a page drop on prompt pages.
  • Double click on prompt page.
  • From tool box drag a value prompt drop on prompt page.
  • Enter the parameter arrrow param_layout arrrow next.
  • Click on finish.
  • Drag text item drop before value prompt.
  • Enter the text as “select the layout” arrrow ok

 

Create radio buttons in the value prompt:

  • Select value prompt from properties select UI arrrow radio button group.

 

Adding the user options to a value prompt:

  • Select value prompt from properties
  • Static choices arrrow click on add Screenshot_8
  • Use arrrow list
  • Display arrrow list.arrrow ok
  • Click on add
  • Use arrrow crosstab
  • Displayarrrowcrosstab arrrow ok
  • Click on add
  • Use arrrow chart
  • Display arrrow chart arrrow ok arrrow ok

 

Creating string variable:

  • Select condition explorer select variables.
  • Drag string variable drop on variables window.
  • Define the following expression in expression definition window.
  • If (param display value (“param_layout”)=’list’) then (‘A’)

Else

If (param display value (“param_layout”) =’crosstab’) then (‘B’)

Else

If (param display value (“param_layout”) =’chart’) then (‘C’)

  • Click on validate arrrow ok
  • From values window click add Screenshot_8

Enter A arrrow ok

  • Similarly add B and C arrrow ok
  • From page explorer select report page
  • From tool box drag “conditional blocks” object drop on empty space in the report page.
  • Select conditional block in the report page.
  • From properties select block variable property
  • Click on browse arrrow variable arrrow string 1 arrrow ok
  • From properties select “current block” arrrow A
  • Select order year in the list from assister select list
  • Drag the list layout, drop in the conditional block.
  • Select conditional block from propertiesarrrow
  • Current block arrrowB
  • Select any item in the crosstab from assister select crosstab.
  • Drag the crosstab drop in the conditional block.
  • Similarly de for chart layout.
  • Save the report.
  • Run the report.

For indepth understanding Cognos Training click on

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Reporting Studio in IBM Cognos"

Leave a Message

Your email address will not be published. Required fields are marked *

Site Disclaimer, Copyright © 2016 - All Rights Reserved.