I have a data set which has transaction details and I need to categorize current service prioritization based on the last exit destination response that i can use in a cross tab table to aggregate clients on their return status.
I have a minus query set up to identify returning clients as opposed to new clients that works beautifully, but i need to make the binding decision structure to identify client 1 as a return from home discharge and peg that status to him or her until he or she has a new exit destination response that will supercede the Exit Destination recorded on 10/1. Same with client 2, If the report ran between the 7/15 re-entry and the 8/30 re-entry, the exit that occured on 7/5 would have categorized him as a Home return, but his 7/31 exit to Rehab categorizes him as an institutional return, so he qualifies for different services.
I have a variable (think it's set as a dimension, at the moment, but I've set it as a detail and a measure, too) which returns exit destination for the second entry that returns a null for every row except the second and it is returning the correct exit destination for the second most recent entry. When I try to filter the cross tab table on this, it returns zero clients (there should be several hundred unique clients with anywhere between 2 and 100s of entries, each.) I also cannot use this variable as a row or column in the cross tab.
I know i'm missing something fairly obvious.
Other information that you need to know, The universes are locked down so I cannot modify them.