Checking values in Report through macro

[saval]

Hi All,

Maybe just a simple question, is it possible to check values in a cognos report through a macro.

We want to schedule a report to run weekly and export the data to eg. Excel. But it should only do this when specific values are in the report.

Thanks.

Paul

 

[drlex]

Paul,
When you mention "report", what Cognos product are you using to generate the report; Impromptu, PowerPlay, reportNet etc?


soi la, soi carré

 

[saval]

Sorry, forgot to mention that.
We are using Cognos ImpromptU version 7.1.529.0

Regards
Paul

 

[drlex]

Paul,
It is possible - the CognosScript command objImpRep.GetDatavalue(x,y) will return the value in a list report at column x of row y. A few points to bear in mind:
1) retrieve the information prior
2) x is determined by the SQL sent to the database, so count from the Query data list in Report>Query. Similarly y is determined by any sort/group you have.
3) a null report will generate an error for the command.

Alternatively, always generate the Excel report and use the macro to read the values in the Excel sheet to determine subsequent actions.

Hope that helps,
Lex

soi la, soi carré

 

[saval]

Hi Lex,
Thanks for your quick reply, I will try it out and let you know.

Regards,
Paul

 

[drlex]

You're welcome; waiting for a couple of jobs to finish, so can't start anything new. Just to re-iterate, I've only used this on list reports; cross-tabs and forms may well require more consideration of what x & y should be.
I tend to end up doing a lot of "post-production" in Excel, so feel free to ask Qs on that too...


soi la, soi carré

 

[saval]

Hi Lex,

I did try the getdatavalue but we have null values in the report so it won't work (like you said).

I am looking at the export excel option at this time, but in this case, can Cognos script check excel as well or do we have to write a VBA macro in Excel?

Thanks for your help so far

Regards
Paul

 

[saval]

Hi Lex,

Just found an earlier reply from you in a thread which said that you can use almost any Excel VBA command in Cognos. Tryed a little and I am almost there, just have to put in some actions in an if then statement.

Thanks for your help,

Regards

Paul

 

[drlex]

Paul,
It would appear that most of the Excel VBA commands can be used in CognosScript. I have found a work around for those that do not - basically adding a macro to the workbook to perform the actions, executing it and then removing it prior to saving the file. Tedious, but effective.
To speed things up, keep Excel invisible, turn off both the alerts (to allow over-writing of the file) and the screen refresh:
objExcel.Application.DisplayAlerts = False
objExcel.Application.ScreenUpdating = False

You'll also either need to convert Excel constants to integers or try and include an Excel constants file like that for Transformer ("TRANSCONST.INC") found in the install location ...\cer5\data\samples\PowerPlay\Macros\

Lex

soi la, soi carré

 

[saval]

Hi Lex,

I am still runing in a small problem perhaps you know what to do.

In the excel file I want to check if a cell is empty (a2) if it is it should run another macro if not it should not do anyhting.

For testing purpose I just put in a msgbox. But no mather wat is in cell A2 it thinks it should run macro

Here is wat I got in the code

Set ObjImpApp = CreateObject("CognosImpromptu.Application")
ObjImpApp.Visible True

ObjImpApp.OpenCatalog PathCat & "Check Dataset before Export.cat","user",,,,1
Set objImpRep = ObjImpapp.OpenReport (PathRep & "Check Dataset before Export.imr")

ObjImpApp.WindowState = 1
ObjImpRep.WindowState = 1

On Error Resume Next
ObjImpRep.ExportExcelWithFormat LocExp & "Checkdataset Before Export.xls"

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.DisplayAlerts = False
objExcel.Application.ScreenUpdating = False

ObjExcel.Application.Workbooks.Open LocExp & "Checkdataset Before Export.xls"
If objExcel.Sheets("Checkdataset Before Export").Range("A2") = Null Then
msgBox "RUN MACRO"
Else
msgBox "DO NOT MACRO"
End If

Do you see anything wrong with this?

Thanks
Paul

 

[drlex]

Paul,
Cognosscript doesn't understand null. If I were checking, I'd read the value of A2 using FormulaR1C1 and then test that.
If objExcel.Sheets("Checkdataset Before Export").Range("A2").FormulaR1C1 = "" then ...


soi la, soi carré

 

[drlex]

I should have said that, whilst objImpRep.GetDatavalue(x,y)will throw an error if the cell referenced is Null, you can always use 'ON ERROR RESUME NEXT' prior to the line so as to cause the macro to ignore it and then test for a nil (zero or blank string, depending on the definition of the variable).

soi la, soi carré

 

[saval]

Hi Lex,

using FormulaR1C1 worked like a charm, thanks for your great help

Regards

Paul

 

[drlex]

You're welcome, chap.
If you have an Excel Q, either post back here or in "VBA Visual Basic for Applications (Microsoft)" (forum 707).

soi la, soi carré