Wednesday, January 11, 2012

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..

13 comments:

  1. Great solution.
    Provides a general approach to convert string values to be used in crosstabs for automated table and matrix.
    I am implementing this to the letter..
    Thanks.

    ReplyDelete
  2. OK, this works when there are a limited number of possible values, since you encode the text as a numeric first. What if I have a "Status Comment" field where product managers type in whatever they want to say (limited to 20 characters). I want to create the same crosstab (Product in columns and Order ID in rows) but the "measure" is the "Status Comment" field from the database. This approach does not appear to work. Is there something that will?

    Thanks

    ReplyDelete
  3. Hi, Thanks for this solution. I've implemented this and works great.
    But another requirements I have is to have the sub-totals of the numeric values based on conditions. As of now, it just adds up all the numeric values in the column that i coded with (1 = Positive and 2 = Negative).But I need the count of Positives and count of Negatives. Could you suggest a solution for this ? Thanks a lot !

    ReplyDelete
  4. Its working fine. Thank you for sharing this... :)

    ReplyDelete
  5. This works only when you have limited cell values and encoding such. It does not work/feasible when you have hundreds or "feedback" results.

    ReplyDelete
  6. Thank you, this was very helpful. I now have a rockin' HR report complete with QA advice! Thanks!

    ReplyDelete
  7. just learning Cognos, and didn't realize that I could unlock the cells. This is very helpful because I am inserting an image and an HTML object, and based on the value of the crosstab cell I am selecting the URL of the image(by using variables). I am a happy camper!!

    ReplyDelete
  8. Hi ,I have a crosstab in which C is a PRoduct Quantity is a measure and product id and order id are dimensions.I have another data item Flag which has values 1 and 0 .I need to apply conditional style variable based on the colour of the flag.however i cannot apply the same directly as the flag is not being used in the crosstab and throwing a error " data item is not being referenced in the layout component reference.
    Need your help in doing the same.

    ReplyDelete
  9. This works fine, but am trying to create two such measures one in separate crosstabs,but not working..

    ReplyDelete
  10. It's not working for me..Working for one table data and not working for other table data..could any one of you suggest reason for that..

    ReplyDelete
  11. I believe this string: Case When [SQL1].[FEEDBACK] = 'Positive' Then 1 When [SQL1].[FEEDBACK] = 'Negative' Then 1 Else Null End

    Should read, "... When [SQL1].[FEEDBACK] = 'Negative' Then 2 Else Null End'

    Maybe I'm wrong, but if 'Negative' is assigned 1 it is the same value as 'Positive'. Negative needs to be assigned 2, instead of 1.

    ReplyDelete
  12. Very helpful, and much better than other people's attempts at explaining it. I would just add that some implementations of this solution may want to adjust the aggregation value (bullet 2): 'Set the "Aggregate Function" and "Rollup Aggregate Function" properties of this query item to "Total"'
    Thank you.

    ReplyDelete
  13. A teenager undergoing teen bodybuilding should be extra cautious about what he is eating every day. legal steroids do they work

    ReplyDelete