Related Links in drill down/drill up .or drill through definations

Hi Guys ,

I am trying to make you aware of one feature of cognos which i noticed recently but was there from begining in case you have not noticed it .It a fun to try.

First thing to notice is drill through defination on home page .It allows you to create drill defination which will be available only when certain item from model is available in report .Like drill through defination whose scope is product type .

It allows you to make use of dynamic filtering for drill through defination .When you click on any intersection in crosstab .The values from intersection will be used to dynamically filter the drilled report .

Scope in Framework

Hi Guys,

Scope allows you to set till what level a measure is avaiable for a dimension

Shortcut and Alias shortcut explained

Hi Guys

Below is a explaination on alias shortcut and shortcut

I  have created shortcut (reference ) to employee history created join between employee history and shortcut and published the model

Now what is interesting in this .

Notice the behavior of the generated sql and you will neve has issue of differentiating between Shortcut and alias .

See its picking values from only one table Position Department .as if the shortcut is only for our understanding

select "POSITION_DEPARTMENT"."POSITION_CODE" "POSITION_CODE", "POSITION_DEPARTMENT"."DEPARTMENT_CODE" "DEPARTMENT_CODE"
 from "GOSALESHR"."POSITION_DEPARTMENT" "POSITION_DEPARTMENT"

Now Alias Shortcut 

Look at the sql generated 

select "Alias_to_POSITION_DEPARTMENT"."POSITION_CODE" "POSITION_CODE", "POSITION_DEPARTMENT"."DEPARTMENT_CODE" "DEPARTMENT_CODE"
 from "GOSALESHR"."POSITION_DEPARTMENT" "Alias_to_POSITION_DEPARTMENT", "GOSALESHR"."POSITION_DEPARTMENT" "POSITION_DEPARTMENT", "GOSALESHR"."EMPLOYEE_HISTORY" "EMPLOYEE_HISTORY"
 where "Alias_to_POSITION_DEPARTMENT"."POSITION_CODE"="EMPLOYEE_HISTORY"."POSITION_CODE" and "EMPLOYEE_HISTORY"."POSITION_CODE"="POSITION_DEPARTMENT"."POSITION_CODE"


Comment 

It treats the alias shortcut as a separate new table .Its same as creating alias in oracle statement

Similar to below oracle statement 

select * from emp a , dept b
where a .dept_id =b.dept_id

here a , b are alias to emp and dept table respectively .

Difference between Model query subject and Alias 

The key difference between model objects and shortcuts is that model objects give you the freedom to include or exclude items and to rename them. You may choose to use model objects instead of shortcuts if you need to limit the query items included or to change the names of items.


IBM Link stating difference

Model Query subjects 

If you create a new model query subject on Position department .and take postion code from model query subject and department code from original Position department you will see that cognos can recognise its the same table and below is its query .It works like shortcut(reference) 

select "POSITION_DEPARTMENT"."POSITION_CODE" "POSITION_CODE", "POSITION_DEPARTMENT"."DEPARTMENT_CODE" "DEPARTMENT_CODE" from "GOSALESHR"."POSITION_DEPARTMENT" "POSITION_DEPARTMENT"

However as soon as you create relationship to this model query subject it behave as Alias shortcut 

select "New_Query_Subject"."POSITION_CODE" "POSITION_CODE", "POSITION_DEPARTMENT"."DEPARTMENT_CODE" "DEPARTMENT_CODE" from "GOSALESHR"."POSITION_DEPARTMENT" "New_Query_Subject", "GOSALESHR"."POSITION_DEPARTMENT" "POSITION_DEPARTMENT", "GOSALESHR"."EMPLOYEE_HISTORY" "EMPLOYEE_HISTORY" where "New_Query_Subject"."POSITION_CODE"="EMPLOYEE_HISTORY"."POSITION_CODE" and "POSITION_DEPARTMENT"."POSITION_CODE"="EMPLOYEE_HISTORY"."POSITION_CODE"


Very Important note -

Alias shortcut as name suggest is a shortcut any changes that you make to original will be reflected like change of column name will change column name in alias shortcut not the case with model query subject

2) When you create a relationship to model query subject it will ask whether you want to replicate all the original relationships .Like suppose you create model query subject of product it will ask if you want all product relationship to be assigned to model query subject .So if you say yes the model query subject will behave just as original product with your new relationship . This is not the case with alias shortcut


I hope this is one of the best explanation available out there .

Exporting Cognos package

Hi Guys,

Below is how to move reports from one server to another ,

http://www-01.ibm.com/support/docview.wss?uid=swg21382301


A) Moving the reports manually

Open the report in Server1's Report Studio
Select Tools > Copy Report specs to clipboard
Open notepad and paste the output into the workspace
Save the file
Launch Server2's Report Studio
Select Tools > Open Report specs from clipboard

Be careful here 

Prompts in Cognos

Hi Guys,

Below are some prompts in cognos .Nothing complex .I am pasting it as these are the prompts that we dont use much often .So just as a reference

List of prompts available in Cognos

Interval Prompt

Range option selected from properties

Text Box prompt

Date prompt 

Use parameter info

If you set this property to YES. then the queries with this property set to YES ,. are first executed then the queries with no parameter in them are prepared later .this will help you if its takes long time for the first prompt page to load which may be because the report has a heavy query in it.Know understand how this helps 

IBM Extract 

When you set this property what you are simply doing it suggesting to Rsvp that it only include those queries you have tagged for the first part of the conversation. Doing this can speed up the time to first prompt page cycle as you are deferring the prepation of queries unrelated to prompting until the later phase

Using Singleton,Crosstab space

Hi Guys,

Below is a simple use of singleton in congos .It allows  you to do summary calculation and its the only thing that you can add in a list crosstab that is coming from separate query .

Notice that even after filtering the overall calculation remains unaffected by filter .This has been done making use of singleton .

Steps
1) Create a summary total
2) Unlock the cell
3) delete total
4)Insert singleton
5)In the singleton query define total( Quantity) and put it there

Crosstab space 

Crosstab space with fact cells 

Automatic summary and Total difference

Hi Guys ,


Below is a interesting note between using Total and using Automatic summary for creating summary row.When you say aggregate it takes care of % and resolves percentages correctly .Check out below example

Notice that Total is giving me wrong values .But Summary is giving correct .Simple way to make total to give correct value is clicking on Percentage column and setting sort order as 2 .This solves it however if you use automatic summary the issue does not come at all .So next time you try to summarise go for Automatic summary