Using CAMID for Bursting

To burst reports to Cognos Directories that need to be visible to all users with access to the respective folder / report, you would need to burst to the Everyone group.

Retrieve the CAMID of everyone group and create a data item with the CAMID included as an expression. Set this as the recipient data item.

'CAMID("::Everyone")'

Likewise you can burst to various groups of users by retrieving their CAMIDs and setting them up as recipients in your database recipient table.
 

Master Query Cannot Be a Nested Query - Error Message

When you try bursting a report that has multiple pages with page breaks set on different queries, the report could throw errors "Master Query Cannot Be a Nested Query" unless you have master detail relationships set up between the Burst query and the Page Set Queries.


Master Detail relationships can be set up using the "Master Detail Relationships" property of the Page Set query.
 

Single Select Tree Prompt on a Relational Model

You can base Tree prompts not just on Dimensional Model but on relational Model as well. This involves using macros and decoding MUNs. This has been covered in article on IBM's support site but I am covering the same here for the benefit of those who use this blog as a one-stop-site for all Cognos needs.

The below solution works for single select Tree Prompt. I am working on multi-select Tree Prompts and would update this article once I have the solution.

Requirement: Create a Date Dimension Tree Prompt based on a Relational Date Dimension Table and filter the report to retrieve data for the selected dates.

Solution:

Step 1: In the FM Model, create a stand alone Date Regular Dimension based on the existing Date Dimension query subject in the Presentation Layer View.

This Dimension would be used for creating the Tree Prompt.

Step 2: In the report create a tree prompt based on Date Dimension.

Step 3: Create a filter as below:

[Date] = #csv(substitute(']','',grep(']',split('].', grep('all',split('->',prompt('dte','memberuniquename','[Presentation Layer].[Date Dimension].[YEAR].[DATE]->[all].[2008].[Q1 2008].[Jan 2008].
[Wk 1].[2008-01-01]')))))))#

The above filter works as explained:

• First the MUN for the selected Tree Prompt value is retrieved.
  In the above Example: [Presentation Layer].[Date Dimension].[YEAR].[DATE]->[all].[2008].[Q1 2008].[Jan 2008].[Wk 1].[2008-01-01]



• Then we split the retrieved MUN into 2 parts based on the symbol "->".
  In the above Example: The MUN is split into [Presentation Layer].[Date Dimension].[YEAR].[DATE], [all].[2008].[Q1 2008].[Jan 2008].[Wk 1].[2008-01-01]



• We then select the part containing "all".
  In the above Example: [all].[2008].[Q1 2008].[Jan 2008].[Wk 1].[2008-01-01] is selected.



• Then split the part again into multiple parts based on the symbol "].[".
  In the above Example: [all].[2008].[Q1 2008].[Jan 2008].[Wk 1].[2008-01-01] is split into [all, 2008, Q1 2008, Jan 2008, Wk 1, 2008-01-01]



• Retrieve the part containing "]".
  In the above Example: 2008-01-01] is selected.



• Replace "]" with "".
  In the above Example: 2008-01-01] is converted to 2008-01-01.

Thus [Date] is compared with 2008-01-01 and the filter works as expected.
 

User Query - Display Totals for Top 2 Ranks while displaying data for all other ranks in a crosstab?

This is in response to a user query.

Requirement: Display Ranks across Columns in a Crosstab and the Total column needs to include only the Top 2 ranks along with Overall Total column for all Ranks.

Solution:

Step 1: Create the base query Query 1 with the following data items: Product, Year, Sales, Rnk, Top 2 Sales Total.

Rnk - Rank([Sales] for [Product])
Top 3 Sales Total - case when [Rnk] < 3 then [Sales] end

Step 2: Create the outer query Query 2 by referencing the base query Query 1. Drag the data items - Product, Year, Sales, Rnk, Top 2 Sales Total from the referenced query.

Step 3: Set the aggregate function of Rnk to None.

Step 4: Create the crosstab by dragging in the data items Product, Sales, Top 2 Sales Total from Query 2.

Step 5: Create the Overall Total column by clicking on the Summary button for Rank column.




 

User Query - Creating Overall Percentage Summary Values in Reports

This requirement is in response to a user query:

Requirement: Display Dates, Sales, Percentage Sales along with summary total.

Solution:

Step 1: Create a crosstab report with Year, Month, Sales data items.

Step 2: Create a data item Total Sales - total(...[Sales] for report).

Step 3: Set the Aggregate and Rollup Aggregate functions to calculated.

Step 4: Create a data item Percentage - [Sales] / [Total Sales] and set aggregate and rollup to calculated. Drag this query item into the crosstab.



Here are a couple of other ways to format the above information:





 

Cognos 8.4 Query Studio Bug - Including Model and Query Filter

Recently I ran into an issue with Cognos 8.4 Query Studio. When I try to include both a model filter and a query studio filter the query studio report just hangs forever and then it throws an "object not found" error on the Model filter even though the object exists in the model.

This has been identified as a bug in Cognos 8.4 and seems to have been corrected in version 8.4.1.

So here's one more reason to move to 8.4.1.
 

Tree Prompts Explained

A Tree Prompt provides the user with ability to navigate levels in a hierarchy easily and select members across Levels or even select multiple levels within a hierarchy.

I have covered below a few points on Tree Prompts

• To set up a tree prompt filter, in your report embed a prompt function inside a data item
  
  Example: To display only a selected Product Hierarchy members / levels and associated revenue:
  
  [Sales and Marketing].[Products].[Products]->?Products?







• To create a multiselect tree prompt, change the expression to -
  
  set([Sales and Marketing].[Products].[Products]->?Products?)







• To display members of levels starting from a particular level -
  
  Example: Display a Tree Prompt that allows users to select members or levels starting from Product Type.
  
  In the query associated with your tree prompt, drag the level from which you wish to be able to display members and descendants and set this as the use value for your tree prompt.
  
  
  
  



• Default values can only be hard-coded selections.


• To use a Tree Prompt in a Macro -
  
  set(#promptmany('Products','memberuniquename','[Sales and Marketing].[Products].[Products].[Product type]->:[PC].[@MEMBER].[951]')#)



• Tree prompts are based on Dimensional model, but you can still create Tree Prompts using a Relational Model which I shall explain in a later article.