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><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></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>
Cheers for this.
Not quite as pretty as I’d hoped, the rows scroll into the header, but of the many solutions I’ve tried this seems to be the only one that works at all!
Thanks, works well for me.
Any updates for Cognos 10?
Is there an additional solution?
If the report is quite huge and if you can’t list all rows in one page (because of performance issues) COGNOS automatically makes pages and when you press “Page Down” button at the bottom the header hides.
Unfortunately this really isn’t good for large data sets. CSS expressions are fired every time the mouse moves and it will simply kill your browser. I did once experiment with trying to splitting out the header into a new table, but that didn’t work out well. Maybe an alternative solution? When dealing with vasts amount of data, using the expand and collapse method usually works well.
Works well in IE but doesn’t work in Firefox or Chrome though.