RSV-RND-0016 Reports with more than 256 columns can not be rendered in Excel.

[myd3p]

Hi,

I would appreciate if any one can help me on any resolution in terms of 256 columns limitation when downloading a report to an excel. My report is a crosstab that has 8 months as the top column, each month has 4 major column and each major column is breakdown into to 3 columns each. So ideally when you look at the lowest level it should only have 96 columns but when I extract the report it returns an error of 256 columns limitation in excel. I'm not sure if Cognos treating the 1 and 2 row as columns during download. Any help is great!

-------------------------------------------------------------
                    |                             1C 1..8                         |           
-------------------------------------------------------------
                    |          2C1..4               | ...                         |
-------------------------------------------------------------
| H1 | H2 | H3 |  3C1 | 3C2 | 3C4 | 3C5 | ...                         |
-------------------------------------------------------------
  XX    XX   XX    XXX   XXX   XXX   XXX
  XX    XX   XX    XXX   XXX   XXX   XXX
  XX    XX   XX    XXX   XXX   XXX   XXX
--------------------------------------------------------------
Sub Total:        XXX    XXX   XXX   XXX
--------------------------------------------------------------
Total:              XXX    XXX   XXX   XXX
--------------------------------------------------------------

[omgyme]

myd3p,

Your information suggests that Cognos may not be finding data that should be summed to be equivalent.  What I mean is that data that you think should be rolled up into your various columns and summarized is being seen as distinct values.  The reason I suggest this is because as you say, if everything was grouped correctly you would only have 96 columns at the most.  It sounds like in your join logic somewhere there is a 1..n where there should be a 1..1 or some other many to many relationship that is causing a Cartesian product.

I work mostly with relational models, so if you're on a dimensional model or a cube my suggestions may be irrelevant. /shrug

3 ideas:

1) Flip the columns and rows in your crosstab and re-run the report to see all values that are actually being found when Cognos compiles your crosstab. 


                         | H1 | H2 | H3 |
--------------------------------------
1C1 | 2C1 | 3C1  |
1C1 | 2C1 | 3C2  |
1C1 | 2C1 | 3C3  |
1C1 | 2C2 | 3C1  |
1C1 | 2C2 | 3C2  |
1C1 | 2C2 | 3C3  |

2) Create a list with your data and see what values are returned in the fields you are summing.

3)  Go to the Query Explorer in Report Studio and make sure each of your source queries are set to "Auto Group and Summarize = Yes" in the properties pane under your insertable objects.