19 January 2018

Expand & Collapse in IBM Cognos Report Studio

Introduction

IBM Cognos Report Studio is a web-based report-authoring tool that professional report authors and developers use to build sophisticated, multi-page, multi-query reports against multiple databases. With Report Studio, it is possible to create any reports that your organization requires, such as invoices, statements, weekly sales and inventory reports.

The reports can contain any number of report objects, such as charts, crosstabs, and lists, as well as non-BI components such as images, logos and live embedded applications that you can link to other information.

Similar to my last article, where I provide step-by-step procedure in order to use expand, collapse and drill features in IBM Cognos Workspace, this guide will clarify how to use expand and collapse on IBM Cognos Report Studio, arising as a complement for the users that want comparable features but on this IBM Cognos module.

As such, I will provide the necessary steps to enable users to assemble and explore data in a different way, namely recurring to expand and collapse capabilities that will enrich reports ability to help organizations drive alignment and build consensus for more agile and responsive decision-making.

Audience

In order to use this guide, you should have experience using a web browser, writing reports and basic IBM Cognos concepts as the aim of this document is to provide a path to improve the reports that you already developed or aim to develop in the future.

Please be advised that if you opt to follow this guide using your own IBM Cognos product the appearance of the user interface, the features that are available to you, and the functions that you are able to perform, depend on a capability that is set for you.

A user with the capability to assemble a report can create reports and add components, whereas a user with the capability to consume a report does not see these features.

Expand and Collapse Feature

As it was already referred, one of the most requested features in Cognos is the ability to expand and collapse data. There is a solution that works for report studio that consists on passing the member unique name (MUN) 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. This means that it is possible to keep the third level visible while hiding the second level.

Enabling Expand and Collapse feature for column and row headings in a crosstab

First of all, we will begin with the report studio query. Each crosstab node refers to three data items: The set, the mun and the count of children.

1. The set will be the data item “LO”:

#promptmany('LocationSet','mun','[OLAP].[Location].[Location].[District]
','hierarchize(union([OLAP].[Location].[Location].[District];descendants(set(','[OLAP].[ Location].[ Location]',');1)))')#

If nothing is selected, it defaults to [OLAP].[Location].[Location].[District]. 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.

2. The count will be data item “LO_Children” :

count(1 within set children(currentMember([OLAP].[Location].[Location])))

This is to control when the expand button appears. Evidently, it does not make sense to show the button if there are no children.

3. The member unique name, mun, will be data item “LO_Mun”:

roleValue('_memberUniqueName';[Location])

This is how your report should look like in Page Design and HTML respectively:

Page Design

 

HTML ITEM S1

<script>

var myScripts = {}

  , oCR = cognos.Report.getReport(“THIS”)

  , ocv = eval(“oCV” + “_THIS_”).getRV().getCV();

myScripts.getControl = function(promptName) {

  return oCR.prompt.getControlByName(promptName);

};

function getMethods(myObject)

{

  var funcs=[]

  for(var name in myObject)

  {

    funcs.push(name)

  }

  return  funcs.join(‘, ‘);

}

myScripts.getSelMuns= function(){

var selLength =ocv.getSelectionController().getSelections().length

  , myObj=[];

 

  if(!selLength) return false;

 

  for (i=0;i<selLength;++i){

    myObj.push({

      ‘mun’:ocv.getSelectionController().getSelections()[i].getMuns()[0][0],

      ‘dataItem’:ocv.getSelectionController().getSelections()[i].getDataItems()[0][0]});

  };

 

   return(myObj);

}

 

myScripts.passMunsToPrompt = function(){

  var obj = myScripts.getSelMuns()

    , objLen = obj.length;

 

  for(var i=0;i<objLen ;++i){

    myScripts.getControl(obj[i].dataItem).addValues([{‘use’:obj[i].mun}]);

  }

}

 

myScripts.passMunsToPromptCompat = function(promptName,mun){

    myScripts.getControl(promptName).addValues([{‘use’:mun}]);

}

myScripts.removeMunsFromPrompt= function(){

  var obj = myScripts.getSelMuns()

    , objLen = obj.length

    , updatedPrompts=[];

  for(var i=0;i<objLen ;++i){

    for( var j=0;j<myScripts.getControl(obj[i].dataItem).getValues().length;++j){

      if(myScripts.getControl(obj[i].dataItem).getValues()[j].use == obj[i].mun) {continue;}

      else {updatedPrompts.push(myScripts.getControl(obj[i].dataItem).getValues()[j])}

    }

    myScripts.getControl(obj[i].dataItem).setValues(updatedPrompts);

  }

}

myScripts.removeMunsFromPromptCompat= function(promptName,mun){

  var updatedPrompts=[];

  for( var j=0;j<myScripts.getControl(promptName).getValues().length;++j){

    if(myScripts.getControl(promptName).getValues()[j].use == mun) {continue;}

    else {updatedPrompts.push(myScripts.getControl(promptName).getValues()[j])}

  }

  myScripts.getControl(promptName).setValues(updatedPrompts);

}

</script>

HTML ITEM S2

<div style=”display:none”>

HTML ITEM S3

</div>

REPORT EXPRESSION FOR ITEM S4

case when [Report_Data].[LO_Children] = 0 then ” else

case when ParamDisplayValue(‘LocationSet’) contains ([Report Data].[LO_Mun])

then ‘<input type=”button” onclick=”

myScripts.removeMunsFromPromptCompat(”LO”,”’+[Report_Data].[LO_Mun]+”’);

oCR.sendRequest(cognos.Report.Action.FINISH);

value=”-“

>’

else ‘<input type=”button” onclick=”

myScripts.passMunsToPromptCompat(”LO”,”’+[Report_Data].[LO_Mun]+”’);

oCR.sendRequest(cognos.Report.Action.FINISH);

value=”+”>’

end

end

The report expression basically states that if there are no children, then it isn’t rendered. If the member already appears in the parameter, then the collapse control is shown. Otherwise it shows the expand button.

Please take in consideration that the count and mun must be added to the properties of the node, so we can refer to them in the report expression described above.

HTML

 

 

 

Blog