BusinessObjects Board

Cascading Prompts Problem in BO 6.5 version

I tried that…but still it doesnt like it. It displays all the first names and last names in the report for the choose firstname


mak1600 (BOB member since 2006-02-09)

This is a question that gets asked repeatedly – here’s another one in this same forum, with the same answer. Like a lot of us, you’d like cascading prompts to impose cascading limits – and as the other topic shows, it unfortunately just isn’t possible in current versions.

Dave was suggesting that you actually build your second LOV to display Employee numbers (or some such key) as the first column (so, then, the LOV is actually on Employee numbers, not ON first name), with the First Name as the second column. So – when the user picks an entry, they’re selecting a key value. They just “see” the employee name.


Anita Craig :us: (BOB member since 2002-06-17)

Thanks very much for your reply. I even tried that. When I choose Em-ployee Number along with First Name and full the First Name filed in the report, I get all the employees with that First Name ( It disregards the last name that I had choose earlier in the prompt). I donn know if we can even do this in BO6.5. Anyways I’m still trying to find a work around. I appreciate all your help. I have one more quicky question.
I have a model database and a production database ( seperately).
Do I have to choose the option export LOV’s with universe option?( coz the data is going to change in production) as I have modifed the LOV’s for the above problem.

Thanks very much in advance.


mak1600 (BOB member since 2006-02-09)

Then, you haven’t done what Dave suggested. Based on the behavior, you must still be building the LOV on First Name, and including Employee number as an additional object in the LOV.

You need to build the Lov on Employee number – and include Employee name as a second object in the LOV.


Anita Craig :us: (BOB member since 2002-06-17)

Thanks very much Anita. I dont really know where I’m doing wrong. Anyways I’m attaching a file with the screen shots and a read.txt file that has info regarding the screen shots. I have got it to detail level. I would really really appreciate if you can take a look at this and let me know where I’m doing wrong.

Thank You.
pics.zip (206.0 KB)


mak1600 (BOB member since 2006-02-09)

That wasn’t what I expected at all. I was expecting that you were building LOV queries, as covered in this entry from our Designer FAQ! Is there a reason you’re using “prompt” rather than picking from a LOV?

If someone else has done something like this with Prompts instead of LOVs – and has an idea of whether he can even do this with “Prompts” – please chime in!


Anita Craig :us: (BOB member since 2002-06-17)

The final LOV has to be based on employee number, not first name. You can display the first name, but you have to pick the number. That is what the query condition has to be on, otherwise you don’t have a unique value, and you get everyone with the same first name.


Dave Rathbun :us: (BOB member since 2002-06-06)

It looks like I get to be the bad guy here. You’re heading down a very dangerous path. BO is not a trivial application. What you’re building is not going to look like what people recommend.


Steve Krandel :us: (BOB member since 2002-06-25)

Hey Dave,
I was not able to achieve what you said. Please take a minute to look at the screen shots that I have attached in my previous message and do let me know where I need to do the change [or] please give me example on how to implement this…

Thanks very much in advance…


mak1600 (BOB member since 2006-02-09)

I have given an example. :slight_smile:

Assume 3 objects, customer number, last name, first name

Create a custom LOV for Customer Number in Designer. This LOV should include the Customer Number as the first result object, and the First Name as the second result object. Apply an ascending sort to First Name. Create a condition for this custom LOV that prompts for a last name. Save the LOV.

Save the universe.

Create a query with Customer Number, First Name, and Last Name as output objects. Create a condition on Customer Number, use Equal To, and provide a prompt for the customer number.

Execute the query. When prompted for the customer number, click the Values button. You will be prompted for the Last Name. Review the list, pick a last name. Note that your original LOV now includes a list of customer numbers + first names for those customers that have the last name selected from the prior LOV. Select a customer and execute the query. You will only get the customer for the selected customer number.

That’s essentially what I already posted, as re-described by Anita here:

You can build this quite easily in Island Resorts Marketing as as prototype.


Dave Rathbun :us: (BOB member since 2002-06-06)

Thanks very much Dave. I was successful in displaying the results accordingly. Once again thanks to You and Anita for all the support. Appreciate it.


Was wondering if this is possible in BO.

Instead of choosing from the list of values, can i type in some letters in the name and the lov's only would show the names starting with the letters that I typed in. Like it works for search. For example, If I typ ein Jo and click Values for last names, I should see only the values starting with Jo.
Just wanted to know if this can be achieved in BO.

Once agin kudos to you and Anita.


mak1600 (BOB member since 2006-02-09)

Dave,
I used


Lastname MatchesPattern Prompt ('Please Choose the Last Name') in my Employee Number Edit Lov's Condition.

Would appreciate if you can let me know where I’m going wrong.

Thanks in advance.


mak1600 (BOB member since 2006-02-09)

You need to either train the user to enter the wildcard or append it yourself. To append it yourself, you’ll need to create a predefined condition object in Designer that performs that logic. Something like:

table.last_name like @prompt( ... ) || '%'

You will need to fill in the @prompt syntax properly, of course, you can review the Designer help for that function if you’re not familiar with it. The || is the concatenation for Oracle and some other databases, you’ll need to know what the concatenation operator is for your database.

Once you’ve created that predefined condition object, use it in the LOV rather than a manually created condition. That way you ensure that the user can enter a number of letters and the % will automatically be appended at the end.


Dave Rathbun :us: (BOB member since 2002-06-06)

Dave,
Thanks for your reply. I want to incorporate this feature for what I have been doing.

I have created cascading prompt by following your steps.

In the first step : I pull Emp NUmber, First Name as Query Objects and my Condition would be [ LastName in list Prompt ( “choose last name”)

In second step: I would pull all the query objects and my condition would be Employee NUmber equal to Prompt(‘Choose the first Name’).

When run…

  1. I would be prompted to choose First Name
  2. When I click values I would be prompted to choose last Name
  3. When I choose one and click ok. First Name would be displayed and when I choose the first name , the results would get displayed.

What I intend to do.

In the second step…When prompted to choose Last Name{ when I type in something like ‘Jo’ then I should get the lov’s starting with ‘Jo’ }

Can you please tell me where I need to create a condition object and how this object can be used in my above problem.

Thanks a million for all the help.

Thank you


mak1600 (BOB member since 2006-02-09)

I understood your question. Please tell me what you didn’t understand about my previous response as it will solve your problem.

You cannot do what you want to do on the query panel, you must create a predefined condition in Designer and use it on the LOV for Empoyee Number/First Name.


Dave Rathbun :us: (BOB member since 2002-06-06)

Thanks dave.

I went to the SQL code and made the change. And checked ‘Do not generate SQL before running’. It works…

Is this the only way that this can be achieved?..can this be achived without having to modify the code?



tableName.lastname like @variable('Choose last name') ||'%'


mak1600 (BOB member since 2006-02-09)

Do you not know what predefined conditions (condition objects) are?


Dave Rathbun :us: (BOB member since 2002-06-06)

Dave,
Sorry, I did not realize that Predefined condition is nothing but the one that we use in the where clause of the object. Anyways…thanks very much for your help. I was able to achive that functionality…I donn know if this is the correct way…

In the Last Name object,



In the select clause --@Select(AClass\Last Name)  and in the Where clause -- @Select((AClass\Last Name)  LIKE @Prompt('Please select the Last Name','A','Prompts\EmployeeNumber',mono,free) || '%'

In the Edit Lov Properties of the object “EmployeeNumber”, I pulled the objects Employee Number, First Name and Last Name in the query panel and Nothing in the conditions panel.

In the Business Objects Reporter
I pull FirstName, LastName and Employee Number in the query panel
and EmployeeNumber Equalto Prompt (‘Enter First Name’) in the conditions panel…

When I run the report
I’m prompted for First Name --> click values and am prompted for Last Name
When I type in jo and click OK, I get a list of EmployeeNumber, Firstname, LastName starting with ‘jo’

Its working fine…just was wondering if i mised anything.

Let me know if I followed the right procedure…

Thanks


mak1600 (BOB member since 2006-02-09)

No, it’s not. :slight_smile: Using the “where” clause of an object is very limiting, and rarely recommended. Try doing a search of “predefined condition” in the Designer help, or look at any of the sample universes (eFashion, Island Resorts) as they all include sample predefined condition objects.

See attached screen shot.
predefined_conditions.jpg


Dave Rathbun :us: (BOB member since 2002-06-06)

Thanks Dave.
I really appreciate for all your help.

I was able to achive what I want without the pre-defined condition and using the where condition. All the rows were being returned. I did not know about pre-defined conditions. Any ways it s a new learning and thanks very much for all your help…
really appreciate it.

One last question…

Currently I tried this on my local machine ( stored the universe locally and tested)…Do I need to check the Export with Universe checkbox in Lov’s properties.?..when I export this universe to Model …Actually the list of values differ in Localcopy/ MOdel and Production. So am wondering if I need to check this one.

Thanks


mak1600 (BOB member since 2006-02-09)