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

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

based on the user response value need to perform calculation


 
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
pullabh
Senior Member
Senior Member



Joined: 15 Apr 2009

Posts: 36



PostPosted: Mon May 24, 2010 9:08 am 
Post subject: based on the user response value need to perform calculation

I have a report with 2 columns based on the data in the first column i need to perform either sum or average for the next column

For example for c1 the aggregation for the next column should be average
c2 the aggregation for the next column should be sum .....so on

I have created a variable v1 for user response value on column1


is there a way where i can mention if v1= c1,c3,c5,c7 then do sum
else average on the column2


c1,c2,c3.... are column 1 values
Back to top
Debbie
Forum Addict
Forum Addict



Joined: 01 Mar 2005

Posts: 2799
Location: Cheshire, England


flag
PostPosted: Mon May 24, 2010 10:06 am 
Post subject: Re: based on the user response value need to perform calcula

Probably not syntactically correct, but this should get you started:
Code:
=If(UserResponse("data_provider_name";"exact_prompt_text")="c1";sum([object1];average[object2])

Debbie
Back to top
pullabh
Senior Member
Senior Member



Joined: 15 Apr 2009

Posts: 36



PostPosted: Mon May 24, 2010 10:14 am 
Post subject: Re: based on the user response value need to perform calcula

=If(UserResponse("M1:")="PR";Sum([Jul]);Average([Jul]) )


Thanks , this function is working fine if i select only one prompt value but if select 4 values c1,c2,c3,c4 at prompt this function is not working..

can u please let me know what could be the problem
Back to top
Marek Chladny
Forum Advocate
Forum Advocate



Joined: 27 Nov 2003

Posts: 19341
Location: Bratislava


flag
PostPosted: Mon May 24, 2010 10:25 am 
Post subject: Re: based on the user response value need to perform calcula

Hi,

Try a formula like this:
Code:
=If(Match(UserResponse("M1:");"*c1*") OR Match(UserResponse("M1:");"*c2*") OR Match(UserResponse("M1:");"*c3*") OR Match(UserResponse("M1:");"*c4*");Sum([Jul]);Average([Jul]) )

_________________
BO: BI 4.0, 4.1 | XI 3, 3.1 | XI r2 | 6.x | 5.x
DB: Oracle, MS SQL Server, DB2, Teradata, Netezza
HW: Win, Linux and AIX servers
www.perpendulum.com

Latest Blog Posts
• 2013-07-16 Number of reports in a WebI document
• 2013-04-01 Optional prompts in a universe
• 2012-06-08 Calendar table script for Oracle
Back to top
pullabh
Senior Member
Senior Member



Joined: 15 Apr 2009

Posts: 36



PostPosted: Mon May 24, 2010 10:42 am 
Post subject: Re: based on the user response value need to perform calcula

C July

c1 value1
c2 value2
c3 value3
c4 value4
c5 value5
c6 value6

If(Match(UserResponse("M1:");"*c1*") OR Match(UserResponse("M1:");"*c2*") OR Match(UserResponse("M1:");"*c3*") OR Match(UserResponse("M1:");"*c4*");Sum([Jul]);Average([Jul]) )

Value1,2,3,4 aggregation is sum but for c5,c6 also it is giving sum value but not average but i want the aggregation for value 5 and value 6 as avearge
Back to top
Marek Chladny
Forum Advocate
Forum Advocate



Joined: 27 Nov 2003

Posts: 19341
Location: Bratislava


flag
PostPosted: Mon May 24, 2010 1:41 pm 
Post subject: Re: based on the user response value need to perform calcula

What if a user selects c1 and c6 in the prompt? What should happen in the formula?

The thing is that your logic that should be evaluated in the IF statement is ambiguous, I mean not complete. You need to work on it and evaluate every possible combination of inputs.

_________________
BO: BI 4.0, 4.1 | XI 3, 3.1 | XI r2 | 6.x | 5.x
DB: Oracle, MS SQL Server, DB2, Teradata, Netezza
HW: Win, Linux and AIX servers
www.perpendulum.com

Latest Blog Posts
• 2013-07-16 Number of reports in a WebI document
• 2013-04-01 Optional prompts in a universe
• 2012-06-08 Calendar table script for Oracle
Back to top
pullabh
Senior Member
Senior Member



Joined: 15 Apr 2009

Posts: 36



PostPosted: Tue May 25, 2010 11:09 am 
Post subject: Re: based on the user response value need to perform calcula

----------------------------------------------------------------------------

For example take efashion unv

My requirement is if user selects california or newyork the aggregation on sales revenue should be average and sum on the other two states.


Code:
=If(UserResponse("State:")="California" Or UserResponse("State:")="New york" ) Then Average([Sales Revenue]) Else Sum([Sales Revenue])

this formula is not working , iam getting blank rows under sales revenue column

Is there something to do user response function because it returns values as california;new york;massachusetts;texas


could you please post the correct formula

State Sales Revenue
California
New York
Massachusetts
Texas
Back to top
vineshkinu
Forum Member
Forum Member



Joined: 24 May 2010

Posts: 7



PostPosted: Tue May 25, 2010 3:14 pm 
Post subject: Re: based on the user response value need to perform calcula

Hi

User Response is used for displaying purpose and so values are concatenated with ";".
Here I can think of only 1 solution which is already posted above by Marek.
Code:
=If(Match(UserResponse("State:");"California") OR Match(UserResponse("State:");"California");Average([Sales Revenue]);Sum([Sales Revenue]) )


But other thing is why do we need to use User Response. Say the prompt object name is State.
Code:
=If([State] inList ("California";"New York");Average([Sales Revenue]);Sum([Sales Revenue]) )

It would be great if you attach a sample data and how it should be presented.

Vinesh
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.0293 seconds using 17 queries. (SQL 0.0028 Parse 0.0009 Other 0.0256)
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