BusinessObjects Board

List of values in prompt condition

building reports webintelligence
#1

Hello Everyone,

I have to set a prompt which should have a option to select all values under the LOV’s.

Example:

Prompt: DEPTID: Select the values from list.

This Deptid field is coming from a database table. I can select a single deptid from the list and run the report. And even i can select all deptid’s from top to bottom under LOV’s and run the report.
But user looking for a option called “ALL DEPT” under list of values. I know we have option called wild card in crystal reports. Is there any way around can do in business objects designer or webi.

Any suggestions please?

Thanks

santhoshini (BOB member since 2007-11-08)

#2

Hi,

Create a pre-defined condition in universe designer as shown below :

NOTE :- The following code is just an example.Modify it according to your need.

@Select(Invoice Credit Reason\Invc Credit Reason Desc)  IN @Prompt('13. Select Reason Code','A','Invoice Credit Reason\Invc Credit Reason Desc',Multi,Free)  OR  'ALL'  IN @Prompt('13. Select Reason Code','A','Invoice Credit Reason\Invc Credit Reason Desc',Multi,Free)

Then go to the properties of the object and edit SQL defintion of the object as shown below :



SELECT    DISTINCT 
DSPT_INVC_REASON_DM.DSPT_SLS_INVC_CREDIT_RSN_DESCFROM 
DSPT_INVC_REASON_DM 
union 
select 'ALL' FROM DUAL

After modifying the above definition,don’t forget to tick the check box “Do Not Generate SQL” otherwise your changes would not be reflected.

Note : If you want ‘ALL’ to be displayed at the top of the list in LOV, put a space after single quote in both places of the above code (like ’ ALL’).

Hope this helps you :slight_smile:

Regards,
Anoop[/b]

Anoop Shihoorkar :india: (BOB member since 2007-02-15)

#3

There is a sticky post in Designer thread.

https://bobj-board.org/t/15227/2

I think 2nd and 3rd thread can help you.

Note: Also Anoop Shihoorkar gave the right solution.

aniketp :uk: (BOB member since 2007-10-05)

#4

Thanks for the reply.

Lets explain what i did so for.

Created a new object in universe and implemented the condition shown below in where clause.

(@Select(Class\objectname) IN @Prompt(‘Select Dept’,‘A’,‘Class\objectname’,Multi,Free) ) OR ( ’ ALL’ IN @Prompt(‘Select Dept’,‘A’,‘Class\objectname’,Multi,Free))

Parsed OK.

Then clicked object properties

SELECT DISTINCT
Classname\objectname
FROM table
union
select ’ ALL’ from dual

Note: I tried to edit the properties but when i click on Sql button under select statement i am seeing where clause condition by default. Then i removed where clause condition and added " union select ’ ALL’ from dual.

Select the check button " Do not generate the Sql before running"

Then checked the box “Export with universe”

Again exported universe to the repository.

when i run sample report under prompt condition IT DOESN’T SHOW “ALL” but i am able too see each deptid under LOV’s.

Any suggestions are really appreciated!!

Thanks

santhoshini (BOB member since 2007-11-08)

#5

Hi,

(@Select(Class\objectname) IN @Prompt(‘Select Dept’,‘A’,‘Class\objectname’,Multi,Free) ) OR ( ’ ALL’ IN @Prompt(‘Select Dept’,‘A’,‘Class\objectname’,Multi,Free))

This is a pre-defined condition not object definition.

Create a pre-defined condition in universe with this code and use it directly in your report in the condition section.

[/b]

Anoop Shihoorkar :india: (BOB member since 2007-02-15)

#6

I created two data providers from a single table. First data provider returns rows for current year(2008) based on DEPTID. Second data provider returns rows for previous year(2007) based on DEPTID. What if we dont have any data for previous rear(2007) for a particular deptid then it wont returns any rows but we have data for current year.

How can i get data for current year on report irrespective whether if there is no data for previous year for a particular deptid.

NOTE: As Anoop mentioned i created predefined condition and i’m able too see ALL under Deptid prompt. But when i select ALL in my prompt condition and run report it says no data to retrieve. However if i select departments into prompt condition and run the report it working fine. I imagine there is some issue with the pre-defined condition. Does anyone have idea what could be the issue?

I hope my issue is clear. Thanks in advance!

santhoshini (BOB member since 2007-11-08)