Dynamic Crosstab rows and columns?

[patrickthatcher]

Howdy.  We've been tasked with creating a dynamic crosstab report where users can choose the rows and columns based off checkboxes and lists.  We currently do this without issue in a list report utilizing the same technique and hiding the columns using the render variable.   Is there an example I can use that would allow the same thing using a crosstab?

thank you

[R Ambre]

Hi,

Is your column/Row names are coming from single data Item?
For example : Measure data Item Having names of all measures ,So values of Measure data item would be columns in your crosstab

Or you have different data items for each column ?

[patrickthatcher]

The datasource is relational, so I will have individual measures and items

[MFGF]

Howdy.  We've been tasked with creating a dynamic crosstab report where users can choose the rows and columns based off checkboxes and lists.  We currently do this without issue in a list report utilizing the same technique and hiding the columns using the render variable.   Is there an example I can use that would allow the same thing using a crosstab?

thank you

Hi,

Sounds like you're trying to re-invent the wheel here? Cognos Workspace Advanced provides exactly this - the ability to choose which rows/columns/measures to include in a crosstab. Why not just propose this as the solution?

MF.

[briancole]

Hi,

Sounds like you're trying to re-invent the wheel here? Cognos Workspace Advanced provides exactly this - the ability to choose which rows/columns/measures to include in a crosstab. Why not just propose this as the solution?

MF.

I have had the same requirement as the OP, but sometimes you want to give users flexibility in a controlled environment as they aren't necessarily computer literate so you need to limit their interaction to changing a drop-down box and clicking apply.

Anyway, to have dynamic data items in a crosstab, you want to use token prompt data items. You basically have a data item which is in your crosstab and then you can re-point this data item "on-the-fly" to other data items.

Here is an example (written by someone else) that should help:

http://www.imn-unlocked.com/bi-creativecomputing/e_article001494065.cfm?x=b11,0,w

[patrickthatcher]

Brian is correct about a controlled environment.
Brian I've done the token bit on a list report and that works fine.  In fact it works fine on a crosstab as well with the exception that there is no render variable to hide the column(s) that will not be displayed.
I've tried setting the box type to None as others have pointed out, the metric columns shift while the headers stay in their original position

[briancole]

Hi Patrick,

Are you controlling the headers with a style variable linked to the user's selection as well? You'll need to set the box type to 'none' for both the crosstab cells and the crosstab titles which should stop the columns from being misaligned.

If this still doesn't work for your scenario, rather than hiding the columns\rows, you might have to hide the data items in those rows\columns instead, and format the rows\columns so it just looks like it isn't there any more. It's not the most elegant solution, but it works.

[patrickthatcher]

Thanks Brian.  Figured out Style Detail part.  Now to figure out move the headers that don't move when the columns are hidden.

[patrickthatcher]

Well I gave it the good college try and trying to code around the crosstab limitations won't work.  Now time to try to convince the business to use Workspace Advance!
Thank again for your help everyone.