Allow duplicate names in crosstab

[ddusse323]

Hi All,

I'm currently using Cognos 10.2.2 FP1, and I'm trying to accomplish something I thought would be easy, but so far that hasn't been the case.

I have a simple crosstab that has the names of users for the rows.  Everything works well until I have two users with identical names.  Understandably, the two users are combined into one and their measures are aggregated into one value since the name is all the crosstab is given to identify the users by.

Obviously, since the two users with identical names are separate users, I would like them to be listed on separate rows instead of combined into one.  Each user does have a unique UserID and GUID, so I'm trying to figure out how I can use either of these (UserID or GUID) to force the crosstab to treat the two users as two fully separate rows without the UserID or GUID appearing in the crosstab, and without the two users still appearing as one name and only the measures being split into two rows.  The two rows, including the names, need to be completely separate.

Currently, if I have two users with the name Joe Blow, this is what I get:
         
                          |  Measure1  |  Measure 2  |
--------------------------------------------------------
Harry Smith        |        5         |          8         |
Sally Brown        |        2         |          3         |
Joe Blow            |         8        |           9        |
--------------------------------------------------------

What I need is this:

                          |  Measure1  |  Measure 2  |
--------------------------------------------------------
Harry Smith        |        5         |          8         |
Sally Brown        |        2         |          3         |
Joe Blow            |        3         |          5         |
Joe Blow            |        5         |          4         |
--------------------------------------------------------

I thought I had successfully dealt with a similar issue once before in the past, but if I did, I don't remember how I did it.  Thanks for any insight anyone is able to give.

David

[cognostechie]

Cognos is grouping by the user name. The ideal solution would be to ask one of the Joe Blows to change his name. However, since life is strange, every ideal solution is not possible to implement always. In such a situation, you can do this:

1> From the Query Explorer, select the query and add the User ID as a Data Item. This way the User ID will be part of the query but will not appear in the Crosstab (Layout)
2> Go to the Page and click on the rows of the crosstab.
3> In the properties window on the bottom left, you will see a property called 'Properties'. Click on that and select User ID.

Now the report should show the two users separately because Cognos would add the User ID in the group by clause. I haven't tested this here because we don't have two users with the same name but it should work.

[ddusse323]

Hey cognostechie, thanks for your reply.  I tried the name change option, but neither Joe Blow was too keen on the idea, and HR didn't want to deal with the extra paperwork it would involve anyway  ;)  So I decided to try the other option you mentioned.

What you suggested makes perfect sense and seems like it should work, however, it isn't.  After adding the User ID to the query and the Properties property, the resulting code (Show Generated SQL/MDX) isn't doing the expected Group by.  But as much as I've been messing with this report the last couple of days, I can't help but wonder if there may be something amiss in my settings or data that I've overlooked.  I guess before I spin my wheels too much more, I think I'm going to recheck the entire report structure just to make sure there isn't some other underlying issue that might be causing it to get unpredictable results.  If all else fails, I may see if I can just get one of the Joe Blows fired  ::)

Thanks again for your suggestion.  I'll let you know if I figure anything out.