Topic
  • 2 replies
  • Latest Post - ‏2013-12-04T14:03:36Z by chazman113
chazman113
chazman113
2 Posts

Pinned topic Append character to a duplicate value?

‏2013-12-03T21:06:04Z |

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
    OpherB
    664 Posts

    Re: Append character to a duplicate value?

    ‏2013-12-04T00:28:48Z  

    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])

    Good luck,
    Opher

     

  • chazman113
    chazman113
    2 Posts

    Re: Append character to a duplicate value?

    ‏2013-12-04T14:03:36Z  
    • OpherB
    • ‏2013-12-04T00:28:48Z

    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])

    Good luck,
    Opher

     

    Your the man thanks! I just needed the "for [username]" part and it started working!