[Last Call] Learn how to a build a cloud-first strategyRegister Now


Unique Users During Date Range (Cognos)

Posted on 2009-04-27
Medium Priority
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
  • 3
  • 2
LVL 12

Expert Comment

ID: 24244234
Does something like:

count([PatientID] for report)

not work?

Author Comment

ID: 24244454
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

RWrigley earned 2000 total points
ID: 24246854
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

ID: 24250610
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

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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses
Course of the Month18 days, 8 hours left to enroll

825 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