Filtering subset of subset

[teggett]

Hello all,

First time here, so be gentle.

I'm trying to run a report that attributes patients to the physician they have seen the most.  I feel like this is a rather basic problem, but for some reason I'm unable to wrap my head around how to best do this.  So here is what my data looks like:

Data Items:
Patient ID
Encounter ID
Provider ID

Patient ID	Encounter ID	Provider ID
1	1	1
1	2	1
1	3	2

With the eventual result of patient 1 being attributed to provider 1.  My logic is to add a data item that looks something like this:

Provider encounter count by patient
count(distinct [Encounter ID] for [Patient ID] for [Provider ID]) ****not functional, as cognos will not allow two for

after which I would filter by using:

maximum([Provider encounter count by patient] for [Patient ID])

I'm guess this is completely convoluted, so thank you in advance for any help you can provide.

[Lynn]

Ah, we are not so terribly fierce here  ;)  welcome to the forum

You should be able to specify multiple elements in the scope by using a comma separator.

Code Select Expand

count(distinct [Encounter ID] for [Patient ID] , [Provider ID])

Not sure if this will solve your problem because I don't know what that will be like nested inside your maximum function.

[teggett]

 :) Nailed it.  Thank you very much.  You just ended about 4 hours of research with one post.