Filtering a crosstab post-pivot

[fulekia]

Is it possible to apply filters to the rows of a crosstab report after the pivot is applied? For example, imagine a crosstab where each row represents an item in a workflow, the columns represent some subset of the states we care about for this particular report, and the measure is the amount time an object spent in that state.

So data like this:


SOURCE DATA

| Item | State | Time |
|------|-------|------|
| 001  |   A   | 10.7 |
| 001  |   B   |  3.3 |
| 002  |   A   |  1.0 |
| 002  |   B   |  6.6 |
| 002  |   C   | 11.7 |
| 003  |   B   |  9.6 |
| 003  |   C   | 14.7 |


Would result in a crosstab like this:


CURRENT OUTPUT

| Time |  A   |  B   |  C   |
|------|------|------|------|
| 001  | 10.7 |  3.3 |      |
| 002  |  1.0 |  6.6 | 11.7 |
| 003  |      |  9.6 | 14.7 |


I want to filter out rows for items that were never in state A (let's say those followed a different path through the workflow), to get this result:


DESIRED OUTPUT

| Time |  A   |  B   |  C   |
|------|------|------|------|
| 001  | 10.7 |  3.3 |      |
| 002  |  1.0 |  6.6 | 11.7 |


Is there some form of summary filter that can be applied to specific column values once they're been grouped via the pivot (a 'la the after aggregation option)? I've played around with all manner of filters and conditional formatting as suggested in the threads I found, but nothing seems to work.

Edit: I forgot to mention that this is using a relational data package.

[fulekia]

I've not yet found a way to do this, but in case anyone stumbles onto this thread, my workaround is to pivot the data manually, then format the results as a list report, not a crosstab. This allows normal filters to do what I wanted, as the data are all on one row per result.

I've done this using the native oracle PIVOT keyword in a SQL query object. One of my coworkers also suggested splitting the data into multiple queries, each returning the data for one column of the crosstab (e.g., SELECT Item, Time WHERE State = 'A'), and joining them all together into a final query. That works pretty well when you have a small, known set of column values. If there are a lot, I'd probably pivot the data before feeding it into Cognos.

Either way, the workaround is to squish the data into a list. You can then play with fonts and headers to make it look like a crosstab, if that's what your customer expects.

I'm still holding out hope that there's a magical "Process Filter Post-Crosstab" checkbox somewhere...

[Robl]

Did you look at summary filters in the Query?

A summary filter of "A is not null" should do what you want.