Formula with User Response

building reports desktopintelligence
1

Sometimes I feel like I’m beating my head against the wall when talking to tech support.

We are creating a report to compares the a students grade point average for two different terms. The user is prompted for which terms they want to compare when they run the query.

The report will have student name, term1, term2 and other things. Under term 1 is a formula that displays the the credits taken as result of the value they entered for term 1

It looks like :

= Where (=UserResponse (“Query 1 with SAO3”, “Please Enter the First Term”))

I’ve also tried
= Where (=UserResponse(DataProvider() ,“Please Enter the First Term”))

Tech support provides the textbook syntax for the UserResponse function and hasn’t provided an example of it working in with a Where clause. I would be happy to find out I’m making some simple user error. Appreciate any help or insight

Stacey Donahue
stacey@umich.edu

= Where (

We have tried it with diff parameters quoted and unquoted, differing amounts of parentheses, all possible combinations ad infinitum and we always get a syntax error.

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

2

In a message dated 00-03-20 10:09:47 EST, you write:

Sometimes I feel like I’m beating my head against the wall when talking to
tech support.

That’s what we’re here for! :slight_smile:

We are creating a report to compares the a students grade point average for two different terms. The user is prompted for which terms they want to compare when they run the query.

The report will have student name, term1, term2 and other things. Under
term
1 is a formula that displays the the credits taken as result of the value they entered for term 1

It looks like :

= Where (=UserResponse (“Query 1 with SAO3”, “Please Enter the First Term”))

I’ve also tried
= Where (=UserResponse(DataProvider() ,“Please Enter the First Term”))

Tech support provides the textbook syntax for the UserResponse function and hasn’t provided an example of it working in with a Where clause. I would
be
happy to find out I’m making some simple user error. Appreciate any help
or
insight

I’ve posted this before, but it’s been a few months so I guess it’s time for it again. You can’t use a variable or user response in a Sum… Where… expression. You can ONLY use a value, and you can ONLY use an equality check (no greater than, less than, etc.). So, how do you get around this? By working in steps.

First, create a variable with two possible results based on your user response. I use 1 (one) and 0 (zero), but the two values could easily be “Yes” or “No”, “True” or “False”, or even “Fred” or “Wilma” depending on your humor at the moment. Here’s how it works:

Variable Name: Which Term
Variable Formula:
=If = UserResponse(DataProvider(),“Please Enter the First Term”) Then 1 Else 0

What do you have? A variable that is either 1 or 0 depending on whether the value of matches the user response or not. Then you have your next variable, which is where you do the actual sum:

=Sum Where ( = 1)

See how it works? You have a test with a constant (1 or 0) instead of a variable. That’s the trick! You can use this trick to use the Sum… Where… clause with any expression, including user prompt responses, greater than or less than comparisons, etc.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

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

3

Hi Stacey,
Hate to tell you this but you can not do that with the “where” formula. Everything needs to be hard-coded after the where. If I am wrong I would love to know how to do otherwise!
:slight_smile:
I know how you feel about the tech support: they mean well but most do not have real-life experience with the tool.
Simon

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