Ungroup crosstab

[terry_stjean]

I have a cross tab in Report Studio defines as follows:


Warehouse    Sku                                              2013/09/23    2013/09/30 .....
  01                A1223             Beg Balance                 xxx                xxx   
                                             Onhand                        xxx                xxx   
                                             Open Order Qty            xxx                xxx
  01                B17886           Beg Balance                 xxx                xxx   
                                             Onhand                        xxx                xxx   
                                             Open Order Qty            xxx                xxx

What the user wants is this:
Warehouse    Sku                                              2013/09/23    2013/09/30 .....
  01                A1223             Beg Balance                 xxx                xxx   
  01                A1223             Onhand                        xxx                xxx   
  01                A1223            Open Order Qty            xxx                xxx
  01                B17886           Beg Balance                 xxx                xxx   
  01                B17886           Onhand                        xxx                xxx   
  01                B17886           Open Order Qty            xxx                xxx

I've come across a number of messages with suggestions on how to "ungroup" but they work fine if the report is based on 1 measure.
I have 3 measures in the rows and I haven't been able to figure this one out.

Any help would be great.

[adik]

add the column containing the most granular data to be the first column in the crosstab, and then setting the box type of that column to none

[BigChris]

I think in the latest version (10.2.1 ?) you can optionally ungroup a column.

[terry_stjean]

Hi,
We are on 10.2 but I don't see any option to ungroup anything.
For the suggestion about adding the lowest level of granularity.
The lowest level are the measures in the rows.
If I add sku to the first column, it will ungroup the whse but I still get the grouping for the sku with the 3 measures.

[adik]

you shouldn't get any grouping of the sku. can you post a screehshot without the box type = none property set?
you can smuge out the numbers to keep your data privacy

[terry_stjean]

I have attached a sample of the report.
Each Sku and warehouse spans over the 4 measures.
I would like each warehouse and sku duplicated beside each of the 4 measures.
the user wants to be able to open this in Excel and filter on either the warehouse or Sku.

[adam_mc]

Firstly, use the search function within Cognoise to see if your issue has been resolved before

Must give credit to CognosPaul for this solution.
Just used this myself and it worked great!
It's now saved in my Tips/Techniques Folder.

Generic Solution:

Your crosstab looks like this:


   Corner               | <#Columns#>
-----------+------------+------------
<#Node 1#> | <#Node 2#> |    <#1234#>


Drag two crosstab spaces directly before the Columns, and in the new intersections set Define Contents to Yes. By default these items won't have column titles, but for the purpose of this demonstration I'm calling them Space 1 and Space 2


   Corner               | Space 1 | Space 2 | <#Columns#>
-----------+------------+---------+---------+------------
<#Node 1#> | <#Node 2#> |         |         |    <#1234#>


Unlock the report and drag a text item into intersections of Space 1 and Space 2. You don't need to type anything into them. Click on the text item in the intersection of Space 1 and change the Source Type to Data Item Value. Set the Data Item Value to Node 1. Do the same for the Intersection with Space 2.

Finally click on the Crosstab Corner and set Box Type to None. Click on the rows, select anscestor -> Crosstab Rows and set Box Type to None.

If these instructions made any sense your crosstab should look like this now:

Space 1  | Space 2  | <#Columns#>
----------+----------+------------
<Node 1> | <Node 2> |    <#1234#>


And it should function exactly as you wanted.

It's also worth mentioning that you can use this method for getting around the problems with right-to-left (rtl) crosstabs in PDF. If you have a crosstab in Hebrew or in Arabic, and set the text direction in the cell behind it to rtl it will still appear as left-to-right in pdf. Just use this method but stick Space 1 to the right of Columns.

[terry_stjean]

Hi,
I actually come across this earlier this morning and it works great.

[Lynn]

Yup, Paul is star and I've got his original response on this bookmarked:
http://www.cognoise.com/index.php/topic,11363.msg37230.html#msg37230

I read in the notes for the latest release that there is a new option on export to Excel that allows you to specify whether you want the merged cells or not. I haven't tried it yet since we have not yet upgraded, but for anyone on the latest version I'd look to that if the intent is only to avoid merged cells in excel format.