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

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: No events within the next 45 days.

[Webi 4.x] divide by @prompt


 
Search this topic... | Search Web Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Author Message
JordanDulwich
Forum Member
Forum Member



Joined: 25 Oct 2010

Posts: 24



PostPosted: Wed Sep 19, 2018 4:20 am 
Post subject: divide by @prompt

Divide by a prompt value.

Hi guys,

The following works fine:

SELECT 100 * @prompt('Select number of trading days','N',,Mono,,{1}) AS TEST

but...

SELECT 100 / @prompt('Select number of trading days','N',,Mono,,{1}) AS TEST

does not, is there a wau round it? What I am trying to do is this in MSSQL

declare @TDSFIN
set @TDSFIN = @prompt('Select number of trading days','N',,Mono,,{1})
select 100 / @TDSFIN

which gives me a divide by zero error, which is odd since I have hard coded a default value of 1. I have also tried:

select 100 / @prompt('Select number of trading days','N',,Mono,,{1})

which gives me the same error. Where am I going wrong, Is there a way to make this work?

Thanks
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6417
Location: Connecticut, USA


flag
PostPosted: Thu Sep 20, 2018 7:00 am 
Post subject: Re: divide by @prompt

Are you getting a parse error in Designer, or is it actually failing in WebI? If in Designer, then it is likely a red herring; it is just populating the prompt with a 0 in order to parse, which will cause the div/0 error.
Back to top
JordanDulwich
Forum Member
Forum Member



Joined: 25 Oct 2010

Posts: 24



PostPosted: Fri Sep 21, 2018 3:02 am 
Post subject: Re: divide by @prompt

Thanks for the reply. The error occurs when running the report. The report isnt based on any universe, it's data source is free hand sql.

Thanks
Back to top
surya_sudheer
Forum Associate
Forum Associate



Joined: 01 Nov 2006

Posts: 664
Location: Chennai


flag
PostPosted: Fri Sep 21, 2018 4:46 am 
Post subject: Re: divide by @prompt

Have you tested that free hand sql query in back end database and check whether you are getting any error. Is that freehand sql a single query or combination of multiple queries
_________________
SAP BO, BW, Crystal Reports, Tableau
Back to top
JordanDulwich
Forum Member
Forum Member



Joined: 25 Oct 2010

Posts: 24



PostPosted: Fri Sep 21, 2018 6:08 am 
Post subject: Re: divide by @prompt

Hi thanks again for replying. It's just a single query. I did a profiler trace and saw that the prompt has a value of zero, even though I hard coded a default value of 1:

@prompt('Select number of trading days','N',,Mono,,{1})

So how do I set a default value for the prompt?

Thanks
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6417
Location: Connecticut, USA


flag
PostPosted: Fri Sep 21, 2018 6:13 am 
Post subject: Re: divide by @prompt

I wonder if it's parsing the statement first (with a plugged 0 in the prompt) before actually executing the statement. Try wrapping the prompt with a CASE WHEN that converts 0 values to NULL.

Joe
Back to top
JordanDulwich
Forum Member
Forum Member



Joined: 25 Oct 2010

Posts: 24



PostPosted: Fri Sep 21, 2018 6:26 am 
Post subject: Re: divide by @prompt

Got it working icon_smile.gif

DECLARE @TEST INT
SET @TEST = @Prompt('Select trading days','N',{'1','2','3','4','5','6','7','8','9','10'},'Mono','constrained','persistent',{'2'})
SELECT 1000 / @TEST AS TEST


Just needed to put my default value and LOV in quotes (' ')

Thanks again.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  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.0335 seconds using 17 queries. (SQL 0.0029 Parse 0.0010 Other 0.0296)
CCBot/2.0 (https://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