Is this doable entirely from Cognos?

[Knight]

Hi!

I have to "port" a report which was done in an application at work to Cognos 11 reporting.

I am not sure what is the proper naming for this in Cognos 11 but I am doing a normal report in what would have been called Report Studio in Cognos 10...

Now the report I have to do is unlike the ones I have done in the past and I am unsure if this can solely be done in Cognos or if I must resort to doing a view on the Oracle server and do my Cognos report against that view.

Essentially I have to compare records with a status that indicates they have been processed and are ready to be sent to records which have a status that indicates that they have already been sent.

I have to compare a few fields of the processed records to the same fields of the ones which have already been sent to see if they match (or are included in it) so that someone can validate if the processed records are duplicates (or included) of the ones which were sent previously.

I initially thought I might put filters in the query of the processed records which referred to the query of the send records but Cognos complains because it is unable to determine the relation between the two.

I have the SQL that was used to initially make this report so I could make it into a view (or directly use the SQL in Cognos) but I keep on wondering if there is a better way.

Is there a way to do this from Cognos without resorting to SQL or a database view?

Thank you and have a nice day!

Nick

[Reinhard]

Hi,

you can join queries in a report or filter one query by another. Not completly sure what you want to do but comparing a data item of a result set with another data item of a different result set is no problem.

Regards,
Reinhard

[Knight]

Hi and thank you for replying!

you can join queries in a report or filter one query by another. Not completly sure what you want to do but comparing a data item of a result set with another data item of a different result set is no problem.

There is nothing I can join those queries by...

Essentially I must avoid resubmitting something which has already been submitted...

I must compare a few fields of records which have just been processed but not yet submitted to see if they either match past submitted records or could have been included in them...

Both set of records are in the same underlying database table, only their status are different...

Each of these records have unique reference numbers and my report must show the reference number of the just processed record and of the past submitted records (amongst other things).

If this gives you a better idea of what I have to deal with imagine these are invoices and my report must show invoices which have just been processed which appear to be duplicate of past submitted invoices...

Thank you and have a nice day!

Nick

[Reinhard]

Why not filter by that status field?

If you have a query with all records and you have a status than you can show all records without status easily or just form two queries where one is filtered by the other (without any joins).

[Lynn]

An "intersect" can interrogate the records from two queries to return rows that are duplicates. An "except" returns rows from one query except those that exist in another query. Would either of these work for your situation?

If you look in the toolbox when you are in the Queries folder you'll see Intersect and Except.

[sdf]

Both set of records are in the same underlying database table, only their status are different...

Can you describe your "status" column?
If they are in the same table, we have to understand a way to differentiate the submitted to the new ones.

I have to compare a few fields of the processed records to the same fields of the ones which have already been sent to see if they match (or are included in it) so that someone can validate if the processed records are duplicates (or included) of the ones which were sent previously.

Can you provide sample output of what your goal is?


cheers!