|
Return to Newsletter Contents...
Choices, Choices, Choices?
By Aaron Zechman, Senior Consultant, BOCP – CR, BOCP – WebI. MCSE
“There are two primary choices in life: to accept conditions as they exist, or accept the responsibilities for
changing them.” – Denis Waitley
The choices that we allow our users to make inside WebIntelligence happen through prompting inside the query
filter pane. Inside this query filter pane, we sometimes want to display the choices of the user on the report itself.
To do this we use the UserResponse function; however, there are some problems with using this at times with date fields
and now being able to have optional prompts. First, we will go over the UserResponse function and then learn how to
handle some of the other situations that may occur during report writing.
The UserResponse displays a choice or choices that a user has chosen for a prompt. The syntax for the
UserResponse is:
string UserResponse(object
data_provider; string
prompt_text; Index)
Now at times the data_provider is going to be optional and not used because a lot of times we only have one
query that we are dealing with in our WebIntelligence document. If we have more than one then this may come into play.
We are going to pretend we are only dealing with one query.
To get a user’s answer to display on the report, the filter pane has a lot to do with it. Let us say we took
an object called “name” and made it a prompt. It would then say “Enter a user name:” when this is done. This prompting
text is important when using the UserResponse function. We could then put an empty cell on our report and in the
Formula Toolbar type the following:
=UserResponse(“Enter a user name:”)
This has to be exactly how it is in the filter pane in capitalization, spacing, etc. otherwise it will not
work. This should display the name of the person that the user selected.
Now what happens if it is not just one name that you choose, so instead of equality you use the “in list”
operator? It will display the names as “Name1; Name2; Name3”. Now if colons between your choices is acceptable to your
organization your job is finished; however, to most people this is not acceptable. We now would have to combine
functions in order to remove the semicolons and bringing it to a more display friendly format. We need to use the
replace function as well.
=Replace(UserResponse(“Enter a user name:”);”;”;”, “)
Now this is a lot of semicolons in a row so you need to be careful; however, the report should now show all items
chosen separated by a comma and a space.
This is not the only time we have problems though. What if we are working with a date that our user enters? So
somewhere we have “Enter a date:” as a prompting text. If we use the same user response we are going to get a date back
along with a time on our report.
MM/DD/YYYY HH:MM:SS
A lot of times we really don’t care about the time being displayed on the report and all we want is the date to
display. To get around this we would need to use the ToDate and FormatDate functions as well. It should look something
like the following:
=FormatDate(ToDate(UserResponse(“Enter a date:”); “M/d/yyyy hh:mm:ss A”); “MM/dd/yyyy”)
Yes, I know that’s a lot to get the response display as just a date without time but this will help you out from
an end user acceptance perspective.
Lastly, we need to go over how to display an optional parameter of a report. This ultimately removes this from
the WHERE clause and brings back all values if no value is selected.
IF UserResponse(“Enter a date:”) = “” then
“ALL”
Else
UserResponse(“Enter a date:”)
So now you have several different uses of the
UserResponse function inside WebIntelligence. Please choose wisely which you use. |
