BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Streamline and extend SAP BusinessObjects software 
Streamline and extend SAP BusinessObjects software (Opens a new window)  

General Notice: No events within the next 45 days.

how to create a custom prompt in webi using custom sql.


 
Search this topic... | Search WebIntelligence XI... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> WebIntelligence XI  Previous TopicPrint TopicNext Topic
Author Message
bobtom
Principal Member
Principal Member



Joined: 17 Jan 2008

Posts: 128



PostPosted: Mon Nov 03, 2008 10:50 pm 
Post subject: how to create a custom prompt in webi using custom sql.

I have a report with percentage calculation for sales and now user want to raise prompt at report level to choose the percentages.

I need to create a prompt for percentage calculations which is applied at report level in webi xir2. for that i need to change the custom sql. I have used the @prompt() function which is not allowing to use that function since @prompt() is BO Specific not a sql function.

Can any one help me how to create a custom prompt using custom sql in webi boxir2. if any one can provide me the explanation with syntax greatly appreciated.

thanks in advance.
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002
ASUG Icon
speaker.gif*16
Posts: 21958
Location: Dallas, Texas


flag
PostPosted: Mon Nov 03, 2008 11:10 pm 
Post subject: Re: how to create a custom prompt in webi using custom sql.

I'm not sure your information is correct. To the best of my knowledge, you can use the @Prompt() function in free-hand (aka custom) SQL just fine. You just can't use the list of values option.
_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2014-07-08 Airlines Could Save Millions in Fuel Costs By Providing Everyone An iPad
• 2014-06-24 Did Florida State Win National Championship By Using Big Data?
• 2014-06-07 Business Intelligence Lessons from Star Trek – Part Two
Back to top
davidlai
Senior Member
Senior Member



Joined: 03 Nov 2008

Posts: 52



PostPosted: Mon Nov 03, 2008 11:49 pm 
Post subject: Re: how to create a custom prompt in webi using custom sql.

Hi,
You can either create the prompt object inside the custom sql or you can create an object in the universe that holds the prompt item.

Heres the syntax for the prompt function
@Prompt(‘message’,‘type’,[lov],[MONO|MULTI],[FREE|CONSTRAINED],[Persistent|non-persistent],[default value], User:0)

If I wanted to code custom sql and I wanted to select multiple values from a alphanumeric universe object thats persistent and has no default value I would code it like this

Select account
from accounts
where @prompt('Account Category','A','Accounts\Categories','Multi',Free,Persistent,,User:0)


’message’ - Text of the prompt message. The text must be enclosed
between single quotes, for example, ‘Choose a Region’.

'type' - Can be 'A' alphanumeric, 'N' number, 'D' date

'lov' - list of values that user can choose. This can be a hardcoded list or you can point to an existing object in the universe to grab a list.

Mono|Multi - user can either select from 1 value or list of values

Free|Constrained - user can enter value of their choice or be forced to select

Persistent|non-persistent - report prompt selections values will either be saved or not saved for the future

default-value - you can populate the prompt with a default value

_________________
Cheers,
David Lai

Visit my Business Intelligence blog @ http://www.davidlai101.com/blog
Back to top
bobtom
Principal Member
Principal Member



Joined: 17 Jan 2008

Posts: 128



PostPosted: Tue Nov 04, 2008 8:53 am 
Post subject: Re: how to create a custom prompt in webi using custom sql.

Thank you all for reply, I dont have the object at universe level instead I need to use the variable which is created in the report level and dynamiclly raise the prompt when user runs the report. Is this Possible by custom sql.
Back to top
Mak 1
Forum Devotee
Forum Devotee



Joined: 06 Jan 2005

Posts: 11074
Location: London


flag
PostPosted: Tue Nov 04, 2008 9:01 am 
Post subject: Re: how to create a custom prompt in webi using custom sql.

Quote:
Is this Possible by custom sql.


Quote:
You can either create the prompt object inside the custom sql


Well, you already have the answer to that.. icon_smile.gif

You should look at getting the prompt done at the universe level, "hacking" the SQL, behind the query, and checking the do not generate before running check box, is just unacceptable in a production environment IMO.

Also you should be careful with percentages, how are they calculated at the report, or via universe objects?

_________________
Business Objects Versions 4,5,6, XI R2, 3.2, 4.1 SP3
Oracle, SQL Server, Sybase, Netezza, SAP BW.

Red Adair: "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
Back to top
bobtom
Principal Member
Principal Member



Joined: 17 Jan 2008

Posts: 128



PostPosted: Tue Nov 04, 2008 9:06 am 
Post subject: Re: how to create a custom prompt in webi using custom sql.

thank you, could you please elaborate in detail.
Back to top
Mak 1
Forum Devotee
Forum Devotee



Joined: 06 Jan 2005

Posts: 11074
Location: London


flag
PostPosted: Tue Nov 04, 2008 9:19 am 
Post subject: Re: how to create a custom prompt in webi using custom sql.

Quote:
thank you, could you please elaborate in detail.


Welcome icon_smile.gif.

I can't, because you have given none, how about you elaborate on what you are trying to do in detail.. icon_rolleyes.gif?

You haven't given me any idea how your percentages are calculated, nor what database field, you are going to use to filter your data, which you will require to get your prompt working at a SQL level.
Your other option may be to take in a variable value and try and filter on the report using that.

Flitering on Percentages is virtually impossible anyway, although there may be some kind of workaround depending upon exactly what you want to do..

_________________
Business Objects Versions 4,5,6, XI R2, 3.2, 4.1 SP3
Oracle, SQL Server, Sybase, Netezza, SAP BW.

Red Adair: "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
Back to top
bobtom
Principal Member
Principal Member



Joined: 17 Jan 2008

Posts: 128



PostPosted: Tue Nov 04, 2008 9:47 am 
Post subject: Re: how to create a custom prompt in webi using custom sql.

sorry about that,

I have a percentage calculation for Rates on brokerage/sales at report level and my backend database is sqlserver 2005.

and the syntax i have used at report level calculation is

=If(IsError([Brokerage Dollars]/[Dollar Sales, B]);0;[Brokerage Dollars]/[Dollar Sales, B])

Currently I have applied filter at report level for two tab, one tabe will filter less than five percent and other is above five percent which is not that effective in some cases i am missing the records. so in order to avoid that user wants to go for prompts.

I need to create a prompt between range. so that user can enter start and end percentages.
Back to top
Mak 1
Forum Devotee
Forum Devotee



Joined: 06 Jan 2005

Posts: 11074
Location: London


flag
PostPosted: Tue Nov 04, 2008 10:28 am 
Post subject: Re: how to create a custom prompt in webi using custom sql.

You may want to look at @Prompt / @Variable, in conjunction with User Response, the @variable(s) for the range could then be entered by the user and you may then be able to do a between report level filter on that.

You would have to hack the query to get BO to take in your variables, two seperate prompts.

Code:
@Prompt('Enter Start Range','N',,,) @Prompt('Enter End Range','N',,,)


As I say, this is just an idea, I'm not completely sure that you will be able to get it to do what you want.

You could also look at using alerters and having different tabs set to different ranges, displaying as appropriate.

_________________
Business Objects Versions 4,5,6, XI R2, 3.2, 4.1 SP3
Oracle, SQL Server, Sybase, Netezza, SAP BW.

Red Adair: "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
Back to top
bobtom
Principal Member
Principal Member



Joined: 17 Jan 2008

Posts: 128



PostPosted: Tue Nov 04, 2008 6:01 pm 
Post subject: Re: how to create a custom prompt in webi using custom sql.

I have tried but still i am not getting the result...Any help please...
Back to top
Mak 1
Forum Devotee
Forum Devotee



Joined: 06 Jan 2005

Posts: 11074
Location: London


flag
PostPosted: Wed Nov 05, 2008 6:38 am 
Post subject: Re: how to create a custom prompt in webi using custom sql.

Please give more detail, what have you tried?

Have you got the prompts to work, are you having trouble with User response, can you not filter the range on your report?

As stated before, Im unsure this will work at all, it was an idea..

_________________
Business Objects Versions 4,5,6, XI R2, 3.2, 4.1 SP3
Oracle, SQL Server, Sybase, Netezza, SAP BW.

Red Adair: "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
Back to top
bobtom
Principal Member
Principal Member



Joined: 17 Jan 2008

Posts: 128



PostPosted: Thu Nov 06, 2008 9:10 am 
Post subject: Re: how to create a custom prompt in webi using custom sql.

sorry for late reply stuck in some other issues..

I have specified the two prompts in custom sql in where condition as

([Rates] Between @Prompt('Enter Start Range','N',,,) AND @Prompt('Enter End Range','N',,,) )


where Rates is variable created in report level. when i use the prompts the other prompts does not show up. And instead it ask for me to enter the Start and end range values and when i specify, report does not refreshes and in some instance it displays all the data.

I think i am missing some thing
Back to top
davidlai
Senior Member
Senior Member



Joined: 03 Nov 2008

Posts: 52



PostPosted: Thu Nov 06, 2008 10:50 am 
Post subject: Re: how to create a custom prompt in webi using custom sql.

Hi,
Can you post your custom sql?
It's possible you might have something wrong in there and thats the reason why the other prompts dont come up

_________________
Cheers,
David Lai

Visit my Business Intelligence blog @ http://www.davidlai101.com/blog
Back to top
Mak 1
Forum Devotee
Forum Devotee



Joined: 06 Jan 2005

Posts: 11074
Location: London


flag
PostPosted: Thu Nov 06, 2008 11:22 am 
Post subject: Re: how to create a custom prompt in webi using custom sql.

Hi,

You can't directly refer to the report variable in the SQL, the idea is to have the user input the numbers which you - may - be able to use to drive the report level filters[/b] via use of the user response function.

_________________
Business Objects Versions 4,5,6, XI R2, 3.2, 4.1 SP3
Oracle, SQL Server, Sybase, Netezza, SAP BW.

Red Adair: "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> WebIntelligence XI  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0514 seconds using 17 queries. (SQL 0.0030 Parse 0.0375 Other 0.0109)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo