Tag: quickie

Uncategorized

Quickie: Aggregating text in a Cognos crosstab

| cognospaul

The ability to show text in a crosstab is highly requested feature. Various work-arounds have been found, such as using layout expressions like: case when [Flag] = 1 then ‘Yes’ else ‘No’ end. This works, but doesn’t allow for truly dynamic crosstabs.

Consider the following requirement. The user wants a crosstab that shows Region, Retailer Type, and a list of Company Names in the intersections. He wants to be able to filter by Product Line to limit the companies that are shown.

aggregating text

In the above example, I’m using DB2. DB2 has the function listagg, which aggregates text with a specified delimitter. Oracle has something similar, group_concat. SQL Server has some UDFs that can do the same thing, such as found here.

This can now be accomplished as of 10.2.1 Fix Pack 3. One of the new features of this fix pack is a way of using non-standard aggregation functions. By prepending “aggregate:” before the function, we are instructing Cognos to trust us that this is an aggragative function. It won’t try to put it in the group by. This makes it incredibly easy to get this effect, the expression in it’s entirety is aggregate:listagg([Sales (query)].[Retailers].[Company name],’, ‘)

aggregating-text.txt (1938 downloads)
Uncategorized

Quickie: Resizing a value prompt list

| cognospaul

Another problem from a reader. Unlike checkbox or radio prompts, list prompts don’t expand and collapse to fit the number of options available to them.
This prompt is taking far too much room.

In the above image, we have a prompt that has too much space. Let’s shrink that down.
resizing lists

Now it shrinks to the number of rows displayed, or expands to a maximum of 15 items.

It’s actually a very easy script. The first step is to identify the prompt. We’ll use the old fW script (so this should work in every since 8.4).

var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined)
{
  fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );
}
var preFix = "";
if (fW.elements["cv.id"])
{
  preFix = fW.elements["cv.id"].value;
}
var nameSpace = "oCV" + preFix;

Next, we need to identify the prompt object:

var pr = fW._oLstChoicesPromptName;

The prompt itself is a standard SELECT element. We can set the size attribute to easily set the height.

This gives us:

if  (pr.options.length>15)
{
  pr.size=15
}
else 
{
  pr.size=pr.options.length
}

The following report XML is based on 10.2.1, but you can downgrade it easily by changing the schema number on the first line.
Resizing-Value-Prompt-List.txt (942 downloads)