Creting cognos Interactive Reports

The below link is highly useful for starting up with cognos SDK
http://www.fabica.com/blog/2010/01/creating-interactive-reports-info-about-cognos-url/

Master - Detail Relationship

A master-detail query is an outer join. It is useful when you want to include ALL records in the master query regardless of whether there are matching records in the detail query or not.

1. On a blank report page, drag in a new list item (Master). Populate it as needed.

2. Drag in a new list item (Detail) and drop it into one of the columns of the Master list. Populate this list as needed. One of these fields (ID) must be related to an associated field in the Master list to link the two queries. This step must be done correctly to allow Detail to be an available selection in Step 5 below.

3. On the Menu Bar, select Data > Master/Detail Relationships

4. Select Master in the top listbox.

5. Add Detail as a filtered query and set Master.ID=Detail.ID. If Detail is not shown as an available detail query selection, then double-check that you performed Step 1 correctly.

6. Run your report.

Some further notes: You can add multiple detail queries to a single master query. You can enhance the appearance of the report by hiding the detail query list headers as desired.

Cascading Prompts

Cascading Prompts

Cascading Prompts allows a user to use values selected from one prompt to filter values in another prompt.

For example, a report contains the columns Campus and School. Create prompts for these columns, and then specify that the School prompt is a cascading prompt that uses Campus as the cascading source. When users select a Campus, they see only the Schools related to the selected Campus.

Cognos Chart axis clustered

For example if we have y axis as percentage measure
and Y axis shows of summation of percentages that 100,200,300
so to override this situation select series properties and
select group type as clustered

Script For Limiting the Multi-Select Prompt

This script is used to limit the selection in the multiselect prompt.Just place the code in html item

< script>

function CheckNum() {

// initialize the counter
var Counter = 0;
var i;

// get the handle for the 1st checkbox prompt - add div around them to distinguish
var prompt1 = document.getElementById('PayerSelect');

// find all the children of the div of type checkboxes.
for (i=0; i < prompt1.childNodes.length; i++)
{

var node_list = prompt1.getElementsByTagName('select');
for (var i = 0; i < node_list[0].length; i++)
{
var node = node_list[0];
if (node.options(i).selected == true)
{
Counter++;
}
}
}

// If the counter is greater than 15, display an alert message.
if (Counter > 15){
alert("Please select less than 15 Payers.");
return false;
} else {
// Just display a message, we don't really want the form to submit
// for testing. In the case of production, the alert condition
// would be removed and the promptButtonFinish() call uncommented.
//alert("Form passed validation!");
promptButtonFinish();
}
}
< /script>
< b>
< input type="button" style="width:65px;height=25px;border:1px solid black;font-size=9pt; font-family:ARIAL;font-weight:bold; background-color=#3399CC" name="Finish" value="RUN" class="Prompt Button" onClick="CheckNum();">
< /b>


------------------------------------
< div id="PayerSelect">

-----------------------------------------
< /div>

Displaying Report Author Name in Report Studio

To display user name in report studio
we need a write a macro in the data item

#sq($account.personalInfo.userName)#
#sq($account.personalInfo.givenName)#
#sq($account.personalInfo.surname)#
#sq($account.defaultName)#

Important Date and Time functions for reporting

Lag - In Oracle/PLSQL, the lag function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from a previous row in the table. To return a value from the next row, try using the lead function.


The syntax for the lag function is:
lag ( expression [, offset [, default] ] )
over ( [ query_partition_clause ] order_by_clause )
expression is an expression that can contain other built-in functions, but can not contain any analytic functions.offset is optional. It is the physical offset from the current row in the table. If this parameter is omitted, the default is 1.default is optional. It is the value that is returned if the offset goes out of the bounds of the table. If this parameter is omitted, the default is null.

Simple example using LAG function can be as below.

lag(closingperiod([Year],MEMBER),1)
In the above function

closingperiod([Year],MEMBER) provides us with the latest year, for example 2009.

lag(closingperiod([Year],MEMBER),1) provides us with the year prior to current year, for example it will be 2008.

Closingperiod() - The ClosingPeriod() function, according to the Analysis Services Books Online, "returns the last sibling among the descendants of a member at a specified level." In other words, it returns the last member that shares the current member's parent at the specified level.

ClosingPeriod([<>[, <>]])