Union All in report

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