I found that this method sometimes leads to empty cells on the right hand side of the crosstab - I believe due to a bug in Cognos.
Here I describe another way to do it that works fine.
- Add an empty first row to the crosstab by bringing in Crosstab Spaces.
- Set the text of these... add one for each column on the left hand side of the crosstab.
- Set "Define Contents = Yes" for all the facts cells in this first row
- Unlock the editing (lock icon)
- Drag a Text Item into each member cell and set the text to be the column heading of each column in the top (horizontal) side of the crosstab
- Hide the topmost row of the corsstab by setting Box Type = None
a crosstab that looks like this:
| measure1 | measure2
----------------------------------------+----------+---------
dimension member 1 | dimension member 2 | <1234> | <1234>
+--------------------+----------+---------
| dimension member 2 | <1234> | <1234>
+--------------------+----------+---------
will look like
| measure1 | measure2
| |
-------------------------------------------+----------+---------
crosstab space | crosstab space | textitem | textitem
member1" | "member2" |"measure1"|"measure2"
----------------------+--------------------+----------+---------
dimension member 1 | dimension member 2 | <1234> | <1234>
+--------------------+----------+---------
| dimension member 2 | <1234> | <1234>
+--------------------+----------+---------
Posts
I have a cross tab report with multiple measures i want to get different aggregate caluculation at cross tab intersection for different measures at the bottom crosss tab intersection ?
ReplyDeletecan i get a text at the intersection , instead of numerical values? Is this possible?
ReplyDeleteSorry that your discribing seems too abstact to me. I still can not understand the graph you drew.
ReplyDeleteCould you please give it more detailed discription?
minereg,
ReplyDeleteI fixed up the tables in the post, it should be more clear now
This is a great blog..Keep up the great work...I had 1 question regarding the crosstab column headers. How do I get 2 crosstab spaces across the dimesion member, I just get 1 spanning the entire dimension list.
ReplyDeleteZephyr, thanks for the kind words. I don't know the answer to your problem; I'm not working on Cognos at the moment... so I cannot try it out
ReplyDeleteZephyr, I'm having the same problem. I tried splitting the big cell into several, but it said it couldn't split it because it wasn't a cell. If you find the answer, please let me know.
ReplyDeleteOk, the thing is working, but...
ReplyDeleteIf I press "next page" then there will be an error.. it go like this:
CCLAssertError:0:Fatal: CCL_ASSERT(runtimeInfo != NULL);RSV-SRV-0042 Trace back:RSReportService.cpp(802): CCLAssertError: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(239): CCLAssertError: CCL_RETHROW: RSReportServiceMethod::process(): pagingNextPage_RequestRSASyncExecutionThread.cpp(774): CCLAssertError: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(211): CCLAssertError: CCL_CAUGHT: RSASyncExecutionThread::run(): pagingNextPage_RequestRSASyncExecutionThread.cpp(824): CCLAssertError: CCL_RETHROW: RSASyncExecutionThread::processCommand(): pagingNextPage_RequestExecution/RSRenderExecution.cpp(593): CCLAssertError: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(264): CCLAssertError: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(64): CCLAssertError: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(331): CCLAssertError: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(163): CCLAssertError: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(281): CCLAssertError: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(287): CCLAssertError: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(281): CCLAssertError: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(160): CCLAssertError: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(281): CCLAssertError: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(122): CCLAssertError: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(331): CCLAssertError: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSDocAssemblyDispatch.cpp(281): CCLAssertError: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSDocAssemblyDispatch.cpp(331): CCLAssertError: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardRom/RSDataSourceExpression.cpp(123): CCLAssertError: CCL_RETHROW: RSDataSourceExpression::evaluateRSReportFunctions.cpp(1436): CCLAssertError: CCL_THROW:
the thing is that if you wont to have headers on each page (including measures area) Cognos can't make it for you...
maybe someone know how to handle these...
ŁM
to clarify the steps:
ReplyDeleteDrag the first Crosstab space into area of the first row in the Crosstab object. This will be the Crosstab space Fact column(s). Click the Space area just added, in the Properties pane under General set the "Define Contents" to Yes.
Now add additional Spaces to this new 1st row, one for each row column. For each Space, set the Property for Text Source as follows:
Source Type = Data Item Label
Data Item Label = [select the source attribute used in this column]
In the Data Item Property for this column be sure to give the column attribute a meaningful business name for the Label