Display the top 10 records using the Rank function in Cognos

Use the Rank function and then filter the top 10

Steps:Use the function Rank to obtain the ranking for each data item.

The rank column has this calculation: Rank([Revenue] for [Product line])

And then add a filter to the report: Rank <= 10

In the filter definition, set the application to After auto aggregation.

You will get the top 10 revenue generated by product line based on the sales Rep.

Conditional formatting in Report Studio

Highlight data in a report to better identify exceptional results. For example, to identify sales representatives who have exceeded their quota, create a condition that checks if each representative's sales for the year is greater than their quota for the year. 

Steps:

1.Open the report that you want.

2.From the File menu, click Variables.

3.Create a variable by clicking the add button bellow the Variablesbox.

4.In the Name box, type a name for the variable.

5.In the Type box, select the type of variable you want to create:

To create a variable that has only two possible values, yes and no, click Boolean.

To create a variable whose values are string-based, click String.

6.Click OK.

7.Click the Edit button below the Expression box.

8.In the Expression Definition box, define the condition.

9.Click OK twice.

10.In the work area, click the column that you want to highlight based on the condition you just created.

11.Click the Condition Explorer button .

The Condition Explorer dialog box appears.

12.Click the variable you just created.

13.In the Properties pane, click the Conditional Style property.

14.In the Variable dialog box, click the Variable box.

15.Click the variable you want to assign to the object.

16.In the Values box, select the values you want the condition to support.

Tip: A default value exists for each variable you define, and it is always selected.

17.Open the Condition Explorer dialog box again, and click one of the possible values for the variable other than the default value.

For example, if you created a boolean variable, click the Yes value.

18.In the Properties pane, specify the formatting that you want to highlight the column with when the condition is satisfied.

For example, click the Border property to create a thicker border around the column.

19.Repeat steps 17 to 18 for other possible values defined for the variable.

20.Repeat steps 10 to 19 for other objects that you want to highlight.

Conditionally display a column in Report Studio based on User ID

1. With the report open in Report Studio, click on Query Explorer and select the query to be conditionally displayed.
2. Ensure the Toolbox tab is selected on the Insertable Objects pane, then drag and drop a new Data Item (called user) under the Facts column
3. Set the expression of the new data item to: #sq($account.defaultName)#
4. Click on the Conditional Explorer and select Variables
5. Add a new Boolean variable (called user_name) with the expression: [User]='user_name'
6. From Page Explorer, select the appropriate report page
7. Highlight the data item in the column to be conditionally displayed
8. From the Properties pane to the bottom right corner of Report Studio, locate General > Conditional Style
9. Click on the Ellipses and select the conditional variable (user_name).
10. From the Conditional Explorer, go to the Variable > user_name > Yes. [If a user logs in as user_name, the object being conditionally displayed is not visible].
11. From the Properties pane, locate Box > Box Type then click on the down arrow and select None].
12. Repeat step 7-11 for the column label.
13. Double click on the conditional Explorer to unselect it.
14. Save the report, log out and log on as user_name to test. When the column is hidden, it will not appear in HTML, Excel or PDF formats.

Create a report that will display one of three possible reports based on a prompt selection

1. create new report in Report Studio

2. add the objects to be displayed on the report, ie. lists, crosstabs, text items, etc. (you can add 3 list objects using 3 different queries for example or 3 crosstabs which uses only 1 query)

3. create a prompt page and add a prompt object, ie. value prompt (this can be a new query or just using static choices)

4. in the properties pane of the prompt object, add a parameter to associate with the prompt (eg. parameter=displayparam)

5. go to query explorer and add a data item object to your report query (if report has 3 list objects with 3 queries, the data item only need to be added to one of the queries)

6. set the expression for the data item object to refer to your parameter which was associated with the prompt (eg. expression definition for the data item = ?displayparam?)

7. go back to the report page

8. add a conditional block to the report

9. click on conditional block property and a small window will pop up

10. click on the "Variables" button

11. create a new variable (eg. variable=displayvar)

12. set the expression to read the parameter value passed from the prompt (eg. ParamDisplayValue("displayparam") ) and click OK

13. create values to use for the different report types (eg. report1, report2, report3) which should match the values passed by the prompt

14. on the properties pane of the conditional block, select the current block property (eg. report1)

15. drag objects that are to be displayed when user selects a report from the prompt page (eg. report1) into the conditional block, ie. drag the list object to be displayed when user selects report1 in the prompt page into the conditional block

16. change the current block (eg. report2) and repeat step 15

17. set the objects for the rest of the possible values for the variable in the conditional block

18. run the report

How to display a prompt conditionally based on a selection. made in another prompt on the same page in Cognos BI

Use a Conditional Block List to contain the prompt that you wish to display conditionally. In addition, place a re-prompt Prompt Button on the prompt page. Steps: These steps assume that you have already created your report and have defined your parameter filters. The filter for the optional prompt must have its Usage property set to Optional. To build the prompt page:

1. Add a prompt page to your report
2. In the File menu, click Variables.
3. Create a variable of type Boolean.
4. Add the following expression: ParamDisplayValue("Display_Option")= "Show"
5. Add a table to the prompt page with the appropriate number of columns and rows for your needs.
6. Add a prompt to one of the table cells.
7. Select the parameter that this prompt resolves itself to.
8. Add another prompt to the page, and set this prompt to resolve itself to the boolean variable created in step 3.
9. Use the Static Choices property of the prompt to create values for it to display: Example: Use Display Show Show Hide Hide
10. Add a Prompt Button to a cell on the table.
11. Change the Type property of the Prompt Button to Re-prompt.
12. Add a Conditional Block List to the one of the table cells
13. Select the Conditional Block on the page.
14. Set the Conditional Block property to the variable created in step 3.
15. Set the Current Block property to Yes.
16. Add the prompt that you wish to display conditionally to the Conditional Block object.
17. Set the Current Block property to No. The Conditional Block appears empty.
18. For the hidden prompt to display, select Show and click the Re-prompt button.

Using Auto-Submit attempts to submit the report. Ensure the auto submit properties of the prompts are set to No.

Content deployment in Cognos Environments

Keywords: Deploy, Deployment, Export, Import, Content Store

     Outside of a Proof of Concept, any environment where an application like Cognos is installed, it is wise to have at least two instances, one for development and one for production. In these cases you will have to deploy reports and other content from development to production and vice-versa.  There are multiple ways this can be done depending on your needs.

Deployment through the Cognos Portal: 
     This is the recommended and preferred method and works for all requirements. You will be using the deployment wizard to do this:

• Login to your production environment and launch "IBM Cognos Administration" from Cognos Portal.
• Click on "Configuration" tab and "Content Administration" in the left pane.
• From the toolbar item, click "New Export"

• Enter a name of the export and a description (optional) and click Next

•  Next, select the desired deployment method: You can choose to export the entire content store or only selected folders or packages.

• If you pick the entire content store, you can optionally include user account information to have the same security settings as in the production environment
• In the next step, entire a name for the archive. This will be the file name when the export completes.
• If you are exporting the entire content store, cognos requires you to encrypt the archive using a password. Enter a password:

•  After this step, review the settings, save and run the export.
• Check the activities in IBM Cognos Administration to make sure export runs and completes successfully.
• Login to the production cognos server and navigate to: <cognos install directory>\deployment folder.
• Find the zip file with the name you entered in the Export wizard.
• Copy this file to the development environment.
• Now login to the Development environment and navigate to Content Administration.
• Create a "New Import" from the toolbar icon
• Select the archive that you just copied over and click next.
• Select the desired settings and click Finish.
• Now your content from production environment will be imported.
• Go to "Current Activities" in the "Status" tab of "IBM Cognos Administration" and make sure the import completes successfully.