Finding Reports that use a Specific Hot File 1

[sharkatek]

Hi All,

Can anybody tell me how to (please answer any question you can):

1 - Locate all reports (and their location drive, folder) that use a particular hot file?

2 - Open an existing report and determine WHICH hot file it uses and the location (drive, folder) of the hot file?

3 - If you MOVE a report and the associated hot file, how can you tell the report to look at the new location for the hot file without rebuilding the entire report? (It is not an option for me NOT to move both the report and hot file.)

Thank-you in advance!
Sharkatek

 

[sharkatek]

more information:

I am using Cognos Impromptu v 7.1.339.0

 

[DoubleD]

1. You could create a script to save each report as an IQD file, then search through the text in the IQD file for the reference to the HotFile. Or I believe you can do a text search from Windows Explorer, against the IMR files to locate those referencing the HotFile.

2. You could open each report and look at the SQL to check whether it uses the HotFile, but I'm not sure that it would tell you what directory it is looking in for the HotFile. The reference to the HotFile is actually at the catalog level though.

3. Hotfiles are really catalog based, so you should just need to update your catalog.

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

DoubleD

 

[goofaholix]

I have the same need, though to look for reports referencing tables and Views rather than hotfiles.

>Or I believe you can do a text search from Windows >Explorer,

I've tried this, it doesn't work, Windows can't find the text string in the report file.

>You could create a script to save each report as an IQD >file, then search through the text in the IQD file for the >reference to the HotFile.

Is it possible to write a script that goes through all the report files in a directory? rather than hard coding each report name into a script. I know how to do the latter but it would be far too time consuming, if you have a script for the former you could share that would be great!

Thanks.

 

[DoubleD]

I believe you can do a text search from Windows Explorer

I did a "Containing Text" search using Windows Explorer in Windows 2K and was able to identify all IMR files with references to a specified database field name. Not sure why it isn't working for you.

I don't think I have a CognosScript version of cycling through all the files in a folder, but here's a snippet of VB code that should translate very closely.

' look for IQD files in current directory
Set fs = Application.FileSearch
With fs
  .LookIn = "C:\Data\"
  .FileName = "*.imr"
  If .Execute > 0 Then
    For I = 1 To .FoundFiles.Count
      strImr = .FoundFiles(I)
      intImrCount = intImrCount + 1
      ' Do what you want with the IMR file here.
    Next I
        
    MsgBox intImrCount & " IMR Files searched and" & Chr(13) & intMatch & " matches found."
  Else
    MsgBox "No IMR files found. Process ended"
  End If
End With

This chunk of code searches an IQD file for a specified string:

Function FindString(strIqd As String, strFindIt as String) as Boolean
On Error GoTo Err_FindString

    Dim strSql As String
    Dim strText As String
    
' Searches for text string within IQD file
    Open strIqd For Input As #1
    Do While Not EOF(1)
      Line Input #1, strText
      If InStr(1, strText, strFindIt, 0) > 0 Then
        FindString = True
        Exit Do
      Else
        FindString = False
      End If
    Loop

Exit_FindString:
    Close #1
    Exit Function
    
Err_FindString:
    MsgBox "Error #" & Err & Chr(13) & Err.Description
    GoTo Exit_FindString
    
End Function

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

DoubleD

 

[sharkatek]

DoubleD:

I appreciate your help and I tried to use your vb code. I had a problem with references on the .LookIn

It keeps telling me that it is not referenced. I am using Access 2000 VBA and instead of searching .imr files, I am trying to go through .iqd files (I would think it should work with either) - can you tell me which reference to use in order to get it to work?

I already have the following checked:
Visual Basic for Applications
Microsoft ActiveX Data Objects 2.1 Library
Microsoft VBA Extensibility 5.3
Microsoft Access 9.0 Object Library
Microsoft Office 9.0 Object Library
(also checked are every other Microsoft Office reference except for the Euro Converter)

What am I missing -- or does something else need to be loaded? I know that I do not have the capability to make an .mde file with this Access, so I may be missing some functionality or references.

Thank-you,
Sharkatek

 

[DoubleD]

Sharkatek,
Please post your code. I'm guessing it's having a problem with the your reference to the directory you want to search.

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

DoubleD

 

[sharkatek]

Thanks for helping DoubleD!

I am getting

Run-time error '438':

Object doesn't support this property or method

It stops on the .LookIn line below

* * Also, I know I cannot reference the file name as
.foundfiles.filename -- I have to use .foundfiles.item(index as long)
but do I have to define an index name for item myself and increment it or is there a better way?
The code never made it this far anyway.


--- my code ----

Private Sub cmdIQDSearch_Click()
Dim intMatch As Integer
Dim intIQDCount As Integer

' look for IQD files in current directory

Set fs = Application.FileSearch

With fs
.LookIn "C:\Documents and Settings\cognos\My Documents\IQD Files\"
.filename = "*.iqd"
If .Execute > 0 Then
For i = 1 To .foundfiles.Count
intIQDCount = intIQDCount + 1
If FindString(.foundfiles.filename, "enrv5620\Reports\") = True Then
intMatch = intMatch + 1
End If
Next i
MsgBox intIQDCount & " IQD Files searched and" & Chr(13) & intMatch & " matches found."
Else
MsgBox "No IQD files found - process ended."
End If
End With

End Sub


Function FindString(strIQD As String, strFindIt As String) As Boolean
On Error GoTo Err_FindString

Dim strSQL As String
Dim strText As String

' Searches for text string within IQD File
Open strIQD For Input As #1
Do While Not EOF(1)
Line Input #1, strText
If InStr(1, strText, strFindIt, 0) > 0 Then
FindString = True
Exit Do
Else
FindString = False
End If
Loop

Exit_FindString:
Close #1
Exit Function

Err_FindString:
MsgBox "Error #" & Err & Chr(13) & Err.Description
GoTo Exit_FindString

End Function

 

[DoubleD]

Try this, remove the last / in the LookIn Statement.
Use I to reference the index of your file.

With fs
    .LookIn "C:\Documents and Settings\cognos\My Documents\IQD Files"
    .filename = "*.iqd"
    If .Execute > 0 Then
        For i = 1 To .foundfiles.Count
        intIQDCount = intIQDCount + 1
        If FindString(.foundfiles(I), "enrv5620\Reports\") = True Then

The only reference I have that you don't show listed is:
OLE Automation

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

DoubleD

 

[sharkatek]

I removed the last \ and tried it again (I do have OLE Automation checked) -- same error.

 

[DoubleD]

How about using this instead:

Sub FindFiles(strPath As String)

Dim fsoSysObj      As Object
Dim fdrFolder      As Object
Dim fdrSubFolder   As Object
Dim filFile        As Object
Dim intMatch       As Integer
Dim intIqdCount    As Integer

Set fsoSysObj = CreateObject("Scripting.FileSystemObject")

' Get folder.
Set fdrFolder = fsoSysObj.GetFolder(strPath)

' Loop through Files collection
For Each filFile In fdrFolder.Files
  If Right(filFile.Name, 3) = "IQD" Then
    intIqdCount = intIqdCount + 1
    If FindString(filFile.Name, "enrv5620\Reports\") Then
      intMatch = intMatch + 1
    End If
    ' Do your search
  End If
Next filFile

If intIqdCount > 0 Then
  MsgBox intIqdCount & " IQD Files searched and" & Chr(13) & intMatch & " matches found."
Else
  MsgBox "No IQD files found - process ended."
End If

End Sub

I've heard people have had problems with Application.Filesearch.

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

DoubleD

 

[sharkatek]

thanks DD - It will be tomorrow before I get a chance to try this, but I'll let you know how it does!

 

[sharkatek]

DoubleD: THANK-YOU!!! This code worked beautifully -- I can now add code to save the report names to a file so I can go directly to them. I'll be able to use this all kinds of ways. I appreciate your help!

Sharkatek