Get all displayed fields of a report

[ret83]

Hi,
I'm writing a macro which loops through the columns and rows of a simple list report with some filters applied. I use the following code to do that:

rowcount=objImpRep.RetrieveAll + 1
colcount=objImpRep.QueryItems.Count
For rc = 1 To rowcount
For cc = 1 To colcount
fieldvalue = objImpRep.GetDataValue(cc, rc)
Next cc
Next rc

This worked fine until I applied the filters: They seeme to be a queryitem too. But these columns are not displayed.
Is there a collection which returns just the visible columns or is it possible to detect whether a column is visible or not (without selecting the frame or .SelectedFrames).

 

[griffindm]

Ret83,

Be careful with this kind of query. Remember that this is based on the query data, not the items shown in the query output. Thus sub-totals and non-displayed columns also count as query objects as well.

If you want to pull specific data into a macro for other use consider an ODBC-based SQLExec and SQLRetrieve which can pull all the desired data directly into a macro array. This is a better alternative as long as the processing would not include local (i.e. Impromptu-only) functions.

Regards,

Dave Griffin

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

 

[ret83]

Dave Griffin,

thanks for the fast answer. Actually you're right. But I need to do it this way. You think it isn't possible to do it better than my solution but as a macro?
thx.

 

[griffindm]

Your solution should work fine, but is a little more complex than what I suggested. To detemine what Impromptu is counting as QueryItems, try the following (from the help file):

Sub Main()
Dim ImpApp as Object
Dim ItemCount as Integer
Set ImpApp = CreateObject("CognosImpromptu.Application&quot
For ItemCount = 1 to ImpApp.ActiveDocument.QueryItems.Count
MsgBox ImpApp.ActiveDocument.QueryItems(ItemCount).Name
Next ItemCount
End Sub

Run this with your report open and only one report and one instance of Impromptu up.

Step though it as see how other report objects are being handled. I am a little too busy to do it now. Please post back if you find an interesting pattern.

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

 

[ret83]

That works fine on a simple list report, it shows all columns, also the filters, and the .Name is just the column name in the corresponding table.
When I use it on a sample report with nested sql statements (e.g. Percentage of Group.imr) the QueryItems are the values from the inner query, not from the displayed fields.

 

[ret83]

I found two other possibilities to do this task:
° To loop through the active report:
for x=1 to 1000
for y=1 to 1000
getdatavalue(x,y)
next y
next x
° To export the report as a text file, read in the text file and replace all these long spaces with my delimiter. I guess that is the best way.

 

[griffindm]

ret83,

Sounds like this last approach should get all the data out as text. I like the idea of using QueryItems though, as it can pull out non-data elements such as the filter. Given some free time I may explore it a bit in the future.

Good luck,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!