Cognos/Misys  Impromptu modify a query from macro

Posted on 2009-02-13
Medium Priority
Last Modified: 2012-06-22
I have a large number of reports I am trying to automate in Impromptu. One thing I need to be able to do is substitute parameters, which I can do no problem EXCEPT if it is an IN filter like this:
IN (1,2,3,4,5)
It appears to not be legal to do this:
IN (?Parms?)
There is a workaround that Cognos provides, but you have to know the number of items in your list ahead of time - I have a variable number of items. I was hoping that there would be a way to use GetFilterFor (or something similar) and somehow programatically change the filter and set it back.

This is what I tried that doesn't work - see 01,02,03 as a parameter below.
Sub Main()
   Dim objImpApp As object
   Dim objImpRep As Object
   Set objImpApp = CreateObject("CognosImpromptu.Application")
   objImpApp.Visible 1
   objImpApp.OpenCatalog _
      "C:\data\Misys\Query Workspace\Catalog\MQuery02.cat","XXX","xxx","XXX","xxx",1
   Set objImpRep =  _
End Sub

Open in new window

Question by:BJ2005
  • 3

Expert Comment

ID: 23649716
The only way I have gotten around this is to set up a table with the values for each report and then used the table for filtering.   Not sure how you are capturing the value currently but this might work for you.

Author Comment

ID: 23649858
That sounds promising! Do you have an example that might show how to do this?

Accepted Solution

welchb123 earned 2000 total points
ID: 23650245
I have not doen this for a while but from memory I did the following.  I started with a spreadsheet and exported that into a DBASE file that I turned into a hot file.  That way I could replace the hotfile via a script whenever I wanted.  The users would create the spreadsheet with the values they wantd to select and then in the evening a script would run read the excel sheet export to dbase and create the hot file.  Then the reports would run using these values as a filter.  

Creat Hotfile
1) Added the values to an excel spreadsheet.
2) In excel create a DBASE file by saving the excel file as Dbase IV file.
3) Open Cognos imporptu administrator
4) Go to the Catalog menu item , select Databases, select ODBC and click New Database button.
5) In the Database Definition - ODBC dialog box click Administration buttion
6) In the ODBC dialog box click System DSN tab and then click Add button
7) select Miccrosoft dBAse Driver
8) In ODBC dBAse Setup dialog box type in name and select dBAse IV version.  Click OK
9) In ODBC Data source dialog select your new data source and click OK
10) In Database Definition dialog box type name of new data source and uncheck user id and password prompts.  Click test and get a successful connection message.
11) Click OK
13) Now you have a catalog with one table
14)  Create a new report against the new catalog and save as hotfile.
15)  Open your starting catalog and add the new hotfile.
16)  Select Joins from the catalog menu, and join the field you want to filter on to the field in the hotfile.

Now you have a filtered report based on the values in the spreadsheet.  Note: I actually had to create two versions of my reports one to use the hotfile and one to run outside the script.  I typed this from memory so I may have missed a steo but I think I got them all.

Expert Comment

ID: 23650278
I also found this on the cognos web site on adding a hot files to an existing catalog

How can you add a hotfile to an existing catalog that was based around another database such
as Oracle or Microsoft SQL Server.

Solution Description

There are two parts to this process:

1. Enabling the 'Retrieve Tables' button:
a. Click on the Tools menu item.
b. Select Options from the Tools drop-down menu. An options dialog will appear.
c. Ensure that the 'Retrieve Database tables when editing catalog tables' option is
d. Click OK

There will now be a 'Retrieve Tables' button in the Tables Dialog Box.

2. Adding the Hotfile(s):
a. Click on the Catalog menu item.
b. Select Tables from the Catalog drop-down menu. A Tables dialog will appear.
c. Ensure that the 'Edit' property tab has been selected.
d. Select the Hotfiles option in the lower left-hand corner of the Tables Dialog Box.
e. Browse to the drive and directory location of the Hotfiles.
f. Click OK
g. Click on the 'Retrieve Tables'. You will see the hotfiles located in that previously
selected directory.
h. Add the Tables or Columns that you want to include in the catalog.
i. Click OK. There will be folders generated based on the Hotfile(s) you have included.

You have now successfully added the Hotfile(s) to your catalog and will now have to setup the join structure. If you want to remove the 'Retrieve Tables' button, repeat the steps
in part 1, Enabling the 'Retrieve Tables' button and ensure that 'Retrieve Database tables when editing catalog tables' is unchecked.

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question