BusinessObjects Board

Selecting all values in the Prompt

Hi ,

I am new to Business Objects.

I have created a prompt, and when I run the report to select all the records I use ‘%’. But only the not null are retrieved and the null values are not retrieved.

I checked the old discussion threads which says use ‘ALL’ and @prompt to select all the values.

Can you please give a detailed step by step on how to go about doing the same. Please see that I am new to BO and a detailed steps will be very much helpful to me.

Thanks,

Regards,

Pchettiar.


pchettiar9 (BOB member since 2002-10-30)

:mrgreen: Hi :mrgreen: ,

and welcome in the real world :lol:

:arrow_forward: here is an example of how to enable users to select “All” in a prompt:

'(All)'  in @Prompt('Countries or (All) :','A','Geographical data\Country LOV', multi, free) OR @Select(Geographical data\Country)  in @Prompt('Countries or (All) :','A','Geographical data\Country LOV',multi,free)

This code must be put in the “Where” part of the pre-defined condition you create in Designer.

:!: You will need to create in your universe the object used in the prompt “Country LOV” in the class “Geographic data”. The best thing is probably to create it as a hidden detail object of “Geographical data\Country”.

You will find a clear explanation of how @prompt works in the designer help (C:\Program Files\Business Objects\Online Guides\EN\Designer51EN.pdf").

Hope my explanations are clear enough. :roll_eyes:

Have fun,


Pierre :luxembourg: (BOB member since 2002-09-05)

Check also the post Designer FAQ…it is all there :wink:


Andreas :de: (BOB member since 2002-06-20)

Thanks a lot…which check it out…


pchettiar9 (BOB member since 2002-10-30)

This will return all rows with null contents plus those which match your LOV selection, correct? How would you return ALL (including null) only if ‘ALL’ is selected, but only rows matching a particular value in the LOV if that is selected??


Melissa Piccolo (BOB member since 2002-08-16)

Could you please clarify what you are trying to achieve??

Reema


reemagupta (BOB member since 2002-09-18)

I want to have a prompt where the user can decide to only select particular rows or all rows. This works fine on if the selection is based on columns which are not nullable. However, if the column which the select is based on is allowed to be null, a particular selection will return all rows with null values as well as the selection entered. Does that make sense?


Melissa Piccolo (BOB member since 2002-08-16)

It will work in those cases too, as when you say ALL

It means
ALL=ALL that means all rows irrespective of their values.

Hope it is clear

Reema


reemagupta (BOB member since 2002-09-18)

The problem is when I don’t say ALL. If I say I want the Value to be equal to A, I get all rows where the value equals A and those which are null. I don’t want the null rows if a particular value is selected.


Melissa Piccolo (BOB member since 2002-08-16)

Then you must be doing outer join between those two tables.

Can you make the join as inner join in designer in Universe

Reema


reemagupta (BOB member since 2002-09-18)

No because then I won’t get those with null values when I select all. The value is null because no row exists in that table for the row being reported on.


Melissa Piccolo (BOB member since 2002-08-16)

Could you pls send me sql generated by BO when you just say ‘A’ as propmt value

Reema


reemagupta (BOB member since 2002-09-18)

I’ve pasted it below. I bolded the applicable part in the where statement.

Thanks, Melissa

SELECT
  ( PersonMatchDetail_LN.TargetValue ) + ':' + ( PersonMatchDetail_FN.TargetValue ) + ':' + ( PersonMatch.HNEId ),
  PersonMatch.HNEId,
  PersonMatch.PersonMatchId,
  PersonMatch.TotalScore,
  PersonMatch.SufficientScore,
  PersonMatch.CreateDate,
  PersonMatch.Action,
  PersonMatchDetail_LN.CandidateValue,
  PersonMatchDetail_FN.CandidateValue,
  ISNULL(PersonMatchDetail_MN.CandidateValue,' '),
  PersonMatchDetail_Sex.CandidateValue,
  ( PersonMatchDetail_BM.CandidateValue )  + '/'  + ( PersonMatchDetail_BD.CandidateValue )  + '/'  + ( PersonMatchDetail_BY.CandidateValue ),
  PersonMatchDetail_SSN.CandidateValue,
  PersonMatch.MatchHNEId,
  PersonMatchDetail_MRN.CandidateValue,
  PersonMatchDetail_MRN.CandidateSuppValue,
  ( PersonMatchDetail_BM.TargetValue )  + '/'  + ( PersonMatchDetail_BD.TargetValue )  + '/'  + ( PersonMatchDetail_BY.TargetValue ),
  PersonMatchDetail_FN.TargetValue,
  PersonMatchDetail_LN.TargetValue,
  ISNULL(PersonMatchDetail_MN.TargetValue,' '),
  PersonMatchDetail_MRN.TargetSuppValue,
  PersonMatchDetail_SSN.TargetValue,
  PersonMatchDetail_Sex.TargetValue,
  PersonMatchDetail_MRN.TargetValue
FROM
  PersonMatchDetail  PersonMatchDetail_LN,
  PersonMatchDetail  PersonMatchDetail_FN,
  PersonMatch,
  PersonMatchDetail  PersonMatchDetail_MN,
  PersonMatchDetail  PersonMatchDetail_Sex,
  PersonMatchDetail  PersonMatchDetail_BM,
  PersonMatchDetail  PersonMatchDetail_BD,
  PersonMatchDetail  PersonMatchDetail_BY,
  PersonMatchDetail  PersonMatchDetail_SSN,
  PersonMatchDetail  PersonMatchDetail_MRN
WHERE
  ( PersonMatchDetail_BM.PersonMatchId=*PersonMatch.PersonMatchId  )
  AND  ( PersonMatch.PersonMatchId*=PersonMatchDetail_MRN.PersonMatchId  )
  AND  ( PersonMatch.PersonMatchId*=PersonMatchDetail_LN.PersonMatchId  )
  AND  ( PersonMatch.PersonMatchId*=PersonMatchDetail_BD.PersonMatchId  )
  AND  ( PersonMatch.PersonMatchId*=PersonMatchDetail_Sex.PersonMatchId  )
  AND  ( PersonMatch.PersonMatchId*=PersonMatchDetail_SSN.PersonMatchId  )
  AND  ( PersonMatch.PersonMatchId*=PersonMatchDetail_BY.PersonMatchId  )
  AND  ( PersonMatchDetail_MN.PersonMatchId=*PersonMatch.PersonMatchId  )
  AND  ( PersonMatchDetail_FN.PersonMatchId=*PersonMatch.PersonMatchId  )
  AND  ( PersonMatchDetail_BD.ComponentName= 'birth day'  )
  AND  ( PersonMatchDetail_BM.ComponentName= 'birth month'  )
  AND  ( PersonMatchDetail_BY.ComponentName= 'birth year'  )
  AND  ( PersonMatchDetail_FN.ComponentName= 'first name'  )
  AND  ( PersonMatchDetail_LN.ComponentName= 'last name'  )
  AND  ( PersonMatchDetail_MN.ComponentName= 'middle name'  )
  AND  ( PersonMatchDetail_MRN.ComponentName= 'mrn'  )
  AND  ( PersonMatchDetail_Sex.ComponentName= 'sex'  )
  AND  ( PersonMatchDetail_SSN.ComponentName= 'ssn'  )
  AND  ( PersonMatch.CreateDate  >= @Prompt('Beginning Date for Report','D',{'01/01/1900 12:00:00 AM'},MONO,FREE)
AND
PersonMatch.CreateDate <= @Prompt('Ending Date for Report','D',{'12/31/2100 11:59:59 PM'},MONO,FREE)  )
  AND  ( PersonMatch.Action  != 'Exact Match'
AND PersonMatch.Verified = 0
AND PersonMatch.Action IN @Prompt('Include Associate and/or Report Matches','A',{'Associate', 'Report'},Multi,Constrained)  )
  [b]AND  ( ( PersonMatchDetail_MRN.TargetSuppValue ) IN @Prompt('Enter MRN Assigning Authority','A','Association Report\Assigning Authority Name - Select',Multi,free) or 'ALL' IN @Prompt('Enter MRN Assigning Authority','A','Association Report\Assigning Authority Name - Select',Multi,free)  )[/b]ORDER BY
  1, 
  14, 
  3

Melissa Piccolo (BOB member since 2002-08-16)

Mellissa,
You do have outer joins…isn’t it…thats why it is pulling null values also…


dcdas :us: (BOB member since 2002-06-17)

yes and yes.

Sorry I didn’t mention that earlier. Perhaps I should have created a new thread rather than complicating this one. But it represents a fairly pervasive issue for me, and I’ve yet to figure out a way around it.


Melissa Piccolo (BOB member since 2002-08-16)

Contact me offline and I might be able to help you. I am in Atlanta too.


dcdas :us: (BOB member since 2002-06-17)

Hi, I have prompts like this too. However, when I go to WEbi and refresh the report by selecting one of the values, it is not a drop down list box as the prompts for dates. Could anyone tell me how I can get a drop down list box for this - (All is the default, user can select other LOV as well)?

Thanks a lot.

Lilly


Lilly J (BOB member since 2002-08-22)

Lily,
If you want to show ‘ALL’ also in the LOV you need to create a union query for the customized lov.


dcdas :us: (BOB member since 2002-06-17)

Hi, Durgesh:

Actually, “All” is the default in the prompt, the thing is that how I can get a drop down list box for the LOVs. When I choose one value by clicking “Show Values”, I see a Rich Text Box, instead of drop down list box.

Do I need to have a Custome LOV to achieve this?

Thank you so much.

Lilly


Lilly J (BOB member since 2002-08-22)

What exactly do you mean by “Rich text box” instead of a drop down list?
Are you trying to achieve your goal with BO Full Client and/or WEBi?

Yes, you will need customized LOVs to show the value “All” in your LOV (unless this value ‘All’ exists as an entry in your column that the LOV is based upon).


Andreas :de: (BOB member since 2002-06-20)