Today is my first day using cognos! I have a report I'm using for an import into a third party program. I generate a username that is basically "firstname.lastname". The problem is I have a few duplicates (ex 2 "john.smith") which isn't allowed by the third party program. All I'de like to do is append a "2" to the second duplicate to make them unique. I'm having trouble figuring out how to do that. I've tried using something like "Running-count([username])" to see where is resets but it never seems to reset to "1" (maybe it can't do strings?). I dunno if theres a way to store the previous value in a variable or something? Suggestions appreciated!
OpherB 270003XT4A667 Posts
Re: Append character to a duplicate value?2013-12-04T00:28:48ZThis is the accepted answer. This is the accepted answer.
Welcome to the ball game!
This is going to be a "trial by fire" project for you. You should DEFINITELY get some basic Cognos Report Author training before going too much further.
Let's see how to go about doing this:
First, I don't think you expressed the desired solution properly. Your instructions would result in a "john.smith" followed by all duplicate records being labeled "john.smith2" - no matter how many there were! I suspect you want them all to have unique labels by appending the counter value to each. Fine.
The running-count() function does work on strings, but there are some background topics you have no experience with in the Cognos world: Summary functions look for group breaks to reset their values and by default all queries auto-aggregate at the lowest level of granularity. Also, the core function of Cognos Report Studio is to support a SQL engine, and ANSI SQL does not have a "previous value" concept.
The key to getting the count to reset is either to overtly group the report (which you don't want to do because the username will only appear on the first row for each group) or covertly group by telling the function when you want to reset it...like this:
running-count ([username] for [username]).
Then create another query calculation that tests, casts and appends the value:
if ([running-count]>1) then ([username] || cast([running-count], varchar) else ([username])
chazman113 270006UVH32 Posts