Substituting a related value for the user response in a he

building reports desktopintelligence
1

cell

(Using BO 4.1.5.)

In a report I’m developing I use the UserResponse() function in a header cell to print the user’s response to a prompt condition. But what I’d really like to do is this: instead of displaying the actual user response, I’d like to display a related value.

The fields in question are and . stores “codes” such as “10”, “20”, etc., and has associated text descriptions such as “Fall 1999”, “Winter 2000”, etc. The table in question has been denormalized in the extraction process that populates the universe. That is, every row on the table will store both the code and its associated description for the person that row represents, and there is no “lookup” table in this universe associating s to s.

I understand that in general if I want to display the description field, I should qualify and prompt on that description field so that’s what’s returned by the UserResponse() function. But I want to qualify on the code field because there’s an index on it in Oracle, which greatly improves performance.

I know you can do this sort of thing in a master/detail report – that is, build the master on the code but display the description on the report (not that there’s any benefit in doing this). But when it comes to pulling this sort of switch in a header cell (on any non-report-block cell), I get a #MULTIVALUE error. I’ve tried several different formulas, but nothing seems to work. (In general I understand that it works in the master/detail and doesn’t in the header because the report block cell is backed by microcube data, whereas the header cell is not.) Actually, using the Max() function will eliminate the #MULTIVALUE error, but this is not safe to use if the prompt condition will be returning multiple values.

Any ideas on how to do this? (Without having to make any changes to the universe?)

Michael Matola
The University of Michigan

Listserv Archives (BOB member since 2002-06-25)

2

r cell

Michael,

It sounds like you’d like to use the actual indexed code in your where clause to take advantage of the indexed field. I also encountered this with several reports and what I did, was prompt the user for the indexed code, but also provide the description of that code in the List of Values. In your case, you could modify the report so it prompts users for instead of . However, since the users won’t know what “10”, or “20” means, in the List Of Values, you can modify it so that the user sees:


10 Fall 1999
20 Winter 2000

To do that go into Designer, and modify the properties of - right mouse click on , click on the Properties folder tab, click on the Edit… button, and add to the Result Object. Make sure you also click Export to Universe so other users will get the new properties you’ve added to the object.

As long as your users don’t object to seeing 10 in the prompted box, this may work for you. Once they click on the values button when supplying values to the prompts, they will see the description alongside with the id. They are really selecting the 10 or 20, since that is used in your condition, but they will see the description of the code field when they make their selection.

This way you won’t have to use the UserResponse function as long as you also bring the into the result objects of your query. I don’t think this helps you with the MULTIVALUE problem, though, but it will optimize the query.

Hope this helps,

Irene Walsh
QuadraMed Corporation

Listserv Archives (BOB member since 2002-06-25)