How to link input box with predefined values to an expression in a pivot table?

[#1]

A sample file is attached but I am not sure what I need to do...help me please??!!???!!!?

1. I need to know how to link an input box with predefined values (All; 0%; 50%; 80%; 100% or more) to an expression (% Expended) in a pivot table

All - shows all records regardless of % Expended

0% - shows records that have % Expended equal to 0% or is null

50% - shows records that have % Expended equal to 50% or more

80% - shows records that have % Expended equal to 80% or more

100% or more - shows records that have % Expended equal to 100% or more

2. Last thing I need to do is clear the input box selection and make it default back to All.

Any guidance or assistance would be greatly appreciated!!!!

[#2]

Yes, I understand but if I do it that way then I will get all of the % Expended Values and I don't want that.

I need to be able to group the values as I mentioned before.  I don't want the user to select from any value.  I want to categorize them.

If I misunderstood what you were asking then I apologize.

[fosuzuki]

Hi,

check the attached qvw.

This solution has one small problem when you collapse the pivot table dimensions, the aggregated %Expended value will not work. It is supposed to work only when the table is fully expanded...

I've used the IntervalMatch function which can be kinda hard to understand for newbies. Let me know if you don't get it ...

Hope this helps

Fernando

[#4]

After looking back over your last post, I think I may understand.  The second option may work for me.  I am working on it now so I will get back with you if it works or doesn't.

Thanks!!!!

[#5]

I didn't see an attachment

[sebastiandperei]

If it has resolved your question, why don't mark as "resolved"?

[#7]

I have marked your answer as helpful, but I still wanted to see if this can be done through the input box?

Thank you for your help I really appreciate it!!!!!

[sebastiandperei]

Did you put the script that I sent?

With this you will not need the input box nor the variable. Just select the field Show.

I've attached the solution. In this, you have two ways of filter the table:

- Grouping Show: Using the table Show that was created in script like I sent you

- Expended values: You can choose witch values you want to show.

Im really sorry if my english is not comprensible, Im from Argentina and only "practice" my english in this page!!!

[#9]

Yes, I was able to create the list box with no issues just like you did.  Thank you.

However, even though I don't need an input box, I wanted it that way. The final report will contain multiple variables in an input box and I was going to add this option there.

Can the Show field be linked to an input box? I will use your suggestion if it is not possible.

[sebastiandperei]

Yes.

If you want, use the same variable: Variable1

Check that the input rules has the same values that the field Show.

Go to Document Properties, Triggers, Variable event triggers, find the Variable1 and select it.

Then, click OnChange Add Actions.

Now, Add, and Select in Field.

Field: Show

Search string: =$(Variable1)

Then, when you change the value of Variable1, their value will be selected in Show field.

[#11]

I created the trigger for the variable, but when I select the value from the input box it is not working...

I entered the following under Listed Values:

All;0%;20%;50%;80%

When I went back to the properties, it changed to

All;0;0,2;0,5;0,8

What am I doing wrong?