Need to pass a parameter to cognos reports Ver 6.0 1

[JohnPfox]

I am using MSAccess as an application server to run Impromptu reports. I would like to automatically pass parameters to Impromptu to simplify the reports for the users. Currently, I am using a basic shell command:

Dim RetVal
RetVal = Shell("C:\Program Files\Cognos\cer1\bin\ImpAdmin.EXE S:\DATA\NAIS\Catalogs\Active\110.imr", 1)

This works well, but I would like more automation. Do you have any suggestions?

 

[DoubleD]

Here's a bit of code I use to run Impromptu Reports from Access. You can pass all the appropriate variables.

Sub RunImpromptuReport(sCat As String, sRpt As String, sFile As String, _
    Optional sPrompt As String, _
    Optional sCatUserId As String = "User", _
    Optional sCatPwd As String = "", _
    Optional sDbUserId As String = "", _
    Optional sDbPwd As String = "")
    
On Error GoTo Err_RunImpromptu

Dim objImpApp As Object
Dim objImpRep As Object

Set objImpApp = CreateObject("Impromptu.Application")
Set objImpRep = CreateObject("Impromptu.Application")

'Open impromptu catalog
objImpApp.OpenCatalog sCat, sCatUserId, sCatPwd, sDbUserId, sDbPwd
'Check if successfully connected to database
If Not objImpApp.DatabaseConnected Then
  MsgBox "That UserId and password are not valid for Impromptu." & Chr(13) & "Please try a different UserId and Password combination."
  GoTo Exit_RunImpromptu
Else
  If sRpt = "" Then
    GoTo Exit_RunImpromptu
  End If
End If

objImpApp.Visible -1
'Open Impromptu Report, passing prompt values
Set objImpRep = objImpApp.OpenReport(sRpt, sPrompt)
objImpRep.RetrieveAll
objImpRep.ExportExcel (sFile)
objImpRep.CloseReport
objImpApp.Quit

Exit_RunImpromptu:
  Set objImpRep = Nothing
  Set objImpApp = Nothing
  Exit Sub

Err_RunImpromptu:
  MsgBox Err.Number & ": " & Err.Description
  Resume Exit_RunImpromptu

End Sub

I am what I am based on the decisions I have made.

DoubleD

 

[JohnPfox]

Thanks DoubleD!

This is a great start. I will use this as a starting point in creating user specific solutions using cognos. It definitely works and is flexible enough to customize.

 

[DoubleD]

Yep, I've had really good luck with it. The only piece I haven't played with yet is setting up a variable for the output format.

I am what I am based on the decisions I have made.

DoubleD