BusinessObjects Board

Refresh in Macro supresses the Prompt - Default Behaviour?

sdk development
#1

Hi Guys

Searched the threads for this problem but couldnt get any answers. Please help :?

My report has 2 DPs. One is Free hand with 3 Prompts and the other Excel. I am using the following code to refresh

Application.Interactive = False
ActiveDocument.Variables.Add ("Please Select Category Code")
ActiveDocument.Variables("Please Select Category Code").Value = sCategory
ActiveDocument.Variables.Add ("Please Select Firm ID")
ActiveDocument.Variables("Please Select Firm ID").Value = sFirm
ActiveDocument.Variables.Add ("Please Select Reporting Date")
ActiveDocument.Variables("Please Select Reporting Date").Value = Format(Me.txtDate.Text, "YYYY/MM/DD")
ActiveDocument.DataProviders.Item(1).Refresh
ActiveDocument.DataProviders.Item(2).Refresh

Application.Interactive = True

When I start the new report and link to the 2 DPs and I click the refresh button I get the usual BO prompt window. But after I put in the Macro code and did a refresh from VBA, the BO prompt stops showing. Ofcourse that is what should happen.
But my problem is, even if I comment the code and click the refresh button, The BO report refreshes with out the prompts. The same happens in the Datamanager Refresh button - No prompts even after purging also. I am using the following code for the refresh

Private Sub Document_BeforeRefresh(Cancel As Boolean)
Cancel = True
frmInput.Show
End Sub

Is it the default behaviour? Or am I missing something…Please Help

mukundsb (BOB member since 2003-10-27)

Steps to write a macro
#2

You may want to look at the code in thread (page 2). It will show you at least one way to call object conditions (conds.add) in your vb.

https://bobj-board.org/t/24319

Hope this helps.
Have a great day!

MayhewM :us: (BOB member since 2003-10-22)

#3

Hi MayhewM

Can you please eloborate. I dont understand. You mean to say that by adding conditions and / or adding the .IsRefreshable = true / false will solve the problem.

Sorry If I missed something :frowning:

mukundsb (BOB member since 2003-10-27)

#4

Hi All,

Any suggestions… :expressionless:

mukundsb (BOB member since 2003-10-27)

#5

In the thread below, you will see code that adds conditions to the data provider. You may try to use conds.add instead of the variables.add.

Take a look at the code and you will see what I mean.

Once you have the new conditions in place, dataprovider.refresh should be all you need.

Hope this helps,
Have a great day.

MayhewM :us: (BOB member since 2003-10-22)

#6

Hi Mukund,
I am a bit confused about your problem.
Once BO executes the code Application.Interactive = False then you will not get any prompts till you close and restart BO or till Application.Interactive = True is executed. Is this what your problem is?
Please elaborate.

Nimoh :singapore: (BOB member since 2003-06-24)

#7

Hi MayhewM

Thanks for the help. The problem in my report is a bit weird. I am using a stored procedure for getting my data. But I am calling the stored procedure through freehand sql as

EXEC StoredProcedureName @CatgCode = @Prompt('Please Select Category Code','A',,MONO,FREE), @FirmId = @Prompt('Please Select Firm ID','A',,MONO,FREE),@CurrSubmPeriod = @Prompt('Please Select Reporting Date','A',,MONO,FREE)

As you see there are no objects linked to the Prompts. I am using a VBA form to capure the user input which may include multiple values (list). I then build the input strings with the required single quotes etc and pass it to the respective variables. The report works well except that after one refresh through the Macro, BO stops prompting in the Data manager. But still I could use my Macro to refresh the report and the values are reported correctly. In this situation is it possible to use conditions? Please suggest if there is any other way to do this :crazy_face:

mukundsb (BOB member since 2003-10-27)

#8

I’m a little confused myself. Do you or do you not want the prompts to show? If you want them to show and they don’t automatically appear each time, I would try using the .hide = False in your vb coding portion of your macro. I could probably come up with the code, but vb coding is not my expertise (as noted in the thread I sent you). The .hide maybe .show or .hidden, it’s been a while since I used it.

If you are unable to get it working like you want let me know. By the way, is the whole report in free-hand sql or just the procedure call for the variables?

Have a great day!

MayhewM :us: (BOB member since 2003-10-22)

#9

hi MayhewM

Sorry for my late reply. First to your question. I dont want the default BO prompt to show. I am luanching a user Form to collect the user input parameters and assign these values to the BO variables.

Now the problem. After refreshing the Report through the macro for the first time the report refreshed properly from the next refresh onwards BO stopped prompting even when when I am refreshing thro data manager.

The cause. The document.variable created by the macro is not deleted after the refresh. The variable is retained by the report along with the value supplied. So the report doesnt need any new values for the refresh so no prompt :mrgreen:

And the solution. After adding the values and supplying the user selected parameters and refreshing, I looped through the doc.variables and deleted those variables that I added. So no variable and no parameter values for the next reresh. So BO started promting the usual way.

My problem solved. Hope it is not :crazy_face: :crazy_face:

Thanks again MayhewM

mukundsb (BOB member since 2003-10-27)