Hi,
We would like to be able to prompt the user on the company name so they will have a meaningful list of values. However, when the SQL is generated we would like it to actually build the where clause with the company number so the index will be used. Is this possible?
Thanks,
Cori Griswold
Listserv Archives (BOB member since 2002-06-25)
Hi, Corinne!
You can set up a custom list of values for the company number which would include the company name. Then, when users click on the values button on the prompt, they would be able to choose a company name and BO would pass the company number to the query.
Open up Designer and bring up the universe. Double-click on the company number object. Choose the Properties tab in the Edit Properties dialog. Click on the Edit button in the lower right-hand corner of the Properties tab. This brings up a Query Panel. From here, you can double-click (or drag) the Company Name into the query. Choose Run. You now have a list of values with the Company Name and the associated Company number. You will now be at the Edit Properties dialog box. Be sure to check the Export with Universe checkbox so that the LOV will be available to all users of the universe.
Hope that helps,
Michael
Listserv Archives (BOB member since 2002-06-25)
Hi Cori,
The best way I know of to do this is to have your list of values show both the company ID and the company name. They would have to choose the ID; however, the name would be displayed to make it easier to identify.
When you build your list of values simply show both objects. You can also sort the list by company name if you desire.
Bill
Listserv Archives (BOB member since 2002-06-25)
Michael,
Thanks!!! This does exactly what we want until one point…when we display the user response on the report it will show the numbers rather than the text. Since the companies could change, we would like to avoid having to hard code IF statements for each possible combination of responses. Is there a way around this?
–Cori
<<<Open up Designer and bring up the universe. Double-click on the company number object. Choose the Properties tab in the Edit Properties dialog. Click on the Edit button in the lower right-hand corner of the Properties tab. This brings up a Query Panel. From here, you can double-click (or drag) the Company Name into the query. Choose Run. You now have a list of values with the Company Name and the associated Company number. You will now be at the Edit Properties dialog box. Be sure to check the Export with Universe checkbox so that the LOV will be available to all users of the universe.>>>>>
Listserv Archives (BOB member since 2002-06-25)
Thanks!!! This does exactly what we want until one point…when we display the user response on the report it will show the numbers rather than the text. Since the companies could change, we would like to avoid having to hard code IF statements for each possible combination of responses. Is there a way around this?
–Cori
Simply have the user select both Company Name and Company Number as Result Objects in the query panel, and build the custom LOV into a pre-defined condition – this will see to it that you’re using the index on Company Number whilst still returning the Names (Company Numbers can be set to ‘hidden’ in the report)
HTH,
mirko
Listserv Archives (BOB member since 2002-06-25)
Thanks!!! This does exactly what we want until one point…when we display the user response on the report it will show the numbers rather than the text. Since the companies could change, we would like to avoid having to hard code IF statements for each possible combination of responses. Is there a way around this?
–Cori
Simply have the user select both Company Name and Company Number as
Result
Objects in the query panel, and build the custom LOV into a pre-defined condition – this will see to it that you’re using the index on Company Number whilst still returning the Names (Company Numbers can be set to ‘hidden’ in the report)
HTH,
mirko
Let me expand my explanation of the problem…We are able to get the company name displayed as a result object. The problem we are trying to avoid occurs when I insert another cell in the heading to use the “user response” function to display back to the user what was selected at the prompt. Since the user actually selected the company numbers when prompting, they are returned to the function. I am trying to avoid having to do some sort of huge IF statement that outlines all of the possible company numbers and displays the names because companies can always be added. Any ideas?
Thanks once again,
Cori Griswold
Listserv Archives (BOB member since 2002-06-25)
Hi, Cori.
Here’s an idea…
You already have your object with LOV sorted by . If you don’t already have it, create another object, , with a standard LOV.
Then create another object, say . The SELECT equivalent is a constant: ‘hide’ The WHERE equivalent is something like:
@Prompt(‘Enter Company Name’,‘a’,‘Your Class\Your Object’,) =
@Prompt(‘Enter Company Name’,‘a’,‘Your Class\Your Object’,) You do not have to associate a Table to this object. It will work with any combination of objects.
When you add this new object to your query, the user will be prompted for a Company Name, and will be offered the LOV for , but nothing will actually run on the database. This object will simply return the constant value ‘hide.’ You can then delete this column from your report. What you gain is the user entering the Company Name into a prompt, thereby making the value available for the UserResponse function.
It actually works pretty well, the only catch is that the user is required to 1) select the Company ID as usual, but then 2) select the matching Company Name. Since there is no actually check happening on the database, they must ensure they enter matching values. Otherwise the results of the UserResponse functions may give contradictory results! It may help to make the LOV for the reverse of . In other words, sort the Company Names by Company IDs, such that the user selects the Name that matches the ID previously selected.
I hope this makes sense.
Good luck!
-Luis Gonzalez
Let me expand my explanation of the problem…We are able to get the company name displayed as a result object. The problem we are trying to avoid occurs when I insert another cell in the heading to use the “user response” function to display back to the user what was selected at the prompt. Since the user actually selected the company numbers when prompting, they are returned to the function. I am trying to avoid having to do some sort of huge IF statement that outlines all of the possible company numbers and displays the names because companies can always be added. Any ideas?
Listserv Archives (BOB member since 2002-06-25)