COGNOiSe.com - The IBM Cognos Community

I have a crosstab in which obviously the rows are grouped. So:

Item A         Item B           Item C        Value 1        Value 2
1                 1.1               1.1.1          Bleep           Blop
                                      1.1.2          Bleep           Blop
2                 2.1               2.1.1          Bleep           Blop
                   2.2               2.2.1          Bleep           Blop
                                      2.2.2          Bleep           Blop

Is it possible to get it like this?
Item A         Item B           Item C        Value 1        Value 2
1                 1.1               1.1.1          Bleep           Blop
1                 1.1               1.1.2          Bleep           Blop
2                 2.1               2.1.1          Bleep           Blop
2                 2.2               2.2.1          Bleep           Blop
2                 2.2               2.2.2          Bleep           Blop

I did find the link http://www-01.ibm.com/support/docview.wss?uid=swg21342062 but that only seems to be applicable to csv's

Hi,

Select each grouped column and set the 'group span' property of each to be Item C.

Regards,

MF.

Thanks! I have been looking for a setting like this one, but wasn't able to find it.

Now that you mention the 'group span' in specific I strarted looking for that one, but I can't find it. As soon as I select (for example) "Item A" that should be visible right?

Sorry - I just noticed this is a crosstab not a list.  The Group Span property applies to grouped columns in list reports.

Apologies - I've been very distracted of late!

MF.

Nevermind that! I appreciate the sole fact that you take the time to respond here.

But I guess for cross tabs this is not possible? If that's the case: not a problem.

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.

Paul, you are a star!!

Thanks Paul a million!!!!
I've been looking for a way to get to this for some time...
Cheers,
Mario

Could be a stupid question, in the above scenario why can't we go for a LIST report rather forcing the Cross-tab to act as a List?

Thanks
P

 hi paul,
i tried for my crosstab that
i got error like this
RSV-RND-0136

      The data item reference in the crosstab space is not valid.


appreciate any help

thanks
brijon

Ungroup crosstab columns for Parent child scenario

Stepwise Instructions are as follows:
Approach1
Step 1.To get started, let's create a simple Crosstab report with the following fields:
As shown in image

Step 2.You need to create a new data item to concatenate the highest level of detail and lowest level of detail to do that go to the query create a data item concatenating the Product name and Product line using below expression:
[Product line]+[Product name]
In this case assuming Product line is highest level and Product name is lowest level

Step 3.Unlock crosstab node member containing Product line and add this data item before the Product line as shown

Step 4.Data Item1 | Product Line | Product name And then hide the data item1, with the Data Item1 selected go to properties pane and set the visible to No
The basic idea is to bring the highest level of detail into the lowest level of detail so that the new effective granularity shows in detailed manner overriding the crosstab default grouping.

Step 5.Apply the desired sorting on the report
This technique works with any number of nested rows as long as the concatenation is done between highest & lowest levels. Make sure they both are varchar or string data types. If there are nulls for any of your rows, replace them with any character.


Approach 2

Make another Data Item as a concatanation of the three levels.Place them as the first level of the crosstab and make its box type as none to hide it.
eg. For the above case the expression for data item would be:
[Product Line]||[Product Type]||[Product Name]

Approach 3
You can use Javascript-
Add an HTML item before the Block Crosstab and add the code <div id= 'listBlock'> in it.

Add another HTML outside the after the block and add the following script in it -
</div>
<style>
.lockColumnHeaderlist{
top: expression(parentNode.parentNode.parentNode.scrollTop);
position: relative;
z-index:11;
}
</style>

<script type="text/javascript">
  var listDiv = document.getElementById('listBlock');
  var reportTable=listDiv.firstChild.firstChild;
       if(reportTable.tagName=='TABLE'){
          var headerRow=reportTable.firstChild.firstChild;
          headerRow.className='lockColumnHeaderlist';   
       }
</script>

But you wont be able to use it in excel

Hope this helps  :)

hi arpit,
thanks for reply
but in  first two approch it not work
please tell any one i use relational package

thanks

 
hi arpit,
while i prforming
Approach1
got that error
The operands of '[Product line] + [Product]' cannot be converted to consistent dimensional object.

appreciate any help
thanks
brijon

While creating the data item can you check for the datatype of both the columns before concatenating.I have used the same for 3 dimensions in one of my projects and it works

hi arpit
i go through your
Approach1

in my crosstab i have
location|description|wonum
i create data item [LOCATION]+[DESCRIPTION]+[WONUM]
uses in location & apply visible no
this is enough or some another steps required
actually i not understand meaning of your
Step 5.Apply the desired sorting on the report

thanks

HI ARPIT,
finally your approach 2 work for me

thanks a lot  :D

thanks
brijon

you are welcome,although both approach 1 and approach 2 are kind of similar the idea is bringing the top level column in the crosstab to the lowest level and just hiding the lowest level

hi arpit
i go through your approach 2 it works
thanks

:D

Just an additional question closely related to this:

I need to concatenate three properties of three different dimensions and sort that by a value in one of the columns.  I've struggled for a day and a half trying to figure out how to do this without COGNOS coughing up a hair ball.

I second the comment that Paul is star!

Now, to finish this off, how do we sort the new crosstab result based on one of the data columns?

Suppose there are three columns A,B,C and you need to sort the report based on Column A For this you can sort this column first by Conatinated data item ie A||B||C Then by the corresponding column A

Arpit,

I have tried your approach one and the server is responding with an out of memory.  Just to clarify a couple of points.  I am reporting from a cube.  Also, you do mean to insert the data item into same cell of the first crosstab node?  Mine looks like this after doing so:

|  <di_LocClientCombo><set_Location> | <set_Client> | measure |

And then set the <di_LocClientCombo> visible property to no.

Correct?

For the data item, di_LocClientCombo, I added the two hierarchies to one another:

[PLSummTM1].[acctLocations].[acctLocations].[Locations_Code]+[PLSummTM1].[acctClients].[acctClients].[Clients_Code_Num (AS)]

Is this what you meant?

The first thing to remember is what Cognos does when you use any string functions on a cube. It dumps everything into a temporary file, then queries that with Cognos SQL. Click on tools -> View generated SQL to see what I mean. You should only be able to view MDX when querying a cube, if you can select Cognos SQL then there's local processing going on. This temporary file is stored in memory. And that is probably why you're getting the out of memory error.

Now there's no reason you can't use a layout expression to concatenate the value on the crosstab.

The crosstab should have 3 nodes, all nested. The outer node should be the level you're sorting on:

sort([Cube].[Dim].[Hier].[Level],[Cube].[Dim].[Hier].[Level].[Attribute],DESC)

The other two nodes should be the levels of the dimension you need. Make sure all three attributes are dragged into the query. For each node, click on the properties row in the properties (yes I know, it's confusing. Someone needs to be smacked at IBM for that) and select the correct attribute for that level.

Now drag in the crosstab space to make the blank area, and drag in a layout calculation. Perform the concatenation and any other string functions inside the layout calculation. If the space is blank when the report is run, make sure the attributes are correctly selected in the properties.

It's a bit late for me, so I'm not sure how much sense any of that made. Good luck!

Quote from: PaulM on 05 Nov 2010 08:25:54 AM
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.

Hi Paul,
When tried using this method the dimensions are not getting displayed in Space1 and Space2. They are blank. But the measure and Column values are getting displayed and the measure is aggregated to one row since the Space1 and Space2 is blank. Please provide your help in this scenario.

Can you post a screenshot? I'm having trouble visualizing the problem.

Hi Paul,

I tried your approach 2 and it is working fine. However the columns headings are moving to right when I hide the first row. Could you please let me know how can we avoid this.

Regards,
Veera

You should remember to hide the crosstab corner as well. That is the usual reason for things getting moved around.

Hi CognosPaul,

I have a  question on the solution that you have provided, When I drag a Crosstab Space directly before the column, the measure of the report disappear even after I change the Define Contents to Yes. , its like blank. The   format of the report looks like . What could be the issue? Please advise.

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


 

It's odd that it's causing the data under <#Columns#> to disappear. Is it possible that it's somehow inserting under the columns node? At worst you can recreate the <#1234#> by dragging a text item into the intersections and changing the Source Type to Cell Value.