Alternatives to using prohibited crossjoin

[sbelli]

The report I'm working for an insurance company isn't pulling the correct data in a drilldown despite having the same filters. The reason I think this is an issue is that I'm using a coverage field, but also pulling prescription information from another table. The prescriptions do not have any correlation to the coverages except that they all belong to the same applicant id. I think what's happening is that even with a join on applicant id, Cognos is displaying prescriptions without coverages and vice versa. I've tried combining filters, etc.

My thought to eliminate this issue was to create a second query with just the applicant ids and the coverage ids with the filter that I require for the coverages. Then connect the two. Ideally I would have used something like this:

select applicant id from Query 1 where applicant id is in (select applicant id from Query 2)

But I can't write a filter with that condition and using an Query1.applicant_id = Query2.applicant_id as a filter to the main list report causes a crossjoin not permitted error. I know you can set this off in Report Studio, however, our team that runs the Cognos servers will not allow this to occur so I still get the same error.

Any suggestions on how to get around this?

[RKMI]

Hi,

Ok, trying to understand you tables/ join.

So you have three tables Prescription which is joined to Applicant and that is Applicant is joined to Coverage table is this a correct assumation?

If not this correct then you should be able to build query 1 with Applicant/ Presciption and query 2 with Applicant/ Coverage/ Plan type . Then create query 3 with joining the two tables on applicant id than create your list with Coverage with applicant id and list of the applicants prescription.

Note: Cross join error would only if you were to add a filter with query 2. appid = query 3.appid. So just avoid the filter and let the nataural inner join on applicant id should take care of it.

Thanks,
RK