User prompt to act as filters and a calculated filter

[Former Member]

Hi,

I need to get a date as user input , then display the data from that day to last 7 days. I need to do this at report level only and not create a filter at universe level. Could some suggest the exact variable/formula/steps? Thanks in advance for all help!

[amitrathi239]

Hi,

At webi level first capture the user entered date by userresponse function.

Like Var date=useresponse("Prompt text")

Convert Var date variable to date type.

Var=todate([Var date];"dd/MM/yyyy") According to date format coming change the format in the todate funciton.

Var pre week=Relativedate([Var];-7) with this you get the date-7.

Use these two variable according to your use.

Amit

[Former Member]

Hi Amit,

Thanks for your response.

How do I add user enter date so that data doesn't get filtered by that date only.

Right now I have added date field as a query filter user prompt, so the data is getting filtered only for that date.

[jeff_crisp]

Did you happen to change your filter type to "Less Than" or "Less Than or Equal To"

[Former Member]

Hi Jeff,

Thanks for your response. No; I didn't. Now ,when I have changed the filter condition to "Less Than or Equal To" in Query filter for the date prompt , My Rolling7variable is returning 0.

Here are the variables I have created in report-

Para

Formula :

=ToDate(UserResponse("Enter value(s) for Stat Date");"MM/dd/yyyy hh:mm:ss A"

End Date

Formula:

=RelativeDate([Para];-7)

Rolling7

Formula:

=[Stat Date] Between ([Para];[End Date])

Please advise.

[jeff_crisp]

Here is what I did (some of this appears like you already completed and have working):

Created the query filter:

I ran the query and entered "11/12/2015" when prompted for a date.

Then I created a variable like this:

= RelativeDate(ToDate(UserResponse("psEnter Date");"");-7)

This part may not be needed, but I then created a 2nd variable:

=If [ID Expiration Date] >[Varible] Then 1 Else 0

Lastly created a report level filer:

After clicking OK on your report level filter, then you should have your desired report.

[Former Member]

Hi Jeff,

Thanks so much for your response. The above works perfect for the  table but shows error when applied to chart. Here is the error message-

Formula Evaluation-Error in dataset value#unavaliable.Check formula definition in this dataset context.

[jeff_crisp]

It seemed to work for me.  What type of chart are you using?  What version of BO are you using?

I am running 4.1 SP6.

[Former Member]

I am using bar chart and running on 4.1 Support Pack 5 Patch 4