Row count in Impromptu Macro 3

[camjai]

Hi,

I've spent the last hour trying to find out how this is done. There doesn't seem te be a simple way of doing it.
I want to loop through all the rows in my report and stop when the last row is reached. For this I need to determine the number of rows in my report.

for row = 1 to number_of_rows
myitem = objImpRep.getdatavalue (1,rows)
next row

Something like this: number_of_rows = objImpRep.Rows.Count ?

 

[drlex]

How about adding a column to the report with the value of count(anyfield)? You can then pick this value up with number_of_rows = objImpRepGetdatavalue(x,1), where x is the number of columns to reach this new one.

If you don't want to change the report, you could always create another version with the row count and then run the original.

soi la, soi carré

 

[camjai]

thank you drlex. This works well.
I'm just amazed there's no simple macro command for this.

 

[drlex]

You're welcome - thanks for the star.
A command you mention would be useful; perhaps it's due to the fact that the default retrieval is just 100 rows and so a RetrieveAll would be required prior.
You could always try requesting it from Cognos as an enhancement, but they'd probably point to the SQL count function as an acceptable work-round.

soi la, soi carré

 

[griffindm]

Actually there is a macro call to an API that is supposed to do this. It doesn't work.

Dave G.


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[drlex]

Dave,
Out of insatiable curiousity, what's the macro call you are referring to? I can only find the count property of the object objImpRep.QueryItems, which returns the number of DB fields used in the report.
thanks,
lex

soi la, soi carré

 

[griffindm]

Shucks. Now I have to search my older stuff until I find that call. I'll let you know when/if I find it. I remember that it would return 1 row if there was either 1 row in the report OR zero rows were returned, so it wasn't real useful.

Dave G.


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[camjai]

Think I came across the API in question while googeling for a solution. Seemed a bit extravagant for a simple row count so I didn't even try it.

Declare Function GetPrivateProfileString Lib "Kernel" Alias "GetPrivateProfileString" (ByVal lpApplicationName$, _
ByVal lpKeyName As Any, ByVal lpDefault$, ByVal lpReturnedString$, _
ByVal nSize%, ByVal lpFileName$) As Integer

Declare Function GetINIString(INISection as String,INIEntry as String, INIFile as String) as String


Function GetINIString (INISection as String, INIEntry as String, INIFile as String) as String
Dim Buffer as String * 256
Dim iRetVal as Integer



iRetVal= GetPrivateProfileString(INISection, INIEntry, "", Buffer, Len(Buffer), INIFile)
GetINIString=Left$(Buffer,iRetVal)

End Function



Sub main ()

Dim ObjImpApp as Object
Dim ObjImpRep as Object
Dim count as Integer
Dim inentry as String

Set ObjImpApp = CreateObject ("Impromptu.Application.30")
ObjImpApp.Visible 1
ObjImpApp.OpenCatalog "mycat.Cat", "Creator"
Set ObjImpRep = ObjImpApp.OpenReport ("myreport.IMR")
ObjImpRep.Retrieveall

ObjImpRep.CloseReport
Set ObjImpRep = Nothing
ObjImpApp.Quit
Set ObjImpApp = Nothing

inentry=GetINIString("Query Statistics", "myreport.IMR", "D:\COGNOS\BI96\workspce\IMP30.INI")
count=len(inentry)
MsgBox GetField ( inentry, 3, ",")

End sub

 

[drlex]

That's a left-field workaround - look at the Impromptu.INI file for the report title and return the row count recorded!

Thanks, camjai - interesting, although "Error in Loading DLL Kernal" prevents me testing it further.

soi la, soi carré

 

[griffindm]

Actually, I found the API in question. It is:

rcnt = ImpApp.activedocument.retrieveall+1
(you need to define the ImpApp object first of course)

This does return a row count. It just does not do a good job of telling you when your report has zero rows returned.

I did this back in version 5. Anyone that has the time can check it in the current version to see if it is any better now.

Dave G.


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[drlex]

Thanks, Dave.

Works in 7.3MR2, albeit probably in the same way that it 'works' in 5.
Returns the row count OK if there are rows.
Returns '1' if there are no rows.
Thus no way to distinguish Null reports and singular results.

Have a star for dredging that up from your memory.

belated happy labo(u)r day...
lex

soi la, soi carré