Macros in Cognos

[jayramaswamy]

Hello,

I am writing a Macro in which I defined a DropComboBox to list 4 reports. The user can select one report and clikc 'Ok' to view the report.

The following is the code and I am trying to get the selected recrod from the dopdowncombobox. How do I get the value from it ? Help will be highly appreciated. The following is the code.. . .

************8
'******************************************************************************
'*
'* Open Report.mac
'*
'******************************************************************************

Option Explicit

Sub main()
Dim objImpApp As Object
Dim strReport As String
Dim answer As Integer
Dim DropListBox1() As String
Const ReportPath = "C:\KPICatalog\"
Const ReportOne = "NationalOverview.imr"
Const ReportTwo = "OrderInfo-Main.imr"
Const ReportThree = "OrdersPerHour.imr"
Const ReportFour = "SalesOrderLine-Detail.imr"
Const ReportFive = "TotalSales-AllSites.imr"
On Error GoTo ErrorRoutine

Set objImpApp = CreateObject("Impromptu.Application")

'if Impromptu is not running visibly, end the macro
If objImpApp.Visible = True Then
'do nothing
Else
MsgBox "Please open Impromptu and run the macro again."
objImpApp.Quit
Set objIMpApp = Nothing
Exit Sub
End If

ReDim DropListBox1(4)
DropListBox1(0)= "Sales-National Overview"
DropListBox1(1)= "Orders-Main Report"
DropListBox1(2)= "Orders Per Hour"
DropListBox1(3)= "Sales Orders-Detail"
DropListBox1(4)= "Total Sales-All Sites"

Begin Dialog UserDialog 166, 100, "xxxxx, MI, USA"
Text 9, 3, 69, 13, "Reports:", .text1
DropListBox 9, 14, 97, 119, DropListBox1(), .DropListBox1
OKButton 115, 6, 45, 14
CancelButton 115, 26, 45, 14
End Dialog

Dim mydialogbox As UserDialog
answer= Dialog(mydialogbox)
if answer = -1 Then
MsgBox "You pressed OK" & "Sales-National Overview"
elseif answer = 0 Then
Goto Done
End If

'objImpApp.Activate
'Set objImpApp = Nothing

Done:
Exit Sub
ErrorRoutine:
MsgBox "Error Number: " & Err & " occurred at line " & Erl
Resume Done
End Sub

 

[flex13]

Here's a ssnippet from a macro that I have that does pretty much what you are looking for.

For x=0 to (Options - 1)

DropListBox1(x) = PPRep.DimensionLine.Item(x+1).Name

Next x

Begin Dialog UserDialog 186, 62, "Dimension Filter"
Text 8, 4, 42, 8, "Select Dimension:" , .Text3
DropListBox 8, 16, 95, 44, DropListBox1(), _
.DropListBox1
OKButton 124, 6, 54, 14
CancelButton 124, 26, 54, 14
End Dialog

Dim Dimension as UserDialog
On Error Resume Next
Dialog Dimension

'*** Sets selection to correct index value ***
DimIndex = Dimension.DropListBox1 + 1

 

[jayramaswamy]

Thanks for your valubale time and the reply. But, this is not really helping to solve this.

All that I have is a dropdownlistbox and there are couple of reports. When I select a report and click 'OK', it should run the relevant report.

Thanks,
jay

 

[drlex]

Jay,
flex was giving you a pointer as to the selection of the report.
Here is some code that will offer up a list of all impromptu reports in a folder (upto 100) and then run it. Amend as necessary.

Option Explicit

Sub main()
   Dim objImpRep        as object
   Dim objImpApp        As Object
   Dim strReport        As String
   Dim answer           As Integer
   Dim DropListBox1()   As String 
   Dim strfilelocation as string
   Dim strfileprompt as string
   Dim strfilename(100) as string 
   Dim Directory, Count 
   Dim X as integer
   Dim reportnumber as integer
   
   On Error GoTo ErrorRoutine
   
   strfilelocation = "k:\User Reports\"     'address on server drive of Impromptu reports
   directory=Dir (strfilelocation & "*.imr")    'way to get file names of just IMR reports

   '
      Do While directory<>""
      count=count+1
      Strfilename(count)=directory
      Strfilename(count)=Left(strfilename(count),Len(strfilename(count))-4)
      directory=Dir
   Loop
   Close #1        'close file   

   ReDim DropListBox1(100)   
   For x=0 to (count - 1)
    
     DropListBox1(x) = strfilename(x+1)
   
   Next x
   
   Begin Dialog UserDialog 186, 62, "Reports available:"
      Text  8, 4, 42, 8, "Select Dimension:" , .Text3
      DropListBox  8, 16, 95, 44, DropListBox1(), _
         .DropListBox1
      OKButton  124, 6, 54, 14
      CancelButton  124, 26, 54, 14
   End Dialog

   Dim Dimension as UserDialog
   answer= Dialog(Dimension)
   if answer = 0 Then Goto Done
   On Error Resume Next

   '*** Sets selection to correct index value ***
   Reportnumber = Dimension.DropListBox1 + 1

   Set objImpApp  = CreateObject("Impromptu.Application") 
   objImpApp.Visible 1
   objImpApp.OpenCatalog "k:\catalogues\test.cat","Creator",,,,1
   Set objImpRep = objImpApp.OpenReport (strfilename(reportnumber)+".imr")
   msgbox "done"
   objImpApp.Quit
   Set objImpApp = Nothing

Done:
   Exit Sub
ErrorRoutine:
   MsgBox "Error Number: " & Err & " occurred at line " & Erl
   Resume Done
End Sub

soi la, soi carré