Crosstab with attributes in cells not numbers

[GoWestGw]

I need to report options selected when purchasing a car.  I have 800 feature groups each with up to 999 categories that can be selected, but they can only select one within each group.

But for a simple example say I have 5 feature groups and 20 feature categories within each, of which they can select one.

What I want to see is something similar to

OrderNo  AB       AC       AD       AE       AF
1             AB001 AC001 AD002 AE001 AF001
2             AB020 AC019 AD005 AE019 AF008
3             AB020            AD002 AE007 AG020
etc.

Being unable to drop anything other than a number within a crosstab obviously I can't think of a way to do the above.  I was thinking of setting up each column header and using if/then/else logic within a report to bucket each value, however looking forward to setting up 800 buckets doesn't exactly fill me with joy!

I could setup a database table with 800 buckets but that's just as painful, and what if I were to get new Categories, I would need to do more work to add a column etc.  So can anybody think of an easy way to allow me tp produce the above report?

[CoginAustin]

You can add it to a cross tab. Just unlock it and drag the query items into the cell you wish.
you may wish to set a conditional format to only display if the value of cell > = 0 or you will get results like "AB" "AC" even if there is no data.

On the cells with data it will show "AB001"

Your cell should look something like
<ColumnHeaderQueryItem><Value>

[CoginAustin]

Here is a gosales example

Code: [Select]

<report xml:lang="en-us" xmlns="http://developer.cognos.com/schemas/report/1/"><!--RS:1.1-->
<modelConnection name="/content/package[@name='GO Sales and Retailers']/model[@name='model']"/>
<querySet xml:lang="en-us">
<BIQuery name="Query1">
<cube><dimension name="Product line"><level name="Product line"><item refItem="Product line" aggregate="none" key="true" isHeader="true"/></level></dimension><factList><item refItem="Production cost" aggregate="automatic" isHeader="true"/></factList><dimension name="Introduction date"><level name="Introduction date"><item refItem="Introduction date" aggregate="none" key="true" isHeader="true"/></level></dimension></cube>
<tabularModel><dataItem name="Product line" aggregate="none"><expression>substring([gosales_goretailers].[Products].[Product line],0,2)</expression></dataItem><dataItem name="Production cost" aggregate="average"><expression>[gosales_goretailers].[Products].[Production cost]</expression></dataItem><dataItem name="Introduction date" aggregate="none"><expression>[gosales_goretailers].[Products].[Introduction date]</expression></dataItem></tabularModel></BIQuery>

<BIQuery name="Query1_crosstab"><cubeReference refQuery="Query1"/><summary><rowEdge><level refLevel="Introduction date"><item refItem="Introduction date"/></level></rowEdge><columnEdge><level refLevel="Product line"><item refItem="Product line"/></level></columnEdge><dataCells><item refItem="Production cost"/></dataCells></summary></BIQuery></querySet>
<layoutList>
<layout>

<pageSet>

<page name="Page1">
<pageBody>
<crosstab refQuery="Query1_crosstab">
<style>
<CSS value="border-collapse:collapse"/>
</style>
<crosstabCorner/>
<crosstabRows/>
<crosstabColumns/>
<crosstabLevel refLevel="Introduction date"><textItem><queryItemRef refItem="Introduction date"/></textItem></crosstabLevel><crosstabLevel refLevel="Product line"><textItem><queryItemRef refItem="Product line"/></textItem></crosstabLevel><crosstabCell><textItem><queryItemRef refItem="Product line"/><conditionalStyle refVariable="HideProductLine"><style><CSS value="display:none"/></style><style refVariableValue="1"/></conditionalStyle></textItem><textItem><queryItemRef refItem="Production cost"/><style><numberFormat decimalSize="0"/></style></textItem><member refMember="Production cost"/></crosstabCell></crosstab>
</pageBody>
<pageHeader>
<block class="reportTitle">
<textItem class="reportTitleText">
<text/>
</textItem>
</block>
<style>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter>
<table>
<tableRow>
<tableCell>
<textItem>
<expression>AsOfDate()</expression>
</textItem>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<textItem>
<text>- </text>
</textItem>
<textItem>
<expression>PageNumber()</expression>
</textItem>
<textItem>
<text> -</text>
</textItem>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<textItem>
<expression>AsOfTime()</expression>
</textItem>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableRow>
<style>
<CSS value="border-collapse:collapse;width:100%"/>
</style>
</table>
<style>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
</page></pageSet></layout>
</layoutList>
<variableList><variable name="HideProductLine" type="boolean"><expression>[Production cost]&gt;0</expression><variableValueList><variableValue value="1"/></variableValueList></variable></variableList></report>

[GoWestGw]

CoginAustin,

I thought you had it, but having played with it for a few hours I still do not get what I want.  If I were to use  your example from GoSales I would like to see <Product Type><Production Cost> within the cells.

The GOSales data may cause you problems, but I can guarantee you that for my data there would only ever be one ProductType per ProductLine bucket, resulting in one row of data displayed per order.

For GOSales imagine that each on each order they will always order Camping Equipment and Golf Equipment, but only one type within each - this is what I have in my data.

I would then like to see

Order Number          Camping Equipment      Golf Equipment
123                           Lantern                         Irons
124                           First Aid                         Woods
125                           Lantern                         Woods

My real world would look like the following
Order Number          Engine Size          Interior      Wheels etc.
123                          1.6                        Leather     GoodYear185
124                          1.8                        Plastic       Michelin165
.....

[CoginAustin]

I'm confused. Can you explain in a bit more detail and maybe show what you want? I think what I gave you duplicated what you showed above and if it is not correct I would need to see exactly what you want.

Use gosales if you can.