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.

Dynamic Year prompt

One of the requirements that I have often encountered is a dynamic year prompt. The user would like a list of the past n years, defaulting to a specific one (the previous year for instance). Cognos does not have a built-in way of dynamically setting a default value on a prompt. There is a way around this though.

To start go to the query that needs to be filtered. In this example I’ll be using a relational source.

The filter line should be

[ns].[Time].[Year] = #prompt('Year','integer','year(getDate))-1')#

Use a prompt macro to get the default year

The prompt macro will automatically insert the default year into the filter, in this case the current year – 1. Note that the year(getDate()) is T-SQL, PL/SQL users should using something like to_char(add_months(sysdate, -12),’YYYY’).

If there is no value returned the parameter on runtime the filter will default to the previous year. This works well, but the user wants the year selected on the value prompt.

Add some fields to the report page to test it.

For that we’ll need the prompt on the page. Create the value prompt, give it the parameter name Year, and click finish. Set the name of the prompt to Year. Set Auto-Submit to Yes. Add static choices 1 – 5.

The next step is to change the numbers into years, and to automatically select the previous year.

Add an HTML item to the right of the prompt:

<script>
var form = getFormWarpRequest();
var YearList = form._oLstChoicesYear;
var i = 0;
d=new Date();
year = d.getYear()
while (i <= 4)
{
YearList.options[i+2].text=year - i;
YearList.options[i+2].value=year - i;
i++;}
YearList.remove(0);
YearList.remove(0);
YearList.options[1].selected=true;
YearList.removeAttribute("hasLabel");
</script>

Note that the while statement loops 5 times. If you want more than 5 options increase the number of loops accordingly.

If it works the report will look like: 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!

The best way to solve this is to set the HTML Source Variable with a Boolean variable.

Use the report expression

ParamValue('Year') is not null

Select the new variable from the Condition Explorer and click on the HTML Item. If it is not already, set the HTML Source Variable to the variable that was just created. Set the Source Type to Report Expression and paste in the following code:

'<script>
var form = getFormWarpRequest();
var YearList = form._oLstChoicesYear;
var i = 0;
d=new Date();
year = d.getYear()
while (i <= 4)
{
YearList.options[i+2].text=year - i;
YearList.options[i+2].value=year - i;
i++;}
YearList.remove(0);
YearList.remove(0);
YearList.removeAttribute("hasLabel");
YearList.options[' + number2string(extract("year",date2timestamp  (Today()))-string2int32(ParamValue('Year'))) + '].selected = true;
</script> '

Now when no year is selected it will default to the previous year, and select the second value in the prompt. When a year is selected, it will find the number of years between that year and current year (extract(“year”,date2timestamp (Today()))-string2int32(ParamValue(‘Year’))), and will select the year at that index. Some locales use a semi-colon instead of a comma.