Impromptu 6.1 Filter Bug? 7

[DrSimon]

I have encountered a totally bizarre bug(?) and I'm wondering if anyone else has seen anything like it. I have an Impromptu (6.1) report that uses data from 2 hotfiles together with data from a database. The report extracts employee payments for various elements by cost centre. The normal filter is a statement with 4 ANDs in it.
When I run the report normally, it only reports one element for a particular employee instead of 8. However if I then add to the filter to be more selective using e.g. employee number or cost centre, the correct number of elements appear for the employee. What is more bizarre is that the one element that does appear for this employee seems to be selected at random for every session of Impromptu.
I have checked that the underlying data has not been changed and as far as I know it only seems to be effecting one employee's data.
Any ideas would be gratefully received becuase this report took a long time to produce and has been useful up till now.

Simon Rouse

 

[BruceReed]

Is it possible you are filtering on the wrong field? I have done it several times as Impromptu is not always helpful in showing the full name of a data element.

EX. filter is set to (employeeFile-->)EmployeeNum, instead of (SalaryFile-->)EmployeeNum.

Impromptu (unless you look at the SQL) does not show the file name, so what you think is happening, is not really happening.

Just a shot in the dark, hope it helps.
Bruce

 

[DrSimon]

Nice try Bruce, but no. I've spend many hours (weeks) checking this report before I encountered the problem. The really odd thing is that it happens at random and doesn't effect all employees. It goes to support my aphorism: "Computers are only human after all"

Simon Rouse

 

[CognosProfessional]

Maybe something to do with the file path names of the hotfiles being refreshed in the catalog vs. the report you are now using. Check all path names leading to the filter. You can verify this in the SQL disconnected from the catalog with the .imr open.

Just a thought...

CP

 

[DrSimon]

Thanks CP, but nothing obvious. I've decided that the simpleest thing is not to waste any more time on this and develop the report using Access.

Thanks both for trying

Simon Rouse

 

[BruceReed]

If you have not yet thrown in the towel, I was going to suggest that you post the SQL of the working query, and the incorrect query & I would look at it for you.

Maybe you should take your computer out for happy hour & it would behave better!!

Tkx
Bruce

 

[DrSimon]

That's very kind of you Bruce, but the towel has hit the floor now

Regards
Simon Rouse

 

[benbotektips]

I used to print the .icr (impromptu content report), and look thru scores of tables hunting for the rite filed name. However, why break your eyes like that? This sounds all too familiar, and is #3 of my top 10 aggravations in Impromptu. It used to drive me crazy, as all those hours of my life, wasted trying to figure out which table is the field referring to, are never coming back.
Bizarrely, hidden away buried in the last 2 pages in the pdf documentation for step-by-step Impromptu catalogs, and in a totally inappropriate place, is a powerful, time-saving tool. To run, simply go to the field in the report you're trying to figure which table it's coming from, hit the 'edit definiton' button, click once on the expression (to hilite it), then hit the F1 pfkey. What pops up is a window with the fully qualified field name, table source name and all. It's not perfect, as:
1) you may have to start it up, by going to the taskbar 'start' button, going to 'run', then type in the following:
"C:\Program File\Cognos\cer1\samples\Impromptu\Macros\Annotationserver.exe".
That's rite, this time-saving tool is in the sample directory!
It doesn't always work fast enough, you'll sit there and it'll look like nothing's happening, but eventually you'll hit pf1 and the window will pop up
2) the window size is fixed, and the text is truncated, so if you have a long table name, you might not see the field name itself.
3) it'll be working, you try it later, then it's suddenly not working. Run task manager, and look in applications & processes, find 'annotation server.exe', kill it, then the window will ressurect.
80% of the time, this hidden, valuable tool will save hours of work, in place of dragging your eyes over the .icr (impromptu content report).
for further clarification, search the Cognos knowledge base, 'annotation server'

 

[DrSimon]

benbotektips my mouth is open in astonishment at this - well worth a star. It worked but ..... yes you've guessed it some of the paths and names are too long - is there anything to be done?

 

[iguanadon]

Holy cow, what a useful tool! All of these years crossing my eyes looking at the SQL or .iqd .....

 

[benbotektips]

DrSimon,
Here's a couple of trix I've done to get the fully qualified field name form annotation server, when the window is too small. As far as I know, the window is not sizable, so u gotta work on the text in it.
WARNING!!! I've found Cognos objects to be very volatile, so if u experiment on any cognos object, catalog, report, model, whatever, first make a backup copy, and experiment on the backup. I've corrupted catalogs, xformer models, & reports when I forgot this.

1. experiment with changing the qualification of the tablename in the catalog. Open the backup catalog (and connect), then goto 'tables'. There are 3 tabs. In the 1st, 'tables', ensure that the database pointed to is the rite one. Goto the 2nd tab, and here's where u shorten the qualified name. Find the database or schema name, and click the button for 'qualify less'. This will shorten the path name in the annotation server window when it pops up, and you'll see more. However, u can use the partial name as a search string 'index' into the .icr, anyway. It's still better than scanning the whole .icr
2. If this won't work, create a test database named 'x', and alias the table names, with a short name like 'a', 'b', etc., from the tables, except the ones you'll know it's impossible the field came from, paying careful attention to recreating the joins as needed.
Then, make a backup copy of the report in which u need the table.fieldname , go to the 'report - general' option, and point this report backup's catalog to the backup copy of the catalog u created for testing (I know it sounds confusing, let it percolate for a minute - in short, every experiment on the backups). Do the test on the hilited field, and u will see something like 'x.a.fieldname from table a '.
1 of these will do the trick.

 

[DrSimon]

Thanks again benbotektips. I'll consider your suggestions.
Simon Rouse