Displaying text in Cognos 8 ReportStudio CrossTabs

[Vivarto]

I have a requirement to display text in the cells of of a cross tab. I'm most of the way there but I just can't quite get it to work correctly.

The text item I want to display (SalesRepName) is held in a field in the table. When I select a numeric value from the the same table when populating the cross tab, it displays correctly, that is the number appears only in the cell it relates to e.g. $30 for Product1 in Jan appears in the intersection of Product1 (rows) and Jan (cols).

When I change the CrossTab Fact Cell properties to display a 'Data Item Value' and select the Data Item SalesRepName I get the the same name for every month whether or not a sale has been made in the month (each product is handled by only one SalesRepName)

I'm trying to decide if this is actually a logical representation of the data but I can't see how it can be. For example there is no record for Product1 in Feb and so no dollars are shown when displaying CellValue but the name still appears when using 'Data Item Value'

Working on the basis that this is the way it works:

1) Have I missed a setting?
2) Do I need to take a different approach ?
3) Should I just retire and take up basket weaving?

Tom

 

[almeids]

Tom,
This may be of no help (I don't know what your experience has been with ReportNet and I have no experience with C8 yet) but in case you're new to this there have been a number of inquiries regarding non-numeric data in crosstab cells in this forum and others. Thus far the bottom line is that there isn't any such functionality in ReportNet; your post is the first I've heard of the possibility in C8.
But I won't let my ignorance stop me from giving some input: if one COULD display text in a crosstab, one would have to be able to aggregate it across the crosstab dimensions. Do you have some sort of aggregation (presumably MIN or MAX) defined for SalesRepName?
I'm afraid that's my only suggestion. If this helps get you started on that lucrative basket-weaving career, please send me a basket.
Steve

 

[Vivarto]

Hi Steve,

I've been thinking this one through and I think Cognos have got it half right. A cross tab is like a section through a cube and there are very few cubes, if any, that support text. Being able to show any text in a cross tab is progress but I think Cognos are working on the basis that anything textual is dimension related and therefore applies across the whole row or column it relates to. Logically then the text item should appear in every cell in the row or column.

What I thought had was a textual attribute of the fact table, one that like a measure is uniquely identified by the dimension keys. After thinking about this for a while I can’t really come up with a real example of a textual attribute of the fact table. In my example the sales rep is actually an attribute of the product, i.e. an element in a dimension and so ‘should’ be shown across the rows. I say should as I think this could be an area where Cognos could bend the rules and say ‘only display the nominated Data Value Item where a cell is has a matching fact record’.

I’ve managed to produce the report using a convoluted kludge that involves conditional formatting and using the SalesRepName as a currency symbol. I’m going to start a discussion with Cognos and perhaps put in an enhancement request.

As for roll-ups they would have to be limited to non-arithmetic aggregates such as MIN, MAX etc.

Basket weaving career temporarily on-hold…

Tom

 

[almeids]

Currency symbol?! Now that's thinking out of the box, or basket as the case may be. I've heard mention of conditional formatting to decode a small predictable set of values to text but I'll have to add this to my just-in-case file.
thanks,
Steve