Cognos/Misys  Impromptu modify a query from macro

Posted on 2009-02-13
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\","XXX","xxx","XXX","xxx",1

   Set objImpRep =  _


End Sub

Open in new window

Question by:BJ2005
    LVL 5

    Expert Comment

    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

    That sounds promising! Do you have an example that might show how to do this?
    LVL 5

    Accepted Solution

    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.
    LVL 5

    Expert Comment

    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

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Suggested Solutions

    Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
    This article describes some very basic things about SQL Server filegroups.
    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…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now