How to pass a prompt value from a report to a macro. 1

[ChallengedProgrammer]

Does anyone know how to pass a prompt value from a report to a macro?

I have a main report that presents the user with 3 prompts. I need to use these prompt values to pass to a macro which is run from this main report. This macro opens 3 other reports using these prompt values.

Any ideas?

Thanks.

 

[griffindm]

Make calculated columns in the first report equal to the three prompts. Drag them to the top of your query list. You do not have to make them visible in the report. Have the macro do a GetDataValue call to read the first three query items from the first row of the open report. These will be your prompts. Then have the macro open the other reports with these prompts.

I've never had to do this for prompt passing, but there is no reason why it shouldn't work fine.

Regards,

Dave Griffin


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

http://www.decisionsupportgroup.com

 

[ChallengedProgrammer]

Thanks a million - it worked perfectly. Things are always so much easier with a bit of direction.

NCU

 

[ashishh]

I have the exact same situation. I am happy to see someone trying to do the same. I run into some problems with this.

I have the following problem.

I have a "Wrapper Report". This is the main report. I have a prompt defined here and a variable which gets the value from the prompt. Now, I want to call the second report and pass the prompt values from macro.

I call the macro from main report and use GetDataValue to obtain the passed parameter. I try to use ActiveDocument to tell macro to use the main report as the calling report, but it complains. According to documentation, for ActiveDocument must have a report Open.

My macro which is called from my main report, looks like this

Sub Main()
Dim objImpApp As Object
Dim objImpRep As Object
Dim strReturnValue As String
Set objImpApp = CreateObject("CognosImpromptu.Application&quot
Set objImpRep = objImpApp.ActiveDocument
strReturnValue = objImpRep.GetDataValue(1,1)
Set objImpRep = objImpApp.OpenReport("c:\reports\ABC Report.imr",strReturnValue)
Set objImpRep = Nothing
Set objImpApp = Nothing
End Sub

I get error on the ActiveDocument line.
The question is how do I resolve this. I need to run the main report which prompts me for input but then the macro fails.

Any help will be highly appreciated.

Thanks,
Ashish

 

[griffindm]

ashishh,

I haven't tested this, but I suspect you may have to open the second report using a second instance of CreateObject, to get a second different object name that reflects the second report. I see no other way in single instance mode (most commonly used) to achieve the ability to 'toggle' between two open reports within the macro. The alternative, which I usually use, is to read all the data from the main report into an array and then close that report and open the second report within a loop that passes the prompts from the array into the report call.

Hope this helps,

Dave Griffin


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

http://www.decisionsupportgroup.com

 

[ChallengedProgrammer]

I think Dave is right. Try using

Set objImpApp = GetObject("CognosImpromptu.Application&quot

instead of:

Set objImpApp = CreateObject("CognosImpromptu.Application&quot

NCU