create object with UserResponse

system · July 25, 2011, 7:02pm

i my webi report i create the following object:

=UserResponse(“Enter Period:”)

This object i use in one of my column so it will show for all rows same period.

But i need to use Qaaws, but because you can not create objects within qaaws, i need to create this object within universe.

How can i do that?

Hopefully someone can help me

Swimmer (BOB member since 2006-08-23)

system · July 25, 2011, 7:11pm

Use @prompt() function.

Jansi (BOB member since 2008-05-12)

system · July 25, 2011, 7:15pm

You mean create an object:
period format = yyyymm

@Prompt(‘Enter Period:’, ‘A’, mono, constrained)

Add place that object in the report?

Swimmer (BOB member since 2006-08-23)

system · July 25, 2011, 7:15pm

Yes.

Jansi (BOB member since 2008-05-12)

system · July 25, 2011, 7:53pm

im doing something wrong

object name Start

in select:

@Prompt(‘Enter Period’,‘A’,‘Sales\Period’,mono,constrained)

when parsing i already got an error:
Syntax error, expected something like name or a Unicode delimiter identifier or ‘(’ between the From keyword and ;.

But i still exported universe to repository and i used this object in report(not as filter) but in the results objects

But when running report im getting the famous Api error

Swimmer (BOB member since 2006-08-23)

system · July 27, 2011, 11:15am

my issue is more complex then i thought

Currently i have the following prompt in my webi report
Period BETWEEN Start and END

Start is a prompt name
End also

In my webi report i am using
=UserResponse(“Start”) and in other column I am using
=UserResponse(“End”)

My values are 201005 and 201107, so im getting two column with only 201005 and 201107 and the rest of the report is showing data being calculated for this period
So in my webi report its working

Now i need to do the same in Qaaws

so i created two objects in my universe
Object name: Start
Select: SALES.Period
Where: SALES.Period>=@Prompt(‘Start’,‘A’,Mono,Free,Persistent)

Object name: End
Select: SALES.Period
Where: SALES.Period<=@Prompt(‘End’,‘A’,Mono,Free,Persistent)

Now i add both objects within my report, and if i run the report, im getting my two prompts where i can fill in 201005 and 201107, but in my report im getting values for Start from 20105 -201107 and for end the same. So its not taking my inpyut for the prompts.

Hopefully you understand what is going on, and can you help me.
the problem is the between and that i need both values in seperate column

Swimmer (BOB member since 2006-08-23)

system · July 27, 2011, 2:46pm

It’s simple, I guess.

Object name: Start
Select: SALES.Period
Where: SALES.Period>=@Prompt(‘Start’,‘A’,Mono,Free,Persistent)

Object name: End
Select: SALES.Period
Where: SALES.Period<=@Prompt(‘End’,‘A’,Mono,Free,Persistent)

Replace <= and >= by =

Jansi (BOB member since 2008-05-12)

system · July 27, 2011, 3:14pm

no that won’t work either because then i have the following SQL code:

SELECT
( SALES.PERIOD ),
( SALES.PERIOD ),
SALES.REGION,
SALES.EMPLOYEE_NAME,
sum(SALES.NET_SALES)
FROM
SALES
WHERE
( ( SALES.PERIOD )=@Prompt(‘Start’,‘A’,Mono,Free,Persistent) )
AND ( ( SALES.PERIOD )=@Prompt(‘End’,‘A’,Mono,Free,Persistent) )
GROUP BY
1,
2,
3,
4

And because you are getting a "AND"in your prompt you won’t get any data.

Swimmer (BOB member since 2006-08-23)

system · July 27, 2011, 3:19pm

Can you replace the where in your definition by a case statement?

Jansi (BOB member since 2008-05-12)

system · July 27, 2011, 3:24pm

what do you mean?

What is in the where clause is coming from the both objects right.

Swimmer (BOB member since 2006-08-23)

system · July 27, 2011, 3:35pm

I didn’t get what you meant. However this FAQ can give tips.

Jansi (BOB member since 2008-05-12)

system · July 27, 2011, 3:38pm

this is the created SQL code by business objects:

Posted: 27 Jul 2011 16:14 Post subject: Re: create object with UserResponse

no that won’t work either because then i have the following SQL code:

SELECT
( SALES.PERIOD ),
( SALES.PERIOD ),
SALES.REGION,
SALES.EMPLOYEE_NAME,
sum(SALES.NET_SALES)
FROM
SALES
WHERE
( ( SALES.PERIOD )=@Prompt(‘Start’,‘A’,Mono,Free,Persistent) )
AND ( ( SALES.PERIOD )=@Prompt(‘End’,‘A’,Mono,Free,Persistent) )
GROUP BY
1,
2,
3,
4

As you can see in the where clause i am getting "AND"instead of BETWEEN, that is ofcourse becasue im having two objects seperately within universe.
So my issue is that is want to have a between, and show the input values of both objects within my report (QAAWS)

Swimmer (BOB member since 2006-08-23)

system · July 28, 2011, 1:01pm

Swimmer,

I think your query should look something like:

SELECT 
SALES.PERIOD,
@Prompt('Start','A',,Mono,Free,Persistent), 
@Prompt('End','A',,Mono,Free,Persistent), 
SALES.REGION, 
SALES.EMPLOYEE_NAME, 
sum(SALES.NET_SALES) 
FROM SALES 
WHERE SALES.PERIOD between @Prompt('Start','A',,Mono,Free,Persistent) AND @Prompt('End','A',,Mono,Free,Persistent) 
GROUP BY 
1, 
2, 
3, 
4,
5

Create 2 objects and drag them in your Qaaws:

Object_start: @Prompt('Start','A',,Mono,Free,Persistent) 
Object_end: @Prompt('End','A',,Mono,Free,Persistent)

Hope this helps,
Johan

JdVries (BOB member since 2006-02-06)

system · July 28, 2011, 1:18pm

It’s working perfectly thanks!!

Swimmer (BOB member since 2006-08-23)

system · July 28, 2011, 1:24pm

Swimmer,

You’re welcome

Johan

JdVries (BOB member since 2006-02-06)