I think what you are suggesting is exactly what Stefan was suggesting. But you can't add the data in the section access, it has to be in the section application. if you want to keep state values that have existing orders, you will have to create dummy values for whatever your reduction field is to keep them in.
im assuming that I wouldnt have to create dummy orders (I can't do that.) I have a table that explicitly has states and one that has orders. So the way I set up the reduction table (above) wouldn't it reduce the orders for Joe, but still let him see the explicitly authorized states even if the are no orders for those?
section access with data reduction is adding a 'reduce to possible values' after applying selections based on the reduction field values for the logged in user.
So, to develop a solution to your requirement, forget about the section access table for now.
You need to develop a data model, that fulfills your requirement (limiting the possible record set to only values the user is allowed to see, but without removing dimensional values he needs to see) just by making selections in fields of your data model.
If you've created such a data model, creating the section access table should be easy then.
Hence you need to create a data model where you can limit - for any given user scenario - the order number by making selections. This selection shall not limit the possible state values.
I believe my suggested data model fulfills your requirement. You may have some additional requirements or you are using the order number field in some expressions where additional DUMMY values are unwanted (well, it looks like a key field, so you probably shouldn't do this anyway).
You can think about creating an additional field in your order table to handle this:
REDUCTION, Order Number, State
1, 1, NY
2, 2 , NY
3, 3, FL
4, 4 , OH
DUMMY, , NY
If your user should only see order number 3 and 4, create a section access table that authorizes him to REDUCTION field values, 3,4, DUMMY.
Now he sees only Order number 3 and 4, but still all states values.
You can automate the creation of the additional DUMMY values by concatenating your distinct State values together with a constant DUMMY value to Orders table:
LOAD 'DUMMY' as REDUCTION, State
Hope this helps,