Login page in jsp

Add data level security using CSVIdentityMap macro

Macros are used in Framework Manager as well as Report Studio. The Cognos engine understands the presence of a macro as it is written within a pair of hashes (#). It executes the macros first and puts the result back into report specification like a literal string replacement. We can use this to alter data items, filters, and slicers at run time.

Illustration:

A report shows the employee names by region and country. We need to implement data security in this report such that a user can see the records only for the country he belongs to. There are already User Groups defined on the Cognos server (in the directory) and users are made members of appropriate groups.
For this sample, I have added my user account to a user group called 'Spain'.

1. Drag the appropriate columns (Region, Country, and Employee name) on to the report from Employee by Region query subject.

2. Go to Query Explorer and drag a new detail filter.
3. Define the filter as:

[Country] in (#CSVIdentityNameList(',')#)

How it works:
Here we are using a macro function called CSVIdentityNameList. This function returns a list of groups and roles that the user belongs to, along with the user's account name. Hence, when I run the report, one of the values returned will be 'Spain' and I will see data for Spain.
The function accepts a string parameter which is used as a separator in the result. Here we are passing a comma (,) as the separator.
If a user belongs to multiple country groups, he will see data for all the countries listed in the result of a macro.
Limitations:

This solution, conspicuously, has its limitations. None of the user accounts or roles should be same as a country name, because that will wrongly show data for a country the user doesnot belong to. For example, for a user called 'Paris', it will show data for the 'Paris' region. So, there need to be certain restrictions. However, you can build upon the knowledge of this macro function and use it in many practical business scenarios.

Cognos - Column title for crosstab rows

For the example show, let us bring column titile for the crosstab node 'Product Line' and 'Product'

Click the crosstab corner 'Quantity' and then make box type to none in properties pane.

Now pull a 'crosstab space' and drop along the product line and crosstab columns as shown (marked in red)

Cognos - Difference between 'Aggregate Function' and 'Rollup Aggregate Function'

The 'Aggregate Function' is used to rollup the values when "Auto Group & Summarize" is set to yes for the query, it applies some logic depending on the datatype. The 'Rollup Aggregate Function' is used for summary values like totals in the report

For example:

Fred       $5
Fred       $15
Barney   $20
Barney   $30

Put both columns on your report,
Aggregate function tells Cognos what to do with them with each other, ie.if you specify Total, this will show...

'Aggragate function'-Total
Fred     $20
Barney $50

'Aggragate fuction'-Average
Fred     $10
Barney $25

'Rollup Aggragate Function' tells Cognos what to do when you want a "Rollup" at the end of the Report or a Grouping (usually a Total or Subtotal, but could be any of the functions.)
So, if Aggregate is set to Total and Rollup is set to Total you get this...
Fred        $20
Barney    $50
Summary $70

If Aggregate is set to Total and Rollup is set to Average you get this...
Fred $20
Barney $50
Smmary $35

Cognos - Ungroup data in crosstab

Create a new data item(product&color) as Product||Color

Now place this data item in place of product. Then unlock report studio using lock/unclock button in top tool bar.

Then select the text item of this data item/node(please make sure only text item is selected not the complete node). And in the properties pane-> change the data source property to Product data item instead of product&color.

The output will be as:

A  WHITE 10
A  RED      3
B  BLUE    8