Uncategorized

Expand and Collapse in Cognos

| cognospaul

One of the most requested features in Cognos is expand/collapse feature. At the moment you can expand members in Workspace, but my users find the tool slow and generally difficult to use. There is a JS solution, but it only works in the rows and requires all of the data preloaded. If your data contains thousands of members, this can make the report run slow.

My solution works by passing the member unique name of the current row to a hidden prompt, appending that value to a set, and reordering the set to the natural order. Collapsing the row is a simple matter of removing the member from the prompt. As an added feature, this means you keep the third level visible while hiding the second level.

Expand Collapse

We’ll begin with the query. Each crosstab node refers to three data items. The set, the member unique name, and the count of children.

The set looks like:

#promptmany(
  'ProductsSet'
  ,'mun'
  ,'[sales_and_marketing].[Products].[Products].[Product line]'
  ,'hierarchize(union([sales_and_marketing].[Products].[Products].[Product line],descendants(set('
  ,'[sales_and_marketing].[Products].[Products]'
,'),1)))'
)#

If nothing is selected, it defaults to [sales_and_marketing].[Products].[Products].[Product line]. That can be any valid set expression. If a value is selected, it unions the descendants to the set, and reorders it to the natural order.

The count is:

count(1 within set children(currentMember([sales_and_marketing].[Products].[Products])))

This is to control when the expand button appears. It obviously doesn’t make sense to show the button if there are no children.

The member unique name is simply:

roleValue('_memberUniqueName',[Product line])

The count and mun are added to the properties of the node, so we can refer to them in an HTML expression.

case when [Query1].[PLChildren] = 0 then '' else 

case when ParamDisplayValue('ProductsSet') contains ([Query1].[PLMun])
then '<input type="button" onclick="
paulScripts.removeMunsFromPromptCompat(''Product line'','''+[Query1].[PLMun]+''');
oCR.sendRequest(cognos.Report.Action.FINISH);
"
value="-"
>'
else '<input type="button" onclick="
paulScripts.passMunsToPromptCompat(''Product line'','''+[Query1].[PLMun]+''');
oCR.sendRequest(cognos.Report.Action.FINISH);
"
value="+">'
end
end

If there are no children, then don’t render it. If the member already appears in the parameter, then show the collapse button. Otherwise show the expand button.

This method will also work in previous versions of Cognos if you adapt the JS to work with the old JS API.

expand-collapse.txt (3769 downloads)