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>
It works except for one small issue. When the user selects another year, the report is correct but the Javascript forces the year in the prompt back to the default selection!
Use the report expression