Cognos Tips – Using Text Variable on a Null/Missing Crosstab Value

The Problem: No Data in the Fact Cells

A common requirement that I come across is to have 0 values render in crosstab cells where there is no data at that intersection of the column and row dimensional/attribute data.  This is especially common when the output to Excel (spreadsheetXML) is used and empty cells are not conducive to proper Excel techniques.  In this post, I will show a helpful Cognos reporting tip – how using Text Variable property can fix this problem.

Resolution: Using Text Variable

Utilize the status() and CellValue() report expression functions, along with the Text Variable property of the crosstab’s Fact Cells to conditionally return 0 when there is no intersection of data (NULL/Missing), we are cooking with gas.  Let’s take a look.

Implementation

Assumption(s): Report exists that already contains a crosstab; Developer knows how to create variables, and work with components of a crosstab, the ancestor picker, etc..

Select the crosstab’s Fact Cells (this can be done by clicking a crosstab node, then using the right-click menu in C10 or the floating toolbar’s “More” … in C11 to select crosstab Fact Cells)

Find the TEXT SOURCE > Source Type property… change it from Cell Value to Report Expression
Set the report expression to 0 (this will actually never be used after the following steps are taken)

With the crosstab Fact Cells still selected, locate the Text Variable property, click the … button

  • Select New boolean variable

  • Set the name… I will use v_Status=0

  • In the Variable’s Expression, use:
    status(CellValue()) = 0

  • Click OK

Set the design variable v_Status=0 to Yes

  • Set the TEXT SOURCE > Source Type property to Cell Value

Set the design variable v_Status=0 to No

  • Set the TEXT SOURCE > Source Type property to Report Expression

  • Set the report expression to 0

  • Save

Since the Boolean variable v_Status=0 is always True (Yes) or False (No) the crosstab’s Fact Cells will only ever be CellValue() when True, or 0 when False.

Conclusion

I hope you found this article on using Text Variable property helpful.  You can get more Cognos reporting tips and tricks on our blog and newsletter.  If you haven’t already, be sure to subscribe to our e-newsletter the PMsquare Journal for more technical articles and updates delivered directly to your inbox.

Next Steps

If you have any questions or would like PMsquare to provide guidance and support for your analytics solution, contact us today.

Cognos AnalyticsMark Karas