using a user response for a variable

[txwylde]

I have a report that I need to add the paid claims and the denied claims to give me a total claims. I have two prompts which a user enters one date and the other to compare a month in 2003 to 2004. Here is what I have for the variable for TOTAL CLAIMS 2003

=((<Number of Denied Claims>) + (<Number of Paid Claims>)) Where (<Hdr Paid Month> = UserResponse("Query 1 with MARS" , "1. Enter First Month:")))

I get a syntax error. Is there any way to create this type of query?
Thanks!
Bill

 

[skrandel]

The WHERE operator requires that you use a literal constant. You cannot ouse a formula. What you can do is build an intermediate variable of some sort.

FLAG
=if (<Hdr Paid Month> = UserResponse("Query 1 with MARS" , "1. Enter First Month:"))) then 1 else 0

Then you can check for the value of FLAG.

This sometimes works. Try it and let us know.


Steve Krandel
VERITAS Software

 

[txwylde]

Ironically I can add a value to my WHERE statement.
=(<Number of Paid Claims>) + (<Number of Denied Claims>) Where (<Hdr Paid Month>= '200310')

The problem with that is that I dont want to have to have the person running the report change the Paid Month every time she runs the report. I remember using the UserResponse before but cant remember for the life of me the format for it.

 

[xlbo]

Userresponse is not the issue here - it is using it WITHIN the where clause that is the issue. You can't. Full Stop

In fact, you can't use ANY aggregate or function in a where clause

As Steve has said, you need to create a flag and then test that flag - as he also says, this sometimes works - depends on the granularity and type of data

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[txwylde]

I understand that I need to create a flag and I did try that but I get an "incorrect data type" when creating the flag.
=if (<Hdr Paid Month> = UserResponse("Query 1 with MARS" , "1. Enter First Month:")) then 1 else 0

I can see where I can add the
=(<Number of Paid Claims>) + (<Number of Denied Claims>) Where (<Hdr Paid Month>= '1'))

to my Total Paid claims for 2003.

Thanks
Bill

 

[xlbo]

You have a text / number or text / date or number / date issue

You will probably need to use one of the conversion functions on one of the variables

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[skrandel]

Look at what you did. You did =if(.....) then 1 else 0

But then you use '1' in your where statement. That simply doesn't make sense.

Build a variable called FLAG. User your if-then-else stuff to assign it 0 or 1.

Then use: =(<Number of Paid Claims>) + (<Number of Denied Claims>) Where (<FLAG>= 1))


Steve Krandel
VERITAS Software

 

[txwylde]

What if I created a Flag for each of the years?
The flag for the first year would be

=UserResponse("Query 1 with MARS" , "1. Enter First Month:")

I could then create the Total Claims for 2003
=(<Number of Paid Claims>) + (<Number of Denied Claims>) Where (<Hdr Paid Month>= (<Flag First Year>)

But then I still get a Syntax error.

 

[skrandel]

You can't put anything but a constant on the right side of a Where.

Your mistake is that you keep comparing to Hdr Paid Month. You need to compare to your flag variable.

Steve Krandel
VERITAS Software

 

[txwylde]

Thanks Steve. You have pointed me in the right direction. My only problem now is getting the incorrect data type situated for the FLAG variable.

It doesnt like:
=if (<Hdr Paid Month> = UserResponse("Query 1 with MARS" , "1. Enter First Month:")) then 1 else 0

I am trying to use the conversion function to change the data type. The <Hdr Paid Month> will be equal to 200310 which I am sure is a date field and I am trying to make my Flag a numeric field and it is not liking that.

 

[skrandel]

UserResponse always returns a character string. You need to convert it a numeric before comparing it.

Steve Krandel
VERITAS Software

 

[txwylde]

Forgive my ignorance, but Should I do that at the variable that has the User Response? When you edit the variable you can not covert the variable to numeric here:
=if (<Hdr Paid Month> = UserResponse("Query 1 with MARS" , "1. Enter First Month:")) then 1 else 0

Where exactly should I be changing the @prompt to a number?
thanks
Bill

 

[xlbo]

use
ToNumber(UserResponse("Query 1 with MARS" , "1. Enter First Month:"))

to convert the userresponse string to a number

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[txwylde]

Thanks!

I tell you.. I used to be a heavy BO user/developer.. then stopped.. then taught it for awhile.. now I am back into development and I have forgotten a TON of stuff.

Thanks for everyone's help.

 

[txwylde]

Ok.. I finally get my Flag converted to a number but the variable

=(<Number of Paid Claims>) + (<Number of Denied Claims>) Where (<Flag> = 1)

nor does

=(<Number of Paid Claims>) + (<Number of Denied Claims>) Where (<Flag> = "1")

Does not work.. I am getting a Syntax error. Any ideas?

 

[txwylde]

Ok.. I got it to work.

If you create the FLAG variable and make it
=(<Hdr Paid Month> = ToNumber(UserResponse("Query 1 with MARS" , "1. Enter First Month:")))


The ToNumber will either make it a 1 or a 0. I also moved it from a measure to a dimension since the <Hdr Paid Month> is a dimension. I moved the FLAG and the Header Paid Month to make sure which year was 1 and which year was 0. I could then make the Total Paid Claims for 2003

=(<Number of Paid Claims>) + (<Number of Denied Claims>) Where (<Flag> = 1)

The other way is I could have just brought in the Header Paid Month in and made it a Master Detail report, but then I couldnt have created my variance variable.

Thanks for everyone's help and advice.
take care
Bill