CognosLb

8/13/2019 CognosLb

1/23

8/13/2019 CognosLb

2/23

Shekhar

DWH technologies, 505, Annapurna Block, Ameerpet. Phone:04066751666

Select Conn_Sample26, NextExpand Scott> Expand Tables> Check Emp and DeptNextGenerate Relationship----------------------Deselect generate criteriaImport and Finish

Project Viewer------------Proj_Sample26

Conn_Sample26DEPTEMP

RGHT CLICK ON Conn_Sample26> Create > NamespaceName: Database ViewDrag EMP and DEPT inside Database View NamespaceRGHT CLICK ON Conn_Sample26> Create > NamespaceName: Business Viewcopy emp and dept from database view and paste shortcut in business view(select required querysubject> rght click > edit> copyselect required namespace>rghtclick > edit> paste shortcut)Rename ShortcutsRght Click on the Workspace, Create > RelationshipName: R1Query Subject .. Browse DEPT Query Subject .. Browse EMPLink Deptno from DEPT to Deptno From EMPok

RGHT CLICK ON Conn_Sample26> Create > Namespace

Name: Presentation ViewRght click on Presentation View> Create> Query Subject PackageName: Pkg_Employee26

nextHide Database ViewHide Business View

Allow Presentaion ViewNextFunction SetsInclude Oracle FunctionsetFinishPublish Package Wizard ? Yes

8/13/2019 CognosLb

3/23

Shekhar


Nextclick oN 'Add' button, Click on Cognos NamespaceSelect Authors =>, NextPublish , FinishFile(menu)> Save=======================================Open Cognos URLCreate a professonal Report Run HTMLFile> SaveNote: To refelect Changes made in Database table with respect to values(Insert,update, delete)we have to set 'use local cache' property for a report level query to 'No' by Default it is 'Yes'To set, gotoView(menu)> QueriesSelect the Query, gotoproperties---------use local cache NoSave and run the report

Steps to import Tables in Oracle database from dump filecopy salesnew.dmp in d:\

connect to oracle as dba

sql> create user dwh02 identified by dwh02;

sql>grant dba to dwh02;

sql> host cmd

command prompt .. > imp

user dwh02@oracle

passowrd dwh02

path> d:\salesnew.dmp

size > enter

list content of import file only> no

ignore create error due to object existence> yes

8/13/2019 CognosLb

4/23

Shekhar


import grants> no

import table content> yes

import entire export content > yes

> exit

Steps for Solution1

Project: Proj_Sales02create connection for dwh02include custdim, proddim, sales, emp,deptimport and finishgenerate relatinship between dept and empclick on explorer, select dept+emp(ctrl key)> rghtclick> create > relationshiplink deptno to deptno, okcreate package pkg_sales02, include all, publish and save projectLaunch Cognos ConnectionCreate professional reportselect Pkg_Sales02Create a new report or template,select List, OkInsert Country, City, Fullname, Amount_SoldSelect country+city(Ctrl key), Structure(menu)> Group/ UngroupSelect Amount_Sold, Data(menu)> Aggregate>Totalselect amount_sold, STructure(menu)> sort> Sort Descending

rght click on the list> Style> Apply Table Style> Select Contemporary, okSelect City List Cell, gotoPropertiesHorizontal Alignment RightSelect Country List Cell, gotoPropertiesHorizontal Alignment RightFile> SaveName: Sol1

CONDITION IN QUERY CALCULATIONList-------Insert jobFrom the toolbox, drag query calculation inside listName: headcount1, okCount([job])Validate and ok

From the toolbox, drag query calculation inside list

8/13/2019 CognosLb

5/23

Shekhar


Name: headcount2,okCount(if([ ].[ ].[sal]>1000) then (1) else (null))Validate and okSave and run

Condition in data itemList report---------------View> queriesFrom the toolbox, drag sql beside query1Properties-----------DATASOURCE SELECT Conn_Sample26sql ... summaries> drag countdataitems tab, drag firno inside countcount([SQL].[FIRNO])VALIDATE AND OKPROPERTIESNAME:TOTAL_FIRfrom the toolbox, drag dataitem inside dataitemsf(x) tab> summaries> drag countdataitems tab, drag firno inside countCOUNT(IF([SQL].[INSDATE]=[SQL].[UPDDATE]) THEN (1) ELSE (NULL))VALIDATE AND OK

PROPERTIES-----NAME:ONLINE_FIRfrom the toolbox, drag dataitem inside dataitemsFROM DATAITEMS TAB, DRAG[TOTAL_FIR]-[ONLINE_FIR]VALIDATE AND OKPROPERTIES-----------NAME OFFLINE_FIRVIEW> REPORT PAGESDBLCLICK PAGE1

FROM DATAITEMS TAB, INSERT DISTRICT, TOTAL_FIR, ONLINE_FIR, OFFLINE_FIRSAVE AND RUN

FiltersThe Vice President of Sales has requested a report that shows sales performance byrepresentatives in each country for 1998. He would also like to see the performance forrepresentatives in Asia in order to present an award to the top seller when he visits next month.Region,country,fullname,amountsold

8/13/2019 CognosLb

6/23

8/13/2019 CognosLb

7/23

Shekhar


Valueprompt

Management has asked us to create a report that shows Quantity and Amount for each productin order to identify products. Because we produce so many products, we will add a prompt tothe report page. This way users can change the product data displayed, without having to close

and run the report each time.Value Prompt: is used to display database column values or user defined values. and allows tofilter data based on values selectedsol6a)ListInsert Emp query subjectfrom the toolbox, drag valueprompt beside list(leftside)name: Parameter1nextpackageitem select job from emp query subjectoperator innext

FinishSelect valueprompt on the page, gotoproperties------------autosubmit yessave and run

sol6b)ListInsert EMP query SubjectFrom the toolbox, drag value prompt beside listName: Parameter1, Finish

view> queriesfrom the toolbox,drag query on the workspacedblclick query2from source darg hiredate inside dataitemsdblclick hiredateEDIT..TO_CHAR([ ].[ ].[HIREDATE],'YYYY')

VALIDATE AND OK

VIEW> QUERIESDBLCLICK QUERY1FROM THE TOOLBOX, DRAG FILTER INSIDE DETAIL FILTERSTO_CHAR([ ].[ ].[HIREDATE],'YYYY')=?Parameter1?okpropertiesusage select optionalview(menu)> report pagesdblclick Page1select value prompt on the page, goto

8/13/2019 CognosLb

8/23

Shekhar


properties------------query query2use value HIREDATEauto submit Yessave and run----------------------sol6c)ListInsert emp query subjectfrom the toolbox, drag value prompt on the pagename: Parameter1nextPackage Item select Deptno from Empoperator =check make the filter optionalnextValue to Display .. select Dname from deptFinishSelect valueprompt, gotoProperties---------

Autosubmit Yessave and Run

Select & Search prompt

The Sales Department has requested a report that shows revenue for each salesrepresentative. Reps should be grouped by the country in which they work. Because there areso many reps and it is often difficult to remember sales rep names, we will add a prompt on thereport page that will let users search for specific reps, and then filter the report based on theirselections..sELECT AND sEARCH PROMPT: ALLOWS TO DISPLAY VALUES BASED ON SEARCHCRITERIA AND FILTERS DATA BASED ON SELECTIONListInsert Country, Fullname, Amount_soldGroup Country, Total> Amount_Soldfrom the toolbox, drag Select & Search promptName: Parameter1NextPackage Item .. Select Custid from Salesoperator inCheck make the filter OptionalnextValue to Display Select Fullname from customersFinish

8/13/2019 CognosLb

9/23

Shekhar


From the toolbox, Drag Prompt Button on the pageselect Prompt button, gotoProperties----------Type Select FinishSave and run

Cascade Prompts

Cascade prompts: allows to select values based on value selected in another prompt

ListInsert Catname, Subcatname,Prodname,Listprice,MinPriceSelect catname+subcatname+prodname(ctrl)> tools(menu)> Build PromptpageSelect Valueprompt1, gotoPropertiesMultiselect NOautosubmit Yes

Select Valueprompt2, gotoPropertiesMultiselect NOautosubmit YesCascade Source Select CATNAME

Select Valueprompt3, gotoPropertiesMultiselect Yesautosubmit NoCascade Source Select SUBCATNAME

save and run

Conditional Format

A manager has requested a report that compares revenue across all product for all salesRegions. She would like to be able to quickly identify revenue greater than $1,000,000 and lessthan $25,000 in order to identify high and low revenue-generating product Category. We willformat the revenue to appear in green text if it is considered high, and in red text if it isconsidered low.Conditional format---------------------is used to Change styles based on condition.variables- variable represents a value that can be changedreport studio- three types of variables1. Report language variable : variable return language name, use this to run report in differentlanguages based on user selection

8/13/2019 CognosLb

10/23

Shekhar


2. String Variable: variable returns string/ text values3. Boolean Variable - variable returns yes or no, use boolean variable when expression orcondition returns yes or no9a) string variableCrosstab

Drag region into row, catname into columns, measure into Amount_soldview(menu)> variablesdrag string variable into variablesExpressionif([Query1].[AMOUNT_SOLD]>1000000) then ('H') elseif([Query1].[AMOUNT_SOLD] report pages> dblclick page1select crosstab intersection, gotoPropertiesStyle Variable select String1, okPause mouse pointer on condition explorer, click HPropertiesforeground color Green

Pause mouse pointer on condition explorer, click LPropertiesforeground color Reddblclick on green bar, save and run

9b Boolean variableCrosstab

Drag region into row, catname into columns, measure into Amount_soldview(menu)> variablesdrag Boolean variable into variablesf(x) tab> report functions> drag column number and row number

ColumnNumber ()=RowNumber ()validate and ok

View> report pages> dblclick page1select crosstab intersection, gotoPropertiesStyle Variable select Boolean1, okPause mouse pointer on condition explorer, click Yes

Propertiesforeground color Yellowdblclick on green barsave and run9C)ListInsert Emp query subject inside ListFrom the toolbox, drag Query calculation inside ListNAME: chk, ok

8/13/2019 CognosLb

11/23

8/13/2019 CognosLb

12/23

Shekhar


Conditional layout: use conditional layout to change page layout based on user selection

Task: display ename,job,sal,dno when report is run in HTML format and displayename,job,hiredate, mgr,sal when report is run in PDF formatReport Function : ReportOutput() Return report format like HTML,PDF, CSV etc-----------Blank-View> variablesdrag string variable inside variablesfrom f(x)> report functions> drag ReportOutputvalidate and ok

Add two valuesHTML and PDF

File>conditional layout prompt pagesFrom the toolbox, drag page inside prompt pagesFrom the toolbox, drag value prompt inside prompt pageName: paremeter1, finishSelect valueprompt, gotoProperties------------Static choices ..

AddUse displayL sales by regionC sales by category

X category vs region sales

OkView> variablesDrag string variable inside variableFrom the parameter tab, drag parameter1 and editParamvalue('parameter1'), ok

Add three values l, c and x

8/13/2019 CognosLb

13/23

Shekhar


View> report pages > dblclcik page1From the toolobox ,drag conditional block on the pageSelect conditional block, gotoProperties--------Block variable string1Current block select lFrom the toolbox, drag list inside conditional blockInsert region and amount_sold

PropertiesCurrent block select cFrom the toolbox, drag chart inside conditional blockInsert catname and amount_sold

PropertiesCurrent block select xFrom the toolbox, drag crosstab inside conditional blockInsert region,catname and amount_soldSave and run

Render Variable-------------use render variable to render/show objects based on user selection

--------------blank reportfrom the toolbox, drag list on the pageinsertemp query subjectfrom the toolbox, drag query calculation inside listname: C1, okrunning-count([ENAME]), Validate and okview> report pagesFrom the toolbox, drag page inside report pagesfrom the toolbox, drag list on the pageinsertemp query subjectfrom the toolbox, drag query calculation inside listname: C2, okrunning-count([ENAME]), Validate and okview> Prompt pages

from the toolbox, drag page inside prompt pagesfrom the toolbox, drag value prompt on the pagename:Parameter1, nextpackage item .. select ENAMEoperator innext, Finishview> variablesdrag boolean variable inside variables

8/13/2019 CognosLb

14/23

Shekhar


[QUERY1].[C1]=1OKdrag boolean variable inside variables[QUERY2].[C2]=1OKView(menu)> Report pages> dblclick Page1select pagebody, gotoproperties ^ select page ancestorRender Variable select Boolean1Query query1

View(menu)> Report pages> dblclick Page2select pagebody, gotoproperties ^ select page ancestorRender Variable select Boolean2Query query2View(menu)> queriesDblclick on Query1, Drag deptno inside detial filter[DEPTNO]=10View(menu)> queriesDblclick on Query1, Drag deptno inside detial filter[DEPTNO]10VIEW> REPORT PAGES> DBLCLICK PAGE1save and run pdf=====================tASK2:ListInsert EName,job,Hiredate,Mgr,Sal

View> Prompt pages

Drag page inside prompt pages, Dblclick Promptpage1Drag value prompt on the padName: Parameter1, FinishSelect Prompt, gotoPropertiesStatic choices ...USe Displayc1 ENamec2 JobokRequired NoMultiselect Yes

Select UI Radio Button groupView> variables > drag boolean variableParamValue('Parameter1') contains ('c1')okdrag boolean variableParamValue('Parameter1') contains ('c2')okView> report pages > dblclick page1select ename column, goto properties

8/13/2019 CognosLb

15/23

Shekhar


select List column ancestorRender variable Select Boolean1, ok

select job column, goto propertiesselect List column ancestorRender variable Select Boolean2, ok

save and run PDF

Table of Contentsu se Table of Contents to prepare indexBlank ReportFrom the toolbox, drag Table of Contents on the pageView(menu)> report pagesdrag page inisde report pagesdblclick page2from the toolbox, drag list on the pageinsert Country, Prodname, Amount_soldGroup Country, Total> Amount_Sold

Select country, structure> set pagebreakstructure(menu)

Lock Pageobjects < click to unlockFrom the toolbox, drag Table of contents entry beside country column body

structure(menu)Lock Pageobjects

save and run pdf

Pagebreak--------------use pagebreak to display each value of query item on new page

listInsert Region, Catname, Amount_sold

Group Region, Total- Amount_soldSelect Region, Structure- Select Set pagebreak

save and runNote: To remove pagebreakview(menu)> report pagesdrag page1 outside Region pageset

-

8/13/2019 CognosLb

16/23

Shekhar


Layout Component Reference:

use layout component refernce to reuse individual object or set of objects in the same report oranother reportNote: Objects must be named to reusePropertiesoverrides to edit componentsembed to set as reference (default) or copy

Blank ReportFrom the toolbox, Drag Block on the pageselect Block, GotoPropertiesName; Block1From the toolbox, Drag Image inside blockSelect image, gotoproperties-----------url browse imagename Image1

From the toolbox, drag textitem inside blockSample text Goes here, okSelect text, gotoproperties----------name: text1From source tab, Drag Emp query Subject On the report pageFile> SaveName:LCR

File> New> blankFrom the toolbox, Drag Layout Component Reference on the pageSelect Block1, okFrom source tab, Drag dept Query subject on the pageSave and Run

Master Detail reports

Master detail reports are used to merge summary and detail query. Containers must be nestedto create master detail reportsThere are two ways to create master detail reports1. create master query for parent frame and detail query for nested frame10a)ListInsert Dept Query subjectfrom the toolbox, drag list inside above listInsert emp query subject inside listselect nested list column body(click on second list), data(menu)> Master detail Relationshipclick on 'new Link' button, okSave and run

8/13/2019 CognosLb

17/23

8/13/2019 CognosLb

18/23

Shekhar


Master detail relationship between two pagesBlank PageFrom source tab, drag dept query subject on the page

view> report pagesfrom the toolbox, drag page inside report pagesdblclick page2From source tab, drag emp query subject on the pageview> report pagesfrom the toolbox, drag pageset inside report pagesdrag page1 inside detail pages of pageset1from the toolbox, drag pageset inside above pageset(nest)drag page2 inside detail pages of pageset1select pageset1, goto

properties------------

query query1grouping and sorting drag deptno inside group folderselect pageset2, gototproperties--------query query2master detail relationshipclick on newlinkselect depnto of query1 and deptno of query2

Drill through reports

The Vice President of Sales has requested to see all of the sales representatives who havegenerated more than $1,000,000 in revenue in 2006 to determine who will receive a bonus thisyear. He would also like to be able to access contact information for each sales representative,including their phone number, fax number, and email address so that he can notify them of theirbonus. We will create a report containing his contact information and link it to the SalesRepresentative Performance report for easy access.

Package Pkg_SaleshistoryListInsert Custid, Cust_first_name+cust_last_name as Fullname, Cust_StreetAddress,Cust_Main_PhonenUmber, Cust_Email

Data(menu)> filtersadd detail filter[ ].[ ].[CUSTID]=?xyz?okFile> SaveName: Contact_DetFile> NewListInsert Custid, Cust_first_name+cust_last_name as Fullname, Amount_sold

8/13/2019 CognosLb

19/23

Shekhar


Select Amount_Sold, Sort> DescendingData> filters[ ].[ ].[CALENDAR_YEAR]=1999 AND [ ].[ ].[AMOUNT_SOLD] >100000SELECT AFTER AUTO AGGREGATION, OK,OKrght click on fullname column body> Drill Through Definitionsadd drill throughTarget Report tabreport ... Select Contact_Det

Action Run the reportFormat HTMLcheck open in New windowclick on edit- parametersNAME TYPE REQUIRED METHOD VALUEID NUMBER YES Pass Dataitem Value CustidokokSave and run

DRILL THROUGH BOOKMARKS------------------------

Allows To Navigate From One Position To Another In The Same Report

11b)BookmarkBlank Reportfrom the toolbox, drag bookmark on the pageselect bookmark, gotoproperties------------source type textlabel Main

from the toolbox, drag list on the pageinsert Countryview> report pagesfrom the toolbox, drag page inside report pagesdblclick page2Drag list, insert country, prodname, amount_soldgroup country, Total Amount_soldselect country, structure>set pagebreakstructure> lock page objects < click to unlockfrom the toolbox, bookmark beside country column body(leftside)select bookmark, gotoproperties------------source type select data item valuestructure>lock page objects headers and footers>page header and footercheck page footer

8/13/2019 CognosLb

20/23

Shekhar


from the toolbox, drag text item inside page footer, goto Main Page, okRght click on the above text, drill through definitionsbookmark tabsource type Select Texttext MainokView> report pages > dblclick page1rght click on country column body> drill through definitionsbookmark tab

type select data item valuesdataitem values select Countryok,ok

Report Bursting---------------Report Bursting is a process in which report is run once and result is distributed to user bybreaking content based on group columnReport bursting is used when set of users require same report format with specific content

Blankview> Queriesdrag query on the workspacedblclick query1, insert Customerid, fullname,catname,amountsold, city(email column)view> report pages> dblclick page1Drag List on the workspace- change query for list to Query1from dataitems tab, Insert Fullname, Catname,amount_soldGroup Fullname, Total > Amount_soldfile(menu)> burst optionscheck make report available for burstingquery query1 query query1

label fullname dataitem citygroupedit - drag customerid type email addressinside group folderokfile> SaveNAme: Burst_Rep----------Cognos connectionPkg_Sales > Burst_rep > run with options, select save the report, click on advanced options>check burst the reportruncheck view the detailsokrefershreport output -- click on yes

8/13/2019 CognosLb

21/23

8/13/2019 CognosLb

22/23

Shekhar


8/13/2019 CognosLb

23/23

Shekhar

CognosLb

Documents

Transcript of CognosLb