Accessing Tree Prompts with Javascript (on Cognos 10.1.1)

There are many reasons why you may want to interact with a Tree Prompt with JavaScript. Maybe you want to enable the finish button if a member on the bottom level is selected, or to select the last member, or to ensure only 5 members are selected.

This post isn’t to detail every possible scenario, but to detail the some functions available and how to use them. It’s important to note that I am hardly a JavaScript expert, so there may be better ways to do anything I say here.

First you need to identify your tree prompt. Unlike most prompt controls, where the identifier changes based on the type viewer being used, the tree prompt can be called using window.treePROMPTNAME. Unfortunately we can’t apply an onmousedown event to the tree prompt, so we have to wrap it in a div.

Createa tree prompt and give it the name “Time”. Drag an HTML item to the left of the tree prompt

<div id='myTree'>

and an HTML item to the right

</div>

.

Now we can attach an event to capture the clicks:

<script>
document.getElementById('myTree').onmousedown=function(){runTree('Time')};
</script>

Any click inside that div will now trigger the runTree function passing ‘Time’ as an argument.

Because there are a number of JavaScript functions are run upon selecting an element we can’t immediately get the value of the element. So we can use the setTimeout function to wait 200 milliseconds before getting the data.

<script>
function runTree(id)
{
 t=setTimeout('checkTree("'+id+'")',200);
}
</script>

After 200 milliseconds the checkTree function will run, also passing Time as the argument.

<script>
function checkTree(id)
{
  selectedTreeNode  = window['tree'+id].getLastSelectedNode();
  if(!selectedTreeNode) {return}
  alert(selectedTreeNode.getName());
  alert(selectedTreeNode.getValue());
  alert(selectedTreeNode.getLevel());

}
</script>

The checkTree function will now alert the selected elements Name, MUN, and Tree level. Note the Tree Level is from the tree prompt, not the member’s hierarchy level. But knowing these, we can then call other functions. You could check the level number of the selected element and enable or disable the tree prompt while popping up a message.

You can programmatically set the default value of the tree prompt using JavaScript. Unfortunately it appears it is only possible to do this on the first level.

<script>
var node = window.treeTime.getRootNode().getChildren()[window.treeTime.getRootNode().getChildren().length-1];
node.setSelected(true);
node.updateNodeSelection();
node.updateParent();
window.treeTime.setLastSelectedNode(node);
</script>

This will only effect the prompt after the page has been loaded. Prompt pages should be fine, but prompts on the report page will need to have a default value set in the prompt macro.

I learned about these functions by going through the js file associated with tree prompts. Check out ..webcontentpromptingCTreeIE5NS6.js for more Tree Prompt functions.

It worth noting that these functions are written by IBM, and are liable to change on upgrade. I’d be interested in hearing if these work in any of the previous versions of Cognos.

Quickie: Generate function (first N months of years)

Problem: How do you return the first N months of each year in the time hierarchy? It must be dynamic (no static sets, because who wants to go back every year and fix it?), and the user wants to be able to select the number of months that are displayed.

Solution:

generate
(
   [great_outdoors_company].[Years].[Years].[Year]
   , head
     (
        descendants
        (
           currentMember([great_outdoors_company].[Years].[Years])
           , [great_outdoors_company].[Years].[Years].[Month])
     , #prompt('n','integer')#
     )
)

The generate function will evaluate the head(descendants(currentMember)) expression for each member in the years level.

The importance of using Report Expressions

Today I was asked to analyze a series of reports to improve performance. All of the queries were built on a very simple Power Cube. Three dimensions and two measures. Dimensions are: Time, Plants, KPIs. Measures: Actual, Target

One of the things that struck me was the rampant use of string functions on attributes. The attributes of each KPI were used, and since it’s a Power Cube they couldn’t add any more. An example Long Name: 001 – Items Sold. In some reports they needed to show the entire Long Name, some they needed to show only the number, and some only the name itself.

A simple crosstab might have KPIs in the rows, plants in the column, actual as the measure and a specific month in the slicer. The MDX would appear as:

SELECT [Plant]..[Plant 2].MEMBERS DIMENSION PROPERTIES PARENT_LEVEL ON AXIS(0), [KPI]..[KPI 2].MEMBERS DIMENSION PROPERTIES PARENT_LEVEL ON AXIS(1), {[MEASURES]..[Actual Value]} DIMENSION PROPERTIES PARENT_LEVEL ON AXIS(2) FROM [Global_Operations_KPIs] WHERE ([Date].[Current Month].[@MEMBER].[Current Month])

Simple and to the point. The problem happens when they want to truncate the Long Name of the KPI to show only the first 3 characters. They use the expression: substring([Cube].[KPI].[KPI].[KPI1].[KPI – Long Name],1,3) and the resulting MDX is:

SELECT [Plant]..[Plant1].MEMBERS DIMENSION PROPERTIES PARENT_LEVEL ON AXIS(0), [KPI]..[KPI1].MEMBERS DIMENSION PROPERTIES PARENT_LEVEL, [KPI].[KPI1].[substring(Cube_KPI_KPI_KPI1_KPI – Long Name from 1 for 3)0] ON AXIS(1), {[MEASURES]..[Actual Value]} DIMENSION PROPERTIES PARENT_LEVEL ON AXIS(2) FROM [F:/Program Files/cognos/c8./bin../tempdmbTemp_2712_0002_12910169730.dmc] WHERE ([Date]..[Current Month (Root)].[Current Month])

Power Cubes don’t support string operations, so Cognos is dumping the resultset into a temp file. After it’s done dumping it into the file it runs Cognos SQL on top of it:

with
Query1_0_tab_oqpTabStream4 as
(select
Query1_0_tab_oqpTabStream.”Current Monthkey”  as  Current_Monthkey,
Query1_0_tab_oqpTabStream.”Current Month0″  as  Current_Month0,
Query1_0_tab_oqpTabStream.Plant0key  as  Plant0key,
Query1_0_tab_oqpTabStream.Plant1  as  Plant1,
Query1_0_tab_oqpTabStream.Plant1key  as  Plant1key,
Query1_0_tab_oqpTabStream.Plant10  as  Plant10,
Query1_0_tab_oqpTabStream.KPI0key  as  KPI0key,
Query1_0_tab_oqpTabStream.KPI1  as  KPI1,
Query1_0_tab_oqpTabStream.KPI1key  as  KPI1key,
Query1_0_tab_oqpTabStream.KPI10  as  KPI10,
Query1_0_tab_oqpTabStream.”substring(Cube_KPI_KPI_KPI1_KPI – Long Name from 1 for 3)01″  as  c11,
Query1_0_tab_oqpTabStream.”Actual Value”  as  Actual_Value
from
TABLE(_ROWSET(“Query1.0_tab_oqpTabStream”)) Query1_0_tab_oqpTabStream
)
select
Query1_0_tab_oqpTabStream4.Current_Monthkey  as  Current_Monthkey,
Query1_0_tab_oqpTabStream4.Current_Month0  as  Current_Month0,
Query1_0_tab_oqpTabStream4.Plant0key  as  Plant0key,
Query1_0_tab_oqpTabStream4.Plant1  as  Plant1,
Query1_0_tab_oqpTabStream4.Plant1key  as  Plant1key,
Query1_0_tab_oqpTabStream4.Plant10  as  Plant10,
Query1_0_tab_oqpTabStream4.KPI0key  as  KPI0key,
Query1_0_tab_oqpTabStream4.KPI1  as  KPI1,
Query1_0_tab_oqpTabStream4.KPI1key  as  KPI1key,
Query1_0_tab_oqpTabStream4.KPI10  as  KPI10,
substring(Query1_0_tab_oqpTabStream4.c11 from 1 for 3)  as  c11,
XSUM(Query1_0_tab_oqpTabStream4.Actual_Value  for Query1_0_tab_oqpTabStream4.Current_Monthkey,Query1_0_tab_oqpTabStream4.Current_Month0,Query1_0_tab_oqpTabStream4.Plant0key,Query1_0_tab_oqpTabStream4.Plant1,Query1_0_tab_oqpTabStream4.Plant1key,Query1_0_tab_oqpTabStream4.Plant10,Query1_0_tab_oqpTabStream4.KPI0key,Query1_0_tab_oqpTabStream4.KPI1,Query1_0_tab_oqpTabStream4.KPI1key,Query1_0_tab_oqpTabStream4.KPI10,substring(Query1_0_tab_oqpTabStream4.c11 from 1 for 3) )  as  Actual_Value
from
Query1_0_tab_oqpTabStream4
group by
Query1_0_tab_oqpTabStream4.Current_Monthkey,
Query1_0_tab_oqpTabStream4.Current_Month0,
Query1_0_tab_oqpTabStream4.Plant0key,
Query1_0_tab_oqpTabStream4.Plant1,
Query1_0_tab_oqpTabStream4.Plant1key,
Query1_0_tab_oqpTabStream4.Plant10,
Query1_0_tab_oqpTabStream4.KPI0key,
Query1_0_tab_oqpTabStream4.KPI1,
Query1_0_tab_oqpTabStream4.KPI1key,
Query1_0_tab_oqpTabStream4.KPI10,
substring(Query1_0_tab_oqpTabStream4.c11 from 1 for 3)

That’s a lot of effort for a simple substring.

Instead they could have used a Report Expression. On the crosstab node change the Source Type from Data Item Value to Report Expression. Use the expression: substring([Query1].[KPI – Long Name],1,3) remembering to add Long Name to the properties of that row.

When Cognos executes the report the MDX is untouched. Cognos will only execute the substring function when it draws the results on the crosstab. This allows for much more efficient reports, while still providing you with the results you expect.