Crosstab with Non-Numeric Fact

Business Case:
The user wants to display the data in a crosstab format but the values in the intersection are not numeric.

Example 1:
User wants a matrix of product reviews. The crosstab rows should have Orders (Order ID)and columns should have products (Product Name). The crosstab cells (intersection of product and order) should contain the text "Positive" or "Negative" depending on what review the customer gave for each product.

Solution:
Crosstab reports in Cognos are used to display summary values. For example, Order Quantity for each product/order combination would be easy to achieve. If you have a fact that is non numeric, the report will be blank.

However, there is a work around for the kind of requirement explained in Example 1 above. Let's look at the implementation below.

Report Input and Ouput:
Let's look at the data structure first. In the example above, we have 3 query items: Order ID, Product Name and Feedback. The data will look like this:

The expected report output is like this:

Implementation:

• First, we need to create a numeric fact equivalent for the non-numeric fact. In this case, I created a data item Feedback_Numeric with this expression:

Case When [SQL1].[FEEDBACK] = 'Positive' Then 1 When [SQL1].[FEEDBACK] = 'Negative' Then 1 Else Null End

• Set the "Aggregate Function" and "Rollup Aggregate Function" properties of this query item to "Total"
• Now, create a crosstab report in the report page, drag [Order ID] to the rows, [Product Name] to the columns and [Feedback Numeric] to the measures.
• Create a new String Variable "Feedback Variable" with the following expression:

Case [Query1].[Feedback Numeric]
  When  1 Then 'Positive'
  When  2 Then 'Negative'
  Else 'Null'
End

• Your condition explorer should look like this:

• Next, unlock the cells by clicking the lock icon on the toolbar. 
• Select the text of the query item in the crosstab. Make sure, you are selecting only the text portion of the cell, not the entire cell (see image below).

•  In the properties pane, edit the "Text Source Variable" property and set it to "Feedback Variable"
• Now expand the "Conditional Explorer" and select "Positive"
• Click the text part of "Feedback Numeric" measure in the crosstab and in the properties pane, change the "Source Type" property to "Text".
• Edit the text item in the crosstab cell and type "Positive"
• Do the same thing for "Negative" case in the Conditional Explorer.
• For "Null" condition, edit the text to put "No Feedback" or you can put a white space as I did.

 Now, if you run the report, the output should be like the second image above.

Have fun..