Consider a Case
where we have a Health insurance plan which requires each individual to have a
Primary care physician (Family doctor) assigned. To do this we have a automated process ,
During first run of the process a person does not get doctor during next run he
gets a doctor assigned.
So below is how the
data looks in table
|
Member name
|
Provider key
|
Automated process
run id
|
|
Dhoni
|
-1
|
1
|
|
Gambhir
|
11
|
1
|
|
Virat
|
12
|
1
|
|
Sehwag
|
-1
|
1
|
|
Dhoni
|
14
|
2
|
So from the above we
can say that Out of 5 member 4 have
doctors assigned . So below is how calculation will look like
Case when
provider_key <> -1 then member name --- Member with doctors
But we cannot say that case when provider_key = -1
then member_name -- Member without doctors
because then count would be two dhoni in first run did not have a doctor
assigned
What we need to do
it Total members ( that is 5 ) minus
Members with doctors (that is 4) = 1
Now consider we need
to show this on a Chart . Count of people with doctors and Not with doctors as
stacked graph . This is not possible directly because there
we have two calculation and each one would appear as a column and for
stacked graph , only one column should have both values of the member with
doctor and without doctor.
If we could write
the calculation like below it would be possible
Case when provider
key <> -1 then 'Member with doctor' else 'Member without doctor' end
But due to our logic this is not possible so we need
to go for Union on Cognos. This union wont have performance impact as we are
union summarised results
Query for Member
with Doctor and Count as two field
Union
Query for Member
without Doctor and Count as two fields
