Hi
I have a reasonble understanding of Business Objects ( using XiR3.1 ) but I am a complete amateur when it comes to Visual Basic. Im fairly certain this can be done, but a little frustrated that I have no idea how to do it.
I have created a script to run after refresh that prompts the user for a value ( InputBox)
Dim PN As String
PN = InputBox(“What is the last Actuals period you want”, “periodno”)
I want to store the value as a variable value that I can then use to reference a formula already in the report.
So I need to be able to
1 - Store the variable value ( the value could change each time the report is refreshed and the user populates the input box )
2 - Have this variable available in Desktop Intelligence, so that I can use this variable in formula’s set up in the report
Not being a programmer, I see this as being easy, but in reality appreciate it might not be.
Can anyone offer advice / solutions to this problem…or just tell me its not possible to do.
Many thanks
Stephen
It looks to me that you are making it too complicated. You probably don’t need any VBA to get a value from a report user. Just use @prompt() function in the report to have a prompt. When a user runs the report it will ask him/her for a value. Then you can get the entered value using UserResponse() function.
Please let us know if this is what you need and if any further help in this regard is required.
Hi Marak
Many thanks for the welcome.
I am not sure I understand you.
I have prompts set up in the conditions of report. Under nearly all circumstances the report will run for
Financial Year 2010 and Financial Period 12 as I need full year data to be retrieved. Once the report is refreshed and all data is retrieved I then have to split the financial periods into what is actual and what is forecast. The input box I wanted to set up would require the user to identify what is the last actual period before we get a forecast period. This value could chop & change depending on who runs the report & when the report is run…so I cannot use the current date function to determine the split.
The @prompt() function is new to me…or I don’t know it in this format. How do I create a prompt for a user ( that doesn’t affect what data is retrieved in the prompts / conditions already set up)?
If I can achieve what I need without using VB it would make my life so much easier!
Perhaps I need to question how much I really know about Business Objects!
Is the report based on a universe or is it a free hand SQL report?
Regarding the @prompt() function:
It’s a function that is used in a query (in a universe object, in a free-hand SQL) when you need a prompt in a report.
You can create a condition in the report that is always true so it does not affect data that is retrieved from the database. When you use @prompt() function in such dummy condition, you have a way how to ask report users for a value that does not affect the report results.
We use Deski and Universes. I cannot use freehand SQL as I do not know how to use it ( safely ). My IT department advise against its use.
You wrote
Anything that doesn’t affect the full data retrieved is useful. I can get the full years data now I just need users to type in a number somewhere simple to determine actual / forecast split.
Can you do modifications in the universe? If not you then the BO universe developer(s) could help you with this:
What you need is a predefined filter/condition (the object has a yellow funnel icon) in the universe that will be always true and that will use @prompt() function. Something like:
@prompt('type in number','N',,mono,free) <> @prompt('type in number','N',,mono,free) + 1
As you can see, whatever number a user enters in a prompt the condition is always true. So it won’t affect/limit data retrieved from the database.
The above predefined filter will not parse because it does not reference any real table in the universe. However, when you use it in a real query in Query Panel then it should work without a problem.
And then, on the report level, you will get the value that a user entered in the prompt by using UserResponse() function. Then you can use the value in the report as you need.
(BOB member since 2003-11-27)