I'm not sure if I can explain this as well as an image might help. I'm looking for a way to use a crosstab query and have any blanks that are repeated be filled in (no nesting).
The end result is because I have to export to excel and do much more analysis then cognos can do (or have the time to deal with the complexity).
I've attached a sample excel file, first with what Cognos puts out, and second what I really want it to look like. This would help me tremendously.
Notice how cognos will not repeat region 1 (named ALPHA) on the next line down, but there are two customers that make up ALPHA. I want the 1 and ALPHA to flow down to the next line (as seen on the bottom of the spreadsheet).
Have attached the work around for your requirement.
Hope it helps?
Thanks
Prit
The Crosstab can be made to look like a list by repeating the grouped items throughout the Column. It can be done in following two ways:
For Example: Consider a Crosstab containing Product Line, Product type and Product Name.
1) Drag the lowest level data item throughout, for the rows of the crosstab.
eg. If report has Product Line, Product Type, Product Name then drag Product Name which is the lowest level, thrice (1 for each level) to the rows of crosstab. Now except for the lowest level, change the Source Type from the properties pane, for the previous two columns to Data Item Value - "Product Line" for first and "Product Type" for second.
The crosstab would now show the three levels as -
Data Item - Product Name; Text Source - Product Line
Data Item - Product Name; Text Source - Product Type
Data Item - Product Name; Text Source - Product Name
When executed it resembles like a list.
2) Make another Data Item as a concatenation of the all the levels (in your case two levels). Place this data item 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 the concatenated data item would be:
[Product Line]||[Product Type]||[Product Name]
The above example is a generic one, for your case I guess, the highest level is that of REGION and Lowest of COMPANY.
The concatenated data item expression can be like : [Region]||[Company]
Please let me know if any of these was helpful.
Amruta and Prit, both worked as far as I can tell on my much more complicated reports. I obviously had to add more crosstab spaces and/or concatenates then my example. Thank you both.
I do have one small issue though. Amruta, your concatenate version is the quicker/simpler of the two, but when you select the concatenated box type as NONE to hide it, it actually moves all columns over by one and misaligns the column headings. Perhaps there is another step I missed? Other than that, if I don't hide it I can simply delete it when exported to excel.
Hi Prox,
I missed out the note on the second implementation. Slight change on that....Drag the concatenated data item before your lowest level i.e. Company. Now change its source type, to data item value - Region, which is your highest level.
So now the report contains two levels:
First is the concatenated data item ([Region]||[Company]), the source type of this is changed to the highest level [Region].
The second level is your lowest level i.e. Company.
Hope this works fine. Let me know if it has any issues.
PRIT, from you attachment, how do you undo the box type once you've hidden them?
Find attached screenshot.
Check 'Show hidden Objects'. Now you can see all the columns defined as Box type=None. Just remove it.
Prit that worked great.
Thank you both so much. I have good uses for both of the methods you have demonstrated to me. I really appreciate your help. I talked to IBM Cognos teachers directly and they said it was impossible. I just couldn't believe it. Thank you very much.
Glad to hear that.
Well, IBM guys are someone like you and me, must not be knowing all the techniques.
Cheers
Prit