Weird Issue: "Missing" data!!! Hot(Smart)File related?

[JawKnee]

I'll try my best to explain this behavior that I'm experiencing.

I've written a report that relies on another report, i.e., a SmartFile.

This report, let's call it Report A, essentially returns sales figures and other metrics on a per Product Line per Supplier basis.

The SmartFile, let's call it Report B, contains other metrics and is also on a per Product Line per Supplier basis.

The main report, i.e. Report A, has 1 filter condition (a detailed filter) similar to the following: Report A.Product Line = Report B.Product Line.

Once I make this join or bring in data from the SmartFile, I lose at least 1 row/record (a supplier that only has 1 product line). There could be other missing data but I haven't gone searching yet.

Now get this, if I were to add a prompt to select a Supplier to this report (Report A), and specify the supplier that is being dropped or is missing, the record then shows up! No prompt, it doesn't show up.

Finally, this missing Supplier is returned in Report A without a need for a prompt if I don't join to the SmartFile (i.e. Report B).

I hope that wasn't confusing. We ran across this exact same issue about a year ago that was not solved.

 

[GJParker]

I don't understand why yuo have the filter Report A.Product Line = Report B.Product Line in your report ? if you are using a hotfile the joins for this should be made in the catalog and not the report.



Gary Parker
Systems Support Analyst
Manchester, England

 

[griffindm]

Jawknee,

One of the limitations of "Smart Hotfiles" as a report source, in my experience, is that they operate only with inner-joins. I have been unable to have them respect an outer-join, regardsless of whether it is defined in the catalog or report. The result is that data that has no corresponding value in the "other" side of the join disappears from the report. You may want to check to see if this is what is happening in your circumstance.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[JawKnee]

Gary,

In short, I don't want to create a join to a Hot/SmartFile in the catalog for now.

Dave,

Please re-read my initial post to see how weird this issue is. With the inner join to the Smart/HotFile, one particular Supplier record is dropped. However, if I create a prompt to prompt for a supplier and select the Supplier that is "missing" or being dropped, the record then shows up!

Removing the join to the Smart/HotFile, all suppliers show up. So it has to do with the join to this Smart/HotFile but I don't see how yet. And for what it's worth, the missing supplier is indeed in the Smart/HotFile.


Regards,

Johnny

 

[cindyw]

We had a similar issue, and it took 4 months working with Cognos to finally diagnose. The data does not show, but you filter and it does show.... It was intermittent, but on some reports, it ALWAYS occurred.

Are you using Oracle 9.2.0.3? That is what caused our issue. If your have Oracle 9.2.0.3, you'll need to make sure your client machines have oracle 9.2.0.1 with the oracle 9.2.0.3 patchset applied.

 

[JawKnee]

Cindy,

Thanks for the insight. We're using SQL Server 2000 SP4 as our database, which we connect to through Impromptu with an ODBC connection.

Regards,

Johnny

 

[JawKnee]

A correction.

I meant SQL Server 2000 SP3, not SP4. The database server has MDAC 2.7 and my client had MDAC 2.7 as well. I just upated my client to MDAC 2.8 while leaving the server at 2.7 and still no luck.