need help with Hotfiles 1

[KellyK]

Hi all,

In a similar post, I described a problem in which I could not programmically select all members of a file picklist in an Impromptu report. As a workaround, I'm looking at setting up a hotfile. I have a hotfile which contains all "approved carriers" for my company. I then want to run a report that shows all shipments carried by a vendor NOT in our approved list. I've added the HotFile column 'Vendor ID' to my catalog. Now in my report filter I want to say something like
... and Carrier ID not in ( Vendor Id ) ...

but it only compares the Carrier ID to the FIRST id in my Vendor ID list. How do I get it to search the entire hotfile for the ID before it goes to the next one? I apologize for the simplicity of this question, I am new to Cognos and HotFiles.

Thanks in advance for any tips.

 

[CognosProfessional]

. I then want to run a report that shows all shipments carried by a vendor NOT in our approved list. I've added the HotFile column 'Vendor ID' to my catalog. Now in my report filter I want to say something like
... and Carrier ID not in ( Vendor Id ) ...

but it only compares the Carrier ID to the FIRST id in my Vendor ID list. How do I get it to search the entire hotfile for the ID before it goes to the next one? I apologize for the simplicity of this question, I am new to Cognos and HotFiles.

You say you added the column to your catalog and not the report as a column also? If you have this column in your report, that could be the problem. You must choose the Vendor Id from the Hotfile directly from the catalog.

When you choose the Carrier ID, are you choosing it from the catalog directly or a column in the report? You must choose it from the catalog columns for the filter. This should search the entire hotfile for the ID and bring back all but that is in the hotfile, but what do you mean "goes to the next one"?

CP

 

[KellyK]

Sorry for being unclear.

I added a field to my CATALOG called Vendor Id. This field is pulled directly from the hotfile which contains all Vendor Ids for "approved carriers". I then have a separate report in which I want to compile load information for shipments carried by vendors NOT approved by my company. In my report I have a field called Carrier ID. This field is the same format as the Vendor Ids in my catalog (from the HotFile). However, this Carrier ID field contains ALL carriers that have moved a load. Now I want to filter within the report to only show carriers who are not on the approved list. My filter is like this:

... and Carrier Id not in ( Vendor Id )

Therefore, I only want to see Carrier Ids that are NOT in the Vendor Id (Hotfile -> catalog column).

After running the report I see many Carrier Ids for vendors who are in the HotFile (approved carriers) so I know something is going wrong. I try a test report where I look for only the vendor that is first in my HotFile and sure enough, it does not show on the report. I then change the order of the vendors in my HotFile so that a different vendor is on top and after running the report a second time, THAT vendor is not on the report. Thus I've concluded that when comparing Carrier Id to the Hotfile column Vendor Id, it only looks at the first entry in the HotFile and then moves on. I'm not sure what's going on here, as this is the first time I've attempted to work with a Hotfile. Any help is appreciated!

 

[griffindm]

Kelly,

I know we've passed posts on this before. Have you tried to use your first report (the one that creates the Hotfile of approved vendors) as a DATASET for the final report. A dataset allows you to use query results from another report as a filter for a second report. The syntax would be:

and Carrier Id not in ( DATASET )

In defining the dataset, you pick your original report, then tell Impromptu which column in the report to use in the filter. The only caveat is that both reports have to share the same catalog.

Give this a try. If it works right, you've also skipped the step of having to create the hotfile.

Dave Griffin

 

[vijayleads]

Kelly,

The Dave approach is good and you can certainly try that. However you may want to work with the file picklist by defining a notepad file with all the vendors in it. If this list has more than 100 then go to your impropmtu.ini file and change the setting under [Startup Options]
to picklist limit=500
in case you have 500 distinct vendors.

You can then define a prompt for users to work with. Just a little tip you can add a character A to the vendors that are approved so the users that run a report can pick all the other vendors by using the cntrl key on their keyboard.

Try this and see if it works for you.

VJ

 

[vijayleads]

Kelly,

The Dave approach is good and you can certainly try that. However you may want to work with the file picklist by defining a notepad file with all the vendors in it. If this list has more than 100 then go to your impropmtu.ini file and change the setting under [Startup Options]
to picklist limit=500
in case you have 500 distinct vendors.

You can then define a prompt for users to work with. Just a little tip you can add a character A to the vendors that are approved so the users that run a report can pick all the other vendors by using the cntrl key on their keyboard.

Try this and see if it works for you.

VJ