Copy Metadata information to notepad or excel 1

[Srikam]

Can any one please help me out!!!

I want the metadata(tablename.queryitems)used in an imr file to be exported either as a notpad or excel file.
Will any macros do?

Thanks in Advance...

Srikrishnan

 

[hindsjl]

Srikrishnan -

This method is strictly manual, but it usually works.

From the Impromptu menu, open the report and then go to Report, Query, Profile, SQL. If the report is relatively simple, it will resolve to a single SQL statement which you can manually copy and paste into Notepad or elsewhere. (Unfortunately, Impromptu sometimes handles more complex reports as two or more SQL statements, and occasionally won't show you any SQL at all. But it's usually worth a try.)

It would be cool if the Report object in the macro language included a property containing the SQL statement(s), but I don't recall seeing one, and I don't have the product in front of me today.

As for an item-by-item list of metadata, most of that information is unfortunately stored in the catalog. The report just stores pointers to the catalog.

Hope this helps,
John

 

[Srikam]

John,
Thanks for the reply.I know the manual process of doing this.But there are lot of reports available ,so i can't do it manually.
Actually i have a macro script that gives metadata information,but in a msgbox format.
i want that information in a notepad or text format.
This is the macro.

Sub Main()
Dim objImpApp As Object
Dim objImpCat As Object
Dim objImpRep As Object
Dim objQueryItemsCol As Object ' collection
Dim objFolderItemCol As Object ' collection
Dim objTempFolderItem As Object
Dim objMetaDataItem As Object
Dim i, count As Integer
Set objImpApp = GetObject("CognosImpromptu.Application")
'objImpApp.Visible True
Set objImpCat = objImpApp.ActiveCatalog
Set objImpRep = objImpApp.ActiveDocument
Set objQueryItemsCol = objImpRep.QueryItems
Set objFolderItemCol = objImpCat.Folders(1).Items ' get children of 1st folder
MsgBox "The first folder is " & objFolderItemCol(1).Name
count = objQueryItemsCol.count
MsgBox "QueryItems.Count = " & count
For i = 1 To count
Set objTempFolderItem = objQueryItemsCol.Item(i).FolderItem
Set objMetaDataItem = objQueryItemsCol.Item(i).MetaDataItem
If (objQueryItemsCol.Item(i).FolderPath = "") Then
MsgBox "[ Query Item #" & i & "]" & Chr$(13) & Chr$(13) & _
"Name = " & objQueryItemsCol.Item(i).Name & Chr$(13) & _
"Type = " & objQueryItemsCol.Item(i).Type & Chr$(13) & _
" *** FOLDER ITEM NOT FOUND *** " & Chr$(13) & _
"MetaDataPath = " & objQueryItemsCol.Item(i).MetaDataPath & Chr$(13) & _
"MetaDataItem.Name = " & objMetaDataItem.Name
Else
MsgBox "[ Query Item #" & i & "]" & Chr$(13) & Chr$(13) & _
"Name = " & objQueryItemsCol.Item(i).Name & Chr$(13) & _
"Type = " & objQueryItemsCol.Item(i).Type & Chr$(13) & _
"FolderPath = " & objQueryItemsCol.Item(i).FolderPath & Chr$(13) & _
"FolderItem.Name = " & objTempFolderItem.Name & Chr$(13) & _
"MetaDataPath = " & objQueryItemsCol.Item(i).MetaDataPath & Chr$(13) & _
"MetaDataItem.Name = " & objMetaDataItem.Name
End If
Next i
End Sub

Thanks in Advance...

Srikrishnan

 

[hindsjl]

Srikrishnan -

Cool! I think you've helped me more than I can help you. But I'll try.

It appears that you want everything that follows the word "MsgBox" written to a text file:

...
Else
MsgBox "[ Query Item #" & i & "]" & Chr$(13) & Chr$(13) & _
"Name = " & objQueryItemsCol.Item(i).Name & Chr$(13) & _
"Type = " & objQueryItemsCol.Item(i).Type & Chr$(13) & _
"FolderPath = " & objQueryItemsCol.Item(i).FolderPath & Chr$(13) & _
"FolderItem.Name = " & objTempFolderItem.Name & Chr$(13) & _
"MetaDataPath = " & objQueryItemsCol.Item(i).MetaDataPath & Chr$(13) & _
"MetaDataItem.Name = " & objMetaDataItem.Name

Cognos may have other methods for working with text files, but you should be able to use Scripting.FileSystemObject
and Scripting.TextStream from the Windows Scripting Runtime library. Something along the lines of

Dim objFS As Scripting.FileSystemObject
Dim objTS As Scripting.TextStream
Set objFS = CreateObject("scripting.filesystemobject&quot
Set objTS = objFS.OpenTextFile( _
FileName:="output.txt", _
IOMode:=ForWriting, _
create:=True, _
Format:=TristateMixed)

[loop]
objTS.WriteLine "[ Query Item #" & i & "]" & Chr$(13) & Chr$(13) & _
"Name = " & objQueryItemsCol.Item(i).Name & Chr$(13) & _
"Type = " & objQueryItemsCol.Item(i).Type & Chr$(13) & _
"FolderPath = " & objQueryItemsCol.Item(i).FolderPath & Chr$(13) & _
"FolderItem.Name = " & objTempFolderItem.Name & Chr$(13) & _
"MetaDataPath = " & objQueryItemsCol.Item(i).MetaDataPath & Chr$(13) & _
"MetaDataItem.Name = " & objMetaDataItem.Name
[loop]

objTS.Close

If you can't bring those objects into a Cognos macro, you can typically paste the whole script into VB (or VBA in Excel or Word) and run it there. You'll have to set a reference to the Impromptu (Cognos?) library -- I forget what they call it, but you'll see it on the list of available libraries.


John

 

[drlex]

Srikrishnan - nice code!
John - as you suspect, your scripting doesn't work with CognosScript.
You could just use a .txt or .csv file and use the Write command to get satisfactory output

I've taken the liberty of changing the Chr$(13) to "," to separate the fields and added a Chr$(34) at the start to close the double quotes and make all the fields separate with the commas.
The last field does end up with a trailing ", but hopefully that's not a big issue.

lex

If you excuse the reposting:

Sub Main()
Dim objImpApp As Object
Dim objImpCat As Object
Dim objImpRep As Object
Dim objQueryItemsCol As Object ' collection
Dim objFolderItemCol As Object ' collection
Dim objTempFolderItem As Object
Dim objMetaDataItem As Object
Dim i, count As Integer

Open "C:temp001.csv" For Output as #1

Set objImpApp = GetObject("CognosImpromptu.Application&quot
'objImpApp.Visible True
Set objImpCat = objImpApp.ActiveCatalog
Set objImpRep = objImpApp.ActiveDocument
Set objQueryItemsCol = objImpRep.QueryItems
Set objFolderItemCol = objImpCat.Folders(1).Items ' get children of 1st folder
Write #1, "The first folder is " & objFolderItemCol(1).Name
count = objQueryItemsCol.count
Write #1, "QueryItems.Count = " & count
For i = 1 To count
Set objTempFolderItem = objQueryItemsCol.Item(i).FolderItem
Set objMetaDataItem = objQueryItemsCol.Item(i).MetaDataItem
If (objQueryItemsCol.Item(i).FolderPath = "&quot Then
Write #1, Chr$(34) & "[ Query Item #" & i & "]" & "," & "Name = " & objQueryItemsCol.Item(i).Name & "," & _
"Type = " & objQueryItemsCol.Item(i).Type & "," & _
" *** FOLDER ITEM NOT FOUND *** " & "," & _
"MetaDataPath = " & objQueryItemsCol.Item(i).MetaDataPath & "," & _
"MetaDataItem.Name = " & objMetaDataItem.Name
Else
Write #1, Chr$(34) & "[ Query Item #" & i & "]" & ",," & _
"Name = " & objQueryItemsCol.Item(i).Name & ",," & _
"Type = " & objQueryItemsCol.Item(i).Type & "," & _
"FolderPath = " & objQueryItemsCol.Item(i).FolderPath & "," & _
"FolderItem.Name = " & objTempFolderItem.Name & "," & _
"MetaDataPath = " & objQueryItemsCol.Item(i).MetaDataPath & "," & _
"MetaDataItem.Name = " & objMetaDataItem.Name
End If
Next i
Close #1
End Sub

 

[Srikam]

Thank you very much lex for your code.

But i am not getting any result of it.If i am right,it should write all the metadata into the file c:/temp001.csv,provided i open a imr report.but it's not writing the data items.
Is there any other thing that i need to do,or the code will work fine as such.

Thanks
Srikrishnan

 

[drlex]

Srikrishnan,
The modified code works for me (7.1MR2)
Does the macro run ok but produce a blank file? Are you getting any error messages?

I see the basis of the code comes from the samples in the macro help file.

Here's a slightly modified version, which reports on the catalogue and database and leaves out the first folder reference. (the label structuring is poor!)

lex

Sub Main()
Dim objImpApp As Object
Dim objImpCat As Object
Dim objImpRep As Object
Dim objCatFolder as Object
Dim objQueryItems As Object ' collection
Dim objTempFolderItem As Object
Dim objMetaDataItem As Object
Dim i, count As Integer
Dim strfilename as string
Dim strpath as string
Open "C:Report Details.csv" For Output as #1

Set objImpApp = GetObject("CognosImpromptu.Application&quot
objImpApp.Visible True

Set objImpCat = objImpApp.ActiveCatalog
Set objImpRep = objImpApp.ActiveDocument
If objImpRep is nothing then goto endmac
Set objCatFolder = objImpCat.Folders
Set objQueryItems = objImpRep.QueryItems
'
strfilename = objImpRep.Name
strpath = objImpRep.Path
If strfilename =&quot;&quot; then strfilename = &quot;<Unnamed report>&quot;
If strpath = &quot;&quot; then strpath = &quot;<no path>&quot;
Write #1, &quot;The Report is &quot; & strfilename & &quot; on path &quot; & strpath
strFileName = objImpCat.Filename

Write #1, &quot;The filename of the Catalog is &quot; & strFileName
strfilename = objImpCat.Description
Write #1, &quot;The Catalog Description is &quot; & strfilename
Write #1, &quot;The Catalog Database is &quot; & objImpCat.Databases(1).Name
count = objQueryItems.count
Write #1, &quot;Unique DB field Count = &quot; & count
For i = 1 To count
Set objTempFolderItem = objQueryItems(i).FolderItem
Set objMetaDataItem = objQueryItems(i).MetaDataItem
If (objQueryItems.Item(i).FolderPath = &quot;&quot Then
Write #1, Chr$(34) & &quot;[ Query Item #&quot; & i & &quot;]&quot; & &quot;,&quot; & &quot;Name = &quot; & objQueryItems(i).Name & &quot;,&quot; & _
&quot;Type = &quot; & objQueryItems(i).Type & &quot;,&quot; & _
&quot; *** FOLDER ITEM NOT FOUND *** &quot; & &quot;,&quot; & _
&quot;MetaDataPath = &quot; & objQueryItems.Item(i).MetaDataPath & &quot;,&quot; & _
&quot;MetaDataItem.Name = &quot; & objMetaDataItem.Name
Else
Write #1, Chr$(34) & &quot;[ Query Item #&quot; & i & &quot;]&quot; & &quot;,,&quot; & _
&quot;Name = &quot; & objQueryItems(i).Name & &quot;,,&quot; & _
&quot;Type = &quot; & objQueryItems(i).Type & &quot;,&quot; & _
&quot;FolderPath = &quot; & objQueryItems(i).FolderPath & &quot;,&quot; & _
&quot;FolderItem.Name = &quot; & objTempFolderItem.Name & &quot;,&quot; & _
&quot;MetaDataPath = &quot; & objQueryItems(i).MetaDataPath & &quot;,&quot; & _
&quot;MetaDataItem.Name = &quot; & objMetaDataItem.Name
End If
Next i
Close #1
goto endsub
endmac:
Msgbox &quot;No open report. Please run with an open report&quot;
endsub:
End Sub

 

[Srikam]

Thanks lex..

The latest code also gives a blank file only.Compilation is not giving any errors..But when i run,it runs and comes to the editing mode back.
When i see the file there is nothing in it.
I am also using 7.imr2
I am very new to macro,so please help me out.
What i did is ..i opened an imr and ran this macro..

sri

 

[drlex]

Srikrishnan,
I don't know any reason why the code should not work for you. It would appear to compile and run, yet not generate any content to the file, yet displays the messages correctly when you use MsgBox instead of Write #1.

Try:
Sub main
Dim tempfile as string
tempfile=&quot;C:\TEMP001.txt&quot;
Open tempfile for output as #1
Write #1, &quot;Does&quot;
Write #1, &quot;This&quot;
Write #1, &quot;Work?&quot;
Close #1
End Sub
and check the output of the file.

Otherwise I've no idea why it shouldn't work for you.
lex

 

[Srikam]

sample code works fine and gives the desired output.
i wonder what's happening to the original one..and i am working on this..

Anyway thanks for ur help,it's really useful..
sri