I am migrating a report from Cognos 7 that contains a DATASET (where cars are the color blue) wherein my main detail filter says that a column in the main report (type) DOES NOT equal a column (type) in the DATASET.
However, I am unable to get my Cognos 8 report to filter out all blue cars of a certain type. I have tried creating a Query2 wherein the same data exists that the Cognos 7 DATASET had, then filtering Query1 column (type) to NOT equal column (type) in Query2. I always get a Cross Join error and cannot seem to make any progress in accomplishing my migration.
If I run the Cognos 8 report while keeping the previous Cognos 7 DATASET logic out, all extra rows returned in Cognos 8 report are solely those rows in which the previous dataset would have successfully filtered out thus creating identical Cognos 7 & Cognos 8 reports.
I'm spinning my wheels here....
Hi,
Have you joined the two queries together in your report? If so, how is the join currently specified (ie is it an equi-join)?
MF.
Well, I attempted to created a Query2 that mimics what the C7 DATASET would produce. I am then going into Query1 and using a detail filter to join a column from Query1 with an equivalent column in Query2.
In my model, I have attempted to join the two tables in question as well as not join them and I either get "No Data Available" or a cross-join error.
Thanks for your help!
Dennis
Hi,
Detail filters do not constitute joins between queries in a report in Cognos 8. Go to the Query Explorer and drag in a join from the toolbox, then specify its properties.
Regards,
MF.
Having just finished a big Cognos 7 to 8 migration, I've always found the following to work on relational packages.
1. Create a query representing your "dataset" of rows to exlcude (ie. all the blue cars), including whatever attribute you need to filter the main list by. Assume that this query is called "Dataset" and the data item that represents the rows to be removed is called "CarID"
2. Create your detail query (called "Main"), including the CarID column.
3. Add a filter expression in the "Main" query
[CarID] not in ([Dataset].[CarID])
I've never found joining to be necessary to replicate the dataset functionality.
Good Luck
J
JGirl, that is exactly what ended up being the solution. I created one query that specifically returns only blue cars and called the query, Query1. I then went back into my Main query and created a detail filter stating that I do not want a column in Main query to equal the same column in Query1.
One of those things that can be done in line code much easier than a GUI interface like Cognos 8.
Thanks for your help!
Dennis