Hiding Cognos Connection Elements

Hiding Cognos Connection Elements

By default Cognos offers a ride range of UI options. Based on the group or role you can programmatically decide who can see which UI Elements. A simple modification of the system.xml file is all that is needed to take care of this.

The Admin and Security guide, which can be found in the \webcontents\documentation\en\ug_cra.pdf, has an excellent article on how to accomplish this. You can also find the article online here.

The full list of elements that you can hide can also be found in the guide, or online here.

While those links are for 8.4, they are essentially identical for 8.4.1 and 10.1.

But what happens when you have a requirement to remove elements that don’t appear in the list? This is possible by modifying one of the template files that controls the structure of the portal. These files are saved as XSL files, and are easily modified using any text editor. I strongly recommend using a text editor with XML support, such as Notepad++.

Before I continue, it’s worth mentioning the following. Changing these files may be risky. A misplaced semicolon will prevent Cognos from loading, and IBM has a tendancy not to support installations that have modified the Cognos internals. These changes will also be overridden by any patches or upgrades.

Always make a backup of any files you modify. When trouble does occur and you need IBM’s help, simply switch the active files with the backup, and IBM will be none the wiser (and if the problem is fixed, you know where to look).
You should also maintain a change log of everything you do. Upgrades will replace the template files, and there may be differences between the versions. Instead of simply overwriting the upgraded version with your modified version, you should make all the changes again manually. Fortunately (unfortunately?) patches and version upgrades tend to be rare occurrences.

A brief explanation of the files to modify:

There are two primary XSL files which control the portal.
1. \templates\ps\logicsheets\presentation\controls\presentation.xsl
2. \templates\ps\logicsheets\presentation\main\presentation.xsl

The \controls\presentation.xsl renders, among other things, the HTML behind tabs, and the rows and columns in the Public Folders/My Folders table.

The \main\presentation.xsl renders the links to the correct style sheets, the page headers, and the individual links in the Public Folders/My Folders table.

Between the two, they control the HTML between most of the objects you see on the page.

The system.xml file that contains the UI black list can be found in \templates\ps\portal\system.xml

And now the problem. The client has decided that no user, except administrators and report authors, should be able to see the Properties or More… links for any reports or folders.

First, hiding the properties.

In the \main\presentation.xsl do a search for action_properties.gif. There should be four instances of that string. Each of these instances controls the properties for a different type of object. Series 7 object, CRN object, Job and… well, I’m not entirely sure what the fourth one is for, but I’m sure it’s very important. 5 points to whoever enlightens me.

In each of these four cases the action_properties.gif is part of a xsl:call-template reference.

This statement is calling the renderActions template with values for the onclick, icon and tooltip parameters.

In order to prevent this segment from being rendered we need to add it to the “Elements you can hide” list. The code that controls items on the list is as follows:

<out:if test="not(contains($ui_black_list, 'NAME'))">			
</out:if>

This will check the system.xml for an appearance of NAME in the ui_hide list. Should it appear, it will not render anything inside the code block except for users or groups listed in the show parameter. Knowing this, all that is needed to hide the properties is to add the black list code to all four appearances of the renderAction call.

		<out:if test="not(contains($ui_black_list, ' PROPERTIES '))">			
			<xsl:call-template name="renderAction">
				<xsl:with-param name="onclick">actions('{xtsext:javascriptencode(string($obj-name))}', '{$obj-class}', '{xtsext:javascriptencode(string($obj-path))}','properties_general.xts');</xsl:with-param>
				<xsl:with-param name="icon" select="'action_properties.gif'"/>
				<xsl:with-param name="tooltip" select="'IDS_PROPERTIES'"/>
			</xsl:call-template>
		</out:if>

In this case, I set the black list name to PROPERTIES. Now, in the system.xml simply add the following to the ui_hide param:

	<PROPERTIES show ="Administrators RSUsers"/>

When you restart, only administrators and authors will be able to see the properties for any object.

Now for the More…

The technique to hide the “More…” link is almost identical.

Simply find all occurences of IDS_ACT_MORE in the same file. In my modified file all occurences appear between lines 4640 and 4722. Thanks to Notepad++’s XML support, I can see that all of these are held inside a single group.

Instead of black listing each individual appearance of the More… link, let’s try black listing the entire group.

Now to add MORE to the system.xml black list

As before, only administrators and authors will be able to see the More… link.

The following shows how folders look to normal users:

And this is what reports look like:

It’s very important to do extensive tests to ensure this meets the client needs. You could probably also reverse the black list by getting rid of the “not()” from the code. Remember that the idea behind this article is not to simply show how to hide specific elements from the Cognos Connection. It’s to show how easily it is to shape Cognos to meet your needs. The XSL sheets are there to be modified.

Use Design Locale for Reference ID in 8.4.1

Sorry, no screenshots for this one. Blame InfoSec.

Cognos 10 has a nice little checkbox that you can set to have authors use only the design language when creating reports. Without this option any changes made to any of the non-design languages may break existing reports. Report authors will have to always use the design language when building reports.

Sadly that checkbox doesn’t exist in previous versions. You can, however, manually change the model.xml and add the same tag.

First, back up your model.xml. You don’t want to mess this up.

Place the following code between the </defaultLocale> and <namespace> tags.

 <fixIdsToDefaultLocale>true</fixIdsToDefaultLocale> 

Publish your model, create a new report, drag in a data item into a query. While the data item caption should appear as your active language, the reference should appear in the design language. Remember to close your browser completely before creating the new report, as you may inadvertently use a cached version of the model.

I have only tested this in 8.4.1, but it may work in previous versions. I don’t know and don’t have access to FM of previous versions, so I’d welcome feedback indicating which versions it works.

Adding a Favicon

Favicons are the little images you have to the left of the URL in the URL bar. If you’re reading this through WordPress you should see a white “W” inside a blue circle. That is the WordPress Favicon.

The absolutely easiest way to add a Favicon is to add the image to the root folder of the web server. If you’re using IIS the default would be “C:\inetpub\wwwroot\”. Add the Favicon, flush the cache and reload Cognos. You should now see the Favicon.

This works great, but what happens if you have more than one site, or if you want different Favicons for different skins? You’ll need to modify the XSL.

While Cognos can (and probably should) be used with Firefox, a majority of users still use IE. So the favicon must be in the Microsoft Icon filetype (.ico).

To begin, decide which skin has what favicon and place them under ..\portal\images\ for each skin.

Next backup and open ..\templates\ps\logicsheets\presentation\main\presentation.xls

Search for “mt:stylesheet”. It should be on line 68. I’ll be entirely honest, I don’t know what this choose expression is testing. Either way, add the following line after the last stylesheet link in the “otherwise group”

<link rel="shortcut icon" href="{'{$skin_images}'}favicon.ico"/>

Restart Cognos and check if it works.

I’ve added the IBM favicon to the business skin and added the portal to my favorites:

Now I’ve added the Google favicon to the classic skin, and switched to it:

The favicons in the tab and the URL bar are immediately updated. However the favicons in the favorites will stay with the previous icon. Easy enough to fix – delete and re-add the favorite.

A few postscripts:
It’s worth noting that while this was testing on Cognos 10, I have no reason to believe it would not work on Cognos 8.
This method also relies on modifying Cognos internals. This will need to be redone every time you upgrade.
Also, make sure you make a backup of any file you modify.

Checkbox List Prompt

This technique has been updated here: https://cognospaul.wordpress.com/2013/05/16/checkbox-list-prompts-revisited/

The examples in this post are based on the GO Sales (query) package in Cognos 8.4.1.

Occasionally I receive a request is to create a checkbox prompt that provides additional information. The users might want a list that shows, for instance, the total revenue for each item in the prompt.

As opposed to the standard checkbox prompts, embedding the checkbox into a list gives the user additional insights. With the measures immediately visible, the user knows exactly what to select. Outliers may be highlighted, tooltips can be embedded describing each row. For example, a report that details production costs may be narrowed by this type of prompting approach that allows a user to easily identify low profit margin product lines where efforts on cost cutting may have the greatest return.

This is done with JavaScript (it may also be possible with jQuery, and I invite any jQuery experts to adapt it) so all of the normal warnings apply.

To begin create an HTML item. Put Scripts in the description and copy/paste the following into it:

<script>
function selectInCheckbox(id)
{
   var inputs= document.getElementById('checkbox1').getElementsByTagName('input');
   for (var i=0;i<inputs.length;i++)
   {
        if (inputs[i].value == id) 
		{
			inputs[i].click();
        }
   }
}

function selectInCheckboxRow(id)
{
	var inputs= document.getElementById('checkbox1').getElementsByTagName('input');
	inputs[id].click();
}
</script>

Now create a normal checkbox prompt. This will hold the prompt, the checkboxes in the list will simply check the corresponding checkbox in this prompt. Place an HTML to the left:

<div id="checkbox1" style="display:none;">

and close the div with another HTML item to the right:

</div>

The display:none hides the prompt, but you may want to remove that bit until everything works.

Next create a list. The list should have the USE value of the prompt in the properties, and the display and any associated measures in the body. Drag in an HTML item to the list. It should create it’s own column. Unlock the report and drag in 4 more HTML items.

Change the description of each HTML item so it looks like the following:

Checkbox Start (Source Type = Text):

<input type="checkbox" 

Checked (Source Type = Report Expression):

case when (ParamValue('code') +', ') contains (number2string([Product line code]) +', ') then (' checked ') else ('') end

onClick (Source Type = Text):

onClick="selectInCheckboxRow('

Row (Source Type = Report Expression):

RowNumber ()

CheckboxEnd (Source Type = Text):

');">

This will use the row number of the list and check the corresponding row in the checkbox prompt. Obviously the checkbox prompt and the list will needed to be sorted exactly the same way.

Alternately you could use the code of the line and loop through the checkbox prompt and check the box that matches the code. To do that change the onClick HTML Item to selectInCheckbox and change the Source Type of the Row HTML Item to use Data Item Value and the data item of the code.

Below is the XML of an example report using this method.

<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="he" ignoreFilterContext="false"><!--RSU-SPC-0093 The report specification was upgraded from &quot;http://developer.cognos.com/schemas/report/3.0/&quot; to &quot;http://developer.cognos.com/schemas/report/6.0/&quot; at 2010-12-23. 15:33:53--><!--RS:8.2-->
	<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Sales (query)']/model[@name='model']</modelPath>
	<layouts>
		<layout>
			<reportPages>
				<page name="Page1">
					<pageBody>
						<contents><HTMLItem description="Styles">
								<dataSource>
									<staticValue>&lt;style type="text/css"&gt;

input.updatebutton
{
   font-size:11px;
   font-weight:bold;
   width:140px;
   height:27px;
   color:#000000;
   background-color:#cdc9c9;
   border-style:solid;
   border-color:#003377;
   border-width:3px;
}



input.matchbutton
{
   font-size:11px;
   font-weight:bold;
   width:140px;
   height:27px;
   color:#EEFFFF;
   background-color:#0088DD;
   border-style:solid;
   border-color:#003377;
   border-width:3px;
}

input.selectbutton
{
   font-size:9px;
   font-weight:bold;
   width:60px;
   height:20px;
   color:#000000;
   background-color:#FFFFFF;
   border-style:solid;
   border-color:#FFFFFF;
   border-width:3px;
}

&lt;/style&gt;

&lt;script language="javascript"&gt;

function goLite(FRM,BTN)
{
   window.document.forms[FRM].elements[BTN].style.color = "#FFFF99";
   window.document.forms[FRM].elements[BTN].style.backgroundColor = "#11AAEE";
}

function goDim(FRM,BTN)
{
   window.document.forms[FRM].elements[BTN].style.color = "#EEFFFF";
   window.document.forms[FRM].elements[BTN].style.backgroundColor = "#0088DD";
}

&lt;/script&gt;
</staticValue>
								</dataSource>
							</HTMLItem>
							<HTMLItem description="Scripts">
								<dataSource>
									<staticValue>&lt;script&gt;
function selectInCheckbox(id)
{
   var inputs= document.getElementById('checkbox1').getElementsByTagName('input');
   for (var i=0;i&lt;inputs.length;i++)
   {
        if (inputs[i].value == id) 
		{
			inputs[i].click();
        }
   }
}

function selectInCheckboxRow(id)
{
	var inputs= document.getElementById('checkbox1').getElementsByTagName('input');
	inputs[id].click();
}
&lt;/script&gt;</staticValue>
								</dataSource>
							</HTMLItem>
							
							
							<promptButton type="reprompt">
			<contents/>
			<style>
				<defaultStyles>
					<defaultStyle refStyle="bp"/>
				</defaultStyles>
			</style>
		</promptButton><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse"/></style><tableRows><tableRow><tableCells><tableCell><contents><HTMLItem description="div">
			<dataSource>
				<staticValue>&lt;div id="checkbox1" style="display:none"&gt;</staticValue>
			</dataSource>
		</HTMLItem><selectValue parameter="code" multiSelect="true" range="false" required="false" selectValueUI="checkboxGroup" refQuery="Prompt"><useItem refDataItem="Product line code"/><sortList><sortItem refDataItem="Product line code"/></sortList></selectValue><HTMLItem description="/div">
			<dataSource>
				<staticValue>&lt;/div&gt;</staticValue>
			</dataSource>
		</HTMLItem></contents><style><CSS value="vertical-align:top"/></style></tableCell><tableCell><contents><HTMLItem description="div id=&quot;list&quot;">
																<dataSource>
																	<staticValue>&lt;div id="list"&gt;</staticValue>
																</dataSource>
															</HTMLItem><list horizontalPagination="true" name="List2" refQuery="Prompt">
			
			
			
			<style>
				<CSS value="border-collapse:collapse"/>
				<defaultStyles>
					<defaultStyle refStyle="ls"/>
				</defaultStyles>
			</style>
		<listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><staticValue>HTML Item</staticValue></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><HTMLItem description="Checkbox Start">
																					<dataSource>
																						<staticValue>&lt;input title="Faster but if the sorting is off then this won't work" type="checkbox" 
</staticValue></dataSource>
																				</HTMLItem><HTMLItem description="checked">
			<dataSource>
				
			<reportExpression>case when (ParamValue('code') +', ') contains (number2string([Product line code]) +', ') then (' checked ') else ('') end</reportExpression></dataSource>
		</HTMLItem><HTMLItem description="onClick">
			<dataSource>
				<staticValue>onClick="selectInCheckboxRow('</staticValue>
			</dataSource>
		</HTMLItem><HTMLItem description="Row">
			<dataSource>
				
			<reportExpression>RowNumber ()</reportExpression></dataSource>
		</HTMLItem><HTMLItem description="CheckboxEnd">
																					<dataSource>
																						<staticValue>');
"&gt;
</staticValue>
																					</dataSource>
																				</HTMLItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><staticValue>By Code</staticValue></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><HTMLItem description="Checkbox Start">
																					<dataSource>
																						<staticValue>&lt;input title="Slower but less prone to error" type="checkbox" 
</staticValue></dataSource>
																				</HTMLItem><HTMLItem description="checked">
			<dataSource>
				
			<reportExpression>case when (ParamValue('code') +', ') contains (number2string([Product line code]) +', ') then (' checked ') else ('') end</reportExpression></dataSource>
		</HTMLItem><HTMLItem description="onClick">
			<dataSource>
				<staticValue>onClick="selectInCheckbox('</staticValue>
			</dataSource>
		</HTMLItem><HTMLItem description="Code">
			<dataSource>
				
			<dataItemValue refDataItem="Product line code"/></dataSource>
		</HTMLItem><HTMLItem description="CheckboxEnd">
																					<dataSource>
																						<staticValue>');
"&gt;
</staticValue>
																					</dataSource>
																				</HTMLItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Product line"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Product line"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Quantity"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Quantity"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Revenue"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Gross profit"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Gross profit"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns><propertyList><propertyItem refDataItem="Product line code"/></propertyList><sortList><sortItem refDataItem="Product line code"/></sortList></list><HTMLItem description="/div">
																<dataSource>
																	<staticValue>&lt;/div&gt;</staticValue>
																</dataSource>
															</HTMLItem><HTMLItem description="doAll">
														<dataSource>
															<staticValue>&lt;script&gt;
function checkAll(){
var inputs=document.getElementById('list').getElementsByTagName('input')
for (var i=0;i&lt;inputs.length;i++){
        if (inputs[i].type == 'checkbox') {
            if (inputs[i].checked == true) 
		{}
		else {inputs[i].click();}
        }
}
} 

function unCheckAll(){
var inputs=document.getElementById('list').getElementsByTagName('input')
for (var i=0;i&lt;inputs.length;i++){
        if (inputs[i].type == 'checkbox') {
            if (inputs[i].checked == true) 
		{inputs[i].click();}
		else {}
        }
}
} 
&lt;/script&gt;

&lt;input type="button" value="Select All" class="selectbutton" onmouseover = "this.style.cursor='hand'" onclick="checkAll();"&gt;
&lt;input type="button" value="Clear All" class="selectbutton" onmouseover = "this.style.cursor='hand'" onclick="unCheckAll();"&gt;</staticValue>
														</dataSource>
													</HTMLItem></contents><style><CSS value="vertical-align:top"/></style></tableCell></tableCells></tableRow></tableRows></table><block>
								<contents/>
							</block>
						<combinationChart showTooltips="true" maxHotspots="10000" name="Combination Chart1" refQuery="Report">
								<legend>
									<legendPosition>
										<relativePosition/>
									</legendPosition>
									<legendTitle refQuery="Report">
										<style>
											<defaultStyles>
												<defaultStyle refStyle="lx"/>
											</defaultStyles>
										</style>
									</legendTitle>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="lg"/>
										</defaultStyles>
									</style>
								</legend>
								<ordinalAxis>
									<axisTitle refQuery="Report">
										<style>
											<defaultStyles>
												<defaultStyle refStyle="at"/>
											</defaultStyles>
										</style>
									</axisTitle>
									<axisLine color="black"/>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="al"/>
										</defaultStyles>
									</style>
								</ordinalAxis>
								<numericalAxisY1>
									<axisTitle refQuery="Report">
										<style>
											<defaultStyles>
												<defaultStyle refStyle="at"/>
											</defaultStyles>
										</style>
									</axisTitle>
									<gridlines color="#cccccc"/>
									<axisLine color="black"/>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="al"/>
										</defaultStyles>
									</style>
								</numericalAxisY1>
								<combinationChartTypes>
									<bar><chartNodes><chartNode><chartNodeMembers><chartNodeMember refDataItem="Revenue"><chartContents><chartTextItem><dataSource><memberCaption/></dataSource></chartTextItem></chartContents></chartNodeMember></chartNodeMembers></chartNode></chartNodes></bar>
								</combinationChartTypes>
								<style>
									<defaultStyles>
										<defaultStyle refStyle="ch"/>
									</defaultStyles>
								</style>
								<commonClusters><chartNodes><chartNode><chartNodeMembers><chartNodeMember refDataItem="Product type"><chartContents><chartTextItem><dataSource><memberCaption/></dataSource></chartTextItem></chartContents></chartNodeMember></chartNodeMembers></chartNode></chartNodes></commonClusters><conditionalRender refVariable="renderGraph"><renderFor refVariableValue="1"/></conditionalRender></combinationChart>
						</contents>
					<style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style></pageBody>
				<style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style></page>
			</reportPages>
		</layout>
	</layouts>
<queries>
	
	
	
		
	<query name="Prompt">
			<source>
				<model/>
			</source>
			<selection><dataItem name="Product line code" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Product].[Product line code]</expression></dataItem><dataItem name="Product line" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Product].[Product line]</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[Sales (query)].[Sales].[Quantity]</expression></dataItem><dataItem name="Revenue" aggregate="total"><expression>[Sales (query)].[Sales].[Revenue]</expression></dataItem><dataItem name="Gross profit" aggregate="total"><expression>[Sales (query)].[Sales].[Gross profit]</expression></dataItem></selection>
		</query><query name="Report"><source><model/></source><selection><dataItem name="Revenue" aggregate="total"><expression>[Sales (query)].[Sales].[Revenue]</expression></dataItem><dataItem name="Product type" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Product].[Product type]</expression></dataItem></selection><detailFilters><detailFilter><filterExpression>[Sales (query)].[Product].[Product line code] in (?code?)</filterExpression></detailFilter></detailFilters></query></queries><XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes><reportVariables><reportVariable type="boolean" name="renderGraph">
			<reportExpression>ParamValue('code') is not null</reportExpression>
			<variableValues>
				<variableValue value="1"/>
			</variableValues>
		</reportVariable></reportVariables></report>

Token prompts

Token prompts are an extremely powerful macro. They allows author to create extremely efficient code that would otherwise be difficult or impossible to make.

The following examples will all be based on the Great Outdoor Sales (cube) package.

A simple example would be a static prompt that would allow the user to see a list the top or bottom products by revenue. Without a token prompt you might be tempted to use the following expression:

case #prompt('TopOrBottom','string',sq('top'))#
when 'top' then topCount([great_outdoor_sales_en].[Products].[Products].[Product],5,[great_outdoor_sales_en].[Measures].[Revenue])
when 'bottom' then bottomCount([great_outdoor_sales_en].[Products].[Products].[Product],5,[great_outdoor_sales_en].[Measures].[Revenue])
end

The problem with that is that it simply doesn’t work. You could also try to make a conditional block, but that would make the report needlessly complex.

Instead you could use a token prompt:
#prompt('TopOrBottom','token','top')#Count([great_outdoor_sales_en].[Products].[Products].[Product],5,[great_outdoor_sales_en].[Measures].[Revenue])

When the macro resolves the function will be either topCount or bottomCount.

Locked headers, rows and corner in a Cognos crosstab

Locking headers has often been requested in large crosstabs.  While data visualization experts, such as the brilliant Stephen Few, often decries the practice of displaying overwhelming amounts of information, sometimes the users absolutely insist on seeing everything in one place.

The following solution is based on a PDF written by Vision Solutions.  In their solution they embed the styling inside the CSS, and it is built primarily for list.

This adaptation removes all extra styling – all colors and fonts will have to be handled in the properties of each individual node.  In order to use this you must remove the existing class from the node.

To begin create a blank report.

Drag in a block item, give it a height and width (small enough so you’ll be forced to scroll) and set “Use scrollbars only when necessary”.

Create a crosstab inside the block. Place series that are guaranteed to extend the edges of the crosstab past the block. In my example I’m using Months in the rows and product type in the columns. Use any measure that you want. Change the rows per page to 200.

Run the report now. You should see a crosstab inside a block with scrollbars going down and across.

Create a page header and paste the following code into an HTML item.

<style>/*column Lock*/
.lockCorner{
top: expression(parentNode.parentNode.parentNode.parentNode.scrollTop-1);
left: expression(parentNode.parentNode.parentNode.parentNode.scrollLeft);
position: relative;
z-index:10;
}

.lockRows{
left: expression(parentNode.parentNode.parentNode.parentNode.scrollLeft);
position: relative;
}

.lockColumns{
top: expression(parentNode.parentNode.parentNode.parentNode.scrollTop-1);
position: relative;
}
</style>

Go to Local Classes in the Page Explorer and paste in the following:

<RSClipboardFragment version="2.0"><classStyle name="lockCorner" label="lockCorner"/><classStyle name="lockRows" label="lockRows"/><classStyle name="lockColumns" label="lockColumns"/></RSClipboardFragment>

Note that this is Cognos 8.4 syntax. If you’re using a previous (or subsequent) version of Cognos you may need to create them from scratch. In 8.2 you could explicitly set the class name, but in 8.4 (or possibly 8.3) that option was removed. The class names in 8.4 are along the lines of “cls1”. You can copy it into notepad, fix it, and paste it back in.

Go back to the report page and change the class of the crosstab corner to the lockCorner. Remove the existing class or it will not work.

Do the same for the rows and columns, using the lockRows and lockColumns respectively.

Style the corner, rows and corner. Without a style the background will be transparent and you will be unable to read the labels.

Try running the report now.

The following xml is based on the Sales and Marketing cube.

<report xmlns="http://developer.cognos.com/schemas/report/6.0/"
expressionLocale="en">
  <modelPath>/content/package[@name='Sales and
  Marketing']/model[@name='model']</modelPath>
  <drillBehavior modelBasedDrillThru="true" />
  <layouts>
    <layout>
      <reportPages>
        <page name="Page1">
          <style>
            <defaultStyles>
              <defaultStyle refStyle="pg" />
            </defaultStyles>
          </style>
          <pageBody>
            <style>
              <defaultStyles>
                <defaultStyle refStyle="pb" />
              </defaultStyles>
            </style>
            <contents>
              <block>
                <contents>
                  <crosstab horizontalPagination="true"
                  name="Crosstab1" refQuery="Query1"
                  rowsPerPage="200">
                    <crosstabCorner>
                      <contents>
                        <textItem>
                          <dataSource>
                            <dataItemLabel refDataItem="Revenue" />
                          </dataSource>
                        </textItem>
                      </contents>
                      <style>
                        <defaultStyles>
                          <defaultStyle refStyle="lockCorner" />
                        </defaultStyles>
                        <CSS value="background-color:white;border:0.25pt solid silver" />
                      </style>
                    </crosstabCorner>
                    <style>
                      <CSS value="border-collapse:collapse" />
                      <defaultStyles>
                        <defaultStyle refStyle="xt" />
                      </defaultStyles>
                    </style>
                    <defaultMeasure refDataItem="Revenue" />
                    <crosstabFactCell>
                      <contents>
                        <textItem>
                          <dataSource>
                            <cellValue />
                          </dataSource>
                        </textItem>
                      </contents>
                      <style>
                        <defaultStyles>
                          <defaultStyle refStyle="mv" />
                        </defaultStyles>
                      </style>
                    </crosstabFactCell>
                    <crosstabRows>
                      <crosstabNode>
                        <crosstabNodeMembers>
                          <crosstabNodeMember refDataItem="Month"
                          edgeLocation="e1">
                            <contents>
                              <textItem>
                                <dataSource>
                                  <memberCaption />
                                </dataSource>
                              </textItem>
                            </contents>
                            <style>
                              <defaultStyles>
                                <defaultStyle refStyle="lockRows" />
                              </defaultStyles>
                              <CSS value="background-color:white;border:0.25pt solid silver" />
                            </style>
                          </crosstabNodeMember>
                        </crosstabNodeMembers>
                      </crosstabNode>
                    </crosstabRows>
                    <crosstabColumns>
                      <crosstabNode>
                        <crosstabNodeMembers>
                          <crosstabNodeMember refDataItem="Product type"
                          edgeLocation="e2">
                            <contents>
                              <textItem>
                                <dataSource>
                                  <memberCaption />
                                </dataSource>
                              </textItem>
                            </contents>
                            <style>
                              <defaultStyles>
                                <defaultStyle refStyle="lockColumns" />
                              </defaultStyles>
                              <CSS value="background-color:white;border:0.25pt solid silver" />
                            </style>
                          </crosstabNodeMember>
                        </crosstabNodeMembers>
                      </crosstabNode>
                    </crosstabColumns>
                  </crosstab>
                </contents>
                <style>
                  <CSS value="width:600px;height:400px;overflow:auto" />
                </style>
              </block>
            </contents>
          </pageBody>
          <pageHeader>
            <contents>
              <HTMLItem>
                <dataSource>
                  <staticValue>&lt;style&gt;/*column Lock*/
                  .lockCorner{ top:
                  expression(parentNode.parentNode.parentNode.parentNode.scrollTop-1);
                  left:
                  expression(parentNode.parentNode.parentNode.parentNode.scrollLeft);
                  position: relative; z-index:10; } .lockRows{
                  left:
                  expression(parentNode.parentNode.parentNode.parentNode.scrollLeft);
                  position: relative; } .lockColumns{ top:
                  expression(parentNode.parentNode.parentNode.parentNode.scrollTop-1);
                  position: relative; }
                  &lt;/style&gt;</staticValue>
                </dataSource>
              </HTMLItem>
            </contents>
          </pageHeader>
        </page>
      </reportPages>
    </layout>
  </layouts>
  <classStyles>
    <classStyle name="lockCorner" label="lockCorner" />
    <classStyle name="lockRows" label="lockRows" />
    <classStyle name="lockColumns" label="lockColumns" />
  </classStyles>
  <XMLAttributes>
    <XMLAttribute name="RS_CreateExtendedDataItems" value="true"
    output="no" />
    <XMLAttribute name="listSeparator" value="," output="no" />
  </XMLAttributes>
  <queries>
    <query name="Query1">
      <source>
        <model />
      </source>
      <selection>
        <dataItemMeasure name="Revenue">
          <dmMember>
            <MUN>[Sales and Marketing].[Measures].[Revenue]</MUN>
            <itemCaption>Revenue</itemCaption>
          </dmMember>
          <dmDimension>
            <DUN>[Sales and Marketing].[Measures]</DUN>
            <itemCaption>Measures</itemCaption>
          </dmDimension>
        </dataItemMeasure>
        <dataItemLevelSet name="Month">
          <dmDimension>
            <DUN>[Sales and Marketing].[Time]</DUN>
            <itemCaption>Time</itemCaption>
          </dmDimension>
          <dmHierarchy>
            <HUN>[Sales and Marketing].[Time].[Time]</HUN>
            <itemCaption>Time</itemCaption>
          </dmHierarchy>
          <dmLevel>
            <LUN>[Sales and Marketing].[Time].[Time].[Month]</LUN>
            <itemCaption>Month</itemCaption>
          </dmLevel>
        </dataItemLevelSet>
        <dataItemLevelSet name="Product type">
          <dmDimension>
            <DUN>[Sales and Marketing].[Products]</DUN>
            <itemCaption>Products</itemCaption>
          </dmDimension>
          <dmHierarchy>
            <HUN>[Sales and Marketing].[Products].[Products]</HUN>
            <itemCaption>Products</itemCaption>
          </dmHierarchy>
          <dmLevel>
            <LUN>[Sales and
            Marketing].[Products].[Products].[Product type]</LUN>
            <itemCaption>Product type</itemCaption>
          </dmLevel>
        </dataItemLevelSet>
      </selection>
    </query>
  </queries>
</report>

Macros (part 1 of many)

From the User Guide:

A macro is a fragment of code that you can insert in the Select statement of a query or in an expression. For example, add a macro to insert a new data item containing the user’s name.

These two small sentences belie the great value macros offer to report developers. In my own words, macros allow the author to dynamically alter the SQL at runtime.

Practically this allows the author to specify specific fields, tables, or even databases at run time. The author can create filters that check users’ credentials, or redirect users to different tables based on the month or the users’ language settings.

All macros are delimited with two #s. The following is an acceptable macro: #sq(‘Hello World!’)# The sq() function will wrap a string in single quotes. If you attempted to run the same macro without the sq(), Cognos would try to interpret Hello World! as a function call, yielding you a syntax error. Understanding this behavior is key to building complex macros.

Let’s say that you’re working on a database that has materialized views for each month. The tables are named v_YYYYMM: 201001, 201002, 201003, etc… Your report needs to run against the current month. You can use the macro function timestampMask with the parameter $current_timestamp.

[ns].[table].#sb(‘v_’ + timestampMask($current_timestamp,’yyyymm’))#

$current_timestamp checks the time on the Cognos server (a side note, I am pretty sure that it checks the dispatcher. If you have multiple dispatchers in different time zones this can cause some issues unless they’re all synced). Lets say that returns 2010-09-18 22:20:31.000+02:00. timestampMask() will take the timestamp and return it with the specified format: 201009. sb() will then wrap ‘v_’ and 201009 in square brackets: [v_201009]. Cognos will then attempt to run [ns].[table].[v_201009].

Ultimately this will allow you to run cleaner and faster SQL.