Crosstab question, how to take 'group span' off

[bi4u2]

On a list report I can remove the 'group span' if I want to show a grouped value on every row. Is there something I can do for a crosstab? I have a crosstab with 5 items nested as rows and we would like to see all the data on each row.

Thanks!

[wyconian]

Hi crosstabs are really designed to aggregate data, I don't think there is any way of taking off the group span.  Why did you make the decision to use a crosstab rather than a list?

[bi4u2]

We are using a crosstab because we need to see a time dimension on the columns. It is a timesheet report to show hours by date - with dates across the top.

[wyconian]

OK so whta do you have as your rows and measures.  Can you give an example of what you're getting and what you'd like to get?

[bi4u2]

My rows have groupings such as Project ID, Project Name, Employee ID, Employee Name, Labor Category.

The users were interested in seeing these values on each row so that they can export to Excel.

[wyconian]

cool what are your measures?

Basically if you want to see all data you need to bring the lowest possible level of grain into the crosstab. i.e. if your data source records the measures at 'labor sub category' (for example) you will need to bring that into the rows.  The crosstab will then aggregate to that attribute but as it's the lowest attribute you will end up with a detail report.

Does that make sense?

[Lynn]

Attached is a technique that might be what you are after. It is done with a relational source. Not entirely sure how it would work with a dimensional source.

[CognosPaul]

Another solution:

Hide the crosstab rows. Drag two crosstab spaces directly to the right of the corner. In the facts for the new columns, set "Define Contents" to yes. Unlock the report, drag the fields from the query into the fact cells. Hide the column headers for those two new fields OR hide the crosstab corner.

I wrote a slightly more verbose explanation here.

[bi4u2]

Thank you Lynn and everyone. My data source is relational and Lynn's technique is exactly what I was looking for.

[Lynn]

I just had to do this with a dimensional source and the approach I posted (which uses concatenation of data items) only works for a relational source.

Paul's approach is MUCH simpler and works beautifully for both relational and dimensional sources.

[MFGF]

Rumour has it that Paul is a bit of a genius! I hope he doesn't hear us saying that, though - it will go to his head and he might turn into an evil Bond-type villain with a silly laugh and aspirations of ruling the world! :)