Unique Users During Date Range (Cognos)

Posted on 2009-04-27
Last Modified: 2012-05-06
I have a report I'm trying to make in Cognos 8.2 that I'm having trouble with.

I need to count how many patients visit our clinics during a period of time.  I don't need to know home many visits they have had, just the unique patients.

Because I'm filtering during periods of time, I'm getting duplication of patient id's for those patients seen more than once during that period of time.  I need something to behave like a SQL "First", "Last", "MAX" or the like to just give me unique patients.  Right now I can't accurately count the number of patient id's to get a unique list because of this duplication.

Question by:IntercareSupport
    LVL 12

    Expert Comment

    Does something like:

    count([PatientID] for report)

    not work?

    Author Comment

    I've been trying a variant of that all morning, but I don't think I have the syntax/concept right.  I was trying  count([PatientID] for [Date]) and things like that, but that left me with the same problem of duplication.

    How do I refer to the whole report?  You wrote just "report".  Is that a reserved word, or just your example?  I imagine it's the former, but I'm trying to understand.

    Attached is a screen shot of my query screen.
    LVL 12

    Accepted Solution

    Count([Patient_ID] for report)

    should do it.  Set both the aggregation types to "Automatic" or "Calculated".

    If that still doesn't work, try:

    count(distinct [Patient_ID] for report)

    You normally shouldn't need the distinct clause; if that's the only way you can get this working, you'll probably want to revisit the FM model, becuase somethign hasn't been designed correctly.

    Author Comment

    Well, it sort of works.  The aggregation seemed to be the piece I was missing.

    Now I've messed up the counts for the final output.  The final total is correct, but it's not breaking down by grouping.  I tried

    Count([Patient_ID] for report, [Site])

    But that just errors out.  How can I recreate my groupings?

    Author Closing Comment

    Thanks for your help.  I figured out the other part of my subsequent question, but you answered my initial question.  Thanks!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
    How to increase the row limit in Jasper Server.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now